cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Filtering a gallery by user

Idea: I have a huge list in SharePoint. In power app I want to display only records which are relavant for the users. In the list there is a column with type People or Group which will be used to filter the list in PowerApp.

So I create a new screen, add a galery and in Galery Options under Items I write the following formula:

Filter('Test Travel Request',User().Email = Name_x0020_of_x0020_Traveler.Email)

Unfortunetly it does not work. What would be the correct way of writing this? Also is this the best practise if I want to filter and show only records approriate for each individual User?

 

Thanks!

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering a gallery by user

Hi @Anonymous,

 

Have you checked the Office365users connection?

Office 365 Users connection in PowerApps

There is function available to return the Email attribute:

Office365Users.MyProfile().Mail

In addition, would you please explain a bit for why the Mail attribute under the "Test Travel Request" would contain the "onmicrosoft.com" suffix?

Furthermore, you may consider take use of the substitute function to replace the ".onmicrosoft.com" string with blank:

Substitute(Name_x0020_of_x0020_Traveler.Email,".onmicrosoft.com","")

So the formula should be changed into the following:

Filter(
    'Test Travel Request',
      User().Email = 
 Substitute(Name_x0020_of_x0020_Traveler.Email,
                  ".onmicrosoft.com",
                    "")
          )

Reference:

51.PNG

Please post back if you need any further assistance.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Tudor
Level: Powered On

Re: Filtering a gallery by user

Hi @Anonymous ,

 

I don't think using the Person/Group field to filter is supported yet Smiley Sad, I tried doing a similar thing a few weeks ago and came across these posts:

 

https://powerusers.microsoft.com/t5/PowerApps-Forum/SharePoint-columns-of-person-or-group-type-missing/td-p/970

https://powerusers.microsoft.com/t5/PowerApps-Forum/SharePoint-List-Update-Person-Field/td-p/6980

 

I ended up saving the email address in a text field in my SharePoint list instead and accessing that field to filter the records in my PowerApp, that worked fine.

 

It looks like this feature is currently planned: https://powerusers.microsoft.com/t5/PowerApps-Ideas/SharePoint-Online-Support-quot-Person-or-Group-quot-fields-with/idi-p/3454

 

Hopefully this gets supported soon...

Anonymous
Not applicable

Re: Filtering a gallery by user

After some trial and error I think I found the problem. 

Switching formula to:

 

Filter('Test Travel Request',User().FullName = Name_x0020_of_x0020_Traveler.DisplayName)

Works as intended. However I am not too fond of using this as some employees have same display name, email would be a lot better as it can act as an unique primary key. 

 

The issue with this formula:

 

Filter('Test Travel Request',User().Email = Name_x0020_of_x0020_Traveler.Email)

Is that User().Email is of the following format = Firstname.Lastname@company.com

 

While the Name_x0020_of_x0020_Traveler.Email is of the following format = firstname.lastname@company.mail.onmicrosoft.com

 

Obviusly the = operator will not return any record as true. Is there a way to check using email? Or is there another unique key I can use to check users?

Community Support Team
Community Support Team

Re: Filtering a gallery by user

Hi @Anonymous,

 

Have you checked the Office365users connection?

Office 365 Users connection in PowerApps

There is function available to return the Email attribute:

Office365Users.MyProfile().Mail

In addition, would you please explain a bit for why the Mail attribute under the "Test Travel Request" would contain the "onmicrosoft.com" suffix?

Furthermore, you may consider take use of the substitute function to replace the ".onmicrosoft.com" string with blank:

Substitute(Name_x0020_of_x0020_Traveler.Email,".onmicrosoft.com","")

So the formula should be changed into the following:

Filter(
    'Test Travel Request',
      User().Email = 
 Substitute(Name_x0020_of_x0020_Traveler.Email,
                  ".onmicrosoft.com",
                    "")
          )

Reference:

51.PNG

Please post back if you need any further assistance.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Filtering a gallery by user

@v-micsh-msftThank you Michael so much for the link. Great info!

 

As to why the Mail attribute contains onmicrosoft.com suffix:

The column type is Person or Group for the Name_x0020_of_x0020_Traveler, an user only enters the name and that is it, email is automatically attached to that person. Now I do not know why my organziation chose to set it up this way? Maybe there is a benefit but I work in different department and can not answer you why all our users have this suffix. Do you think that is a bad setup and should I raise it to appropiate levels?

 

This still solves my problem because when I use Office365Users.MyProfile().Mail it gives an email with the onmicrosoft.com suffix so I can easily comapre Office365Users.MyProfile().Mail = Name_x0020_of_x0020_Traveler.Email . 

Daniel_Pipe
Level: Powered On

Re: Filtering a gallery by user

The other problem with this method (maybe not for your use) is that if you change the People column to "allow multiple selections", the filter fails. I can't find a workaround to filter a gallery to only info relevent to a list of staff. Also, you can't seem to filter a gallery by sharepoint group membership. It seems like you have no choice but to give access to all info that the gallery presents to every person you share the app with and cannot limit a person's filtered view?!?!

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,375)