Need to check a list for a particular column value?

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])>>

Indented for clarity:

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

See also:

1 Like

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