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!

A New Start

After three and a half wonderful, interesting, challenging years at ext.IT it is time to move on. I’m now working for Slalom Consulting as the Salesforce.com Solution Architect for the Seattle office.

While it was wonderful to be the big fish in a small pond, I’ve always been interested in how the larger processes work within an organization, and the opportunity to  be able to make a difference outside of just the software delivery cycle, as well as to help build a successful Salesforce.com practice for the Slalom Seattle office was too much of a temptation.

I wish ext.IT and everyone there well and thank them for the opportunities I’ve had and all the things I’ve learned during my time with them.

After focusing on blogging at my company site, I’m going to resume blogging at this site again, so watch this space!