QUICK TIP: Lookup Values Populating with Salesforce 13 Digit Id's | Community
Skip to main content
A
Anonymous
June 23, 2011
Solved

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

  • June 23, 2011
  • 14 replies
  • 6402 views

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!



    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by
    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.

    14 replies

    Show previous replies
    A
    Anonymous
    February 15, 2013
    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.


    A
    Anonymous
    May 19, 2016

    Great tip!

    A
    Anonymous
    June 14, 2017

    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!!

    Heloise_Briere1
    Heloise_Briere1
    Level 2
    February 22, 2018

    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

    Heloise Briere
    Terms & ConditionsAccessibility statement

    Loading footer...