Salesforce IDs – A Riddle Solved with CASESAFEID()

Salesforce IDsHere’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:

Salesforce Ids

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.

MJ Kahn, SVP of Technology at OpFocus

about the author

MJ Kahn

At OpFocus, MJ architects and constructs solutions that would impress the builders of the pyramids. She solves technical puzzles that would frustrate daVinci. She leaps tall buildings (like the new Salesforce tower) in a single bound.

Well ok, maybe she doesn’t. But she does help lead an amazing team of smart, talented, and dedicated consultants who do. MJ’s job at OpFocus is provide technical leadership and guidance to OpFocus clients and team members so that, working together, we can create innovative yet practical solutions to real-world business problems within the Salesforce ecosystem.

Prior to OpFocus, MJ built an extensive background in technology and has held a variety of development, consulting, and management positions at companies like Sybase, Vignette, and Honeywell, among others. In these roles, MJ’s focus was helping companies effectively and intelligently use technology to solve business problems. An Apex and Visualforce consultant since mid-2008, MJ has worked with scores of companies to help them understand and utilize platforms like Force.com to solve business issues and also guide them in developing their own AppExchange product offerings.