Loading records: Using external ids to relate records
Migrating record data between orgs can be a complicated process when you have complex relationships. This article walks through the process of using External Ids on records to load the data and let Salesforce do all the heavy lifting.
We will be using Jetstream to demonstrate all of the functionality, but the process applies to other tools or calling the Salesforce APIs directly.
This demonstration is going to use Salesforce CPQ objects for the example, but this process applies to any Salesforce standard or custom object that have lookup fields and where you want to load in records for one or both objects.
We have one parent object called Price Rule and two child objects called Price Condition and Price Action, both are related to the parent object through a field called SBQQ__Rule__c.
Suppose you have 100 Price Rules and each has between 1 and 10 Price Conditions and 1 and 5 actions and you want to migrate these records from your dev environment to your test environment.
The naïve way to do load the data
Export your records
Load Price Rules
Download the results
Use Excel (or similar) and paste in your Price Rule results, initial Price Rules, your Price Conditions, and your Price Actions into individual worksheets.
Use VLOOKUP to determine what the new id is for each Price Rule
Use VLOOKUP to match the new Price Rule id to each of the Price Conditions
Use VLOOKUP to match the new Price Rule id to each of the Price Actions
Download each worksheet as a CSV
Load in your Price Conditions, then your Price Actions
Pray and hope no mistakes were made
The same process, but using External Ids
Export your records
Load Price Rules
Load Price Conditions
Load Price Actions
done.
As you can see, using external Ids extremely simplifies the process!
Using External Ids does require some pre-work and there are some specific ways to export the data. Let's dive in!
Preparing records to use External Ids
Overview
Using External Ids requires some pre-work, but once you get this setup and get familiar with the process, it is really easy to load your data now and in the future.
Create an External Id field on any object that other records look up to, we will be using External_Id__c for our example
And better yet, add an external id to all objects so you can use upserts for record updates
Populate the External Id field on every record
Over time as you add new records, you will nee to complete this step again
You can also use triggers, flows, or workflow rules to auto-populate this field, but make sure you clear the existing value out if the record was cloned using ISCLONED, or sobject.isCloned() etc..
Export the data and include the related record's external id field (In our case that means include SBQQ__Rule__r.External_Id__c
Creating an External Id field
While you can use the Salesforce UI for this, Jetstream allows you to create the same field on as many objects as you want at the same time! Read the docs for more information.
For this example, we are going to create the External Id field on all three objects using Jetstream. In just a few clicks, we are ready to go!
Populating external id fields
Now that we have the fields created, we need to populate the field with a known value that will never change over time. The best choice here is to populate the External Id field with each records Id from the dev environment.
The value in the External Id field will never need to change for the entire life of the record, no matter what org it is in. Even when the record moves to test and staging and production, the External Id field will retain the Id of the record from the dev environment.
While we don't actually care what the External Id value is, if we use the Record Id from dev, this makes it easy for us to track the record back from prod to dev, and if we wanted to use Jetstream's Load Related Records feature (which is not in scope for this article), it would aid in preparing our data file.
While we could do individual data loads or anonymous apex to populate the External Id fields, Jetstream has an even easier way using the Update records in bulk feature.
Select the Objects that we want to update records for
Choose the field to update and apply to all
Indicate we want to use the value from another field and choose Record ID
And leave the default criteria of All Records
Tip: In the future, you may want to only update records where the External Id is blank so that you don't overwrite the existing record ids after a sandbox refresh
Validate the results to view the record counts and make sure everything looks good, then review your changes and update your records.
Exporting Records
Now that our records are prepared, we can export our data - you will need to do this step for each of your objects. You can review the query builder and query results documentation if needed.
We are assuming we want to migrate all of our records, so you may need to adjust accordingly for your use-case.
For Price Rules, select all the fields you want to load. Normally that will be the Name field and then all custom fields that are creatable.
Download the data as Excel or CSV, based on your preference. The goal of using External Ids is that we never have to open any of these files.
Tip: If you chose CSV and opened and re-saved the file, be careful if you have special characters in your data as Excel may have corrupted the data. Try to avoid opening your files at all prior to loading them to Salesforce. And if you do open, make sure not to save the file.
For all other objects, repeat the process but make sure unselect the root lookup field to the parent, and select the related External Id field.
TIP: While you could select SBQQ__Rule__c in your query, the data load mapping process will be easier if you instead choose SBQQ__Rule__r.External_Id__c.
Loading Records
Now the part you have been waiting for! This is where all of our pre-work pays dividends.
Make sure to change your org in Jetstream to your target environment, for use that means changing it to test.
First load the Price Rule records. Nothing special here since this is the parent object. You can review the documentation if you need more guidance on this process.
You can choose to do an insert or upsert in this step. If you plan to update records over time, then in the future you can use the External Id to perform upserts. This is extremely helpful if your dev sandbox was refreshed and is not a partial copy and has different Ids from your sandbox seeding process.
Load child records
When loading the child records, you will notice something really neat on the mapping page.
Since we exported our data using SBQQ__Rule__r.External_Id__c, Jetstream is smart enough to know that you want to map to a related External Id field and will automatically auto-map the field for you.
Tip: If, by chance, you exported the data using the SBQQ__Rule__c field, you can map that to the External Id field. If you included both SBQQ__Rule__c and SBQQ__Rule__r.External_Id__c, then make sure to de-select SBQQ__Rule__c.
Complete the data load process for all the child objects the same way. This process even works if a record is related to more than one record.
You have now completed your data migration without ever opening up the files that were exported from dev and all relationships were retained! This is a much safer and more consistent process than using Excel to perform VLOOKUPS.
If you haven't yet checked out Jetstream, take a look and see what you are missing!