Filtering a gallery by user

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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?

[v-micsh-msft · Accepted solution]

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

[Anonymous]

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

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