Need to check a list for a particular column value?

[Patrick_Paul]

I need a bit of help figuring out how to write an expression. Here’s what I’m trying to accomplish: Mentor table records have a column that is a list of related trainees. I need to word a SELECT expression in a report template so that the report only shows records where ANY related trainee’s [Current Status] column=“Active Approved”. I can’t figure out how to word it. Here’s what I’ve tried, which fails to execute due to the reference in the ANY() function:

<<Start:orderby(Select(Mentors [Employee ID], (and(ANY([related trainees].[current status]=”Active Approved”), [Mentor Status]=Active Approved))), [Location State])>>

[Steve · Accepted solution]

Indented for clarity:

<<Start:
  ORDERBY(
    FILTER(
      "Mentors",
      AND(
        ([Mentor Status] = "Active Approved"),
        IN(”Active Approved”, [related trainees][current status])
      )
    ),
    [Location State]
  )
>>

See also:

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

[Patrick_Paul]

I never remember IN() exists, and the syntax is strange…