Comparing Emails (SharePoint vs. User()) – Lower/Upper

Comparing Emails (SharePoint vs. User()) – Lower/Upper
Fabian Heil
Fabian HeilAug 02, 2022 04:35 AM
Hi, i just found a strange behaviour in my App. In many cases, i collect items with a filter comparing …
1. Comparing Emails (SharePoint vs. User()) – Lower/Upper

Fabian Heil
Posted Aug 02, 2022 04:35 AM
Hi,

i just found a strange behaviour in my App. In many cases, i collect items with a filter comparing the email adresses (because this is always unique).
E.g. collect all Items, where i am the “created by” in SharePoint
Filter(<SharePointList>,CreatedBy.Email = User().Email)

In most castes, this works fine, but now i found a user where the email adress differs in lower/upper and therefore the filter doesnt work.
The function User().Email shows “Name.Surname@email.com”
The SharePoint created by field shows “name.surname@email.com” (only small letters). This seems to be only the case for some single users.

The only idea would be to put “Lower(…)” to both sides, but this causes the formula to be not delegable.
Do you have any other idea how to solve this?

——————————
Fabian Heil
——————————

2. RE: Comparing Emails (SharePoint vs. User()) – Lower/Upper

Fabian Heil
Posted Aug 08, 2022 07:22 AM
And to understand the issue better: From which system do the information come from?

–> From which system does User().Email come from?
–> From which system does the “Created By” SharePoint Userprofiles come from?

——————————
Fabian Heil
——————————

3. RE: Comparing Emails (SharePoint vs. User()) – Lower/Upper

Bronze Contributor
Steve Bolton
Posted Aug 09, 2022 09:12 AM
Hi Fabian,

The User().Email comes from the logged in user of the power app. Created By is coming from who was logged into SharePoint and created the record. I have seen some case differences as well, and it appears to come from how the Active Directory account was set up by our IT department. SharePoint uses whatever was entered in Active Directory.

I have not tested this but I think you could get around the delegation issue by using an OR statement in your query. Do not add Lower( ) to both sides. Keep that all on the app side and do something like this:
Filter( Or(<SharePointList>,CreatedBy.Email = User().Email, <SharePointList>,CreatedBy.Email = Lower(User().Email) ) )

——————————
Steve Bolton
Sr. Business Analyst
——————————

4. RE: Comparing Emails (SharePoint vs. User()) – Lower/Upper

Bronze Contributor
Frank Voortman
Posted Aug 10, 2022 08:29 AM
I’ve seen this non delegable behavior as well. My suggestion is the following:

UpdateContext({ varUserEmail: Lower(User.Mail)});

Filter( <SharePointList>,CreatedBy.Email = varUserEmail)

——————————
Frank Voortman
Office 365 Solution Architect
Blaricum
Netherlands643781072
——————————

5. RE: Comparing Emails (SharePoint vs. User()) – Lower/Upper

Fabian Heil
Posted Aug 16, 2022 07:53 AM
This will not work, because not all email adresses are written in small letters only.
Some are “name.surname@email.com” and some are “Name.Surname@email.com”

——————————
Fabian Heil
——————————

6. RE: Comparing Emails (SharePoint vs. User()) – Lower/Upper

Top Contributor
Warren Belz
Posted Aug 17, 2022 01:18 AM
if your use
Filter(
<SharePointList>,
StartsWith(
CreatedBy.Email,
User().Email
)
)​
it will work as StartsWth is not case sensitive. It is also Delegable (Lower() is not)

——————————
Warren Belz
Queensland Australia
——————————