I love upserts. They are a combination of Inserts and Updates. The key to using Upsert is to nominate a field that contains an unique identifier for the record. While importing, if a record already exists with the same identifier, the record is Updated. If no such record exists, a new record is Inserted.
A good identifier is, of course, the Salesforce ID that is assigned to each record. However, there’s two significant problems in using the Salesforce ID:
- It varies across instances (eg sandbox, production, developer account), which means that you can’t import the same IDs in different systems
- It is generated by the Force.com platform — you can’t create it yourself
The Good News
Enter the External ID. This is a flag that can be set on Custom Fields that identifies a field as being an Unique Identifer, and allows that field to be used in Upserts.
My favourite use for an External ID is in storing the unique identifier from an External System (eg a customer database). If I load my customers into Salesforce.com, I can then use Upsert to reload them next time without creating duplicate records. The external system doesn’t even need to know that Salesforce exists, and certainly doesn’t have to store the Salesforce IDs.
The Bad News
I load a lot of customer information, and the best identifier for customers is their Email Address. Unfortunately, the Email field on Contact records is a Standard field and only Custom fields can be defined as an External ID.
To get around this limitation, I’ve created my own Email field (
EmailAddr__c) that I define as an External ID. However, this leads to further problems:
- Salesforce.com uses the normal “Email” field for sending emails (obvious, eh?)
- Lots of existing lists, views and reports use the standard Email field
- Things get very confusing when you have more than one ‘Email’ field
So, I created a Workflow that keeps the two fields in Sync. I import into the
EmailAddr__c field and then get the workflow to update the stadnard
I also change the label of the standard
(Unused Email Field)” so that I don’t accidentally select it in Page Layouts.
What’s that you say? Why don’t I just use the normal
I’ve found that by importing directly into the Email field, the Data Loader will reject a record if it is a badly-formed email address, but copying it from my
EmailAddr__c field into the Email field seems to avoid this checking. At times I also had the situation where
EmailAddr__c had a value, but Email was blank. So, I find it a lot safer to use my custom email field, since I know it will always have a value. (We’ve since improved our external system to do similar validation on the email field, but we’ve still got lots of historical records with the problem.)
The Bottom Line
- We can now use an Email Address as an External ID
- We can now Upsert on Email Addresses
- It’s a little messy, but it works
- I wish Salesforce.com would let us define Standard fields as External IDs