Unable to use an IF() statement in a "Valid If" for an EnumList Dropdown

[Nilson_Cain]

I have a dropdown in my app which is made from an EnumList. I want to allow different users access to select different things based on the IF condition. I want them allowed to select any of the values if the condition is true, and a smaller set of values otherwise. But all the options are always available to everyone.

It works as intended when I use either of the expressions by themselves, without the IF statement. Here’s an example of the Valid If expression I am trying to use.

IF(
   (ANY(SELECT(WORKER LIST[POSITION NUMBER], [EMAIL] = USEREMAIL())) < 10),
   TRUE,
   IN([_THIS], SPLIT(SELECT(CAREPLANS[SELECT CARE PLAN(S) TO CHART ON THIS SHIFT], [IPU ID] = [_THISROW].[IPU ID]), ',')))
  )

Can someone please provide some insight into why this Valid If expression always allows all values, Even when the “Position Number” expression is not true?

Thank you!

[LeventK]

@Nilson_Cain
Try it like this:

IF(
	(ANY(SELECT(WORKER LIST[POSITION NUMBER], [EMAIL] = USEREMAIL())) < 10),
	TRUE,
	AND(
		TRUE,
		IN([_THIS], SPLIT(SELECT(CAREPLANS[SELECT CARE PLAN(S) TO CHART ON THIS SHIFT], [IPU ID] = [_THISROW].[IPU ID]), ',')))
	)
)

[Nilson_Cain]

Thanks for commenting, Levent, but this unfortunately did not help either. The dropdown allows all possible selections.

[LeventK]

LeventK:

ANY(SELECT(WORKER LIST[POSITION NUMBER], [EMAIL] = USEREMAIL()))

This expression will always select the first record in Worker List table [Position Number] column and therefore your IF expressions conditional statement will always evaluate to TRUE, and therefore the result will be TRUE. Provided you can tell us what you want to achieve, we may help better.

[Nilson_Cain]

There will only be one record in the Worker List table where the EMAIL field matches the current USEREMAIL(), however, so this should return the correct result, should it not? I’ve used a similar IF statement in other places to alter functionality (mostly showing or hiding Behavior actions).

The intention here is to allow some users (RN Nurses) to select any type of Care Plan from this drop down, but any other users can only select from the types of Care Plans that have already been entered for this patient’s stay (which is linked to the IPU ID). This part of the expression works fine if used by itself, but of course it applies to all users in this case.

Thank you

[LeventK]

@Nilson_Cain
As far as I have understood:

• You have an ENUMLIST field with some content
• As per user, you want to filter the content of this ENUMLIST

Am I correct?

[Nilson_Cain]

@LeventK
Yes, that is correct.

[LeventK]

@Nilson_Cain
Below expression might work better than:

IFS(
	IN(USEREMAIL(), WORKER LIST[EMAIL]), SPLIT(SELECT(CAREPLANS[SELECT CARE PLAN(S) TO CHART ON THIS SHIFT], TRUE, TRUE), ','),
	TRUE, SPLIT(SELECT(CAREPLANS[SELECT CARE PLAN(S) TO CHART ON THIS SHIFT], [IPU ID] = [_THISROW].[IPU ID]), ',')
)

[Steve]

Try:

OR(
  ISNOTBLANK(
    FILTER(
      "WORKER LIST",
      AND(
        ISNOTBLANK([EMAIL]),
        ([EMAIL] = USEREMAIL()),
        ([POSITION NUMBER] < 10)
      )
    )
  ),
  ISNOTBLANK(
    FILTER(
      "CAREPLANS",
      AND(
        ISNOTBLANK([IPU ID]),
        ([IPU ID] = [_THISROW].[IPU ID]),
        IN(
          [_THIS],
          [SELECT CARE PLAN(S) TO CHART ON THIS SHIFT]
        )
      )
    )
  )
)

1. Are any of these true? OR(..., ...) requires one of the two given expressions (...; see (2) & (8)) evaluate as TRUE. This is simply another, simpler way to accomplish what you were doing with IF(..., TRUE, ...). See also OR().

2. Is the current user a qualified worker? ISNOTBLANK(...) evaluates as TRUE is the given expression (...; see (3)) contains any value at all. See also ISNOTBLANK().

3. Is the current user a qualified worker? FILTER("WORKER LIST", ...) gathers a list of rows in the WORKERS LIST table that match the given criteria (...; see (4)). See also FILTER().

4. Are all of these true? AND(..., ..., ...) requires that all of the given expressions (...; see (5), (6), and (7)) evaluate as TRUE. See also AND().

5. Does this worker have an email address? ISNOTBLANK([EMAIL]) matches only rows wherein the EMAIL column value is not blank.

