Users often ignore a very powerful feature when dealing with data in the Upsert operation. This word always gets underlined by my spell checker because it doesn’t exist, upsert is a combination of two words: Update and Insert. And that’s essentially what it does – it figures out whether to insert a new record or update an existing one on the fly. This operation differs from other DML operations in that it needs a special data column to operate: an External Id column. This column must be mapped to either an External Id or indexed field on the Salesforce object you’re working with. In you can set up any Text, Number, or Email field as an External Id during field creation or by clicking Edit:

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.

In this tutorial we’ll show both use cases for the Upsert operation using XL-Connector. You can do exactly the same thing using SFDC data loader, it will just take longer.

Upserting Opportunity records

In our demo scenario, Opportunities need to be synchronized with an external system, and therefore the Opportunity object has a field named Ext. Reference.

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, and the Ext. Id column (that is the name of the column in your spreadsheet that contains external ids).

After you press OK, the standard column mappings dialog will appear for you to map your columns to fields in If you pulled your data using SOQL, column names will be identical to field names, so the dialog will automatically map everything correctly:

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 lets consider another use case of the Upsert function: creating new records under records identified by an External Id.

Our Account table also has an External Id column:

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.

Then we select the five contacts to be inserted as shown in the picture above and click Upsert. The following dialog box shows up:

As shown in the picture above, 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. Click OK, the following dialog box will be displayed:

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>

After clicking OK and confirming the number of records to upsert, our new contacts were created as children of the corresponding accounts.