I love the new GET_LIST function and its ability to get items from a related record and use a filter. But is there any way to use multiple or compound filters?
Use case: I have a STAFF table and an ACCOUNT table plus a junction table called ROLES which defines that Staff have roles for a given account. In the Account table I would like to have a Manager field that looks for the email of Staff who meets more than one criteria.
A simple formula in the Account table can catch one criteria such as:
TRIM(CONCAT(
GET_LIST(Roles].[Role Type] = "Manager", [Roles].[Staff].[Email]
), 1)
But I'm stuck trying to add a second criteria like [Roles].[Status] = "Active".
A second query I'm struggling with would how to implement the above where
[Role Type] = "Manager" OR "Sponsor" (though I could in this case add a formula within
the Roles table like Manager_or_Sponsor = OR([Type]="Manager", [Type]="Sponsor")
So, any ideas on GET_LIST-like functionality with compound/multiple criteria for the Condition?