15 or 18 Character IDs concept in Salesforce.com

Data can be empowering or devastating. Unique IDs can enrich your data and make your life significantly easier. We have taken the concept of using IDs as quick means to locate data (in part 1 of this blog post) and navigate around Salesforce.com, one very big step forward.

You’ve probably also noticed that these IDs are either 15 or 18 characters; however do you know why? A nice example is a standard report look up, where they’ll show up as 15 characters, take a look below:

If you aren’t following this, let’s take the opportunity to take a step back and point out a previous conversation.

If you are still with me, then we are going to explore a few tips and tricks to add some value to your data.

Salesforce.com IDs are case-sensitive so if you wanted to export data to Excel to be used as a lookup table using the VLOOKUP formula, you’ll need to be aware of this. If you happened to have records with IDs of 001A0000006Vm9r and 001A0000006VM9r the VLOOKUP formula, not being “case-aware”, would always find the first record, regardless of the case of the lookup value. Salesforce.com realized the potential problem with this and instigated the 18-character ID.

There was a method to their madness of expanding out by three additional characters. They are similar to “checksum” values in that they were calculated based on 15 character IDs. They gave these IDs what they needed – the ability to be different if the case of ANY of the first 15 characters changed.

More importantly, the addition of 3 characters allow for upward-compatibility. Salesforce made the two types of ID interchangeable.

You could use either ID and they would be regarded as identical, at least within Salesforce.com applications. This was carried forward when the Apex programming language was made available.

For example, when you compare two fields of type “Id”, the values are first converted internally to 18-character values before being compared, so you never need to worry about programming specifically to handle this (this is very useful for developers)

Let’s get technical
The next part of this article assumes a good working knowledge of the following:

Force.com Apex language constructs and syntax

Apex triggers and classes

Apex test methods, code coverage and unit tests

How to add code to your org using an IDE e.g. Eclipse & the Force.com plugin

This highlights one source of annoyance to many users – reports do not show 18-character IDs.

If Salesforce had provided a function to return an 18-character field from an existing ID, that might have given us enough to achieve a “formula field workaround”, but they didn’t. In lieu of a formal workaround I will give you this, albeit in a more technical form.

First, I’ll show you how to attach an Apex trigger to an object, to give you an identical result. With this, every time a record is added to the system, an 18-character (text) field is updated with the 18-character equivalent ID, calculated by the trigger.

Salesforce provide the algorithm they use for this, but not the code. So, here’s the code. Diagrams make things easier, so as you read, here is a mock up of the process:

Account Id	Account Name
001A0000006vm9U	Acme Company

In Step 2, the characters are reversed in each piece.

In Step 3, we check each character. If the character is uppercase A to Z, we replace it with a 1, otherwise we replace it with a 0 character.

In Step 4, we lookup the above table with each of the three 5-character “binary” string.

And finally, in Step 5 we simply append these three characters onto the end of the original ID value.

The clever thing about this is that the algorithm will always change the value of the additional 3-characters if you change the case of any of the original ID characters.

The Code
And now for the code – this assumes a familiarity with Apex code, the Eclipse environment, how triggers work and a little bit of Salesforce.com knowledge.

I’ve created a class called Utility – you can call it what you like. We will create a static method called sfdc15To18

Now, copy the following code into the body of your class file:

