My brother is bald and I am not, which is helpful when my dad is trying to tell us apart. But what happens when we are wearing hats?

Thankfully, my brother and I each come with a unique (at least in our family) identifier: our names. Regardless of how similar we are in appearance, charisma, or wit, when my Dad shouts for Drew to bring him a glass of wine, only one of us comes running.

Similarly, when working with data, each record (or row) should have at least one unique identifier. This is especially crucial when migrating or mass updating records.

Salesforce Unique IDs

Every record in Salesforce has a unique identifier known as the record ID, the Salesforce ID, or simply the ID. The ID comes in two formats:

• A fifteen-digit format, which is case-sensitive

  • Case-sensitive means that “a” and “A” are considered different

• An eighteen-digit format, which is case-insensitive

  • Case-insensitive means that “a” and “A” are considered the same

  • This eighteen-digit format is made up of the fifteen-digit ID plus a three-character suffix

There are many ways to find the ID of a record, but the easiest is to look at the URL:

In this case, the 18 digit ID is 0036e00004P27R7AAJ. It’s made up of a few parts:

• Object prefix – the first three characters are a prefix that determines what object it is. 003 means it is a Contact

• 15-character ID – the first 15 characters are the 15-digit ID. This ID displays in the Salesforce UI, including in reports.

• Case-Insensitve Suffix – the last three digits turn the 15-digit case-sensitive ID into an 18-digit case-insensitive ID.

Be careful when using the 15-digit ID! It can cause problems, especially in Excel when using formulas such as VLOOKUP. If you need to export a report and make use of the Salesforce ID, you should make a formula field on the relevant object named “CaseSafeID__c” and use this formula: CASESAFEID(id)

Example: If I want to delete the saved Email value in all Contacts who have opted out, I could run a report with this CaseSafeId__c, export the results, delete the email values, and run an update with the DataLoader. By telling it to match the spreadsheet data using the Salesforce ID, I can be sure it will only modify the correct records, even if I have two Contacts named Jane Doe in my database.

Data Migration

When migrating records from another source into Salesforce, it’s best practice to include the legacy unique ID. This allows one to create connections between migrated records in Salesforce and also provides a data trail to follow when investigating data issues.

The best way to capture a legacy unique ID is to toggle the “External Field” setting on a new custom field in Salesforce.

If I were migrating data from HAL 9000’s personal database into Salesforce, my custom field might look like this (note that “Unique” and “External ID” options are both checked):

My Contact import data csv might look like this:

If I’m also importing another object related to Contact, like Volunteer Hours, I can use this external ID to make the necessary connection. (The exact method for using external IDs to forge Salesforce connections during data migration is beyond the scope of this blog post.)

Even if the data I am migrating doesn’t have a Unique ID, it’s a good idea to create one before importing, even if it is as simple as numbering the rows in your import sheet.

Deduplication and Data Migration

Moving between databases is a great time to deduplicate data… EXCEPT when it causes problems with unique IDs.

If two records in my legacy HAL 9000 system are identified as duplicates, and I merge them as part of the migration process, I have lost one of their unique IDs.

This can cause problems if I want to do data quality validation, trace the provenance of my data, or create records related to my duplicates.

If you are going to deduplicate data during your data migration, be sure to do one of the following:

1. Deduplicate data in your legacy system BEFORE you export it.

2. Deduplicate data AFTER it has been migrated to Salesforce.

3. Track your duplicate groups and related Unique IDs in such a way that child records can also be migrated. (This could involve multiple External ID fields, apex code, and/or a master duplicate spreadsheet and advanced VLOOKUP work as part of migration.)

Conclusion

To sum up: when in doubt, use the 18-digit Id, keep track of any merged records, and never give two of your children the same name.