Upsert with External IDs in Salesforce Apex

Integrating external data into our Salesforce org without creating duplicates is a critical task. One powerful way to achieve this is by using the UPSERT operation with an External ID field. In this blog post, we’ll explore what upsert means, why external IDs are valuable, and how to implement a simple upsert scenario in Salesforce Apex. 

Salesforce provides a robust platform for managing data, and one of its most useful features is the ability to perform an upsert operation. Using the upsert operation, we can either insert or update an existing record in one call.

The upsert operation checks whether a record already exists and either updates it or creates a new one. It determines this by using the record’s ID, a custom external ID field, or a standard field with the ID lookup attribute set to true. This approach is particularly useful when integrating with external systems that send regular data updates, ensuring that records are efficiently maintained without creating duplicates.

Before going into the implementation, let’s understand two key concepts:

• Upsert – Upsert, as the name suggests, is a combination of ‘update’ and ‘insert’. It allows Salesforce to check whether a record already exists using a unique identifier, such as a record ID or external ID. If the record exists, Salesforce updates it. If not, a new record is created. This simplifies the logic by eliminating the need to separately determine whether to perform an update or insert operation.
• External IDs –External IDs are custom fields that store unique identifiers from external systems. By simply selecting a checkbox on the field, you can designate it as an external ID. This ensures that each record imported from an external source can be uniquely identified, helping to prevent duplicates during data migration or system integration.

Use Case Scenario Overview

Imagine our organization retrieves customer data from an external system. Each customer record from the integrated system comes with a unique identifier. Our objective is to import or update customer records in Salesforce using this unique identifier so that we avoid creating multiple records with the same ID. When a new record triggers, the system should check for the External ID:

• If a record with the same External ID already exists, update the existing one only.
• If a record does not exist, insert it as a new record.

Below are the steps where we are implementing the above requirement:

Step 1: Create a New Custom Object

1. Log in to our dev org and go to Setup:
   • Click the gear icon in the upper right corner and select Setup.
2. Create a New Custom Object:
   • Go to Object Manager and click on it.
   • Click the Create dropdown and select Custom Object.
   • Label: Customer
     Plural Label: Customers
     Object Name: Customer
     Record Name: Customer Number
   • Save the custom object.

Step 2: Create an External ID Field with Unique Identifier as True

1. Navigate to the Customer Object, which we created in Step 1:
   • In Object Manager, click on Customer.
2. Create a New Field:
   • Click on Fields & Relationships.
   • Click the New button.
   • Select Text as the data type and click Next.
3. Configure the Field Details:
   • Field Label: External ID
   • Field Name: External_Id
   • Length: (For example: 50)
   • Check the boxes for External ID and Unique to ensure that each value is unique and can be used for upsert operations.
   • Click Next, we can set field-level security as needed and add the field to the appropriate page layouts as per business requirements.
   • Save the field

Step 3: Create Additional Fields If Required

For implementation purposes, we might want to create another field for customer information. Let’s take an example of the Email field:

1. Create a Field to Store Email:
   • If still under the Customer object, click New in Fields & Relationships.
   • Select Email as the data type and then click Next.
   • Field Label: Email
   • Follow the next steps to set field-level security and page layout assignments.
   • Save the field.

Step 4: Write Apex Code/Script for Upsert

We will write an Apex Script that demonstrates the Upsert operation using the External ID field. We can run this code using the Developer Console’s Execute Anonymous Window.

1. Open Developer Console:
   • Click on the gear icon from the top right from the logged-in org and select Developer Console.
2. Write the Apex Script In Execute Anonymous Window:

// Create a list of Customer records
List<Customer__c> customers = new List<Customer__c>();

// Create a new Customer record with an External ID like 'EXT001' with email
customers.add(new Customer__c(
External_Id__c = 'EXT001',
Name = 'John Doe',
Email__c = 'john.doe@example.com'
));

// Create another Customer record with External ID 'EXT002'
customers.add(new Customer__c(
External_Id__c = 'EXT002',
Name = 'Shantanu Joshi',
Email__c = 'jane.smith@example.com'
));