public static String sfdc15To18 (String original15) {
  // This method expects a 15-char valid Salesforce ID, and returns the 18-char equivalent

  Map lookup5bin = new Map();
  String Piece1 = ”;
  String Piece2 = ”;
  String Piece3 = ”;

  original15 = original15.substring(0,15);

  lookup5bin.put(‘00000′,’A’);     lookup5bin.put(‘01000′,’I’);
  lookup5bin.put(‘10000′,’Q’);     lookup5bin.put(‘11000′,’Y’);
  lookup5bin.put(‘00001′,’B’);     lookup5bin.put(‘01001′,’J’);
  lookup5bin.put(‘10001′,’R’);     lookup5bin.put(‘11001′,’Z’);
  lookup5bin.put(‘00010′,’C’);     lookup5bin.put(‘01010′,’K’);
  lookup5bin.put(‘10010′,’S’);     lookup5bin.put(‘11010′,’0’);
  lookup5bin.put(‘00011′,’D’);     lookup5bin.put(‘01011′,’L’);
  lookup5bin.put(‘10011′,’T’);     lookup5bin.put(‘11011′,’1’);
  lookup5bin.put(‘00100′,’E’);     lookup5bin.put(‘01100′,’M’);
  lookup5bin.put(‘10100′,’U’);     lookup5bin.put(‘11100′,’2’);
  lookup5bin.put(‘00101′,’F’);     lookup5bin.put(‘01101′,’N’);
  lookup5bin.put(‘10101′,’V’);     lookup5bin.put(‘11101′,’3’);
  lookup5bin.put(‘00110′,’G’);     lookup5bin.put(‘01110′,’O’);
  lookup5bin.put(‘10110′,’W’);     lookup5bin.put(‘11110′,’4’);
  lookup5bin.put(‘00111′,’H’);     lookup5bin.put(‘01111′,’P’);
  lookup5bin.put(‘10111′,’X’);     lookup5bin.put(‘11111′,’5’);

  Piece1 = sfdc0Or1(original15.substring(4,5)) +
  sfdc0Or1(original15.substring(3,4)) +
  sfdc0Or1(original15.substring(2,3)) +
  sfdc0Or1(original15.substring(1,2)) +
  sfdc0Or1(original15.substring(0,1));

  Piece2 = sfdc0Or1(original15.substring(9,10)) +
  sfdc0Or1(original15.substring(8,9)) +
  sfdc0Or1(original15.substring(7,8)) +
  sfdc0Or1(original15.substring(6,7)) +
  sfdc0Or1(original15.substring(5,6));

  Piece3 = sfdc0Or1(original15.substring(14,15)) +
  sfdc0Or1(original15.substring(13,14)) +
  sfdc0Or1(original15.substring(12,13)) +
  sfdc0Or1(original15.substring(11,12)) +
  sfdc0Or1(original15.substring(10,11));

  return (original15 + lookup5bin.get(Piece1) + lookup5bin.get(Piece2) + lookup5bin.get(Piece3));

}

private static String sfdc0Or1 (String charX) {
  // This method accepts a one-char string and returns ‘1’ if it’s between A and Z, otherwise ‘0’.

  if (charX.compareTo(‘A’) >= 0 && charX.compareTo(‘A’) <= 25 && charX.compareTo(‘Z’) >= -25 && charX.compareTo(‘Z’) <= 0)
    return ‘1’;
    return ‘0’;
}

public static testMethod void Test15_to_18_a() {
  // For the test methods, I’ve taken three values where I manually calculated the additional 3 chars expected

  String orig1 = ‘001A0000006Vm9r’;
  System.AssertEquals(orig1+’IAC’,sfdc15To18(orig1));

  String orig2 = ‘003A0000005QB3A’;
  System.AssertEquals(orig2+’IAW’,sfdc15To18(orig2));

  String orig3 = ‘003A0000008qb1s’;
  System.AssertEquals(orig3+’IAA’,sfdc15To18(orig3));

}

The code is made up of one main method, sfdc15To18. I’m not going to go into too much detail as I’ve already described the process above, and I don’t think the code is too hard to follow. There is a comment for each method, that should be sufficient. It will give you 100% code coverage and no failures.

One final thing remains, I just need to show you how to use this method in an Apex trigger to keep a custom field updated with the 18-character value. The trigger is quite simple. First, create a new custom 18-character text field to hold the value (there is no reason to add this field to a page layout, unless you really want to):

Here is the trigger code (on the Account object) and the associated Utility class method:

trigger AccountID18 on Account (after insert) {
  // This trigger must be after insert, as no ID values are available before insert.
  // This means we must update the trigger records explicitly as a batch.
  // One other problem, because the system locks the records, they must be updated in an
  // asynchronous (@future) method. We will pass a list of IDs to an @future method.
  // You will need to write your own test method code for this code.

  List lstId = new List();
  for (Account iAcc : Trigger.new) {
    lstId.add(iAcc.Id);
  }
  Utility.updateAccountId18(lstId);

}

…and the associated Utility class method…

@future
public static void updateAccountId18(List IDsToUpdate) {

  List lstAcc = new List();
  for (Id iID : IDsToUpdate) {
    Account wrkAcc = new Account(Id = iID, ID18__c = Utility.sfdc15To18(String.valueOf(iID)));
    lstAcc.add(wrkAcc);
  }
  update lstAcc;
}