Apex Code Development You need to sign in to do that
Don't have an account?
ErikMittmeyer Formula Field: calculating the 18-digit ID from the 15-digit ID
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
Hi there... this is an extremely good answer... adding a formula field "id18" on most important objects makes a BIG difference when having to face "outside" field matching (for example in access) or "inside" id matching (something we had to face with partner portal integration).
Thank you very much for the Formula Field!
Thanks a lot ..
Solved my issue
Really great work /...
Thank you! That was the easiest one that I've seen yet. It will help tremendously to simply run a report to include either the 15 digit Id, 18 digit Id or both! I have put it on the most commonly used Objects which prevents me from having to do an export from DL each time I want the 18 digit Id!
WOWWWWW !!!!
Perfect..
Great ErikMittmeyer
That's Genius! THANK YOU!!!
This is an excellent post.
This post really cool..
Now a native method is avaliable: CASESAFEID(id), it returns 18 char id.
cheers!
I second the use of CASESAFEID function. I definitly have benefited from the original post when the native function is not around, but this new function also checks to return the null value (or "-" in reports) if an empty value is passed.
This may seems like an odd feature to have as every object must have an id value as a unique identifier. But this is useful when you have a custom field which refers to another object and the value for the custom field is optional. Under that situation, sometimes you want to see the Ids for the value that the custom field is referring to (e.g. a channel partner field on Opportunity referencing Account), this would be important as the original formula will return "AAA" when the custom field is null.
If you still want to use the original formula posted here, you may want to place an IF statement to return null when the id value is blank. I think it is better to get a native function to the dirty job as they will cover any future eventualites if you do not have a specific reason to use the original one.
For those who want to use the formula in an Excel spreadsheet, I modified the syntax to make it work in Excel:
NOTE: In my formula, I didn't use Id. I used the cell where the Id is stored. In this example, that is A2.
=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))
=A2&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1
+IF(CODE(MID(A2,1,1))>64,1,0)*IF(CODE(MID(A2,1,1))<91,1,0)*1
+IF(CODE(MID(A2,2,1))>64,1,0)*IF(CODE(MID(A2,2,1))<91,1,0)*2
+IF(CODE(MID(A2,3,1))>64,1,0)*IF(CODE(MID(A2,3,1))<91,1,0)*4
+IF(CODE(MID(A2,4,1))>64,1,0)*IF(CODE(MID(A2,4,1))<91,1,0)*8
+IF(CODE(MID(A2,5,1))>64,1,0)*IF(CODE(MID(A2,5,1))<91,1,0)*16,1)
&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1
+IF(CODE(MID(A2,6,1))>64,1,0)*IF(CODE(MID(A2,6,1))<91,1,0)*1
+IF(CODE(MID(A2,7,1))>64,1,0)*IF(CODE(MID(A2,7,1))<91,1,0)*2
+IF(CODE(MID(A2,8,1))>64,1,0)*IF(CODE(MID(A2,8,1))<91,1,0)*4
+IF(CODE(MID(A2,9,1))>64,1,0)*IF(CODE(MID(A2,9,1))<91,1,0)*8
+IF(CODE(MID(A2,10,1))>64,1,0)*IF(CODE(MID(A2,10,1))<91,1,0)*16,1)
&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1
+IF(CODE(MID(A2,11,1))>64,1,0)*IF(CODE(MID(A2,11,1))<91,1,0)*1
+IF(CODE(MID(A2,12,1))>64,1,0)*IF(CODE(MID(A2,12,1))<91,1,0)*2
+IF(CODE(MID(A2,13,1))>64,1,0)*IF(CODE(MID(A2,13,1))<91,1,0)*4
+IF(CODE(MID(A2,14,1))>64,1,0)*IF(CODE(MID(A2,14,1))<91,1,0)*8
+IF(CODE(MID(A2,15,1))>64,1,0)*IF(CODE(MID(A2,15,1))<91,1,0)*16,1)
You'll need to replace "A2" with the reference of the cell containing the 15-character ID in your spreadsheet, and strip the newlines out of the text above. Instead of searching a string of capital letters, this formula checks to see if the character's character code (ASCII code) is within the range of capital letters, from 65 - 90, avoiding the need for internal error checking.
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
I needed this in Powershell so I ported deepbluesea VBA, here it is in case anyone needs it:
Function Generate_18CharSFID($SalesForceID) {
$InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"
$InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray() | %{[int][char]$_}
$SalesForceID_Rev = $SalesForceID | %{ -join $_[$_.Length..0] }
$SalesForceID_RevArray = $SalesForceID_Rev.ToCharArray() | %{[int][char]$_}
$Checksum = ""
$InCnt = 0
$pos = 15
foreach ($Char in $SalesForceID_RevArray) {
$InCnt = (2 * $InCnt) + $InUpper.Contains($Char)
if ($pos % 5 -eq 1) {
$Checksum = $InChars.Substring($InCnt,1) + $Checksum
$InCnt = 0
}
$pos = $pos - 1
}
return $SalesForceID + $Checksum
}
I'm sure this code can be optimized but it works...
Question about this: Does this create a UNIQUE id accoss SF (not just HCM)?
We were given an Excel formula from SF that, we were told, would be absolutly unique.
Does this formula do that?
Thanks!
And it's all done for you!
https://chrome.google.com/webstore/detail/sf-15-to-18/cogllpmaoflgaekieefhmglbpgdgmoeg?h1=en