// Use upsert to insert new records or update existing records
upsert customers External_Id__c;

How the Code Works

• Creating Records:
  We are creating a list named customers that holds instances of our Customer__c object. Each record is initialized/retrieved with a unique external ID along with a Name and Email.
• Upsert Operation:
  The upsert statement uses External_Id__c as the key field. Salesforce checks if any record exists with the given external ID.
  • If a record with that external ID exists, it will update that record.
  • If no record exists, a new record will be inserted.

This code shows the integration process by removing the need for manual checks and making sure the data is consistent.

Verifying the Records

After executing the Apex Script, verify that the records have been created or updated correctly:

1. Navigate to the Customer Tab:

•  In our Org, we will click on the Customer tab. If it’s not visible, then add it in the App Launcher
• We should see the records with External IDs EXT001 and EXT002.   

2. Run a SOQL Query:

• Open the Developer Console’s Query Editor or Salesforce Inspector.

SELECT Id, Name, External_Id__c, Email__c FROM Customer__c

•  Verify that the query returns the inserted records with correct values.

Updating Existing Records

To confirm that the upsert operation updates existing records instead of creating duplicates:

• Modify one of the record’s fields from our Apex Script.
• Execute the Script ā.
• Run the SOQL query to see that the record with the updated field has the updated values, we will be able to verify that the record was updated rather than duplicated.

Below are some best practices to keep in mind when working with Upsert operations and External IDs:

Use Unique External IDs

Always mark our External ID field as unique. This ensures that each record from the external system is treated as unique and decreases duplications.

Error Handling

While the basic example shown here does not include robust error handling, consider wrapping our Upsert operation in a try-catch block in scenarios to gracefully handle exceptions/errors.

try {
upsert customers External_Id__c;
} catch (DmlException e) {
System.debug('Error during upsert: ' + e.getMessage());
}

Logging and Debugging

Make soft use of system debug statements during development. Logging helps you understand the flow and catch any issues early in the integration process.

Testing in a Sandbox Org

Sandbox orgs are an excellent way to experiment without affecting our production data. They provide a safe environment to test new features and integrations before deploying them to our live environment.

Documentation and Maintenance

Document our integration processes and Apex Script thoroughly. Good documentation helps maintain the Script/Code over time, especially as business requirements increase in future.

With the above script, it will insert records as below with our External ID as Unique:

After passing the new External ID value, it will insert a new one instead of updating:

If we get different values with the same External ID, it will update the existing one:

Also Read – How to use NavigationMixin in LWC in Salesforce

FAQs:

1. What is an Upsert in Salesforce, and how does it function with External IDs in Salesforce?

The upsert operation in Salesforce merges the functionalities of both insert and update. It verifies the existence of a record by comparing it against a designated External ID field. If a record with the same External ID is found, it updates that record, and if not found, it creates a new one.

It is important to note that an External ID field is necessary (this should be a unique, indexed field designated as “External ID”). This way, we can eliminate duplicate records by checking the existing records.

2. How will Salesforce decide whether to insert or update during an Upsert?

Salesforce uses the External ID field value provided in the record to perform a database query.

If record Found: Existing record will be updated. If No Match, A new record will be created.

If multiple Matches are found with the same external ID, A DMLException is raised due to duplicate External IDs.

3. What are the most common challenges when using Upsert with External IDs?

Case Sensitivity: Text-based External IDs may not match due to differences in case.

Duplicate External IDs: Having multiple records with the same External ID can lead to errors.

Missing Field Access: The user executing the operation must have read/write permissions for the External ID field.

Required Fields: Records being inserted may fail if any required fields are absent.

Conclusion

Integrating external data into Salesforce can be a challenging task, but the standard Salesforce Upsert operation with an external ID field simplifies this process very easily. By following the steps outlined in this blog, from creating a custom object and External ID field to writing and testing your Apex Script, we now have a practical example to work on. Happy coding !!

Get a complete Roadmap To Learn Salesforce Admin And Development

Name *

First

Last

Email *

Comment or Message

Submit