Problem filtering SPO List with User().Email

(0) Share

Report

Posted on by EmMa73

Please, help me to solve this mistery.

I have an App with a gallery based on SharePoint list and I filter the gallery on current user:

Filter(MySPOList, Employee = User().Email)

It works perfectly.

Then I have this function (used on OnVisibile of First Screen):

If(
CountIf(
MySPOList,
Employee = User().Email
) = 0,
"No item found"
)

this not work only for particular users.

I found that this users have an inusual email address in SPO. For example: Name.Surname@company.com instead of name.surname@company.com. Here an example (note that the only field that match with User().Email is the UserName:

I know that PowerApps is case sensitive and that User().email is always lower case so it's normal that the second function don't work.... but why the first work????!!!!

Thanks, EmMa73

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@EmMa73

It's not always lowercase, so don't rely on that. Normally the two emails are identical, but this is all based on how Office 365 and SharePoint sync their users and the order in which they are created in the SharePoint user manager.

Please consider changing your Formula to the following:

With({_eml:Lower(User().Email)}, If(CountRows(Filter(MySPOList, Lower(Employee) = _eml)) = 0, "No item found") )

But, you mention this is in the OnVisible action of your screen...this really will have no impact.

I hope this is helpful for you.

Was this reply helpful? Yes No
EmMa73 50 on at

Like (0)

Report

Thank you for your reply.
I used the CountIf in the OnVisibile action of my screen because I need a variabile to print the "No item found" message into a label.
Actually I solved this problem using the Lower() function:
Set(
Varmessagge,
If(
CountIf(MySPOList,Lower(Employee) = User().Email
) = 0,
"No item found"
)
)
But I was really interested in understanding why (with the same user) in the first function - Filter(MySPOList, Employee = User().Email) - the query works correctly without Lower() and not in the second.
Is there any reason related to the context (filter on gallery vs filter out of gallery)?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@EmMa73

Yes, so first, I would simply put that formula in the Label and skip the variable - there's no real need for it.

As for the email address. Check the letter case of the email address both in SharePoint and in the Office 365 users list. They are NOT always the same letter case for every user. The Lower function will lower it all and it will compare properly.

Was this reply helpful? Yes No
EmMa73 50 on at

Like (0)

Report

@RandyHayes
Thank you.
Yes, I verfy that the letter case of the email address in SharePoint and in the Office 365 users list are different. Obviusly the Lower function will lower it all and it will compare properly but I even curious why in one case it's unecessary and in other case it's necessary.
I repeat that if I filter the gallery without Lower (in Items property) it's work correctly and find the user even he have different address beetween SharePoint and Office 365.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report

@EmMa73

So SharePoint and Office 365 both have separate user management. Once SharePoint was connected with 365, it was decided that the users should sync between the two. This happens behind the scenes.

However, there is something about the order in which a user is added to the SharePoint users - I could not tell you what that is, but is causes the case issues.

Also important to note, the Lower function in your formula will cause a delegation issue. If your list will never grow beyond the record limit of the app, or performance is not and issue, then you can ignore it.

Otherwise, you can use the StartsWith function instead as it IS delegable and case insensitive.

So, StartsWith("YourName@Company.com", "yourname@company.com") will return true.

Was this reply helpful? Yes No
EmMa73 50 on at

Like (1)

Report

@RandyHayes
Thanks very much. Will be very usefull the StartsWith function because if I use Lower, I have a problem with delegation.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@EmMa73

Yes, that should work for you.

If all is working, please go back over the posts in this thread and mark one as a solution that best fit your needs so that others can find this should they have similar problems.

Was this reply helpful? Yes No