Skip to main content

Hello,

I am new to Airtable and am trying to translate Excel formulas into the new syntax.


I wrote a formula that works for number fields (below), and would like to create a similar formula for text fields. However, I am not sure how to substitute blank or not blank in place of the = and > in the formula.


IF( AND( {2021 FIELD}>0, {2022 FIELD}>0 ), {2022 FIELD}, IF( AND( {2021 FIELD}>0, {2022 FIELD}=0 ), {2021 FIELD}, IF( AND( {2021 FIELD}=0, {2022 FIELD}>0 ), {2022 FIELD}, BLANK())))


This should be the equivalent of: if 2021 and 2022 are filled, use 2022. If 2021 is filled and 2022 is not filled, use 2021, if 2021 is not filled and 2022 is filled, use 2022, otherwise, blank


Any help would be appreciated!

Your original formula could be simplified to:


IF({2022 FIELD}, {2022 FIELD}, IF({2021 FIELD}, {2021 FIELD}))

^ that same pattern works for string values as well. Writing IF({field name}) asks “does this field have a non-falsy value?” Falsy values include zeros, empty strings, blanks/nulls, and literal “false”. For number fields that means a non-falsy value is any number other than 0, and for string fields it means “anything apart from a blank”

Your original formula could be simplified to:


IF({2022 FIELD}, {2022 FIELD}, IF({2021 FIELD}, {2021 FIELD}))

^ that same pattern works for string values as well. Writing IF({field name}) asks “does this field have a non-falsy value?” Falsy values include zeros, empty strings, blanks/nulls, and literal “false”. For number fields that means a non-falsy value is any number other than 0, and for string fields it means “anything apart from a blank”


Thank you! That’s so much easier. I do have a lot of complex formulas that rely on the blank or not blank logic and it would be helpful to know how to write a formula like the one I described. Could you help me out or point to a post that has an example?

Thank you! That’s so much easier. I do have a lot of complex formulas that rely on the blank or not blank logic and it would be helpful to know how to write a formula like the one I described. Could you help me out or point to a post that has an example?


IF(

{name of field}, 

"this field is has a value", 

"this field is either blank, false, or equals zero"

)

the reverse would be:


IF(

NOT({name of field}), 

"this field is either blank, false, or equals zero",

"this field is has a value"

)

If you want to consider “zero” as a value, then


IF(

OR({name of field}, {name of field}&"" = "0"),

"has a value", 

"no value"

)

Reply


Terms & Conditions