What’s the difference between data loader’s update and upsert actions? When should you use upsert?

Upsert is not a real word.

(The English major in me would be remiss to not get this out of the way first.)

Although it’s not recognized in the dictionary, it is a well known portmanteau in the Salesforce lexicon. A portmanteau is created when parts of separate words are combined to make a new word, like smog.

Or maybe……..

Dictionary search results for Salesforce data loading term UPSERT

Although it’s not recognized in the dictionary, it is a well known portmanteau in the Salesforce lexicon. A portmanteau is created when parts of separate words are combined to make a new word, like smog.

Or maybe……..

Dictionary search results for Salesforce data loading term UPSERT

Although it’s not recognized in the dictionary, it is a well known portmanteau in the Salesforce lexicon. A portmanteau is created when parts of separate words are combined to make a new word, like smog.

Or maybe……..

So what exactly is an upsert?

UPSERT DEFINITION

Upsert is data loading’s method of running two processes simultaneously – an update and an insert. When a record in your file matches an existing record based on the record’s ID, the existing record is updated with the values in your file. If no match is found, a new record is created.

RECORD ID

If you are performing an upsert, your CSV file must contain a column of ID values for matching against existing records. The column is either an external ID (a custom field with the External ID attribute) or ID (the Salesforce record ID).

WHAT IS AN EXTERNAL ID?

You can set a Salesforce field as an external ID to reference a record ID from an external system.

External IDs are searchable in Salesforce (as compared to the Salesforce record ID, which is not searchable.)

External IDs do not have to be set as unique, but this is not best practice.

WHAT IS A UNIQUE ID FIELD SETTING?

Just what it sounds like, dummy. Just kidding just kidding. Don’t get all sensitive.

The ‘Unique ID’ field is a setting which prevents the same value from being used in multiple records for any specific field. The best practice when setting a field as an external ID is to also set it as a unique ID to prevent duplicates. You can set a unique ID as case sensitive or case insensitive.

If you try to set a second record with an external ID that’s been set as unique and has already been set on another record, you will see the error “We hit a snag. Review the errors on this page. duplicate value found. Field__c duplicates value on record with id: xxxxxxxxxxxxxxx”

**Reminder for newbies. You can’t set an object’s record name as an external ID or a unique ID, as the object name is a standard text field that does not have settings for setting it as unique or external. You can’t use native validation rules to prevent creating duplicates based on record name.

Are there other options to prevent duplicates based on record name? Sure, but that’s a rabbit hole for another day.

Back to the task at hand…

HOW TO LOAD A FILE IN DATA LOADER USING UPSERT

Use Case: We have a list of accounts from Oracle, an external system, with the Oracle Id. We want to update the billing address of the Salesforce accounts that have a matching Oracle ID. When we do not find the Oracle Id in Salesforce, we want to insert new account records.

Step 1: Prepare your csv file to include the external identifier and the record information you want to update.

Step 2: Ensure the Salesforce field that contains the external ID referenced by your file has been set as an External ID.

Step 3: Login to the data loader and select UPSERT as your action.

** When using Dataloader.io, select IMPORT from NEW TASK and then choose UPSERT as the operation.

Step 4: Select your object and your file, and proceed to mapping.

Step 5: Before you can map your fields in an upsert action, you have to select the field for matching.

Only applicable fields will appear in the dropdown, i.e., the record ID and any fields that have been marked as external IDs.

In dataloader.io, the smart mapping kicks in and selects the right field.

Step 6: Before proceeding to the field mapping, you can also select external IDs on related objects for matching. If this isn’t applicable, you can leave this blank.

Step 7: Complete your field mapping

Step 8: Select a place for your output files if using data loader. See this post for details and best practices. If using dataloader.io, this isn’t applicable.

Step 9: Click the Finish button. You will see the alert message that says “You have chosen to add new records and/or update existing records. The action you are about to take cannot be undone. Are you sure you want to proceed?” 

Click yes. 

Step 10: Operation status message pops up. 

Step 11: Open your success and error files.

Step 12: Review your files.

In our sample load, we had 6 successful upserts. The success file shows which records were inserted versus which were updated in the status column. (The status column was not part of our originally prepared file – data loader added it to the success file.)

The Salesforce record ID has been added to the success file by the data loader with the record IDs of what was updated as well as what was created.

Step 13: Check Salesforce and cross reference several of the records to ensure the addresses were updated as expected.

Here’s a screenshot of our original Salesforce data before the upsert load.

Here’s a screenshot of our data after the upsert load.

Notice the updated address of the Critically Endangered Aardvark Center. Also note that there’s a new account called Critically Endangered African Wild Dog Foundation. 

CONCLUSION

Use the UPSERT operation when you have a list of records that have an external ID and you’d like to update existing records and insert new records.

Post main image credit to Dominik Vanyi on Unsplash. I edited the image in Canva because I am a creative genius.