If my formula evaluates to blank (no result), can I use that field as BLANK() in an IF statement? I can't get this to work

[Burner]

Hi,

So I have a field called {First Name} and {Last Name}. I have a formula field called {Full Name}, which is simply the result of {First Name}&" "&{Last Name}. Pretty simple.

However, there may be cases when the {Full Name} results in blank. For e.g. when I create a new row in the grid, but no First and Last Name have been entered yet. I assumed that the {Full Name} is equal to BLANK() in this case.

I have a primary field {ID}, which I’m using as an ID with the intent that if the {Full Name} is not blank, then evaluate to Autonumber&" "&{Full Name}, so something like “101 John Smith”, “102 Rebecca Jones”, etc. However, I want ID to be a formula such that if the {Full Name} is blank, it evaluates to something like “103 No Name”, instead of just “103”, i.e. I want to see the ID that has no full name explicitly, so I thought a simple IF() formula for the ID field would work.

I tried to write an IF() formula such that if {Full Name} is BLANK(), then formula result should be Autonumber&" “&“No name” (expecting something like “105 No name”) and if {Full Name} is not blank, i.e. the else part of that formula, then simply result to Autonumber&” "&“Full name” to get something like “107 Jamie Freedman”.

Unfortunately, when there is no {First Name} and {Last Name}, i.e. resulting in no {Full Name}, the IF formula in my ID field does not recognize the {Full Name} as BLANK(). Even when I tried testing it out by simplifying to something like IF({Full Name}!=BLANK(),“Not Blank”,“Blank”) it evaluates to “Not blank” even when there’s nothing in the {Full Name} field.

My guess is that Airtable considers a formula field to be non-blank even if it isn’t holding any formula result or value. Is that the case?

Any help appreciated.

Thanks.

[Justin_Barrett · Accepted solution]

Burner:

However, there may be cases when the {Full Name} results in blank. For e.g. when I create a new row in the grid, but no First and Last Name have been entered yet. I assumed that the {Full Name} is equal to BLANK() in this case.

Not quite. Even with empty fields to pull from, the space that you are concatenating between first and last name is still there, thereby making the field non-blank for comparison purposes.

To make that field blank, test for values in {First Name} and {Last Name} before concatenating them together:

IF(AND({First Name}, {Last Name}), {First Name} & " " & {Last Name})

Notice how I’m not comparing against BLANK() when looking for data in those two fields. A non-empty field (in most cases; there are exceptions) is equivalent to True, and an empty field is equivalent to False, so this formula will only assemble the full name if both fields contain something.

Now that {Full Name} correctly becomes blank with no first and last names entered, you can also use this technique in your {ID} primary field formula:

Autonumber & " " & IF({Full Name}, {Full Name}, "No name")

[Burner]

Thank you so much. You nailed it. I guess I was overlooking that even if {First Name} and {Last Name} have no values, the space between them in my concatenation formula was evaluating the result to a blank space, and hence not BLANK().

Your approach to only concatenate them if the {First Name} and {Last Name} have values is a neat trick and I’ll definitely use that. Thanks a ton, once again. :slightly_smiling_face: