Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Question about nested if(and()) function

Topic Labels: Formulas
1333 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi, I’m trying to figure out why I’m experiencing issues with my nested IF(AND()) function on Airtable. When I limit the fields in AND() to just two conditions, it works, but anything else and Airtable won’t let me use the formula at all.

What works

IF(AND({Person}= “1”, {A}= “Yes”), “Approved”,

IF(AND({Person}= “2”, {B}= “Yes”), “Approved”,

IF(AND({Person}= “3”, {C}= “Yes”), “Approved”,

IF(AND({Person}= “4”, {D}= “Yes”), “Approved”,

IF({Quote} = “0.00”

, “Temporary approval”, “Unapproved”)))))

What doesn’t

IF(AND({Person}= “1”, {A}= “Yes”), “Approved”,

IF(AND({Person}= “2”, {B}= “Yes”), “Approved”,

IF(AND({Person}= “3”, {C}= “Yes”), “Approved”,

IF(AND({Person}= “4”, {D}= “Yes”), “Approved”,

IF(AND({Person}= “5”, {E}= “Yes”, {F}= “Yes”), “Approved”,

IF(AND({Person}= “6”, {E}= “Yes”, {F}= “Yes”, {G}= “Yes”), “Approved”,

IF({Quote} = “0.00”

, “Temporary approval”, “Unapproved” )))))))

4 Replies 4
16 - Uranus

I don’t see anything wrong from a syntax point. AND() does not have a limit on how many clauses it can accept (and if it does, it isn’t two). What types of fields are E, F, and G? There could be an errant comma or quote that I’m not seeing. It also could be the trailing space after “Unapproved” before the closing parenthesis.

Also your formula can be simplified by using an OR since so many outcomes result in “Approved”

``````IF(
OR(
AND({Person}="1", {A}="Yes"),
AND({Person}="2", {B}="Yes"),
AND({Person}="3", {C}="Yes"),
AND({Person}="4", {D}="Yes"),
AND({Person}="5", {E}="Yes", {F}="Yes"),
AND({Person}="6", {E}="Yes", {F}="Yes", {G}="Yes")
),
"Approved",
IF({Quote}="0.00", "Temporary approval", "Unapproved")
)
``````
4 - Data Explorer

Thank you for your answer! I added the trailing space to differentiate between different lines of the formula better but didn’t have that syntax when I used it in Airtable.

The Person fields are text with a colon, possibly an ampersand, and spaces (e.g. {Person} = “C&I: 5000”); the alphabetical fields are names ({A} for example could be {John Smith}).

Based on your comments I’ve edited my formula to the following with all 8 approval person types but still have received the error: “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”

I would appreciate any advice you have!

IF(

OR(

AND({Person}= “1”, {A}= “Yes”),

AND({Person}= “2”, {B}= “Yes”),

AND({Person }= “3”, {C}= “Yes”),

AND({Person}= “4”, {D}= “Yes”),

AND({Person} = “5”, {E} = “Yes”, {F} = “Yes”, {G} = “Yes”),

AND({Person} = “6”, {E} = “Yes”, {F} = “Yes”),

AND({Person} = “7”, {E} = “Yes”),

AND({Person} = “8”, {H} = “Yes”),

“Approved”, IF({Quote} = “0.00”, “Temporary approval”, “Unapproved”)))

16 - Uranus

You’re missing a closing parenthesis for the OR, or its in the wrong spot. The should be no comma after your last AND, there should be the closing parenthesis for the OR.

4 - Data Explorer

I appreciate this and have moved my closing parenthesis for OR–I am somehow still experiencing an error but will do some more troubleshooting on my end before I ask a follow up question.