cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daniel_Pipe
Level: Powered On

Filter a Gallery by User Access List

MY DATA:

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

 

MY GOAL:

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!

3 REPLIES 3
leyburn19
Level 10

Re: Filter a Gallery by User Access List

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

 

Filter(datasource,Col1=user().email||Col2=user().email)

 

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.

Daniel_Pipe
Level: Powered On

Re: Filter a Gallery by User Access List

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!
Highlighted
Daniel_Pipe
Level: Powered On

Re: Filter a Gallery by User Access List

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

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,446)