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

Advertisements

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


  1. 1 Joel Mansford June 21, 2010 at 10:36 pm

    Hi,
    Good write-up. I didn’t know Ron Hess had written that JS example already – certainly would have saved me some time a few months back.
    I persuaded David Padbury to knock up the Javascript example for a client piece where we needed Sales Reps to reference the Opportunity on some paper forms. As in previous threads we initially had a problem that only the 15char ID was exposed and being case-sensitive was very dangerous.
    We therefore implemented a JS 15char to 18char example and proceded.
    However we again started to see inaccuracies in the data coming through. Upon further investigation it turned out that a lower-case ‘l’ is indistinguishable from an upper case ‘i’ in the default font!
    So after all that we switched to a custom numeric incrementing ID anyway!

    I hope someone finds my T-SQL code (reference in the article) useful – the above is why it was written.

    • 2 Alan Shanahan June 22, 2010 at 10:57 am

      Thanks for your comment and interest, Joel. Based on your experiences (and mine), I can only conclude that it would be good practice to ensure that all of our future development precludes the possibility of using case-sensitive data; certainly that strategy would eliminate one headache or “gotcha”.

      There is another technique (or trick) I have used in the past in Excel to eliminate the problem of case-sensitivity in VLOOKUPs: it involves appending the CODE() value of the last 4 characters of the ID in both the search value and lookup table. This worked well for existing datasets, and, though not a perfect solution, it can be very safe under the right conditions – indeed a good, quick workaround for the problem. If anyone would like specifics on this, I’m happy to provide the details.

      Thanks
      Alan

  2. 3 Richard Tuttle June 23, 2010 at 5:18 pm

    Thanks for the very in depth explanation of the 18 character Id. I’ve always wondered what the extra 3 were used for. One thing I have found that is interesting is if you are passing Ids to the Salesforce standard UI for sending/replying to an e-mail you have to pass the 15 character Id for the Ids otherwise it’ll not copy the proper case id reference into the e-mail and will cause the creation of a new e-mail and case. I assume this is the same if e-mailing a lead, but I deal only with the customer support side currently.

    • 4 astadiaemea June 24, 2010 at 5:08 pm

      Hi Richard,

      I assume that, when you refer to Case ID reference, you are talking about the [ref:XXXXXXXX.YYYYYYYYY:ref] part of the subject, added automatically by Email To Case. The XXXX part of this seems to be a cut-down version of the (15-char) org ID, similarly, the YYYY part looks like a (15-char) Case ID with extraneous zeroes removed. I think it’s a safe bet that these are case-sensitive IDs also.

      Sounds like you’ve found out by experimentation that sending the additional 3 chars of the ID causes the internal SFDC matching algorithm to fail. This wouldn’t surprise me, in fact I can’t think why SFDC would build in this sort of tolerance, simply because it would be very much an edge use case.

      What are you doing that let you to discover this? Are you building some programming around the email case ID?

      Also, I’m not sure what you mean by “e-mailing a lead”, are you referring in some way to Web-to-Lead?

      Thanks for your comments
      Alan

      • 5 Richard Tuttle June 24, 2010 at 5:29 pm

        Hello Alan,

        Yes I discovered this by passing from my case management application into the email application. This isn’t a supported method by SFDC, but I was between that and having to write an entire interface for sending reply e-mails on cases. I will have to tackle a custom interface in my app eventually.

        My comment about leads is reference to sending e-mails to leads. My knowledge of the sales side is little, for now. I thought there was a sales object that functioned similar to case in regards to the subject reference Id; I had assumed it was lead.

        I appreciate the response and the great blog. I love how active Astadia is in the forcedotcom community.

        Thanks,
        Richard

  3. 6 astadiaemea June 24, 2010 at 5:49 pm

    Thank you Richard,

    If you have Apex experience, you could build your own email handler in SFDC. You could then construct your encoding/decoding rules for these IDs: http://www.salesforce.com/us/developer/docs/cookbook/Content/messaging_complete_code_example.htm#messaging_complete_code_example

    …assuming you didn’t know about that before.

    Your comments and questions are very welcome!

    Alan

    • 7 Richard Tuttle June 24, 2010 at 5:59 pm

      Thanks for that Alan.

      I haven’t seen that specific email recipe, but I’ve seen a few. The one thing I don’t understand is when using a template, but also allowing the employee sending the e-mail to modify the body. From what I have found this doesn’t bring the merge fields in properly. Any ideas on that?

      Thanks,
      Richard

  4. 9 Richard Runds June 26, 2010 at 9:28 am

    Hi,

    Interesting article. Working with integration, as an integration architect, I have worked on numerous projects integrating Salesforce. IDs are sometimes great, for example to determine object type by inspecting first 3 characters of the ID.

    However, in my experience it is common to overlook the integration aspects when implementing and customising the Salesforce environment. For the integrator, IDs are often more of a problem than an aid. I have seen so many implementations where custom objects, fields and types are represented as IDs only via the Salesforce API. Often there is an ID instead of, what seems to me to be, an enumeration. This not only complicates the integration, it also requires additional API calls to retrieve the objects.

    Thanks,

    Richard

    • 10 astadiaemea June 28, 2010 at 9:57 am

      Hi Richard,

      Thanks for your comments and for your interest in the topic. Fair points, well made.

      In my experience, integrations can be built in a very robust way (and this is the important bit) once the integrator understands how Salesforce.com works. IDs never have to be a problem, especially since it’s possible to discern object types in a generic way with the metadata API, particularly when it comes to custom objects.

      Also, knowledge of unique External ID fields and the Upsert operation invariably solves the problem of the “query-insert-or-update” cycle and reduce that dated idiom to a single operation, rather than a set of “data retrieval, comparison and insert/update” steps.

      Most integration tools have the capability of using SOAP-based web services and can fully implement these features. Many come with the Upsert operation packaged in a specific Salesforce.com connector, thus reducing development times considerably.

      Thank you
      Alan

  5. 11 Manu June 28, 2010 at 9:54 am

    Hi Alan,

    Great post as before and I just had to leave a comment this time 🙂 ! So with all the talk of the complexities behind Ids, the question that would stand out, I guess, would be…which is the best one to use in which case… unless I’ve missed the same in the post itself? Specifically, I’m interested in integration scenarios where we’re inserting records in an external system and that can store the SF Id as a foreign key, would it be ideal to store the 15-char one or the 18-char Id? I know we could use a External Id field, but just wondering for scenarios where the Id could be stored to refer back, which would you propose be used?

  6. 12 astadiaemea June 28, 2010 at 11:23 am

    Hi Manu,

    The answer must be to use 18-char IDs in all cases. It’s a superset of the 15-char value and has the distinct advantage of being case-insensitive – in other words, it will always work, even in situations where external systems cannot cope with case-sensitivity.

    I cannot think of a time when using 15-character ID values would be preferable.

    Good question, thanks for that.
    Alan

  7. 13 David Engel July 6, 2010 at 6:28 am

    Here’s an alternative to the Microsoft Excel fixid function–it’s a Google Spreadsheet Script.

    http://www.engeljournal.com/tools/convert-15-to-18-digit-salesforce-ids-with-google-spreadsheets/

    Google spreadsheets is free. The script is open source and free. This, in my opinion, is a much better alternative for converting 18 digit salesforce ids.

    • 14 astadiaemea July 6, 2010 at 9:35 am

      Hi David,

      Great, yet another alternative and it’s totally cloud-based. It does differ slightly in that it converts existing ID data values inline, but it clearly works and is a very effective solution.

      Thanks for that.
      Alan


  1. 1 15 or 18 Character IDs in Salesforce.com – Do you know how useful unique IDs are to your development effort? (via Astadia EMEA’s Blog) | Integración de servicios de Cloud Computing Trackback on June 21, 2010 at 6:21 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 28 other followers

Astadia EMEA on Twitter

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Our Authors

Previous Blog Posts


%d bloggers like this: