Power of Upsert and External IDs (Part 1)

You’re probably already familiar with insert and update operations. Insert always creates new records whereas update makes changes to existing records that you already know their Salesforce record id.

But what if you don’t know the Salesforce record id of the records you want to update?

Hard Solution

Export the Salesforce records then perform some cross reference on the data to identify matches between your external system and Salesforce then perform inserts on un-matched records and updates on matched records. Or to maintain the Salesforce record ids in your external system, which may be impossible depending on your access and control of the external system.

Easy Solution

Use the upsert operation and an external id field on your Salesforce object.

---

External ID Fields

When creating new text, number, or email fields you may have seen an option to mark it as an External ID. This allows you to store unique record ids from external systems, such as another database you’re importing data from to Salesforce. This also enables you to use the upsert operation so that you can specify which other ID field (other than Salesforce ID field) you want to match on. For deep dive on External IDs, which is helpful if pursuing the Data Integration Architect certification, check out Shannon Howe’s Why Should You Care About External IDs?.

Upsert Operation

The upsert operation intelligently performs an insert or update of a record by matching against a unique identifier (such as the ID field or other External ID field on the object). If no such record already exists that matches by the specified identifier field then a new record is inserted, else the matched record is updated. This saves you time and effort from doing the checks beforehand (e.g. running a Salesforce report to export data then using Excel VLOOKUP to find the corresponding record id).

Please note that if two or more records in Salesforce match by the chosen external id field then the upsert operation fails. That’s why it’s also recommended that any external id field you intend to use for this purpose is also marked as unique.

Let’s Visualize Some Scenarios

The below diagram displays three scenarios of how data could be matched and imported from an External System to Salesforce.

In the first scenario neither system knows the unique identifier of the other, so only inserts could be performed to import data to Salesforce.

In the second scenario the external system knows the unique identifier of Salesforce records, so it can perform inserts and updates. It could also perform upserts matching on the ID field. However, you have to now maintain the Salesforce record id in the external system which is bothersome, or impossible depending on your accesss and control of the external system.

In the third scenario the External System has no knowledge of Salesforce but Salesforce knows the External System’s record ids. This allows data integrations from the External System to Salesforce to use the upsert operation and match on the common known identifier. If the upsert operation does an insert then the Salesforce record will now have the External System’s id. The next time the upsert operation is performed a match will occur and the record will be updated. And no change to the data integration process was necessary!

Thanks for reading Part 1 of the Power of Upsert and External IDs. Check out the below slide decks to see demos how to perform upserts using Data Import Wizard (Summer ’16 or later), Data Loader, dataloader.io, and Apex.

In Part 2 I demonstrate how you can use Upsert and External IDs to assign lookup field relationships to records when you don’t know the lookup record’s Salesforce ID.

My Dreamforce 2016 Presentation

Other Tutorials

The following slide decks give overview how to use upsert with different tools: Import Wizard, Data Loader, dataloader.io, and Apex