Skip to main content

I am encountering something strange- a checkbox brought onto my sheet from another sheet in the same airtable book is being recognized by formulas when it is checked, but is not being recognized when unchecked. The field is called 'Exclude" and this formula works:



IF((Exclude),1,0)


and it returns a ‘1’ for all records where exclude is checked



However this formula does NOT work- it’s returning a 0 for every record


IF(NOT(Exclude),1,0)



I tested the exact above on a boolean checkbox that is a field on the sheet itself (eg, not brought in by lookup), and the IF and the IF(NOT( both work as expected



Is there something about the way a lookup formula treats un-checked boolean values that is not returning valid data? when accessing the same field via an API I get FALSE for the unchecked rows, however in airtable’s own formulas, it’s not recognizing it as unchecked .



Stumped, and need some help,


thx!

Hi @nathaniel_pulsifer



Well, this doesn’t exactly address the issue or bug you are encountering, per se, but a possible workaround for you…



Your first formula is:



IF({Exclude}, 1, 0)


You could make your second formula:



IF({Exclude}, 0, 1)


Just reverse the output - this will have the same effect as:



IF(NOT({Exclude}), 1, 0)

Hi @nathaniel_pulsifer



Well, this doesn’t exactly address the issue or bug you are encountering, per se, but a possible workaround for you…



Your first formula is:



IF({Exclude}, 1, 0)


You could make your second formula:



IF({Exclude}, 0, 1)


Just reverse the output - this will have the same effect as:



IF(NOT({Exclude}), 1, 0)

thanks, the formula is very simplistic for purposes of this post. The actual use case is a more involved IF(AND( that is not working.



The formula i want to use is more like this


IF(AND(localCheckbox1,NOT(localCheckbox2),NOT(Exclude)),My Desired Result ,0)



The two local checkboxes behave just fine, but when i added the NOT(Exclude), the formula returns only 0’s. that led me to isolate the issue which i believe to be invalid data being returned by the lookup formula for un-checked values in the ‘exclude’ lookup field. It recognizes checked = TRUE, but it’s not recognizing unchecked values

thanks, the formula is very simplistic for purposes of this post. The actual use case is a more involved IF(AND( that is not working.



The formula i want to use is more like this


IF(AND(localCheckbox1,NOT(localCheckbox2),NOT(Exclude)),My Desired Result ,0)



The two local checkboxes behave just fine, but when i added the NOT(Exclude), the formula returns only 0’s. that led me to isolate the issue which i believe to be invalid data being returned by the lookup formula for un-checked values in the ‘exclude’ lookup field. It recognizes checked = TRUE, but it’s not recognizing unchecked values


I’ve created a clean base with only this issue isolated if anyone wants to look at it- not sure how to make a base public to post it here, however.



But this does appear to be a limitation of airtable and the lookup formula however- checkbox values that are checked are coming in as valid data and available in formulas, but the checkbox unchecked is not coming thru as a valid and useable datapoint… unless i’m doing something wrong. Any suggestions are appreciated

thanks, the formula is very simplistic for purposes of this post. The actual use case is a more involved IF(AND( that is not working.



The formula i want to use is more like this


IF(AND(localCheckbox1,NOT(localCheckbox2),NOT(Exclude)),My Desired Result ,0)



The two local checkboxes behave just fine, but when i added the NOT(Exclude), the formula returns only 0’s. that led me to isolate the issue which i believe to be invalid data being returned by the lookup formula for un-checked values in the ‘exclude’ lookup field. It recognizes checked = TRUE, but it’s not recognizing unchecked values




Honestly I’ve never used a three-some condition, so I won’t question the syntaxis.


Sometimes interpreters may have trouble in handling complex, compound conditions.


You could try de-composing it this way:


IF(AND(AND(localCheckbox1,NOT(localCheckbox2)),NOT(Exclude)),My Desired Result ,0)



Honestly I’ve never used a three-some condition, so I won’t question the syntaxis.


Sometimes interpreters may have trouble in handling complex, compound conditions.


You could try de-composing it this way:


IF(AND(AND(localCheckbox1,NOT(localCheckbox2)),NOT(Exclude)),My Desired Result ,0)


the point is, airtable does not recognize NOT(Exclude) where Exclude is a checkbox brought in via lookup. It DOES work when Exclude is a checkbox on the sheet itself



This appears to be an airtable problem where a checkbox = true or false on one sheet, but that same checkbox is true or ??? (null? Blank?) when it’s viewed via lookup formula.



Still not getting this to work…

the point is, airtable does not recognize NOT(Exclude) where Exclude is a checkbox brought in via lookup. It DOES work when Exclude is a checkbox on the sheet itself



This appears to be an airtable problem where a checkbox = true or false on one sheet, but that same checkbox is true or ??? (null? Blank?) when it’s viewed via lookup formula.



Still not getting this to work…


anyone know how to post a publicly accessible link to a base? i put the scenario into a base showing why a lookup checkbox does not respond to the NOT formula while a local sheet checkbox DOES respond to the NOT formula. Pretty clear, the lookup formula doesn’t handle an un-checked value properly

the point is, airtable does not recognize NOT(Exclude) where Exclude is a checkbox brought in via lookup. It DOES work when Exclude is a checkbox on the sheet itself



This appears to be an airtable problem where a checkbox = true or false on one sheet, but that same checkbox is true or ??? (null? Blank?) when it’s viewed via lookup formula.



Still not getting this to work…


I get your point, but if your supposition is right then there’s no purpose in inspecting your test table just to see the phenomenon in action.



Did you actually try the formula I suggested?



Also, you could try a form like {Exclude}=False (or similar accepted syntax) instead of NOT(Exclude) which seems to be not resolved by the linked field.

I get your point, but if your supposition is right then there’s no purpose in inspecting your test table just to see the phenomenon in action.



Did you actually try the formula I suggested?



Also, you could try a form like {Exclude}=False (or similar accepted syntax) instead of NOT(Exclude) which seems to be not resolved by the linked field.


thx for your suggestions. There was never a situation where NOT(Exclude) worked



I resolved the scenario in a different way and had to use -IF(Exclude = BLANK(),0,{my desired outcome},0)


as t his was the only way the lack of a checkbox was recognized

Reply


Terms & Conditions