Verification that all boxes on a multi-select list are checked
Hello,
I have a multi-select list that has 5 items on it. All 5 items need to be checked off for quality purposes. I created a separate formula field to check this condition, but I can't figure out the logic to get the correct result.
My multi-select field is called "[The SOW was reviewed and SOW has the following items:]".
Here is the Boolean code that I put into the separate formula field. Regardless of what is checked off, the result is always the false choice, "SOW is not complete":
If(
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Anticipated LOE by Contract Labor Cat (Qty & Hrs) by JID") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Place of Performance") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("PoP and Planning Start Date (if before the POP start date)") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Detailed Work Scope") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Non-Labor Authorized - travel, materials, ODCs)"),
"Good",
"SOW is not complete")
Thank you for any help.
------------------------------
Joe Rysavy,
QB user for 1 month
------------------------------
I have a multi-select list that has 5 items on it. All 5 items need to be checked off for quality purposes. I created a separate formula field to check this condition, but I can't figure out the logic to get the correct result.
My multi-select field is called "[The SOW was reviewed and SOW has the following items:]".
Here is the Boolean code that I put into the separate formula field. Regardless of what is checked off, the result is always the false choice, "SOW is not complete":
If(
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Anticipated LOE by Contract Labor Cat (Qty & Hrs) by JID") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Place of Performance") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("PoP and Planning Start Date (if before the POP start date)") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Detailed Work Scope") and
ToText([The SOW was reviewed and SOW has the following items:]) = ToText("Non-Labor Authorized - travel, materials, ODCs)"),
"Good",
"SOW is not complete")
Thank you for any help.
------------------------------
Joe Rysavy,
QB user for 1 month
------------------------------
- @Joseph Rysavy There may be an easier way to approach this.
There is a new function called Size() that largely is used with Query Formulas but also works with multi-select fields.
If(Size([The SOW was reviewed and SOW has the following items:])=5,"Good","SOW is not complete")
I share a bit about this in the "Oh So Fantastic Formula Query Functions" video here: https://youtu.be/OsfngR4DGWg?t=324
If you want to stick with you original approach, use this variation with the "Contains" function:
If(Contains([The SOW was reviewed and SOW has the following items:],"Anticipated LOE by Contract Labor Cat (Qty & Hrs) by JID") and ... <add remaining conditions>
Let me know if this was helpful 👍
-Sharon
------------------------------
Quick Base Junkie
------------------------------
