- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 01, 2017 01:34 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 01, 2017 01:50 AM
BTW: the workaround I use…
IF(LEN({Inventory Change} & "") = 0, TRUE(), FALSE())
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 04, 2017 01:29 AM
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().
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 15, 2019 01:40 AM
@Danielle - Nice that this has been moved to another forum, but it is still a bug / serious inconsistency.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 28, 2020 04:30 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 28, 2020 09:41 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 28, 2020 11:44 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 29, 2020 06:51 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 01, 2020 10:23 AM
Sure. I meant it in the context of the post.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 09, 2021 12:37 PM
Sending virtual hug for COUNTA (). My vote talley’s now work beautifully. Thank you.