Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

15 Digit to 18 Digit Conversion formula

pbaba
7 - Meteor

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.

 

6 REPLIES 6
joshuaburkhow
ACE Emeritus
ACE Emeritus

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

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
StephV
Alteryx Alumni (Retired)

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,

Steph Vitale-Havreng
pbaba
7 - Meteor

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
7 - Meteor

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

 

5 DigitCorrect 18 DigitFormula (incorrect 18 digit)
00U4u00000n5Adm00U4u00000n5AdmEAE00U4u00000n5AdmEAA
00U4u00000mUAds00U4u00000mUAdsEAG00U4u00000mUAdsEAC
00U4u00000mQw7A00U4u00000mQw7AEAS00U4u00000mQw7AEAC
00U0g00000m9AsW00U0g00000m9AsWEAU00U0g00000m9AsWEAQ
danilang
19 - Altair
19 - Altair

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

r.png

 

 

 

Dan   

 

 

pbaba
7 - Meteor

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

Labels