Trying to get a Filtered View based on Enum List


I’m trying to create an action for Search Form to get filtered view of Users which have the Accounts of all chosen Account Types.

App tables:

  • Users | User Name | Related Accounts (virtual list of refs) |
  • Accounts |Account ID | User(Ref to Users) | Account Type(Ref to Account Types) |
  • Account Types | Type | Related Accounts (virtual list of refs) |
  • Search | ID | Search Types(Enum list of Refs to Account Types) |

If Search Types is empty - show all users without accounts
If Search Types = Type 1 - show all users who have Type 1 acc
If Search Types = {Type 1, Type 2} - show all users who have Type 1 AND Type 2 accounts

Unfortunately, INTERSECT (LIST, ENUM LIST) is not an option:

COUNT(INTERSECT([Related Accounts][Account Type],[_THISROW].[Search Types])) =
COUNT([_THISROW].[Search Types])

gives error: INTERSECT: both lists must be of the same type.

Any ideas?

Hi @Steven_Brinksma! Welcome to the community!

I’m having trouble parsing your expression. Actually, I’ve never used intersect before but I see that you need to have two lists for this expression:

INTERSECT( list1 , list2 )

I’m confused about the presence of two COUNTS() in your expression. Is COUNT() really needed?

This is a known bug. The work-around is to force both lists to be of the same type by adding the intended lists to empty lists:

INTERSECT((LIST() + list1), (LIST() + list2))

It’s super ugly, but it works.

That said, it looks to me like you don’t need to use INTERSECT(). By my interpretation, your entire filter condition can be expressed as:

ISBLANK([_THISROW].[Search Types] - [Account Type])

This starts with the list of wanted account types ([_THISROW].[Search Types]), then removes those contained in the user’s own account type list ([Account Type]). If all of the wanted types were removed (ISBLANK(...)), the user matches all of the wanted types.

See also:


Hi Kirk_Masden!

Yeah, you’re right - there’s no point for COUNT here - this was inspired by another formula)

1 Like

Thanks, Steve!
Your solution looks really nice, I just added a virtual column “Acc Types” to Users table with formula [Related Accounts][Account Type].

The working action looks like:
ISBLANK([_THISROW].[Search Types] - [Acc Types])).

1 Like

Good catch on my [_THISROW] mistake. I’ve corrected my recipe. :slight_smile:

1 Like

@Steve Do you know if there is a timeline on this getting corrected?

I do not. When I ask, I’m told it’s in the queue. :frowning: