Using Upsert to Update or Create Records in Salesforce

Users often ignore a very powerful feature when dealing with data in Salesforce.com: 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 Salesforce.com 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 or for reparenting existing records based on the parent External Id.

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

Upserting Account records

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 on Upsert up in the XL-Connector 365 sidebar:

You’ll need to map your columns to fields in Salesforce and also specify which of the columns in your Excel data holds the External Ids:

You can optionally choose a column in your data where you’d like to write the created/updated records Ids after the operation is done, as well as specify which rows will be affected by the operation (the default value is Selected):

Click on Run and Save to run the Upsert operation according to the provided settings and save all the settings as a reusable Flow Step. In this case you’ll be able to run the same Update operation again from the same area in your sheet with a single click on the Run Flow or Run Flow Step button.
Click Run to only run the Upsert operation of the selected data and not save the mappings and other settings as a reusable Flow Step.
Click Save to save all the provided settings as a reusable Flow Step. In this case you’ll be able to run the same Upsert operation again from the same area in your sheet with a single click on the Run Flow or Run Flow Step button.

After the operation is completed, all operation results, along with the updated record Ids will be written on the right from your data, one column over:

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, then click on Map Columns.

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. The Mappings 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 in the list of fields that you can map your columns to. Such fields will look as follows <RelationalFieldName/ObjectName/ExtIdFieldName> (see above).

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 map this column to the Id field on the Contact object. We’ll also populate that column with the Salesforce Ids by selecting that column in the Select column for result Ids setting:

 

After you click Run and Save or Run 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: