All about the Salesforce CaseSafeID field.

At our company, we’ve decided to use an ID18 field on all objects in Salesforce. It was setup organically as needed an at one point in 2019, it was like wild fire! We were setting up ID18 fields all over the place.

The Premise: All IDs in Salesforce, should be represented as 18 digit ID’s.

Why? Well, in the beginning, there was the heavens and the earth… wait wrong story. In the beginning, Salesforce created a data base, which I believe in on an Oracle back end. Someone figured 15 digit case sensitive ID’s were OK. This appears to be a major bug because Salesforce fixed it with the CaseSafeID field early. Like in 2003.

Record ID formats returned via API

Note: API access requires Enterprise Edition or Unlimited Edition.
API versions prior to 2.0
The ID of a record is always 15-characters and case-sensitive. It should not be compared in a case-insensitive manner.
API versions 2.0 and higher
The API can return either a case-sensitive or a case-insensitive ID field value.
The case-insensitive ID is identical to the 15-character case-sensitive ID, but with 3 extra characters appended to indicate the casing of each of the original 15 characters.
When inserting or updating Records, the API accepts either the 15-character case-sensitive ID or the 18-character ID with the 3 extra characters appended, being case-sensitive.
When querying or searching records using the API, you must specify a value of “1” for the “useCaseSafeIDs” parameter to indicate that you want the API to return case-insensitive IDs. If you don’t specify the “useCaseSafeIDs” parameter, you automatically receive case-sensitive IDs.
API version 2.5
The API defaults to 18 characters on the ID (case-insensitive) and provides no option to use the 15-character case-sensitive ID explicitly.
The “Reports” tool queries the database directly and therefore returns a 15-character case-sensitive ID. Tools like the Data Loader, Demand Tools, or the Weekly Data Export service will export records with the 18-character ID.
https://help.salesforce.com/articleView?id=000324087&language=en_US&type=1&mode=1

Classic Users have the problem. Lightning users do not.

Hey Classic Users! Guess what’s in your reports. 15 digit ids. wha wha wha… This might be OK for Salesforce, but excel and all databases including sql do not not recognize case sensitive.

The Solution

No Worries! The Formula for the ID18 is super easy to make. You go into any object, create a Formula field. I like to simply call it ID18. The Formula is CASESAFEID(id). You can use this to convert any 15 digit salesforce ID to 18 digits.

Example ID18 custom field on Lead object.

So, now we know that A1O00000abg123a and A1O0000aBg123a are the same value if you ignore case and we know they fixed it in API 2.5. But HOW, you might ask. They fixed it with an algorithm that runs on capital letters only! Smart.

You need to imaging the original ID (which exists in 2020 in the DB and can be seen running reports in classic using the native ID field) is 15 digits divided into 3 segments of 5 characters each. You cannot see these 15 digit ID in the API. You must use reports or the classic UI URL addresses.

The Algorythm:

For this example lets look at this 15 digit ID: A1O00000aBg123a
First 5 characters “A1O00”. (There is a capital “A” and “O” there).
- If Position 1 is a Capital letter, Value = 1 (The “A” fits)
- If Position 2 is a Capital letter, Value = 2
- If Position 3 is a Capital letter, Value = 4 (The “O” fits)
- If Position 4 is a Capital letter, Value = 8
- If Position 5 is a Capital letter, Value = 16
- Add these up. (1 + 4 = 5)
Middle 5 characters “000aB”. (There is a capital “B” there).
- If Position 1 is a Capital letter, Value = 1
- If Position 2 is a Capital letter, Value = 2
- If Position 3 is a Capital letter, Value = 4
- If Position 4 is a Capital letter, Value = 8
- If Position 5 is a Capital letter, Value = 16 ( The “B” fits)
- Add these up. (16)
Last 5 characters “g123a”. (There are no capital letters here).
- If Position 1 is a Capital letter, Value = 1
- If Position 2 is a Capital letter, Value = 2
- If Position 3 is a Capital letter, Value = 4
- If Position 4 is a Capital letter, Value = 8
- If Position 5 is a Capital letter, Value = 16
- Add these up. (0, not null)

Now you have 5, 16, 0 to represent your three end characters. Applied, the algorithm has a list of Letters and Numbers it can use. The output of the algorithm is a number that corresponds to a letter in this list.

ABCDEFGHIJKLMNOPQRSTUVWXYZ012345

So A = 0 and B = 1, then F = 5, 16 = Q, and 0 = A. Result: “FQA”

Appended to the end of the 15 digit ID, you have your ID18. Regardless of case sensitivity, the value is unique.

The RESULT:

The 18 digit, “Case Safe ID’s” are equivalent and both will upload to Salesforce correctly.

a1o00000abg123afqa (18 digits)	a1o00000abg123afqa (18 digits)	Case Insensitive
A1O00000aBg123a (15 Digits)	a1o00000abg123a (15 Digits)	Case Sensitive

Microsoft Excel does not recognize Case Sesitive, for example. Use 18 Digit ID, always.

In the old days before CaseSafeID, there used to be a bunch of formula’s like this. People in the community still post these sometimes. You don’t need this. It works fine, but you don’t need it anymore. The neat part is, you can see the Algorithm inner workings.

=CONCATENATE(A2,
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1),
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,6,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,7,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,8,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,9,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,10,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1),
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,11,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,12,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,13,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,14,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,15,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1))

“1) Out of clutter find simplicity; 2) From discord find harmony; 3) In the middle of difficulty lies opportunity.”
― Marc Benioff, Behind the Cloud: The Untold Story of How Salesforce.com Went from Idea to Billion-Dollar Company-and Revolutionized an Industry

Troy Center 4/14/2020