Dynamic Enum List (Valid_If) not filtering options based on ANY(SELECT()) relationship lookup

LEO_MAIA_ROQUE · April 24, 2026, 1:31pm

I am facing an issue with dynamic filtering on an Enum column (rendered as Buttons). My goal is to restrict the options a user can select based on the type of relationship (“Role_Type”) they have with the evaluated employee. This relationship data is stored in a separate table.

Data Structure:

Current Table (Evaluation_Form):
- [Employee_ID] (Text) - Evaluated employee’s identifier.
- [Initial_Score] (Text/Enum) - The employee’s initial score category (e.g., “1”, “2”… up to “9”).
- [Suggested_New_Score] (Enum, base type Text) - The field where the user selects the new value (Buttons). This is where the Valid_If needs to work.
Relationship Table (Evaluator_Mapping_Table):
- [Evaluator_Email] (Email) - The email of the person doing the evaluation.
- [Employee_ID] (Text) - The employee’s identifier.
- [Role_Type] (Text) - The relationship type (e.g., “Direct Manager” or “Peer Manager”).

The Business Rule:

If the logged-in user (USEREMAIL()) is the “Direct Manager” of the current employee, they can only select a restricted set of options (e.g., if the initial score is 1, they can only choose 3).
If the logged-in user has any other relationship (e.g., “Peer Manager”), they get a different set of options (e.g., if the initial score is 1, they can choose 2, 3, or 5).

The Formula Used in Valid_If of [Suggested_New_Score]: I tried using the expression below to check the relationship table and return the appropriate list:

Snippet de código

IFS(
  ANY(SELECT(Evaluator_Mapping_Table[Role_Type], AND([Evaluator_Email] = USEREMAIL(), [Employee_ID] = [_THISROW].[Employee_ID]))) = "Direct Manager",
  SWITCH(LEFT(TEXT([Initial_Score]), 1),
    "1", LIST("3"),
    "2", LIST("5"),
    "3", LIST("1", "6"),
    "4", LIST("7"),
    "5", LIST("2", "8"),
    "6", LIST("3"),
    "7", LIST("4", "9"),
    "8", LIST("5"),
    "9", LIST("7"),
    LIST()
  ),
  
  ANY(SELECT(Evaluator_Mapping_Table[Role_Type], AND([Evaluator_Email] = USEREMAIL(), [Employee_ID] = [_THISROW].[Employee_ID]))) <> "Direct Manager",
  SWITCH(LEFT(TEXT([Initial_Score]), 1),
    "1", LIST("2", "3", "5"),
    "2", LIST("1", "3", "4", "5", "7"),
    "3", LIST("1", "2", "5", "6", "8"),
    "4", LIST("2", "5", "7"),
    "5", LIST("1", "2", "3", "4", "6", "7", "8", "9"),
    "6", LIST("3", "5", "8"),
    "7", LIST("2", "4", "5", "8", "9"),
    "8", LIST("3", "5", "6", "7", "9"),
    "9", LIST("5", "7", "8"),
    LIST()
  )
)

The Problem: Despite the conditional logic appearing correct, the formula is failing in practice. Sometimes AppSheet throws a context error indicating it cannot find the column using [_THISROW], or it completely ignores the restriction from the LIST() generated by the SWITCH. In the latter case, it simply displays all buttons from 1 to 9 and allows any selection, breaking the business rule.

My Question: Is there any known limitation when using ANY(SELECT(...[_THISROW]...)) as a condition inside an IFS to generate a LIST() in a Valid_If expression? What would be the most efficient and robust approach (perhaps utilizing Virtual Columns) to ensure that the Enum column strictly obeys the options provided by the formula according to the USEREMAIL()?

Thanks in advance for your help!

Steve · April 24, 2026, 1:46pm

First, your use of IFS() here is particularly inefficient, as the second condition (... <> "Direct Manager") is the only possible alternative to the first (... = "Direct Manager"), so you don’t need the (expensive!) second condition at all, and could simply use IF():

