Author’s note: everything I say about checkboxes applies to flags and stars
An interesting thing I’ve noticed is the data type ‘null’ in Smartsheet. If we look at a given checkbox column, all of the checkboxes appear to be unchecked:
Let’s put example data in here. Suppose you are the head of a shipping and logistics branch, and you wanted to make your sheet track whether or not a shipment was sent.
Let’s suppose that in this way, you’re using Delivery Date to represent the date in which you clicked your checkbox. However, at some point, you want to send the checkbox back to no because the driver miscommunicated and accidentally checked off Test 7 when they should have checked off Test 8:
There, we fixed it right?
Not entirely. Look at the column Out for Delivery below, as I change it into Dropdown (Single Select):
I’ve now introduced the value false. Note that Smartsheet is actually doing a favor to you by updating these in plaintext. In fact, true false are protected values in a checkbox column. Whenever you are writing a formula to count the number of ‘true’s and ‘false’s, Smartsheet is doing some data type reconciliation.
In essence, a checkbox truly holds 3 types of data: true, false, and null. Only checkboxes that have never been used hold null. They will generate a checkbox for you visually when you insert any value on any other row-level cell. It is difficult for the user, then, to know whether something was false or null; we had a technical escalation once, as once upon a time, Pivot App was distinguishing between false and null and was erroneously (at least to the end user) producing an incorrect result.
In this way, if you are doing API work, be aware of this difference. It likely does not matter most other times.
When you are checking a checkbox column, note that your formula will return ‘true’ (in logic terms, not Text/Number) if you do [Out For Delivery]7 = 0 within our original screenshot. This is because Smartsheet is also doing a nice thing by allowing you to count 1’s and 0’s within checkboxes.
Thus, null and false map to 0 and true maps to 1. In formulas and sheet-level comparisons, it is equivalent to say [Out For Delivery]7 = true and [Out For Delivery]7 = 1.
Another implication of this data reconciliation aspect of the column is that it reads logical statements. If you wanted to make the checkbox automatically turn “true” when Delivery Date is not blank, you can write a formula that says =[Delivery Date]@row <> "". No =IF() needed!
Note that this formula only really works in the checkbox column, as it produces a logical value. Otherwise, you will get:
After changing the column back into a checkbox and viewing cell history, I see:
In this way, this does NOT present false when you are switching the column back, as the data type is still system true or false. Strangely, if you do our standard technique of checking system values, by doing
=[Out for Delivery]@row + “” in order to convert the data type into text, it says false (as shown in the screenshot below). However, this is true about our null cells as well.
How you CAN tell is when you change the column value back:
