Skip to main content

Hi Airtable community !


I’m new to formulas, loving it but struggling a bit as well…


I’m trying to automate a change in my records, from a phase of the process to the next, at a specific date using IF/AND.


I created a fromnow field called “Calculation” and when it gets to 0 days, I would like to change my Phase en cours to the next phase. It looks like this :


IF(

AND(

{Calculation} = “0 days”,

{Phase en cours}= “Envol 2”

),

“Accélération 3”,


IF(

AND(

{Calculation} = “0 days”,

{Phase en cours}= “Accélération 3”

),

"Accélération 4”,


IF(

AND(

{Calculation} = “0 days”,

{Phase en cours}= “Accélération 4”

),

“Propulsion 5”,


IF(

AND(

{Calculation} = “0 days”,

{Phase en cours}= “Propulsion 5”

),

“Propulsion 6”,


IF(

AND(

{Calculation} = “0 days”,

{Phase en cours}= “Propulsion 6”

),

“Coaching terminé”,


)

)

)

)

)


It’s unfortunately not working and I can’t figure out why 😦 (it says “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.”)


Thanks for your help !

Most of it looks fine, but toward the end of your formula, you have this:


"Coaching terminé",

There should not be a comma after that string unless you’re providing the third argument for the IF() function. Remove that comma, and it should work.


That said, there’s a lot that could be optimized with this. First off, because {Calculation} = "0 days" is a part of every condition, do that once in an outer-level IF() function, then only check the {Phase en cours} field in the inner tests. No need to use AND() every time.


On top of that, because you’re just switching the output based on what’s in that {Phase en cours} field, you could further optimize that central portion with the SWITCH() function.


With those changes, your formula could be this:


IF(

    {Calculation} = "0 days",

    SWITCH(

        {Phase en cours},

        "Envol 2", "Accélération 3",

        "Accélération 3", "Accélération 4",

        "Accélération 4", "Propulsion 5",

        "Propulsion 5", "Propulsion 6",

        "Propulsion 6", "Coaching terminé"

    )

)

Most of it looks fine, but toward the end of your formula, you have this:


"Coaching terminé",

There should not be a comma after that string unless you’re providing the third argument for the IF() function. Remove that comma, and it should work.


That said, there’s a lot that could be optimized with this. First off, because {Calculation} = "0 days" is a part of every condition, do that once in an outer-level IF() function, then only check the {Phase en cours} field in the inner tests. No need to use AND() every time.


On top of that, because you’re just switching the output based on what’s in that {Phase en cours} field, you could further optimize that central portion with the SWITCH() function.


With those changes, your formula could be this:


IF(

    {Calculation} = "0 days",

    SWITCH(

        {Phase en cours},

        "Envol 2", "Accélération 3",

        "Accélération 3", "Accélération 4",

        "Accélération 4", "Propulsion 5",

        "Propulsion 5", "Propulsion 6",

        "Propulsion 6", "Coaching terminé"

    )

)

Oh wow that worked like a charm, thanks so much !!

Reply


Terms & Conditions