15 or 18 Character IDs in Salesforce.com – Do you know how useful unique IDs are to your development effort?

By Alan Shanahan – Principal Technical Architect for Astadia.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:

• 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<String,String> lookup5bin = new Map<String,String>();
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<Id> lstId = new List<Id>();
for (Account iAcc : Trigger.new) {
lstId.add(iAcc.Id);
}
Utility.updateAccountId18(lstId);

}


…and the associated Utility class method…


@future
public static void updateAccountId18(List<Id> IDsToUpdate) {

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

}


You can now go ahead and generate reports using the new 18-character ID fields. If you Export them to Excel you’ll have no problems using VLOOKUP (or similar) functions.

One minor point I should mention: @future methods may not always run immediately (but typically do), and in extreme cases there may be a few minutes wait before they update your data. Bear this in mind when you are testing!

Alternatives…

There are alternative ways of doing the same thing. You could install the Excel Connector:

http://www.salesforce.com/community/crm-best-practices/administrators/data-management/data-tools/excel-connector.jsp

Follow the instructions to set this up and you now have a new inbuilt Excel function called FIXID available. You can enter a formula such as:

=FIXID(B2)

… and this will give you an 18-char ID using the same algorithm. If you have problems installing this (or you just don’t want to), and you have some Excel/VBA expertise, you could add this code to your Excel installation:


Function FixID(InID As String) As String
If Len(InID) = 18 Then
FixID = InID
Exit Function
End If
Dim InChars As String, InI As Integer, InUpper As String
Dim InCnt As Integer
InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"
InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

InCnt = 0
For InI = 15 To 1 Step -1
InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare))
If InI Mod 5 = 1 Then
FixID = Mid(InChars, InCnt + 1, 1) + FixID
InCnt = 0
End If
Next InI
FixID = InID + FixID
End Function


Or if you are working with 15-char Ids in a Microsoft SQL Server database, you can find a T-SQL function at this address:

http://joelmansford.wordpress.com/2010/01/21/function-to-convert-15char-salesforce-com-ids-to-18char-ones-in-t-sql-sql-server/

And Ron Hess, Salesforce Evangelist, has posted the code for a JavaScript equivalent function here:

http://community.salesforce.com/t5/General-Development/display-18-character-ID-in-Page-Layout/m-p/49924

The very best of luck!

Follow me/AstadiaEMEA on Twitter:

http://www.twitter.com/AstadiaAShan
http://www.twitter.com/AstadiaEMEA