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
Unlike Excel you generally don't need to nest your IFs. You just list the conditions and the formula will keep checking them in sequence and use the reduk5 for the first condition which is true.
|
Formula goes up to "3- Checklist Complete" but stops there and I cannot figure it out. Try this IF( [Level]="Level 4" and [Documents Available]=false, "1 - Waiting on Documentation", |
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
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
------------------------------
