Help

Re: Question about nested if(and()) function

1900 3
cancel
Showing results for 
Search instead for 
Did you mean: 
_cupofchino
4 - Data Explorer
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.

Please advise! Thank you.

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

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")
)

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”)))

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.

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.