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:
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:
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!