Security filter with more than one match

AppSheet AppSheet Q&A
1

Can someone help ?
I am trying to have a security filter that only displays rows in a table where the ‘academy name’ in that row is one that belongs to a user in another table. I already created a slice from my users table to show just the rows (and therefore academy names) for the current user, and I am trying to use that in my security filter. (that bit is fine)

The user table slice (called currentUser) shows
Name | email | AcademyName

The table I am trying to filter contains
AcademyName | DescriptionText | Status

In my user slice, I have two different AcademyNames associated with a user (so that is showing 2 rows for the current user).

In the table I am trying to filter, I started by using this
[AcademyName]=ANY(currentUser[AcademyName])

But this only shows records for ONE of the two AcademyNames that the user has assigned
(I read that the ANY randomly picks one value so assumed that this was the problem)

I then tried to change the security filter to use IN instead
[AcademyName]=IN([AcademyName],currentUser[AcademyName])

… but this returns no rows at all.

How can I apply a filter to my table to show rows where the AcademyName matches EITHER of the two AcademyName values in my slice?
Thanks

2

I found a different solution … which didn’t require the slice and seems to work …

IN([AcademyName], SELECT(Users[AcademyName], [email] = USEREMAIL()))

Not sure why the same approach with IN didnt work when I tried to use it on the slice rather than the users table. Hey Ho.

1 Like
3

The IN() function produces a Yes/No value, which is either TRUE or FALSE. The academy name is probably never either of those. You used the correct approach in your solution: just use IN(...) directly rather than comparing it to the academy name.

IN([AcademyName],currentUser[AcademyName])

Using the slice is actually the better choice.

2 Likes
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.