3 Ways To Filter A Power Apps Gallery By The Current User
Want to filter a Power Apps gallery by the current user? There are many ways to do it. The easiest way is to get the current user’s email and use a filter function to show only matching records in the gallery. A more secure way is to set item-level permissions by building a Power Automate flow or changing permissions in the list settings itself. In this article I will show you 3 ways to filter a Power Apps gallery by the current user so you can decide which is best for you.
Table Of Contents: Introduction: The Performance Reviews App Setup The Performance Reviews SharePoint List Option #1: Filter Gallery By Current User With The User Function • Get The Current App User's Email Address Option #2: Use Power Automate To Set SharePoint List Item Permissions • Build A Power Automate Flow Update List Item Permissions • View The Secured SharePoint List As The Current User Option #3: Change Item-Level Permissions in SharePoint List Settings • Grant The User Who Created The SharePoint List Item Edit & Read Access • View The Filtered SharePoint List As The Current User
Introduction: The Performance Reviews App
The Performance Reviews app is used by employees at a construction company to view their annual performance reviews. Employees can only see their own performance reviews. Other employees reviews should not appear in the app because performance reviews are confidential.
Setup The Performance Reviews SharePoint List
The Performance Reviews app is connected to a SharePoint list also called Performance Reviews with the following columns & data:
- Employee (Person)
- ReviewDate (Date Only)
- Score (Number)
- Manager (Person)
|David Johnson||1/21/2022||4||Matthew Devaney|
|Mary Baker||1/19/2022||5||Matthew Devaney|
|Sarah Green||1/12/2022||3||Matthew Devaney|
|David Johnson||1/19/2021||3||Matthew Devaney|
|Mary Baker||1/10/2021||4||Matthew Devaney|
|Sarah Green||1/9/2021||3||Matthew Devaney|
|Mary Baker||1/15/2020||4||Matthew Devaney|
|David Johnson||1/3/2020||2||Matthew Devaney|
Option #1: Filter Gallery By Current User With The User Function
The simplest way to filter a Power Apps gallery by the current user is to obtain their email address using the User function to an email address found in a person type column of a SharePoint list and see if it matches. When both email addresses match the SharePoint list item will be included in the gallery and when they do not match the list item will be excluded from the gallery. This is the easiest option to filter a gallery by the current user but it has one drawback. If the user navigates to the SharePoint list they will be able to see the SharePoint list items for all users. Therefore, it should not be considered adequate security for sensitive information
Get The Current App User’s Email Address
Open Power Apps Studio and create a new app that looks like the one shown below. Add the Performance Reviews SharePoint list as a datasource and then create a gallery to display the list items.
Write this code in the OnStart property of the app. The User function retrieves the current logged in user’s email and this value gets stored in the variable varUserEmail.
To execute the code in OnStart for testing purposes, click on the three dots beside App and select Run OnStart. varUserEmail and varIsProjectManager are now updated with values.
Now we will filter the gallery by checking to see if the Employee’s email in each SharePoint list item equals varUserEmail.
Write this code in the the Items property of the gallery. Only SharePoint list items for the current user will shown.
Filter('Performance Reviews', Employee.Email=varUserEmail)
Option #2: Use Power Automate To Set SharePoint List Item Permissions
Another way to only show records for the current user is to build a Power Automate flow that triggers when a SharePoint list item is created or modified. Then it sets the the item permissions to only allow the person in the Employee column to edit or view the item. The SharePoint list can be shared with many users but they will only see items they have permissions for. True confidentiality and privacy are achieved. The drawbacks are it consumes a flow run each time a record is created or modified and there is no place to globally manage access to records via a security role because permissions are maintained with the list item itself.
Build A Power Automate Flow To Change Item Permissions When An Item Is Created
Open Power Automate and create a new automated flow. Name the flow Set Performance Review Permissions and choose the SharePoint flow trigger When an item is created or modified.
In the flow trigger, select the SharePoint Site Address and List Name – Performance Reviews.
When a record is modified we only want to update the SharePoint list item permissions if the employee field was changed. Add the flow action Get Changes For An Item Or File (Properties Only) to check this. Set the ID field with the trigger ID. Populate the Since field with the Trigger Window Start Token and the Until field with the Trigger Window End Token.
Next, use a Condition step to check if a new record was created or the employee field was changed. If the employee field was modified the property Has Column Changed: Employee from the previous flow action will be equal to true.
When a SharePoint list item is created it inherits the permissions of its SharePoint list. We want to break inheritance because it gives all SharePoint list users access to the item. Instead, we only want the employee who owns the record to have access. To break inheritance we use the SharePoint action Stop sharing an item or a file.
Finally, we add a flow action to Grant Access To An Item Or A Folder, assign the flow trigger’s item ID, include the Employee Email as the recipient and select the role can edit. The recipient is the user who will access the list item.
After creating the flow we must delete all items in the Performance Reviews list and then add them back to the list. This will give the flow an opportunity to set item permissions on the newly created list items. The completed flow should look like this.
View The Secured SharePoint List As The Current User
Login to the app as a user who is not the SharePoint site administrator and browse to the SharePoint list. We will only see the SharePoint list items belonging to that user. If you still see all of the list items there are two reasons why this might be occurring: you are logged in as the site administrator who can see all records regardless or permissions or the records were created before the flow was turned on so the permissions have not been set.
In Power Apps Studio, open the Performance Review app and change the Items property of the gallery to this code.
The gallery will only show records for the current user even though it is not filtered.
Option 3: Change Item-Level Permissions in SharePoint List Settings
The final option to only show records for the current user is to change the SharePoint list settings to grant edit and read permissions for the user who created the list item. Once again, the same list can be shared with many different users but the current user will only see their own records. This option has the advantage of being easy to setup and it does not consume any flow runs unlike Option 2. The downside is the user must always create their own records and permissions can never be changed to another employee because they are based on the Created By field.
Grant A User Who Created The SharePoint List Item Edit & Read Access
As the SharePoint site administrator, open the Performance Reviews list and select List settings.
Go to Advanced Settings.
Browse to Item-level Permissions and change read access to read items that were created by the user and update Create and Edit access to Create items and edit items that were created by the user. Save the settings and close the SharePoint list. That’s all we must do to set it up.
View The Filtered SharePoint List As The Current User
As the user who is not the site administrator open the Performance Reviews SharePoint list. Now we can only see the records that we created. All other records are hidden and cannot be accessed.
In Power Apps studio the gallery will only show records for the current user even though it is unfiltered.
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
If you have any questions about 3 Ways To Filter A Power Apps Gallery By The Current User please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
There is a way we have used to make option 1 a bit more secure by making some changes to the SharePoint list, which stops a user being able to view the list via SharePoint.
– Change the default (only) view to filter for ID=0 or some other filter to return no rows
– Set user permissions to stop standard users from creating views
– Customise the forms in PowerApps to not show any fields, but just show a message along the lines of ‘use the app to see the data’ (in case a user knows the ‘old’ DispForm/EditForm URLs)
– (optional) as admin, create a personal view to see all the records
This worked in our scenario since we only used a flow to create records, however we couldn’t use the other options as we needed a manager to see all the records below them (multiple levels, not just the level directly below). It is still not as secure as options 2 or 3 since it wouldn’t stop someone creating a new PowerApp and connecting to the list, or using Power Query/Power BI.
This is an important concept and I’m really glad you took the time to write such a detailed post. I refer to it as “security by obscurity” – making things very difficult to find/access but still open for read/write. As app makers we do this when better options are not available.
The question we must always ask ourselves before doing security by obscurity is “how sensitive is this data?” If the SharePoint list contains private information that should remain between an employee an their manager we need to find a way to provide actual security. However, if the SharePoint list is used for something like “request a new project number in the accounting system” I feel this is acceptable.
I believe you have done the right thing for your scenario. But I would like to write a blog post on this topic someday to show how you did it and have a good discussion surrounding security.
As always great explanation of the whole idea 🙂
Enjoying the coffee while reading and wanted to share that I faced situations (in huge and old AAD) where result of the User().Email not equals SharePoint Person Email.
It is like that as User().Email returns Users UPN and SharePoint Person Email is Primary Email and both this values might be different. That might happen when for instance somebody is changing the Username after getting married or organization change the domain.
If you want to be sure you can either look for the value of SharePoint Person Claims (which is containing UPN)
Or use Office 365 Users connector to get the User Email.
I hope it will help if somebody will face such issue.
Re: Second method with Power Automate worth to mention is that there can be max up to 50k of such items (unique permissions created), thou MS is recommending 5k. Think worth to keep this in mind while designing / architecture of such solution 🙂
Hope this will be useful 🙂
+1 to what Dawid said. In few org’s the UPN and Email doesn’t match. In that case filtering by User().Email might fail. Solution for this is to filter by OR condition:
User().Email || Office365Users.UserProfileV2(User().Email).mail
Came here to say exactly that Dawid!
In our AAD ~1 in 50 users have a difference in User().Email and the SharePoint Persons mail. Took me a looong time to figure out what happened when it first occured.
But for us it was mostly lower vs upper case problems like “Name@domain.com” vs “email@example.com”
Office365Users.MyProfileV2().mail works fine, like you said
I have an app for creating a site diary
The sites are stored in a Sharepoint list which is controlled in Sharepoint
So this means that when different users login they only see the sites they have access to which is perfect
However, I dont want to replicate this permission principle on the Site Diary Entries, instead I want it to be implied
I.e. Only obtain site diary entries for the sites that are available to a user
What is your recommendation for implementing this approach?
I would have 1 entry per day per site probably around 10 sites or so, which would exceed default delegate limits within a couple of years or so
My initial idea is to setup folders in the Site Diary Entries list that match the ‘Sites’ in your other list. Folders can be assigned permissions. Any list item belonging to a folder can inherit the permissions of the folder it belongs to. When a new site entry is made we move it into the folder.
We can setup a Power Automate flow to create a new folder when the 1st site diary entry is made, move it to the folder, break permissions on the list item and have it inherit the folder permissions instead.
The question is – how can we update the folder permissions to match the ‘site’ permissions? I’m not sure how you’re doing it currently. Perhaps there could be a table called Job Site Users with only two columns: a lookup to the ‘sites’ list and a person column for the users. When a list item is created/modified/deleted in Job Site users a Power Automate flow could be built to update the permissions of the folder in Site Diary Entries.
Thanks for this I wasnt aware of folders in Sharepoint
When you say table, do you mean list? I am storing the data in Sharepoint only
At the moment when the app loads I look at the site list in Sharepoint, or if the app is offline, a cached list of sites
I then only get the diary entries where the site name is in that list, but thats not really too efficient as I get the performance warning
I would be interested in seeing how to do that Power Automate flow
How about extending this article a bit to show how folders could be used to store performance review related documents?
I’m also not sure how I can hook the app into this? At the moment it’s a single list of diary entries. The site diary entries have the same structure regardless of the site. Would I have to manually create this structure for each site?
It would have to remain a single centralized list of diary entries. If you are creating a SharePoint site for each new project and a new list for diary entries on each site that approach will not be possible.
Yes, by table I mean SharePoint list. Sometimes I get SharePoint list and table mixed up because I work in Dataverse all day 😉
I don’t currently have plans to extend this article to SharePoint document libraries, but fortunately, Reza Dorrani just published a great video on this exact subject!
What you have in mind is definitely possible. I’ve thought through what I would do and its achievable.
Ok I’m a bit confused now sorry
Not sure how the diary entry list remains a centralised list if they are stored within folders
Maybe I’m not reading this right?
Thanks so much for this article! I’m a very novice PowerApps user and chose to use option #1, which seems like it should work perfectly for my purposes. But I keep getting a delegation warning. The gallery that should be filtered instead shows no records. Do you have any ideas for how I can fix this? My data set is far fewer than 500 records.
Can you please share the code you’ve used to filter the gallery? A screenshot would be great because it will show me where the delegation issue is.
Thanks for your help!
Here is what I used to filter the gallery:
Filter(‘Student Learning Plans’,Teachers.Email=Office365Users.MyProfileV2().mail)
I found that the email associated with the Sharepoint Person and the email associated with Office365 user weren’t the same.
I have some news that will make option 3 best option.
There is a flow that you can create which will change the created by person to match a user email field and make the item created by User’s email mentioned in the same item.
The flow which makes such process needs to do this once every item is created.
check this one
Update the Created By (AuthorID) Field of a ShareP… – Power Platform Community (microsoft.com)
Thanks for the article. In my scenario I have a Microsoft list (a list of our client sites) with three different “people” columns (one column for my managers, one for my district managers and one for my VP’s. I successfully used your option 1 to filter the gallery by user. However, I am only able to accomplish this against one of the people columns. I would like the formula to use the “or” function (or some other solution) for example to look at all three columns. So, when a VP uses the App he/she will see all of their accounts, a DM will see only theirs and a manager only their one…
Do you know of a way to do this in Power Apps? Even know my list view in the Microsoft List works just fine it does not carry over to the Power App.
I tried using a person column that allowed multiple people (and put all three names in that column) but the formula in Power Apps did not like that solution and would not work.
Thanks in advance,
This is a complex security model. I wrote some generic code below that might work but its going to have delegation errors since SharePoint cannot use the IN operator in a delegation friendly way.
Filter(‘Performance Reviews’, varUserEmail in Manager.Email Or varUserEmail in DM.Email Or varUserEmail in VP.Email)
I’d sooner use a Dataverse as a datasource for your scenario and a Dataverse view for this type of filtering:
i need to set the Person column to multiple selections and insert multiple user in it, in the power app dose not show me the gallery if iset it a multiple selection.
Great guide Matthew, thanks.
I have two kinds of records the I would like to be seen by a person:
1) the record in which the person appear in the field FROM
2) the record in which the person appear in the field TO
I think the ways you proponed are not my case.
The ideal solution would be a sort of “ini” file in which I could store the name of the person, store this ini file in every my collegues computers (specifying the name of the user) o and refer to the ini file in the filter function. Is it science fiction? Thank you! Roberto, Italy
In my opinion, Dataverse would be better for an advanced security scenario like this one. I don’t know of any way to pull it off in a SharePoint list. SP list security settings are very basic.
Great article! I have an additional need where the manager should be able to view and update all of her reporting people. How are people approaching this? Fortunately, the data isn’t confidential so we can get by will onStart filters or the like.
Use method #3 but give the Manager full read access to all records in the SharePoint list settings.