IF(
  "Direct Manager" = ANY(
    SELECT(
      Evaluator_Mapping_Table[Role_Type],
      AND(
        [Evaluator_Email] = USEREMAIL(),
        [Employee_ID] = [_THISROW].[Employee_ID]
      )
    )
  ),
  SWITCH(
    LEFT(TEXT([Initial_Score]), 1),
    "1", LIST("3"),
    "2", LIST("5"),
    "3", LIST("1", "6"),
    "4", LIST("7"),
    "5", LIST("2", "8"),
    "6", LIST("3"),
    "7", LIST("4", "9"),
    "8", LIST("5"),
    "9", LIST("7"),
    LIST()
  ),
  SWITCH(
    LEFT(TEXT([Initial_Score]), 1),
    "1", LIST("2", "3", "5"),
    "2", LIST("1", "3", "4", "5", "7"),
    "3", LIST("1", "2", "5", "6", "8"),
    "4", LIST("2", "5", "7"),
    "5", LIST("1", "2", "3", "4", "6", "7", "8", "9"),
    "6", LIST("3", "5", "8"),
    "7", LIST("2", "4", "5", "8", "9"),
    "8", LIST("3", "5", "6", "7", "9"),
    "9", LIST("5", "7", "8"),
    LIST()
  )
)

Please post a screenshot of this.

Nope.

Consider adding a virtual column named (e.g.) Valid_New_Scores with an App formula of:

IFS(
  ("Form" <> CONTEXT("ViewType")),
    LIST([_THISROW_BEFORE].[Suggested_New_Score]),
  "Direct Manager" = ANY(
    SELECT(
      Evaluator_Mapping_Table[Role_Type],
      AND(
        [Evaluator_Email] = USEREMAIL(),
        [Employee_ID] = [_THISROW].[Employee_ID]
      )
    )
  ),
    SWITCH(
      LEFT(TEXT([Initial_Score]), 1),
      "1", LIST("3"),
      "2", LIST("5"),
      "3", LIST("1", "6"),
      "4", LIST("7"),
      "5", LIST("2", "8"),
      "6", LIST("3"),
      "7", LIST("4", "9"),
      "8", LIST("5"),
      "9", LIST("7"),
      LIST()
    ),
  TRUE,
    SWITCH(
      LEFT(TEXT([Initial_Score]), 1),
      "1", LIST("2", "3", "5"),
      "2", LIST("1", "3", "4", "5", "7"),
      "3", LIST("1", "2", "5", "6", "8"),
      "4", LIST("2", "5", "7"),
      "5", LIST("1", "2", "3", "4", "6", "7", "8", "9"),
      "6", LIST("3", "5", "8"),
      "7", LIST("2", "4", "5", "8", "9"),
      "8", LIST("3", "5", "6", "7", "9"),
      "9", LIST("5", "7", "8"),
      LIST()
    )
  )
)

You could then display the results for debugging: set the Show? for Valid_New_Scores to:

(USEREMAIL() = CONTEXT("OwnerEmail"))

What the heck is this, you might ask:

IFS(
  ("Form" <> CONTEXT("ViewType")),
    LIST([_THISROW_BEFORE].[Suggested_New_Score]),

Virtual columns that use queries (SELECT(), FILTER(), etc.) can be (potentially very!) expensive to calculate. Valid_New_Scores exists solely to support forms, so computing it at any other time is pointless. But! There might come a time when you want to modify the row outside of a form, such as with an action or by a bot. Should you do that, you’ll want this VC to identify the current value of Suggested_New_Score so that its existing value remains valid and doesn’t cause an error.

With Valid_New_Scores in place, your Valid if for Suggested_New_Score would then be:

[Valid_New_Scores]

If only one value is allowed, you could preset the Suggested_New_Score column’s value with an Initial value of:

IFS(
  (COUNT([Valid_New_Scores]) = 1),
    ANY([Valid_New_Scores])
)

If only one value is allowed, and it’s already set to that value, why allow editing it? Set Editable? to:

OR(
  (COUNT([Valid_New_Scores]) > 1),
  NOT(IN([_THIS], [Valid_New_Scores]))
)

If it’s not editable, why even display it (in a form view)? Set Show? to:

OR(
  ("Form" <> CONTEXT("ViewType")),
  (COUNT([Valid_New_Scores]) > 1),
  NOT(IN([_THIS], [Valid_New_Scores]))
)

Topic		Replies	Views
Unable to use an IF() statement in a "Valid If" for an EnumList Dropdown AppSheet Q&A databases , expressions	12	37	August 22, 2019
valid if expression AppSheet Q&A expressions	5	19	July 8, 2022
Variable Enum List AppSheet Q&A users , errors	2	8	September 18, 2024

Dynamic Enum List (Valid_If) not filtering options based on ANY(SELECT()) relationship lookup

AI Suggested topics