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.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 External 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 Update/Insert/Upsert up in the G-Connector for Salesforce add-on menu. In the subseuent dialog, make sure to select Upsert from the list of operations, and the correct spreadsheet column that holds your external ids, in our case it is External_ID__c:

G-Connector will try to automatically map your spreadsheet columns to the corresponding fields in Salesforce, and because we pulled data using SOQL – in this case it will map all columns correctly. However, please verify the mappings each time before proceeding with an update and re-map if necessary. Incorrect mappings may lead to unrecoverable data loss!

The top table in the mapping dialog lists all available fields from the selected object. You can map your spreadsheet columns to fields in Salesforce by dragging fields from the top table and dropping them on the column names in the bottom table.

You can save the column mappings and other settings in this dialog box for future reuse by clicking the Save Template button.

Click Go to perform the Upsert operation.

After the Upsert operation has been completed, you’ll see the operation results on the very right from your data, one column over after the last data column:

Inserting records as children of records identified by an External Id

This functionality is not available yet in G-Connector at the moment.