3 Ways To Filter A Power Apps Gallery By The Current User

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 FunctionGet The Current App User's Email Address
Option #2: Use Power Automate To Set SharePoint List Item PermissionsBuild A Power Automate Flow Update List Item PermissionsView The Secured SharePoint List As The Current User
Option #3: Change Item-Level Permissions in SharePoint List SettingsGrant The User Who Created The SharePoint List Item Edit & Read AccessView 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)

EmployeeReviewDateScoreManager
David Johnson1/21/20224Matthew Devaney
Mary Baker1/19/20225Matthew Devaney
Sarah Green1/12/20223Matthew Devaney
David Johnson1/19/20213Matthew Devaney
Mary Baker1/10/20214Matthew Devaney
Sarah Green1/9/20213Matthew Devaney
Mary Baker1/15/20204Matthew Devaney
David Johnson1/3/20202Matthew Devaney

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.

Set(varUserEmail, User().Email);



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.

'Performance Reviews'



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.





Questions?

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.

Matthew Devaney

Subscribe
Notify of
guest
18 Comments
Oldest
Newest
Inline Feedbacks
View all comments
David McKenzie
David McKenzie
4 months ago

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.

Dawid Ziolkowski
4 months ago

Hi Matt,
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 🙂

Regards
Dawid

Srinivas Varukala
4 months ago

+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

Robin
4 months ago

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 “name@domain.com”

Office365Users.MyProfileV2().mail works fine, like you said

Paul
Paul
4 months ago

HI Matthew

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

Paul

Paul
Paul
4 months ago

Hi Matthew

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?

Paul

Paul Saxton
Paul Saxton
4 months ago
Reply to  Paul

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?

Paul

Paul Saxton
Paul Saxton
4 months ago

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?

Emily
Emily
3 months ago

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.

Emily
Emily
3 months ago

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.

Capture.PNG
Ahmed El Sebaei
Ahmed El Sebaei
1 month ago

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)

Owen Keefe
Owen Keefe
12 days ago

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,
Owen