Hi,

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

Email

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

Where we use:-

The Salesforce API supports the use of External ID fields as foreign keys in place of IDs, but this feature is not yet supported in the SalesforceUpsert step. For example, if there is an External ID on Account named External_Account_Key__c, a Contact can be created as a child of an Account using the value of this External ID rather than the Account Id. See "upsert() and Foreign Keys" at http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_upsert.htm

The way that Data Loader supports specifying an External Id for a foreign key is "Account:External_Account_Key__c", continuing the example above. It probably makes sense to follow this same convention in Kettle.

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.