15 Digit to 18 Digit Conversion formula

[pbaba]

Looking for some help with a formula to convert the Salesforce 15 digit ID to an 18 digit ID. The formula that was originally posted in the community does not work correctly and creates an error for some of the conversions on the 18th digit. Any assistance is greatly appreciated.

[joshuaburkhow]

Is this one you say isn't working? https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Converting-15-digit-salesforce-id-to-1...

[StephV]

Hi @pbaba ,

Thank you for your question to the Community!

I moved your post to the Designer Discussions forum, where you can ask anything technical about Designer. 

Does your question got an answer with this post like @joshuaburkhow asked? 

This article "Q&A: Where Do I Post My Questions?" should also help you out. 

Let us know how it goes!

Cheers,

[pbaba]

Yes. It works for most but in some scenarios the last (18th digit in the converted ID is incorrect). I looked at this a few times but cannot figure out why.

[pbaba]

Here is a sample of the ones that are not converted correctly:

5 Digit	Correct 18 Digit	Formula (incorrect 18 digit)
00U4u00000n5Adm	00U4u00000n5AdmEAE	00U4u00000n5AdmEAA
00U4u00000mUAds	00U4u00000mUAdsEAG	00U4u00000mUAdsEAC
00U4u00000mQw7A	00U4u00000mQw7AEAS	00U4u00000mQw7AEAC
00U0g00000m9AsW	00U0g00000m9AsWEAU	00U0g00000m9AsWEAQ

[danilang · Accepted solution]

Hi @pbaba 

The funky formula that you're using has an error in it...well actually 15 errors.  The > near the end of each the lines needs to be replaced with >=  

iif(FindString("ABCDEFGHIJKLMNOPQRSTUVWXYZ", substring([5 Digit],0,1))>0,1,0)

becomes

iif(FindString("ABCDEFGHIJKLMNOPQRSTUVWXYZ", substring([5 Digit],0,1))>=0,1,0)

What each of these lines is doing is checking to see if the character at that position is a Letter, "A-Z".  If it is, then do some binary math based on the character position.  Each group of 5 characters is summed and used as an offset into the ABC...345 string, to give each of the 3 new characters.

The problem is that FindString returns -1 if the value isn't found.  Since Alteryx strings are enumerated starting at character position 0, the letter "A" in the each of the last group of 5 characters is missed, so the total for the last digit is off. 

The formulas were probably copied over from Excel, where the first character in a string is numbered 1 and not 0

The "new" column is the output from the attached workflow

Dan   

[pbaba]

@danilang Thank you!!! This is the fix that I was looking for. Much appreciated!!