cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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
Highlighted
Super User
Super User

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.

Highlighted
Advocate I
Advocate I

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
Advocate I
Advocate I

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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,986)