Unanswered

Filtering a gallery by user

(0) Share

Report

Posted on by Community Power Pla...

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Community Power Pla... on at

Like (1)

Report

Hi @Anonymous ,

I don't think using the Person/Group field to filter is supported yet , 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...

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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?

Was this reply helpful? Yes No
Verified answer

v-micsh-msft on at

Like (1)

Report
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:

Please post back if you need any further assistance.

Regards,

Michael

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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 .

Was this reply helpful? Yes No
Daniel_Pipe 57 on at

Like (1)

Report

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?!?!

Was this reply helpful? Yes No