QUICK TIP: Lookup Values Populating with Salesforce 13 Digit Id's

[Anonymous]

Have you ever had your Marketo Lookup field populate with these random 13 digit codes instead of the values you want?

If you have, its more than likely because your lookup field on a Lead, Contact, Account, and/or Object is referencing another Salesforce object in the system.  When Marketo fetches this data for synch, Salesforce sends the Record Type ID - NOT the Textual Value it represents. 

Luckily there is a quick fix to get the right values you want to see in Marketo!

Go into your Salesforce Instance (MUST HAVE ADMIN PRIVILEGES):
>>Setup
>>Customize (Create if its on a Custom Object)
>>Fields
>>Create a NEW CUSTOM FIELD

Step 1. Choose the Field Type
>>Formula

Step 2. Choose the Output Type:
>>Text
>>Name the field something consistent like "Citizenship Text" as we WIlLL be using this for Segmentation later

Step 3. Enter Formula
What we want to do is run a relationship query on that object and ask for the Name instead of the ID.

In the formula section put the field that's causing you problems (for example in the above I have Citizenship):
"Citizenship__c"

Now CHANGE the end of this field to represent the query (c => "r.Name":
"Citizenship__r.Name" 

>>Select Next

Step 4. Establish Field Level Security
>>You'll want to remove visibility from ALL but System Admin solely for testing purposes
>>Once you have verified the values are now shown you can remove this field from your System Admin Page Layout as well. 

The data should synch to Marketo over the next synch periods (depending on how much data there is) and now you can segment by the true values!

[Anonymous]

Michael,

This is a great workaround and thanks so much for sharing it! You do however want to think about the limitations of a formula field and how Marketo sees the data.

I put together an article that explains this, it is not for the faint of heart and honestly a bit boring but here it is:

How does Marketo see and pull data from Salesforce Formula Fields?

Thank you for providing such valuable information to the community!

[Anonymous · Accepted solution]

Thanks Reza,

I actually should have added a caveat in there. 

With the above solution, you should not be affected by SystemModStamp or "Last Updated" scenarios in Salesforce as it will be a 1:1 change with another field (i.e. when someone's citizenship changes from 0123456789ABC to 0123456789DEF in Salesforce, the citizenship code field will change from American to Australian).  BUT it is good to note the final area of that solution in that YOU CANNOT EDIT (or Change Data Value) THESE FIELDS:

NOTE: You can only use data from a formula field in Marketo to segment data and filter. If you try to do a change data value, Marketo will accept the change, tries to sync it to Salesforce and fails to update there. Eventually the Salesforce calculated value will come back into Marketo.

[Anonymous]

Marketo also has a formula field that will allow you to do a similar process to Michael's tip without creating the field in SFDC. You can create the rules in Marketo. 

You will need an SFDC admin to provide you with a list of with IDs correspond to which text name.

[Anonymous]

Marketo also has a formula field that will allow you to do a similar process to Michael's tip without creating the field in SFDC. You can create the rules in Marketo.

Admin -> Field management -> New Field -> Formula

You will need an SFDC admin to provide you with a list of with IDs correspond to which text name.

[Anonymous]

As a SFDC admin, if someone were to ask me to create a formula field for each lookup, I would stare incredulously and wonder why they were using Marketo, and politely decline to muck up the database.  

I'm happy that Cathal M has let us know that Marketo can do the same thing  -- this is a much better solution than adding unnecessary fields to SFDC.

[Anonymous]

Hi Julie,

Unfortunately it's a bit unrealistic to put a workflow to replicate this formula in most cases. The lookup is related to a standard or custom object, hence why it shows a SFDC related ID. In most cases organizations would have multiple objects and perpetually grow. The workflow in Marketo is not a scalable solution.

[Anonymous]

Hi Michael,

We have trying to create the relationship query inside Salesforce you described above and are running into some issues. Below is the New Custom Field we attempted to create. Can you please tell us what we're doing wrong?

Appreciate it!

Jessica

[Anonymous]

Hi Jessica,

What is the relationship?  

You actually might be able to find it in the Advanced Formula.

[Anonymous]

Hi Datarati,

The "BDR Assigned" on the account level is showing up at their 13 Digit ID's instead of their names.