If function calling on a number of fields

• 1
• Question
• Updated 2 years ago
Need some help as I want to see if I am going in the right direction as it looks like the an IF formula I am working on is going to be about 50 lines long.

In essance it is and expansion of the below formula to see which approval level is still needed. The below formula just tells me if it is approved or not but I would like this to call on when it is not approved and then multiple varients to call out the level it needs an approval. With all possiable combinations this is going to be very long. I have to use contain as the approval field is time date stamped so it records all changes. Any help appreciated.

If(

[Option] = "Lapse/Expire", "Not Required",

[Fixed Pricing] = false and [Approval Level] = "-" and [Option] <> "" ,"TAM Approval Level",

[Fixed Pricing] = false and [Approval Level] = "DSM" and Contains([DSM Approval], "Approve") ,  "Fully Approved",

[Fixed Pricing] = false and [Approval Level] = "Sales Director" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve"),  "Fully Approved",

[Fixed Pricing] = false and [Approval Level] = "GM" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve") and Contains([GM Approval],"Approve"),  "Fully Approved",

[Fixed Pricing] = false and [Approval Level] = "CCO" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve") and Contains([GM Approval],"Approve") and Contains([CCO Approval], "Approve"),  "Fully Approved",

[Fixed Pricing] = true and [Approval Level] = "-" and [Option] <> "" ,"TAM Approval Level",

[Fixed Pricing] = true and [Approval Level] = "DSM" and Contains([DSM Approval], "Approve")  and Contains([Global Sales Operations Approval], "Approve"),  "Fully Approved",

[Fixed Pricing] = true and [Approval Level] = "Sales Director" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve") and Contains([Global Sales Operations Approval], "Approve"),  "Fully Approved",

[Fixed Pricing] = true and [Approval Level] = "GM" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve") and Contains([GM Approval],"Approve") and Contains([Global Sales Operations Approval], "Approve"),  "Fully Approved",

[Fixed Pricing] = true and [Approval Level] = "CCO" and Contains([DSM Approval], "Approve") and Contains([Sales Director Approval], "Approve") and Contains([GM Approval],"Approve") and Contains([CCO Approval], "Approve") and Contains([Global Sales Operations Approval], "Approve"),  "Fully Approved",

[Option] = "", "Not Submitted",

"Approval Needed")

• 172 Points

Posted 2 years ago

• 1
• 31,758 Points
Often times when there are a lot of different scenarios like this that play into one "Status".
I like to break them out into categories, and use formula checkboxes to say if something is true or not.

Then use those checkboxes to drive your "Status" field.

It makes it much easier to maintain, and make adjustments to one section, without affecting the others.

Maybe break out your approval levels, and pricing levels, then combine later for the status.
• 172 Points
Thank you. This worked really well so much easier to see and easier to adapt. Only question is I then used an IF formula based of the new checkbox to combine into one field.

Quick question...when I created a summary table using the IF function the results are in alphabetical order on the summary not in the order of the IF function steps. Is there any way to change the order?
• 75,144 Points
They will sort in alpha sequence so you need to cheat on your rsult to get them to be alpha. Either number your results like "1. Approved" or else have leading spaces. " Zebra Approved". But the method with leading spaces will be confusing if you then use that result in formula fields.
• 75,144 Points
The reason I like to number them, is that later on in formulas you can use

IF(Begins([Status],"1") ,,,  and you don't have to get the exact spelling perfect for the status words, and you can also tweak the words themselves without breaking all your formulas.  (ie as long as it still begins with "1".