Indulge me for a moment while I wax poetic over Salesforce's record identification method, and then give you some very practical tips on how to use the record ID's in off-platform data manipulation.
You may be aware of Salesforce's clever way of identifying records with an ID that starts with 3 digits that identifies the type of record it is. Pardot users hopefully recognize that when they roll over the little blue cloud in a Pardot list view that by looking at the link address that they can immediately discern a Lead (starts with 00Q) from a Contact (starts with 003).
You may also be aware that the salesforce record ID is a universally unique identifier for that record across all instances of Salesforce. Think of it as that record's license plate, there are no duplicates. This duality, the record itself tells us what it is from it's first 3 digits and the record is universally unique, make this one of Salesforce's most useful characteristics when we're dealing with data manipulation.
You may also be aware that there are 15 and 18 character versions of the hash. The version shown in the Pardot screenshot above is the 18 character version. The 18 character versions always have three capital letters at the end. These are just a checksum for the first 15 digits. Here's why this matters:
The 15 digit hashes are case-sensitive. The 18-digit hashes are case-insensitive
And why does that matter? One word, Excel. Microsoft Excel, for all it's brilliance, cannot deal with case-sensitive values. And if you're pulling record ID's out of Salesforce to do some kind of manual manipulation, or dataset combining, or sorting, or anywhere else that you want to be able to make sure you have a unique key to work with...well the Salesforce 18 digit value is perfect. Except that Salesforce doesn't export the 18 digit version when you include the Record ID in standard exports. It's the 15 digit version. So to get a case-insensitive version that Excel will work with, you need to get your hands on the 18 digit version of the record ID.
So, what can you do? There are a variety of options.
Don't use reporting to export data. If you use dataloader, or workbench, or other API based systems to extract data from Salesforce (even Pardot), it will give you the 18 digit version. But that's not always practical. It works best for exports with simple parameters, like "everything".
Create a CASESAFEID field. This help article details how you can create a field that returns the 18 digit ID for the record instead of the 15 digit version. This works best if you know you'll need this frequently and have the ability (or an admin to help you) to make this field. The downside is you have to make it on every object you might need it for separately. It also might already exist in your org-- be sure to check reporting (some fields are visible only for reporting) or ask your admin if it's already available and you just can't see it.
Quick 'n' dirty conversion. Sometimes you don't have the luxury of creating fields in the org you are working on, or you already have the data in the wrong format. There are a variety of resources out there like this one that will quickly convert 15 digit hashes to 18 digit. Saved my bacon more than once.
Let's face it, sometimes you just have to manipulate records outside of Salesforce. Most often the marketer's tool of choice is Excel. Hopefully now you're better equipped to make the most of the Salesforce record ID as a unique identifier. Happy pivot tabling!
