I’m trying to write a rule to add the status of a Success Plan to a pick list field in Salesforce. Keep getting an error when I run the rule. 

I am trying to merge the data between the Email Log V2 object and the AO Participant Custom Fields object within a Data Designer.  The Email Log V2 object does not include the AO Participant ID field or linking and I cannot match on email address because recipients could have received the email multiple times.  

I was able to find a set of ID fields with matching values between objects but one field has the numeric format and the other is text.  I attempted to transform the field from numeric to text and vice versa with no luck.   

Field:  Email Log V2 > Record External Id

Field: AO Participant Custom Fields > Gs Participant ID > Participant Id 

I’m looking to take an object that has more than 1 row per Company and transpose it to yield 1 row per Company. My source object is Unified Scorecard Fact. I’ve started the work of transforming then merging back in to the original dataset, but want to chat through my configuration and get some advice in case I’m going about this the wrong way.

Hi Scott - 

I’ve got a question about how Scorecard timeline entries work, specifically: do we have the ability to control which fields appear in the template for those entries? I promise my question will make at least marginally more sense when I share my use case 

Hello @sarahmiracle -- didn’t see you on the session yesterday but wanted to ask a quick question that may make your life easier.

Is it critical that the report show the current score at the moment that the report is being viewed?  Or is the score as of yesterday OK?

Asking because using the Account Scorecard History object is the easy answer here -- filter for Snapshot Date greater than or equal to Current Week, because the current week’s snapshot gets updated every night until the first day of a new week, when it starts a new snapshot with a new snapshot date (the first day of the current week).

Much easier object to report on and typically is current enough for most use cases/viewers.

Another option is the Scorecard Mass Edit Report -- you don’t have to do pivoting to run that (however, I’m guessing this report isn’t being run at the Company level so that may be out).

Hope that gives you a good option -- otherwise, jump on next Thursday’s session and we can look at the pivot!

Scott

Hello @jenniferpa - my 15-minute confirmation of a solution for you turned into a 1-hr deep dive with some internal follow-up.

HOWEVER - there is a current workaround even before a Text version of the Participant ID is added to the AO Particpants object.

After fetching the “External Record ID” in your “Email Logs V2” step, add a transformation step (screenshots below).

You can use a formula field to convert this String to a Number with the “To Number” function in the Formula Field builder.  THEN you can use that Number version as the Merge field to match up to the Number field that is the Participant ID in AO Participants.

I’ve tested it - works great!  Drop a line back with questions!

Thanks, @sdrostgainsightcom.  Sorry to send you down the rabbit hole but I am happy to report that the formula field worked!  Thank you! 

Can you explain why we use the -1 as the Default?  I had previously tried that formula field with the default value of 0 and it did not work; just want to understand the logic behind the field.  Thanks again!

Glad it worked @jenniferpa !

A default of 0 should work as well as far as I know-- so I can’t speak to what the issue may have been there.  However, I used -1 because it is a slam-dunk way of showing that there wasn’t a numeric value in the source text field (External Record ID) since we know that there won’t be any negative “numbers” in that string field.

Were you possibly trying to use the “ToString” function on the numeric ID from Participants?  There are some issue with going in that direction changing a Number to a String, so I wouldn’t be surprised if entering a number as the default, or even just 0, would have been an issue.

I tried both the ToString and the ToNumeric options with no luck but I am sure I was doing something slightly different than you recommended.   I like your reason for using the negative number as the default value and will use that going forward instead of a zero.  Thanks again!