So the way an Upsert operation works during a data load, is for each record Salesforce looks at the value loaded in the External Id column, and if the system already contains a record with the same External Id – it updates that record, otherwise – it creates a new one.

One other use of the Upsert operation that is very often overlooked is using an External Id field on a parent object in a master-detail or lookup relationship to create new records as children of that parent record or for reparenting existing records based on the parent External Id.

In this tutorial video we’ll show both use cases for the Upsert operation using XL-Connector.

Upserting Account records in Salesforce

In our demo scenario, Accounts need to be synchronized with an external system, and therefore the Account object has a field named Ext. Account Id.

When we receive our opportunities from an external system, the file also has a column of External Ids:

Now to do an upsert we need to select the records we want to push into Salesforce (any columns may be selected as long as the selection spans across the rows we want to upsert), and click Upsert up in the XL-Connector ribbon. The following dialog box will appear:

Here you’ll need to select the object (Account), and the Ext. Id column (that is the name of the column in your spreadsheet that contains external ids).

Also in the same dialog box you will need to map your columns to fields in Salesforce.com.

Once you click OK in the Map Columns dialog, you will be prompted to confirm the amount of records you’re upserting.

If the amount of records looks correct, click Yes and they will rush into Salesforce. At the end of the operation you’ll see the results column populated to the right of your data:

Inserting records as children of records identified by an External Id

Now let’s consider another use case of the Upsert function: creating new records under records identified by an External Id.

We’ll create new contacts as children of Accounts identified by their External Id field.

Our Contact table may or may not have an External Id column. If there’s no External Id column, the Upsert will work solely to create new records.

For the purpose of this demo the Contact does not have an external Id column. We’ll create a spreadsheet with new contacts as follows:

Please note, that we’ve created an empty Id column. It will be used as the external id column in the Upsert operation. We’ll later map it to the Contact’s Id field, which can also work as an external Id field. We’ll also populate that column with the Ids of the newly created records.

Then we select the five contacts to be inserted as shown in the picture above and click Upsert. A Map Column dialog box will be displayed.

As shown in the picture below, you have to select the object (table) you’re inserting records into. Also the Ext. ID Column selection is mandatory. Since in this specific case we are inserting new records, you need to select the dummy Id column in this drop-down. Make sure to later map this column to the Id field on the Contact object. We’ll also populate that column with the Salesforce Ids of the newly created records by specifying the column index (F) in the Put Ids to column(s) setting.

The Account External Id column by which Salesforce will identify under which account each contact needs to be placed, has to be mapped to the Ext. Account Id field on the Account object. Map Columns dialog that is generated for the Upsert operation differs from the same dialog used with the Insert and Update operations in that it not only lists all fields of the object you’re working with in the top table, but also contains External Id fields of all objects related to the object you work with via a Lookup or Master-detail relationship.

In other words, if the object you’re working with is a child, whose parent has fields marked as External Id, these fields will be also listed at the very end of the top table. You’ll know such field by the presence of forward slashes in the name, as they are named by the following scheme: <RelationalFieldName/ObjectName/ExtIdFieldName> (see below).

After you click Upsert in the dialog box above, the new contacts will get created and linked to the accounts identified by the corresponding external id. The Salesforce Ids of the newly created records will be also populated in the Id column:

If you want to learn more about upserts in Salesforce please read this blog article