Here’s a riddle: When is a Salesforce ID not a Salesforce ID?

Answer: when you’re comparing 15-character and 18-character versions of the same ID.

Originally, all Salesforce IDs were 15-characters and case-sensitive. You could have IDs like 001C000000o4OOI and 001C000000o4Ooi, where the only difference between the IDs is upper and lower case, and they’d reflect two different records.

That worked fine, until people started exporting things like report data to Excel and using the Excel VLOOKUP function to match up records. The problem is that VLOOKUP doesn’t handle case-sensitive data well – it doesn’t recognize that 001C000000o4OOI is different from 001C000000o4Ooi.

To help solve this problem, Salesforce came up with a variant of the 15-character case-sensitive ID, an 18-character version that’s not case-sensitive. For every ID in Salesforce (that is, for every ID that’s stored in an ID field, a lookup field, or a master/detail relationship field), there’s both a 15-character and an 18-character version of it. For example:

The 18-character ID is not case-sensitive. In other words, 001C000000O4OOIIA2 and 001c000000o4ooiia2 both refer to the same record. Because the 18-character IDs aren’t case-sensitive, they work great with Excel’s VLOOKUP function.

Depending on how you query the database, sometimes you get the 15-character version, and sometimes you get the 18-character version. Apex code works with 18-character IDs, but reports and formula fields usually yield 15-character IDs.  If you use a report to export data to Excel with the plan of using VLOOKUPs, the 15-character (case-sensitive) ID leaves you with the same problem people had years ago – the struggle of trying to get VLOOKUP to work properly with case-sensitive values.

In the Spring ’12 release, Salesforce introduced a new formula function that solves this problem. The function, CASESAFEID, accepts a 15-character ID as a parameter and returns the 18-character version. For example, you could create a formula (Text) field on Contact named AccountID18 with a formula function:

CASESAFEID({!AccountID})

With this, if a Contact’s AccountID field is 001C000000o4OOI, the value in the AccountID18 field would be 001C000000o4OOIIA2. The AccountID18 field isn’t a lookup field – you would still use the AccountId field if you needed to look up to the Contact’s Account record. But if you wanted to create a report that allows you to export the 18-character version of the ID, this new field, using CASESAFEID, would do the trick.

I’ve talked about how creating a formula that uses CASESAFEID is useful if you use reports to look at IDs. What other situations can you think of where getting the 18-character ID would be useful? Post your answers in comments below.