Using Exernal ID Fields in Salesforce
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:
- Name
- 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
- Phone
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.
Data Integration
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
Data Loader
Boomi
If you are loading data from an external system, External IDs will definitely become your best friends.
Categories: Salesforce
Just a note, the 3 external ID field limit is per object and not per org. Makes a big difference.
Is there a way to use external IDs when creating relations in Salesforce? I want to use an external id when creating relations between contacts and cases.
@Scott, are you talking about when inserting/updating data via the Data Loader?
Hi Jeff
Do you know if the salesforce Id can be set as the “External Id”?
Thanks
JL
@JL, no it cannot be. Sorry.
@JL — while the SFDC ID can’t be set as an external ID, you *could* create a custom field that is auto-populated with the SFDC ID upon create.
Having said that, I’m not sure how much *real* good it would do. I have found it much more beneficial to choose a unique Identifier from a source system not called Salesforce and use that for the UPSERT command. Of course, your mileage may vary…
what actually is externalid and where is it used. what is diff between import wizard and dataloader. can u brief
It would be fun to be able to delete objects using the external id.
Can an External ID be used when you need to use the Insert command and do not have the option of an Upsert. I need to sync a status field and cannot add all the extra records that do not match?
You can always insert an external id value into a field.
Search the salesforce.com docs for “external id” and you should find all the info you need.
Hi Jeff,
Have you ever come across an issue where the External ID reference actually searches against DELETED as well as active records with that Id?
We’re getting an error during an upsert call where the API is returning an exception of DUPLICATE_EXTERNAL_ID and the IDs it returns for the duplicated is actually marked as deleted.
To prove this, I can only ever retrieve that particular record by using queryAll() and it is clearly marked as IsDeleted = true.
It seems to indicate that whatever matching happens on the SFDC side, it doesn’t use the IsDeleted = false flag!
Cheers
Simon, what tool are you doing the upsert with?
About upserting relationships :
When you upsert a Contact on an Account, in the Data Loader you can specify that Contact is related to an Account identified by ID “ABC001″ in field EXTID of Account, created as external ID on Account.
Is this a feature of the Data Loader (which, somehow, internally, fetches the right AccountId) or is there an option somewhere in the API to define if and which field at the other end of the relationship can be used as an externalid ?
Rup