This is a quick and dirty “how to” post about Salesforce.com formula fields.
I was asked by a customer to implement a Luhn Algorithm to provide a check sum against a six digit autonumber in salesforce. For those of you who’ve never heard of a Luhn Algorithm, here’s the explanation from Wikipedia:
The formula verifies a number against its included check digit, which is usually appended to a partial account number to generate the full account number. This account number must pass the following test:
1. Counting from the check digit, which is the rightmost, and moving left, double the value of every second digit.
2. Sum the digits of the products together with the undoubled digits from the original number.
3. If the total ends in 0 (put another way, if the total modulo 10 is congruent to 0), then the number is valid according to the Luhn formula; else it is not valid.
As an illustration, if the account number is 49927398716, it will be validated as follows:
1. Double every second digit, from the rightmost: (1×2) = 2, (8×2) = 16, (3×2) = 6, (2×2) = 4, (9×2) = 18
2. Sum all the individual digits (digits in parentheses are the products from Step 1): 6 + (2) + 7 + (1+6) + 9 + (6) + 7 + (4) + 9 + (1+8) + 4 = 70
3. Take the sum modulo 10: 70 mod 10 = 0; the account number is valid.
The Salesforce object is integrated with a back office system, and the users might need to manually enter this value into the back office system. This was a bit of a pain to do, so I thought I’d share it here for anyone else who needs to do something similar.
I wanted to use a formula field to do this, rather than an Apex trigger. I try to avoid triggers in Salesforce if I don’t need them, as they’re restricted by governor limits. This means the solution below is a little ugly, but it works.
I first figured things out in Excel. Excel formulas are similar to Salesforce formulas, so I realised I could make this work with a combination of the MOD function and the FLOOR function. I worked on several test six digit numbers to ensure that my formula worked for each digit, then finally concatenated the whole thing together into a single Excel formula field.
When I moved my formula over to Salesforce, I realised that the FLOOR function in Excel and Salesforce behaved differently (Excel allows an input parameter to say how many significant digits you are flooring to, Salesforce assumes you are rounding down to the nearest digit), so I needed to rework my Excel formula a little.
One this was done, I took the following steps to get the field up and running in Salesforce.
- Create a custom autonumber field. In this case, it needed to start at > 120,000 to support existing rows in the back office system.
- Create a second formula field to calculate the checksum of the six digit number. The formula should look like this (I’ve tried to break up the lines to make it more readable; the line breaks are not necessary when entering the formula in Salesforce):
MOD(10 - MOD( IF( MOD(VALUE(CustomID__c) ,10)*2 > 9, MOD(MOD(VALUE(CustomID__c) ,10)*2,10) + 1, MOD(VALUE(CustomID__c) ,10)*2 ) + FLOOR(MOD(MOD(VALUE(CustomID__c), 100)/10,10)) + IF( (FLOOR(MOD(VALUE(CustomID__c), 1000)/100)*2)>9, MOD(FLOOR(MOD(VALUE(CustomID__c), 1000)/100)*2,10)+1, (FLOOR(MOD(VALUE(CustomID__c), 1000)/100)*2) ) + FLOOR(MOD(VALUE(CustomID__c), 10000)/1000) + IF( (FLOOR(MOD(VALUE(CustomID__c), 10000)/1000)*2)>9, MOD(FLOOR(MOD(VALUE(CustomID__c), 10000)/1000)*2,10)+1, (FLOOR(MOD(VALUE(CustomID__c), 10000)/1000)*2) ) + FLOOR(MOD(VALUE(CustomID__c), 1000000)/100000) ,10) ,10)
- Create a third formula field that concatenates the two fields together. This field can be passed to the integration and be used as an external Id when updating rows back from the back office system.
That’s it! Ugly, but it works. Does anyone out there have a better and less ugly way of getting this working in a formula field?