I know how to test for the presence or absence of a value in another field, e.g.
IF( MiddleName != BLANK()....
But it does not seem to work with a field that should return a number result, when the parameter being fed to the formula is empty. Here’s what I think is the standard Airtable formula for an Age calculation:
Works great so long as there is a date in the DOB field. If DOB is empty, on the other hand, this formula returns “NaN” (“Not a Number”). That’s ugly. So I tried
But get the same result. Apparently BLANK() isn’t a number value. This wouldn’t be a problem in FileMaker, but if it were, I could coerce the (empty) result to a (non) value of type Number. In FileMaker these are both valid expressions:
But Airtable doesn’t have a way to do this does it? Something like
Is there another way to get rid of the “NaN” result in my Age fields? (BTW this seems related to the issues with BLANK() discusssed in this long thread:
It works — gets rid of “NaN” — but I have to say that this strikes as very odd. I kind of get that Airtable doesn’t regard BLANK() as a number value. But why does it accept (an empty) date value as a valid result?
And unfortunately THIS leaves the Age field empty if the age < 1. So I need to tweak that a little more. But at least I’ve gotten rid of the “NaN” result.
I’ve gotten pretty used to it now but BLANK() still seems to me a not-totally satisfactory function…
Thanks for response, Kuovonne, and for reminding me that I can just use
To answer your question, I guess I have to say that, in the base I’m working on now, that’s a hard one. As you can see in my last post (right after yours) I decided to go with “0”. As a practical matter in this base, for this client, the age of a child who is not yet 1 is not worth greater precision. In another base, I suppose I could either create a text calc that returns something like “6 months” or a number result that returns “0.5”. But for right now, getting zero is good enough.
To fix that, and get a 0 into the Age field for that child born on 4/3/22, I have to put quotes around the “0”.
P.S. And by the way, once I add the quotes around “0” and get the 0 result I want, the Age column can be sorted on properly: blank fields first, 0 next, then 1, etc. There is something a little goofy about how Airtable understands certain data types.
And final comment. Implemented Kuovonne’s suggestion about avoiding BLANK() I edited formula to read like this now:
And this works, too. But with both of my recent calculations (my original one and the one following Kuovonne’s suggestion), I notice something interesting: the column values are now left aligned rather than right aligned the way number values usually are. That suggests Airtable thinks these are text values.
But they still sort as if they were numbers, not just numerals. In many other platforms I’ve worked with, if you have a bunch of numerals in a Text field, they sort as if they were letters, so you get results like this:
But Airtable is correctly sorting these values like this:
Odd. I may go back to the basic formula for age and live with the “NaN” results. At least it will alert my users to the absence of a DOB value, which is supposed to be required. I could fix that if Airtable supported field-level validation, but that’s another topic for another day. :slightly_smiling_face: