Trying to get a Filtered View based on Enum List

[Steven_Brinksma]

Greetings,

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:

LINKTOFILTEREDVIEW(“Users”,
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?

[Kirk_Masden]

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 )

help.appsheet.com

INTERSECT()

Items common to two lists

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

[Steven_Brinksma]

Hi Kirk_Masden!

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

[Steve · Accepted solution]

Steven_Brinksma:

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

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:

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

[Steven_Brinksma]

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:
LINKTOFILTEREDVIEW(“Users”,
ISBLANK([_THISROW].[Search Types] - [Acc Types])).

[Steve]

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

[Grant_Stead]

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

[Steve]

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

[INN_ENGENHARIA]

Hi Steve,

I'm trying to figure out your expression. Every time a had to filter an list from a list of values i use this expression.

LINKTOFILTEREDVIEW("View",
OR(
INDEX([_thisrow].[UNIQUEID],1)=[UNIQUEID],
INDEX([_thisrow].[UNIQUEID],2)=[UNIQUEID],
INDEX([_thisrow].[UNIQUEID],3)=[UNIQUEID],
INDEX([_thisrow].[UNIQUEID],4)=[UNIQUEID],
INDEX([_thisrow].[UNIQUEID],5)=[UNIQUEID]
))

It seams that your expression can compare list of values and return an yes/no result, Can i achieve this with an isblank() formula.

Many thanks.