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!