Blank() & Zero problem

[Tuur]

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.

[Tuur]

BTW: the workaround I use…

IF(LEN({Inventory Change} & "") = 0, TRUE(), FALSE())

[Tuur]

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().

[Tuur]

@Danielle - Nice that this has been moved to another forum, but it is still a bug / serious inconsistency.

[Sean_Dawson]

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

Differentiating between blank and zero Product Suggestions

For now, you can use COUNTA(cell) which returns 1 when a cell is non-blank (including zero) and 0 if it’s blank. We may in the future consider adding an IS_BLANK() function since, as you pointed out, cell = BLANK() isn’t always equivalent and the COUNTA workaround isn’t very obvious.

[Justin_Barrett]

Sean_Dawson:

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.

[Tuur]

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.

[Justin_Barrett]

Tuur:

let’s be clear that these are all workarounds

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.

Tuur:

Let’s not risk closing a thread because someone thinks it’s been solved.

That I agree with 100%. Comparing against BLANK() still produces inconsistent results, and I’m surprised that it hasn’t yet been addressed.

[Tuur]

Justin_Barrett:

I don’t see the technique I mentioned as a workaround. It’s consistent with how many programming languages look at strings…

Sure. I meant it in the context of the post.

[Jen_Pennington]

Sending virtual hug for COUNTA (). My vote talley’s now work beautifully. Thank you.