May 7th, 2010
Salesforce has baked in some “magical” features into its platform. Two of my favorites are the upsert command and the use of External IDs. If you are new to the platform, you’ve probably seen the option of making a field an “External ID” during the new field creation process.
The External ID field allows you to store unique record IDs from an external system, typically for integration purposes. So if you have a bespoke marketing system running on SQL Server, it is may be easier to load, update and reference these external records in Salesforce using unique IDs from SQL Server.
Salesforce allows you mark up to 3 fields as External IDs and these fields must be text, number or email field types. Values in these External ID field must also be unique and you can also determine whether or not value are case sensitive.
There are three ways that you typically use External ID field.
Loading Data from External Systems
When you load data from external systems you may want to track the record from the external system for reference or if you want to make updates back into the external system. Simply mark a field as an External ID and the Force.com platform will ensure that each value is unique and that you don’t load duplicate records from the external system.
Making Fields Searchable
When searching for custom object records from the sidebar the following fields are searchable:
- All custom auto number fields
You can make fields searchable by marking them as an External ID. Some people “cheat” and mark fields that do not necessarily contain external record IDs so that they are searchable.
For the advanced search, the following fields are searchable:
- All custom fields of type text
- Text area
- Long text area
So if you have a numeric field that is an External ID it will not be searchable via the advanced search. You could create a text External ID field and then write a workflow that updates this field from the numeric External ID field. This way your external ID is searchable.
This is were the External ID field really earns its keep. When using the upsert command during data loading, you can reference the External ID field instead of the Salesforce ID. This is a huge advantage because you typically don’t want to maintain the Salesforce ID in your external system. When uploading data with the Import Wizard, Data Loader or (most) ETL tools like Boomi or Informatica, there is a setting to specify that a field is an External ID.
Salesforce Import Wizard