Filtering a Slice for something that is NOT in a l...

PJMatelli · 12-30-2020 03:50 PM

Not sure if I categorized this correctly, but here is my situation. I have an app for submitting a security request for employees. There are levels of the security request that are handled by different individuals. I would like each individual to mark their ‘section’ done, but not set the status of the request to ‘complete’ until all required sections are completed.

Here are the sections that are possible:

Not all programs will be selected for each request. Let’s say that just Google and JDE are selected for this particular request. I need to mark the request complete when Google is marked DONE AND JDE is marked DONE, but disregard the other programs.

I am pretty sure I need an AND and an OR, but not sure how to say to only look at the program if it is in the list…here it is in English…

If GSuite is in the list for Programs and GSuite has been marked complete AND
If JDE is in the list for Programs and JDE has been marked complete AND, etc. (for all programs)

However, what if GSuite is NOT in the list (not selected), then don’t even look at that, just look at the ones that ARE in the list to see if they are marked DONE.

This is what I am thinking (but need something for the DOESNOTCONTAINS):
AND(
OR(
AND(CONTAINS([Programs],“GSuite”),
[GoogleComplete]=“Done”),
DOESNOTCONTAINS([Programs],“Gsuite”),
)
OR
AND(CONTAINS([Programs],“JDE”),
[JDEComplete]=“Done”,
DOESNOTCONTAINS(Programs],“JDE”)
)

LeventK

NOT(
    CONTAINS(
    ...
    ...
    )
)

PJMatelli

Oooh thanks for the quick reply…going to try it…will let you know.

LeventK

You’re very welcome.

Steve

PJMatelli

Thanks Steve!

PJMatelli

Okay, here is what I have come up with, thanks to your help:

AND(
OR(
AND(CONTAINS([Programs],“GSuite”),
[GoogleComplete]=“Done”),
NOT(CONTAINS([Programs],“GSuite”))
),
OR(
AND(CONTAINS([Programs],“JDE”),
[JDEComplete]=“Done”),
NOT(CONTAINS([Programs],“JDE”))
)
)

This is what AppSheet is summarizing:

ALL these statements are true:
…1: ANY of these statements is true:
…1: ALL these statements are true:
…1: (The value of column ‘Programs’) contains the text value (“GSuite”)
…2: (The value of column ‘GoogleComplete’) is equal to (“Done”)
…2: This statement is false:
…(The value of column ‘Programs’) contains the text value (“GSuite”)
…2: ANY of these statements is true:
…1: ALL these statements are true:
…1: (The value of column ‘Programs’) contains the text value (“JDE”)
…2: (The value of column ‘JDEComplete’) is equal to (“Done”)
…2: This statement is false:
…(The value of column ‘Programs’) contains the text value (“JDE”)

Now I just need to add all the other programs.

Thanks again for the fast response and help!

MultiTech

A note on CONTAINS()…

this formula isn’t the most efficient of formulas when trying to see if something is inside a list.

If you’ve got a list (or an EnumList), IN() is a much more efficient formula to use.

CONTAINS() is primarily used to check if a value is inside a string (a text/longtext value).
- For instance: If I had a longtext field that users could enter anything into, and I wanted to flag something if a specific keyword was entered - then I would use CONTAINS().

But since you’re using an enumlist, IN() will be more efficient.

Filtering a Slice for something that is NOT in a list of values

If you’ve got a list (or an EnumList), IN() is a much more efficient formula to use.