Hi guys,
When I’m using Blank() in a formula (to flag empty fields) it also counts the number ‘0’ as a blank, and the other way around.
So when I have a formula that says {MyNumberField} = 0 it also registers the empty fields as zeroes.
Has this always been the case? At the very least I’d expect the more specific {MyNumberField} = Blank() to neglect ‘0’ fields.
Arthur.
I figured out why I thought it was working before; it does give the correct result when you reverse the argument. In my example:
IF({Inventory Change} != BLANK(), FALSE(), TRUE())
In this case ‘0’ is not equal to BLANK().
@Danielle - Nice that this has been moved to another forum, but it is still a bug / serious inconsistency.
I had this issue and found that:
!=Blank() returns true for empty string columns
!="" returns false for zeros values like ‘0’ and ‘0:00’ time values
So the best solution is to use COUNTA
+21I had this issue and found that:
!=Blank() returns true for empty string columns
!="" returns false for zeros values like ‘0’ and ‘0:00’ time values
So the best solution is to use COUNTA
Perhaps. While COUNTA() is great for number fields, and does technically work for text fields as well, I prefer to use whatever method works best for the field type I’m checking. For example, if I’m checking a text field, there’s no need to compare against anything to see if it’s empty:
IF({Field Name}, true_output, false_output)
The presence of any text in a text field equates to True, while an empty text field equates to false. This also works with other fields that return strings like single select, multiple select, etc.
Perhaps. While COUNTA() is great for number fields, and does technically work for text fields as well, I prefer to use whatever method works best for the field type I’m checking. For example, if I’m checking a text field, there’s no need to compare against anything to see if it’s empty:
IF({Field Name}, true_output, false_output)
The presence of any text in a text field equates to True, while an empty text field equates to false. This also works with other fields that return strings like single select, multiple select, etc.
I use this a lot too, but let’s be clear that these are all workarounds and not solutions @Sean_Dawson. Let’s not risk closing a thread because someone thinks it’s been solved.
Airtable still needs to address this serious inconsistency.
+21I use this a lot too, but let’s be clear that these are all workarounds and not solutions @Sean_Dawson. Let’s not risk closing a thread because someone thinks it’s been solved.
Airtable still needs to address this serious inconsistency.
I don’t see the technique I mentioned as a workaround. It’s consistent with how many programming languages look at strings when used in if() statements: empty strings equate to False, non-empty strings equate to True. In other words, I’m not using that because it works around the issue with BLANK(). I’m using it because of its consistency with how strings are treated in that logical context in programming.
That I agree with 100%. Comparing against BLANK() still produces inconsistent results, and I’m surprised that it hasn’t yet been addressed.
I don’t see the technique I mentioned as a workaround. It’s consistent with how many programming languages look at strings when used in if() statements: empty strings equate to False, non-empty strings equate to True. In other words, I’m not using that because it works around the issue with BLANK(). I’m using it because of its consistency with how strings are treated in that logical context in programming.
That I agree with 100%. Comparing against BLANK() still produces inconsistent results, and I’m surprised that it hasn’t yet been addressed.
Sure. I meant it in the context of the post.
+5Sending virtual hug for COUNTA (). My vote talley’s now work beautifully. Thank you.
currency and number fields do not handle “0” and blank values differently: “field = BLANK()” results in TRUE if blank OR zero “0” ( OR(field = BLANK(), field = 0 ), results in FALSE if checking for “field = 0” even if you enter zero “0” dollars as a value
row1 field = 0: blank = TRUE, field = 0 TRUE
row2 field = empty: blank = TRUE, field = 0 TRUE
Also:
{test number}<.1 evaluates to true when {test number} = 0
{test number}<.1 evaluates to true when {test number} is blank
This is still an issue in 2022. My formula returns the override if its anything greater than zero but ignores if it is zero because it does not recognize zero as a value. And since the rest of the formula relies on heavy math and not just true false logic there doesn’t seem to be a workaround.
F(AND({Expense Approval}=BLANK(),{How is this being paid for?}=“Agent PP”),IF({PP Override}=BLANK(),SUM(IF({Materials/Vendor Cost}=BLANK(),IF({Estimated Material/Vendor Cost}=BLANK(),(Quantity{Estimated Per/Unit Cost}),{Estimated Material/Vendor Cost}),{Materials/Vendor Cost}),IF({Time Spent}=BLANK(),({Estimated Design Time}{External Design Rate}),(((CEILING({Time Spent}/3600)3600)/3600){External Design Rate*}))),{PP Override}))
+29Welcome to the Airtable community!
@Joshua_Pennington think I answered this question for you on the Facebook community.
For others who might find this thread, here is the gist of the solution.
Instead of …
IF( {override field}, {override field}, {original field})
use …
IF( {override field} & "", {override field}, {original field})
This converts the number field into a string. If the number field is truly blank, the resulting string will still be “false”, but if the number field has the value of zero, the resulting string will be “true”.
Welcome to the Airtable community!
@Joshua_Pennington think I answered this question for you on the Facebook community.
For others who might find this thread, here is the gist of the solution.
Instead of …
IF( {override field}, {override field}, {original field})
use …
IF( {override field} & "", {override field}, {original field})
This converts the number field into a string. If the number field is truly blank, the resulting string will still be “false”, but if the number field has the value of zero, the resulting string will be “true”.
Which is basically what I propose in the second post. :slightly_smiling_face:
This is still an issue in 2022. My formula returns the override if its anything greater than zero but ignores if it is zero because it does not recognize zero as a value. And since the rest of the formula relies on heavy math and not just true false logic there doesn’t seem to be a workaround.
F(AND({Expense Approval}=BLANK(),{How is this being paid for?}=“Agent PP”),IF({PP Override}=BLANK(),SUM(IF({Materials/Vendor Cost}=BLANK(),IF({Estimated Material/Vendor Cost}=BLANK(),(Quantity{Estimated Per/Unit Cost}),{Estimated Material/Vendor Cost}),{Materials/Vendor Cost}),IF({Time Spent}=BLANK(),({Estimated Design Time}{External Design Rate}),(((CEILING({Time Spent}/3600)3600)/3600){External Design Rate*}))),{PP Override}))
I’m pretty sure by now we can forget about this behavior ever being changed as it will most likely have way too much impact on current implementations. That still doesn’t make it ‘right’ though.
Welcome to the Airtable community!
@Joshua_Pennington think I answered this question for you on the Facebook community.
For others who might find this thread, here is the gist of the solution.
Instead of …
IF( {override field}, {override field}, {original field})
use …
IF( {override field} & "", {override field}, {original field})
This converts the number field into a string. If the number field is truly blank, the resulting string will still be “false”, but if the number field has the value of zero, the resulting string will be “true”.
Never mind. :slightly_smiling_face: Apparently the additional LEN trick is not needed (anymore?) as casting it as a string works great.
Welcome to the Airtable community!
@Joshua_Pennington think I answered this question for you on the Facebook community.
For others who might find this thread, here is the gist of the solution.
Instead of …
IF( {override field}, {override field}, {original field})
use …
IF( {override field} & "", {override field}, {original field})
This converts the number field into a string. If the number field is truly blank, the resulting string will still be “false”, but if the number field has the value of zero, the resulting string will be “true”.
Yes! This ended up working perfectly. I’m looking forward to learning from you at the Daretable conference.
+4Not sure if needed anymore but my solution is to check if the formula equaled 0 and write 0 as text instead of integer:
IF([formula that could equal 0]=0,"0",[formula that could equal 0])
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.