Filter a SharePoint list using People/Group field using the Email

[johnjohnPter]

I have this formula:-

Filter('Work Orders',
('Work Order Status'.Value = "Assigned" Or 'Work Order Status'.Value="New") And
('Technician Name'.Email = User().Email Or IsBlank(colSelectedTechnicianName)) 
)

where i am comparing the People/Group field email with the login user email. but as i know that such a string comparison is case sensitive, and using ToLower() will make my filter none-delegable.. so how i can fix this  to make my string comparison more robust?

[v-jefferni]

Hi @johnjohnPter ,

Both PeopleColumn.Email and User().Email use user principle names from Entra ID/Azure AD, there shouldn't be case matching issue.

Best regards,

[johnjohnPter]

@v-jefferni but let say someone login to office 365 and hence to power apps by entering his email address with upper case,, so will the User().Email be upper cased?

[WarrenBelz]

@johnjohnPter ,

Yes there can be an issue with emails stored in a field in a different case to their email address in AD. I have a particular issue with inconsistency of capitalising their first and last name (or not) - I use a separate user list as a lot more information is stored in it beyond that in AD. You are correct that using Lower on the store field value will cause Delegation issues - you could set a Variable to Lower(User().Email) OnStart and store everything in your list in lower case.

If however you are getting the field content from a Person AD lookup, you should not have an issue with case as it will be the same as User().Email. If you do have a Delegation issue, you may be able to do this.

With(
   {
      _Data:
      Filter(
         'Work Orders',
         'Work Order Status'.Value = "Assigned" Or 
         'Work Order Status'.Value = "New"
      )
   },
   Filter(
      _Data,
      Lower('Technician Name'.Email) = Lower(User().Email) Or 
      IsBlank(colSelectedTechnicianName)) 
   )
)

but note that the top filter needs to return record numbers under your Data Row Limit.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[johnjohnPter]

@WarrenBelz so you mean i should not have issues with this comparison ?

Technician Name'.Email = User().Email

and can you advice more on this "use a separate user list as a lot more information is stored in it beyond that in AD"?? how you build and manage this list?

[WarrenBelz]

@johnjohnPter ,

It depends how you source the data in that Person column. If it is based on Office365Users.SearchUser(), then the email address will be identical to that the user gets with User().Email as they are both coming from the same place (Entra/ Active Directory).

I was assuming the possibility that you did the same as I have done in a number of clients and kept a separate user list, which in my case contains all sorts of information on their authorities and personal details. It is also much easier with a smaller team that is part of a very large organisation which also has limited access to Entra. If so, there needs to be discipline on storing the email address (my users use all lower case), then have a lower case version of User().Email to filter on.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[johnjohnPter]

@WarrenBelz i am using  a sharepoint people/group fields and i am comparing the email of this field with the User().Email.. will the email be in the same case?

[WarrenBelz]

@johnjohnPter ,

If you are asking whether User().Email should be identical case to finding the user in a search on Office365Users.SearchUser, then yes, I would think it should be the same.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps