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!

[Heloise_Briere1]

We've created a bunch of custom field in Salesforce with lookup, and I've applied this formula. It works perfectly in Salesforce, but when I try to pull the text (not the Salesforce ID) into Marketo, it is just...empty. No data. Marketo has a "read access" to the specific field I've created (Brand X Lead Owner Text), the field shows up in Marketo has a string, but no text or information come through. I've been looking for a solution for days... Please help! Thanks in advance,

Heloise

[Anonymous]

Is it possible to do the reverse on this... well kinda...

I have a LEAD to ACCOUNT relationship lookup field, called "reseller account" on the lead record, that associates the lead to an ACCOUNT.

If I change the value of "reseller account" to accountXYZ in Marketo, even if accountXYZ exists in Salesforce, I get a MALFORMED_ID: error, because Salesforce wants an account ID. Therefore, the "reseller account" on the lead in SF does not update. I can update the NAME of the "reseller account" because I know that, however, I don't know what the account ID is in Marketo.

If I could make this work I could automate LEAD to ACCOUNT associations with our resellers, and saves hours of time every week.

Is this possible?

P.S. I'll love you forever!!

[Anonymous]

Great tip!

[Anonymous]

Hi Jessica,

There has actually been a bit of development on this and you can potentially do it a pretty straight forward as well.

Below is an example of an asset I had where it looks up a product.  Now the product is an object look up so it does have a product ID....in fact that is the value directly below "product" in the second window.  But when you click on the area above which drills into that look relationship, you can simply select Product Name.  This will be the text version of that look up you want.

So my assumption is that your BDR means Business Development Rep and its a User Lookup.

I also assume you have a secondary field that maybe is like "BDR Text" to capture the value that is not the 13 digit code so you can segment and its a formula field that uses "Text" as the formula.  Make sure that you mark blanks as blanks (null) and not zeroes.

 So what you're going to do is go into the formula and you should see a BDR and then a BDR> next to it.  Click on the one with the ">".  Now if you are in fact using user you won't be able to get "Full Name", but you can do a simple formula to get their first name and last name.  

Try this:
"User__r.FirstName+" "+User__r.LastName"  (Assuming that it is a user lookup...otherwise just use the name equivalent fields you have)

Give that a go and let me know you it works.

[Anonymous]

Hi Jessica,
 

Whoops...answered from my wrong account ;).


Ok...so the BDR is on the account level, and is it looking up a user I'd imagine?  Or a custom object maybe?

 

[Anonymous]

Hi Datarati,

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

[Anonymous]

Hi Jessica,

What is the relationship?  

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

[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 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]

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]

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]

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 · 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]

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!