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:
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?
Solved! Go to Solution.
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 @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?
Hi Kirk_Masden!
Yeah, you’re right - there’s no point for COUNT here - this was inspired by another formula)
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:
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])).
Good catch on my [_THISROW]
mistake. I’ve corrected my recipe.
@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.
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.
User | Count |
---|---|
39 | |
28 | |
24 | |
23 | |
13 |