6. Does this worker’s email address match the current user? ([EMAIL] = USEREMAIL()) matches only rows with an EMAIL column value that matches the current signed-in user’s email address (USEREMAIL()), or if the column value is blank (see below). See also USEREMAIL().

7. Does this worker have a qualifying position? ([POSITION NUMBER] < 10) matches only rows with a POSITION NUMBER column value less than 10.

8. Is the current IPU ID among the plans to chart this shift? ISNOTBLANK(...) evaluates as TRUE is the given expression (...; see (9)) contains any value at all.

9. Is the current IPU ID among the plans to chart this shift? FILTER("CAREPLANS", ...) gathers a list of rows in the CAREPLANS table that match the given criteria (...; see (10)).

10. Are all of these true? AND(..., ..., ...) requires that all of the given expressions (...; see (11), (12), and (13)) evaluate as TRUE.

11. Does this care plan have an associated IPU ID? ISNOTBLANK([IPU ID]) matches only rows wherein the IPU ID column value is not blank.

12. Does this plan’s IPU ID match the one in the form? ([IPU ID] = [_THISROW].[IPU ID]) matches only rows with an IPU ID column value that matches the IPU ID column value of the current form ([_THISROW].[IPU ID]), or if the column value is blank (see below).

13. Does the selected form value occur in list of plans to be charted on this shift? IN([_THIS], [SELECT CARE PLAN(S) TO CHART ON THIS SHIFT]) matches only rows with a SELECT CARE PLAN(S) TO CHART ON THIS SHIFT column value–an EnumList–that contains the value of this column of the current form.

Note the use of ISNOTBLANK() to test the EMAIL (5) and IPU ID (11) column values within the tables. These tests are critical if any rows of the respective tables might have a blank value in the respective column. In AppSheet, the is-equal-to operator will evaluate to TRUE if the operand on the left side is blank, regardless of the operand on the right side. For instance, if the expression is ([EMAIL] = USEREMAIL()) and the EMAIL column value is blank, the expression is TRUE no matter what value USEREMAIL() gives! To avoid this non-intuitive behavior, I use ISNOTBLANK() to ensure the column has a value before comparing it.

[Nilson_Cain]

Thank you Steve, but using that one shows me all the options regardless of my “position number” in the worker list.

I still haven’t been able to get anywhere on making this work correctly. It seems like such a simple thing, really, but I can’t get it right.

I don’t understand why my initial formula doesn’t work. If the person’s “position number” is less than 10, all EnumList values should be shown in the list; otherwise only show those EnumValues that match up with the types of careplans already created for this patient…

[Steve]

At this point, I suggest you engage support directly by emailing support@appsheet.com.

[Nilson_Cain]

I will do that, as this app is under a paid account.

Thank you both for your help and time! @LeventK @Steve

[Nilson_Cain · Accepted solution]

We finally got this resolved thanks to some help from the Support email. It looks like the basic issue stemmed from us using “TRUE” for the case when we wanted to show all the Enum values; instead using a List() containing the same values as specified in the EnumList definition for this case works. Here is (basically) the statement I used:

IF(
    AND(ISNOTBLANK(LOOKUP(USEREMAIL(), "WORKER LIST", "EMAIL", "POSITION NUMBER")),
        LOOKUP(USEREMAIL(), "WORKER LIST", "EMAIL", "POSITION NUMBER") < 7),
    LIST("ACUTE PAIN",
         "ANXIETY",
         "CAREGIVER ROLE STRAIN",
         "CHRONIC PAIN",
         "COMPROMISED FAMILY/PATIENT COPING",
         "CONSTIPATION",
         "DEFICIENT FLUID VOLUME",
         "DEFICIENT KNOWLEDGE",
         "FECAL INCONTINENCE",
         "GRIEVING- FAMILY/CAREGIVER",
         "GRIEVING- PATIENT",
         "IMBALANCED NUTRITION: LESS THAN BODY REQUIREMENTS",
         "IMPAIRED PHYSICAL MOBILITY",
         "INEFFECTIVE BREATHING PATTERN",
         "NAUSEA/VOMITING",
         "RISK FOR ASPIRATION",
         "RISK FOR ELOPEMENT",
         "RISK FOR FALLS",
         "RISK FOR IMPAIRED SKIN INTEGRITY",
         "RISK FOR INFECTION",
         "URINARY INCONTINENCE"
    ),
    SPLIT(SELECT(CAREPLANS[SELECT CARE PLAN(S) TO CHART ON THIS SHIFT], [IPU ID] = [_THISROW].[IPU ID]), ",")
)

Thanks for the help.

• Nilson