User().Email not working in a filter on a gallery for some users

[Anonymous]

I am using the User().Email (and more recently the Office365Users.MyProfileV2().mail) function to filter a gallery with a SharePoint list data source. The filter picks out items in the list where the Line Manager email is the same as the user logged in. The Line Manager email address that is in the SharePoint list and being filtered against is taken from the O365 connector so there are no spelling mistakes etc. This works perfectly for me, for everyone I tested it with, and the majority of the people who have used the app since it went live. There are 2 users (that I know of) however that it doesn’t work for. When I filter manually in the app with their email address it works perfectly.

I have used the Lower and Trim function to ensure that the email addresses match exactly and again works for everyone except two users. I have on the front screen of the app the User().FullName field and that does work for them so it doesn’t seem to be an issue with the O365 Data Connection.

Any ideas on how to fix this?

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about the "Line Manager" field in your SP List? Is it a Text type column or Person type column?

Could you please show more details about the formula you used in your canvas app?

Further, do you mean that the User().Email formula could not work in your Filter function?

Please add a Label in your app, set the Text property to following:

User().Email

then check if there are matching records in your SP List whose Line Manager email is same with the displayed Label Text value.

If the "Line Manager" field is a Person type column in your SP List, I think this issue would not be shown up. Because, the Person type column in SP List essentially pulls user Profile info from Office 365 Server.

Please set the Items property of the Gallery to following:

Filter(
       'Your SP List',
       'Line Manager'.Email = User().Email
)

Note: I assume that the 'Line Manager' field is a Person type column in your SP List. On your side, please use Person type column to store the Line Manager email address in your SP List.

If the 'Line Manager' field is a Text type column in your SP List, please set the Items property of the Gallery to following:

Filter(
       'Your SP List',
        Lower(Trim('Line Manager')) = Lower(User().Email)
)

Note: The 'Line Manager' represents the Text type column in your SP List, to store the email address of the Line Manager.

If the issue still exists, please consider re-generate a new app based on your SP List, then check if the issue is solved.

Best regards,

[Anonymous]

Hi,

The PowerApp allows users to submit information that requires their Line Manager approval. They select their Line manager's email from a drop down that is populated using the office 365 data connection.

This is then submitted to the SP list as a text field.

The app then has a separate section that allows the managers to approve/decline the request that has been assigned to them using a gallery. I then originally used this Filter on the Item set of the gallery:

Filter(

    'SPList',

    If(

        'Line Manager Name' = User().Email,

        "Pending" in 'Line Manager Approval'.Value,

        If(

            'General Executive Name' = User().Email && "Approved" in 'Line Manager Approval'.Value,

            "Pending" in 'GE Approval'.Value

        )

    ),

    Ascending

)

This worked fine throughout testing for lots of users. The app went live and two users are now reporting that they cannot see anything even though the request has been submitted.

I then changed the filter to:

Filter('SPLIST',

If(Lower(Trim('Line Manager Email')) = Lower(Trim(Office365Users.MyProfileV2().mail)),

"Pending" in 'Line Manager Approval'.Value,

If(Lower(Trim('General Executive Email')) = Lower(Trim(Office365Users.MyProfileV2().mail))

 && "Approved" in 'Line Manager Approval'.Value,"Pending" in 'GE Approval'.Value)),

Ascending)

Which resulted in the same issue.

The User().Email in a label does display their email address and so does the User().FullName.

I have now tried using the User().FullName instead of User().Email:

Filter(

    'SPLISTl',

    If(

        'Line Manager Name' = User().FullName,

        "Pending" in 'Line Manager Approval'.Value,

        If(

            'General Executive Name' = User().FullName && "Approved" in 'Line Manager Approval'.Value,

            "Pending" in 'GE Approval'.Value

        )

    ),

    Ascending

)

Which has the same result.

 Interestingly I am now being affected when I wasn't before. The filter works for my email address in the Studio but on the published app there are no results when there should be. We are looking into 'Approvals' in PowerAutomate to replace the 'Approve/Decline' process but the filter on users emails is still required for users being able to see what they have submitted.

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Do you mean the issue does not occur when you or your end users run your published app before?

Do you store the submitted email address into a Text field in your SP List?

Based on the Filter formula that you mentioned, I think there is something wrong with it. As an alternative solution, please consider try the following workaround:

Set the OnStart property of App to following:

Set(CurrentUserEmail, User().Email)

Set the Items property of the Gallery to following:

Filter(
      'SPList',
       If(
         Lower('Line Manager Name') = Lower(CurrentUserEmail),
        "Pending" in 'Line Manager Approval'.Value,
         Lower('General Executive Name') = Lower(CurrentUserEmail) && "Approved" in 'Line Manager Approval'.Value,
         "Pending" in 'GE Approval'.Value,
          true
     )
)

If you want to sort above filter result, please modify above formula as below:

Sort(
     Filter(
           'SPList',
           If(
              Lower('Line Manager Name') = Lower(CurrentUserEmail),
              "Pending" in 'Line Manager Approval'.Value,
              Lower('General Executive Name') = Lower(CurrentUserEmail) && "Approved" in 'Line Manager Approval'.Value,
              "Pending" in 'GE Approval'.Value,
               true
          )
     ),
     Title,
     Ascending
)

Please consider take a try with above solution, re-load your canvas app, check if the issue is solved.

Note: There is something wrong with your Filter formula, please do not type a Ascending within your Filter function directly, the Ascending should be acted as Sort Order within Sort function or SortByColumns function.

Best regards,

[paulewert]

My first thought is that those two users where it is not working have a user principal name different from the username portion of their email address. We have this happen quite a bit in our environment (my own account is this way). User().Email may not return a value which matches the Office365User.MyProfileV2().mail action's return value. This is particularly true for users with multiple registered SMTP values (these can be seen using Outlook client's Address Book search and then viewing the "E-mail Addresses" tab of the contact card). Thus, an attempt to match the result from Office365User.MyProfileV2().mail to User().Email may not work for all users (as it doesn't for me).