If statement with multiple conditions
Formula goes up to "3- Checklist Complete" but stops there and I cannot figure it out.
If([Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation", If([Level]="Level 4" ...
Forum Discussion
Didn't work.
Here is the entire formula. The other Levels work but not Level 4.
If([Level]="Level 1" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 1" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 1" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
If([Level]="Level 2" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 2" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 2" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
If([Level]="Level 3" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 3" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 3" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
If(
[Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]<>"", "4 - Waiting on Approvals",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete")
)))
I would suggest updating your formula to read like this - to Marks point you don't have to nest if statements and I find this is easier to read:
If(
[Level]="Level 1" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 1" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 1" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
[Level]="Level 2" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 2" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 2" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
[Level]="Level 3" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 3" and [Documents Available]=true and [Approvals Needed]<>"", "2 - Waiting on Approvals",
[Level]="Level 3" and [Documents Available]=true and [Approvals Needed]="", "3 - Review Complete",
[Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= false, "2 - Checklist Incomplete",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]<>"", "4 - Waiting on Approvals",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
"")
The actual issue with your formula though is that you need to reorder you last two statements
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]<>"", "4 - Waiting on Approvals",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
Quickbase will stop reading the statement once it finds an answer it likes. So for stage 3 and beyond:
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]<>"", "4 - Waiting on Approvals",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
All 3 are checking if Checklist complete = true. But once QB sees that then it never has to go past stage 3 since both conditions pass. Leve is 4, documents available is true and checklist is complete, stop rendering so it will never see that 4 and 5 exist. You should reorder as:
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]<>"", "4 - Waiting on Approvals",
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true and [Approvals Needed]="", "5 - Review Complete","
[Level]="Level 4" and [Documents Available]=true and [Checklist Complete]= true, "3 - Checklist Complete",
------------------------------
Chayce Duncan
------------------------------
THis worked! Thank you!
------------------------------
Sarah Burres
------------------------------