AddThis Feed Button

Recent Items

Tags: Data Loader

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 Email field.

I also change the label of the standard Email field to “(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 Email field since the Workflow sets it to the same value? Well, the answer lies in the field type. The standard Email field is of type “Email”, which means that Salesforce.com performs validation to ensure the address ‘looks like’ an email field. However, my external system does not have similar checking. Therefore, I want to use the email address from my external system as an unique identifier even if it is a badly formatted address.

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

One Response to “Using Email as an External ID for Contacts”

  1. Thomas Goldstein Says:

    As far as I know, this isn’t needed (and my tests confirm this).
    As you said, only Custom fields can have an External ID, however, upserts also work with fields that have the “idLookup” property. And Email happens to have this property for the Lead and Contact tables (as detailed here: http://www.salesforce.com/us/developer/docs/api/apex_api.pdf).
    In other words, the only advantage of your system is that it’ll save malformed (according to Salesforce) e-mail addresses.