Importing checkbox values into Salesforce.com

I came across an interesting problem today, and as it took me a little time to solve it, I thought I’d blog about it.

I was using the Salesforce custom object import wizard, a nifty little tool that allows you to import data into Salesforce. All was going well, till I tried to import some checkbox data.

My custom object had a field called “Billable”, which was defined as a checkbox. My source data had a drop down value that either held “Billable” or “Non-Billable” as a value. The custom object also had a validation rule that meant that if the checkbox was unchecked (i.e. Non-Billable), then the bill rate had to be zero.

My first kick at the can was to use Excel to translate the drop-down into a boolean. I created a formula field, like so:

=IF(J2="Billable",TRUE, FALSE)

After doing a test import of five records, I got the following errors, due to the validation rule failing:

Assignments marked non-billable may not have a bill rate greater than zero.

So then I Read TFM and realise I was being a complete numpty and that if you’re importing to a checkbox field, then you need to pass in 1′s and zeros (this, despite when you export the same data, it coming back as TRUE or FALSE).

From the documentation:

Checkboxes—To import data into a checkbox field, use 1 for checked values and 0 for unchecked values.

So I changed my Excel Formula to this:

=IF(J2="Billable",1, 0)

Unfortunately, the import triggered exactly the same error!

I disabled the validation rule, and sure enough, the field was being imported as an unchecked (false) value.

My workaround was simple. I took all the formula field cells and copied them to the clipboard. I then pasted them back in place using the Paste Special, Paste Values option, so the cells contained 1′s and zeros instead of the formula. This time the data import succeeded.

I am not sure why the import wizard barfs at formula fields for checkboxes. I use them all the time for other data types – concatenating fields and manipulating data, and this is the first time I’ve seen this problem.

Has anyone else experienced this problem? Let me know if this post helped you!

Luhn Algorithm for Salesforce formula field

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:

Informal explanation

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?