• ErikMittmeyer
• NEWBIE
• 35 Points
• Member since 2010

• Chatter
Feed
• 1
• 6
• 0
Likes Given
• 2
Questions
• 3
Replies

I posted an according formula that actually fits into a formula field, but unfortunately submitted it to the wrong board: http://community.salesforce.com/t5/Apex-Code-Development/Formula-Field-calculating-the-18-digit-ID-from-the-15-digit-ID/td-p/189848.

Hope this helps,
Erik

Simply put the following formula into a formula field of type "Text":

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

This is an optimized version of the code example "BPS: 18 digit external ID within the UI" you can find on the Online Help, but unlike the example provided there, it is small enough to fit into a formula field. Also, this code doesn't contain the error found in the online help example, where the second character byte equivalent of the first 5-character chunk was "4" instead of "2". I'd be more than happy if someone could come up with an even shorter version of this formula...Still, I hope you can use this.

Cheers,

Erik Mittmeyer

Simply put the following formula into a formula field of type "Text":

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

This is an optimized version of the code example "BPS: 18 digit external ID within the UI" you can find on the Online Help, but unlike the example provided there, it is small enough to fit into a formula field. Also, this code doesn't contain the error found in the online help example, where the second character byte equivalent of the first 5-character chunk was "4" instead of "2". I'd be more than happy if someone could come up with an even shorter version of this formula...Still, I hope you can use this.

Cheers,

Erik Mittmeyer

I posted an according formula that actually fits into a formula field, but unfortunately submitted it to the wrong board: http://community.salesforce.com/t5/Apex-Code-Development/Formula-Field-calculating-the-18-digit-ID-from-the-15-digit-ID/td-p/189848.

Hope this helps,
Erik

Hey guys,

needed a way to convert from a 15 digit ID to an 18 digit one.

Found the javascript version for just such an action here: http://community.salesforce.com/sforce/board/message?board.id=general_development&message.id=13148

tried to make my own apex version but the last digit keeps coming out wrong.  Any Ideas?

Thank's

String id = '001Q0000002NXad'; //a sample id

String suffix = '';

for(integer i=0;i<3;i++){

Integer flags = 0;

for(integer j=0;j<5;j++){
String c = id.substring(i*5+j,i*5+j+1);

if(c >= 'A' && c <='Z'){

flags += 1 << j;
}
}

if (flags <= 25) {

suffix += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.substring(flags,flags+1);

}else suffix += '012345'.substring(flags-26,flags-26+1);
}

System.debug('15-DIGIT:'+Id);
System.debug('18-DIGIT:'+Id + suffix);

Message Edited by astro on 05-01-2009 10:04 PM
Message Edited by astro on 05-01-2009 10:10 PM
• May 01, 2009
• Like
• 0

Hi, I am having trouble about the mapping the correct record ID. I got a 18 character case-insensitive id field value for account by using Table query, but when I want to use this 18 character to update a field in a record in a custom object (purchase requisition), it will only pick the first 15 character and update the record with a wrong account name.
How to correct this?
Thanks.