Showing results for 
Search instead for 
Did you mean: 
Advocate II
Advocate II

Filter a Gallery by User Access List


I have a SharePoint custom list called "clients" - this is basically just an address book

I have a SharePoint custom list called "staff" - this list has a 'People' column so that each item links to that staff member's Office365 account

I have an App in PowerApps called "your clients" - This is a gallery view of the "clients" list.

I have a column in "staff" called "assigned clients" which is a lookup of the "client name" field of the "clients" list



I would like to filter the gallery in this app to only show the client's which are assigned to a particular staff member. I want to prevent staff from having access to the entire client list.


I understand that user().fullname grabs the current app user, however I don't see how to use that in the gallery item filter to lookup the username in another list, grab the user's clients list and then filter the gallery accordingly? This is a function that I would recycle many times to develop apps for this NFP.


Is this possible? Any pointers would be gratefully received!

Memorable Member
Memorable Member

I do someting similar by adding two or three extra columns in the client list. The number of columns is dependant on who you want to see the client.  In these columns I add the staff profile email.  Then when the staff logs in they will only see a list based on theier profile.  Gallery would have a filter like




In this case Col1 would contain the staff you want to see only cliennnnnts where their email is listed against them and Col2 might be the a supervisor email when the will see more than the staff - Hope that makes sense.  


One issue I got with this was that I found the actual email may differ in casing and therefor failed.  EG Mark@email mismatches if it is mark@email.  I tried to get around this by making it all uppercase,  but the moment I did I then failed teh delegation rules as Upper() is not deleagble.

Advocate II
Advocate II

Thanks for your speedy answer! This is interesting however I would like the ability to choose the user(s) who can access from a dropdown which is populated by a master list of staff. This will validate the input and mean that access can be chosen by name, not email address. Our email addresses can be quite cryptic in their naming convention.
I'm sure that other have done this?
Thanks again!
Advocate II
Advocate II

So, I worked out how to filter the gallery items by a people column which is in the same list BUT it only works if the people column does not allow multiple selections. If the column allows multiple selections, it breaks.


Here is the formula:

SortByColumns(Filter(Clients, Team.DisplayName = User().FullName,StartsWith(First_x0020_Name,TextSearchBox1.Text)),"Surname", If(SortDescending1, Descending, Ascending))


This would work if each items should only be accessed by 1 staff member, however each item needs to be available to a "Team" of staff, thus the "Team" column which I'm trying to compare to User().FullName needs to allow multiple selection


Basically I need something like SortByColumns(Filter(Clients, Team.DisplayName CONTAINS User().FullName,StartsWith(First_x0020_Name,TextSearchBox1.Text)),"Surname", If(SortDescending1, Descending, Ascending))


I think it might be possible if I can convert all entries in the Team column to a list of DisplayNames then do a filter with a search or IsMatch Any or something similar. 


I'd really appreciate some tips!

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,959)