[scripting block] Checkbox filed return null using function getCellValue()

[Malo_Richard1]

Basicly everything is in the title.
I have a checkbox filed that return null instead of false when not checked.
Is that some normal behaviour ? am i missing something ?

Here are some screenshots

[andywingrave · Accepted solution]

Technically it is null if it’s a checkmark, because there is no value. Airtable doesn’t auto-populate fields. If you want the value false, i think you’re best off using a Single Select “True”/“False” Field

[Bill_French]

Malo_Richard1:

Is that some normal behaviour ? am i missing something ?

Actually, it is expected behaviour, although, I have considered this design choice in the coin flip category.

One might argue - rather successfully perhaps - that based on data type, it would be proper for the API to return a false value when any binary select field lacks a deliberate selection choice. The nuance of the single selector field is such that lacking any overt selection, the value of the field is obviously contra (i.e., false). @andywingrave suggests this is not the case, but I would have bet a bag of Krispy Kream doughnuts that this would return a false value (I think I’m wrong about that and would be buying doughnuts).

In any case, in a multi-select field, there is a collection of possible choices and the abstain option. One might add “None” (as I do in many cases), but the difference between no overt selection (null) and “None” are meaningful, right?

Furthermore, given a multi-select (which returns a string I believe) it wouldn’t be appropriate to return a boolean FALSE, right? Or a string “FALSE” either. As such, NULL seems to be the right behaviour. But I believe all select fields should behave consistently - single or multi-selects should return NULL if nothing has been indicated by the user or any automated process.

Just sayin’ …

UPDATE - and through all those words, I missed the true point of this discussion - the checkbox data type. :expressionless: I need more coffee I think.

[JonathanBowen]

Hi @Malo_Richard1 - not sure what you are aiming for with your script, but there’s a few workarounds/solutions for this. This is my data:

        {
            "id": "rec63vgI5YJls18d6",
            "fields": {
                "Field 3": "Two",
                "Field 2": "One",
                "Field 4": "Some text (One) Two",
                "Checkbox": true,
                "Check T/F": 1
            },
            "createdTime": "2020-05-26T22:00:56.000Z"
        },
        {
            "id": "recDpotmtJEVqJoqk",
            "fields": {
                "Field 3": "Four",
                "Field 2": " Three",
                "Field 4": "Some text ( Three) Four",
                "Check T/F": 0
            },
            "createdTime": "2020-05-26T22:00:56.000Z"
        },

In the second record, Checkbox isn’t even present (as you are saying), but I can still test for it (or more specifically, the lack of it) with:

let table = base.getTable('Table 4');
let query = await table.selectRecordsAsync();

for (let record of query.records) {
    if(!record.getCellValue('Checkbox')){
        console.log(record)
    }
}

If you want something a bit more obvious, you could create a formula field based on the checkbox, maybe something like:

IF(Checkbox, TRUE(), FALSE())

You can see the results of this above in the field “Check T/F”, so even when Checkbox isn’t present at all, the Check T/F returns a value rather than null/not present

JB

[kuovonne]

andywingrave:

If you want the value false, i think you’re best off using a Single Select “True”/“False” Field

While this method has a certain aesthetic to it, it has some compications.

• Single select fields don’t take a default value. A checkbox has a default setting of null, which is falsy/false.
• Single select fields with values return strings, so a value of “false” would return a string which is truthy/true. Thus, this would require careful coding.
• Single select fields can be blank/null, which is a falsy/false value, which might not be the intended result.

[kuovonne]

Bill.French:

…it would be proper for the API to return a false value when any binary select field lacks a deliberate selection choice. … I would have bet a bag of Krispy Kream doughnuts that this would return a false value (I think I’m wrong about that and would be buying doughnuts).

In JavaScript, “being false” or returning “false” have many interpretations. There is the value false and there are several falsy values: empty string, 0, false, undefined, not a number, and empty array.

So, any field that lacks a deliberate selection choice does return a falsy value of null. No field returns a value that is equal to and the same data type of false.

[kuovonne]

@Malo_Richard1

Welcome to the Airtable community!

You happened to find a topic that was interesting to several long-time members of this community, and we sometimes get carried away in conversation.

I hope that you could read through the text and get your answer: what you are seeing is expected behavior.

If you have more questions, feel free to ask. If your question is answered, could you please mark one of the posts as solution.

[andywingrave]

kuovonne:

While this method has a certain aesthetic to it, it has some complications.

I know, which is why i put them in inverted commas (to hopefully highlight that they’d be string values) - but - good catch and thanks for the flag.

As a PM I’ve literally seen these types of decisions take developers weeks to fight amongst themselves about what the pros and cons are and what the established best practice is.

What actually fills me with joy is when something works - Which Airtable does. For me, I see exactly why they did it this way (at least I think) - It allows immediate cross compatibility with CSVs, makes the process of converting fields simple, and the end result -whether null or false, really does not impact me as a developer - I just need to know how to interpret the result.

I’m glad the coin flip landed on null

:crazy_face:

[Bill_French]

andywingrave:

I’m glad the coin flip landed on null

The equivalent of splitting a bet on 0/00 at the roulette table.

[Malo_Richard1]

Thank you for this suggestion i will try this as it feels the best alternative.
The thing is I import my data form mongo db database. while importing my bool I set the filed value to true or false, and when i want to push the data back to my db all my false moved to null, which is annoying.
@Bill.French
I think this would be a interesting to offer this as a format choice (just like dates field offer multiple format) false or null