Salesforce provides an upsert method for its business
objects. Upsert is a combination of the words Update and Insert.
With this method, existing records are updated and new records are
inserted. Use of this method avoids creating unwanted duplicate
Creating a Custom Field as an External ID
To use the upsert method, the object must have a custom
field identified as an External ID.
To add a custom field:
- Log in to Salesforce.com.
- Click your name and select Setup.
- Under Customize, select an object, such as Accounts. Click Fields.
- Click New and select a field type, such
- Fill in the field label, for example, Account ID. Fill in the
rest of the required information and select the External
ID - Set this field as the unique record identifier from an external
system check box.
- Click Next, Next,
and then Save.
The Account (or other object
name) Fields page opens and shows the new field with its API Name,
Account_ID__c, as shown in the following image.
- Create the synonym for the upsert method, as described in Creating Metadata for Salesforce. The new field you create appears as the last
field in the INPUT segment.
Using an External ID Field
When creating a data flow using an upsert method, you
must identify the external ID field. This can be done in the JOIN
object that joins the upsert method to an input table, as shown
in the image below. There are two fields that are required in the join:
- A source field with the external ID field, for example,
ACCOUNT_ID = ACCOUNT_ID__C
- The input field name EXTERNALID with the API Name of the external
ID field, for example, EXTERNALID = ‘ACCOUNT_ID__C’