Power of Upsert and External IDs (Part 2)

In Part 1 I gave an overview of what the upsert operation was and how it compares to insert and update. I also described what an external id field is and why it makes sense to store common unique identifiers from external systems in Salesforce to simplify data integrations so you don’t have to know the Salesforce IDs to update the records.

In Part 2 I will show you how we can extend our use case of external ids to assigning lookup field values on the records we are upserting. For example, when upserting contacts we can also assign the accounts by referencing an external id field on the account rather than the Salesforce record id. No more Excel vlookup necessary!

I will show you how to use the power of upsert and external ids to assign lookup field values three different ways:

1. Salesforce’s Data Loader
2. MuleSoft’s dataloader.io
3. Apex

The same concepts shown here also apply to any other tool you may be using to perform upserts to Salesforce, such as directly with their SOAP API, REST API, or other products like Talend, MuleSoft, Jitterbit, Informatica, etc.

The Data

In our example let’s assume we are loading contacts and assigning their account. We have an external id text field defined on the Account object aptly named “External ID” (External_ID__c). We don’t know the account’s Salesforce ids but we do know their external system id. Our data may look like this .csv file:

Salesforce’s Data Loader

Launch the program then click the Upsert button then choose the object whose records you’re loading. In this example, we’re upserting contacts.

On the next screen, the program will ask which field on the contact to use as the identifier. You can choose between the standard id field or any external id field that may exist on the contact object. In this example we will simply choose ID field knowing that our upsert operation will insert a new contact record.

On the next screen, the program will walk through each lookup field defined on the contact object and ask which identifier on those objects you have in your .csv file to map with. In this example, we’re assigning the account lookup field by referencing the Account.External_ID__c field.

On the field mapping screen, note that normally you would assign a contact’s account by mapping the AccountId to a column in your file that included actual Salesforce account ids. However, since we don’t have the actual Salesforce account id we will choose and drag the Account:External_ID__c field reference to map to our file data. When the upsert operation is performed, Salesforce will automatically look up the related account id by matching on a record whose External ID field equals the value in your file. The Excel VLOOKUP magic is done automatically for you by Salesforce!

MuleSoft’s dataloader.io

Visit https://dataloader.io and login with your Salesforce account. Once logged in, click on New Task then choose Import.

Choose the upsert operation and the object we’re loading records to.

On the field mapping page, select Account ID (this is the Contact object’s AccountId field) as the field that our Account.External_ID__c column maps to then check the Lookup via option and select the External ID field (or whatever you’ve named yours). I also recommend choosing the option to Mark record with an error if more than one match is found to avoid unintended data assignments.

Apex

To perform the upsert lookup field assignment in Apex we need to use a special method on the SObject class putSObject(..). This method takes two parameters. The first lets us specify the relationship field on the record we’re upserting and the second specifies an SObject with one of its external id fields populated so Salesforce can do the lookup matching. Note that for custom lookup fields the relationship name usually ends with __r suffix, but if you’re referencing a standard field then there is no suffix.

Thanks for reading! I hope this two part series has inspired you to begin using the Upsert operation and External IDs in your data loading endeavors.