cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Andres-san
Frequent Visitor

Filter dropdown by multi-people picker = User().Email

Hello,

 

I am trying to display dropdown values by filtering a SharePoint list that contains a people picker "Assigner" that could have more than 2 people and I am checking if the current user logged in is within the "Assignee" people picker. If it is, then I need to display the title of the record. 

For example (IF you are "Bob"):

TitleAssigneeManager
Project ABob, Matt, JoeMary
Project BBob, JoeDiego
Project CAntonioMary


My dropdown should only display Project A and Project B since "Bob" is assigned as one of the "Assignees". 

The tricky part about this is that the "Assignee" column gets returned as a table and when you wrap around the filter it doesn't work as expected. 

Andres-san_0-1600755655865.png

 

so far I've tried many methods but these have been the closest to the solution:

 

Filter(Projects,!IsEmpty(Filter(Assignees.Email,Email= User().Email))).Title

Filter(Projects,LookUp(Assignees, Email = User().Email,Email) = User().Email).Title

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi@Andres-san,

Based on the issue that you mentioned, do you want to filter the Dropdown based on the current user?

Could you please share a bit more about the scenario, is this 'Assignee' column a Person column that allows multiple select?

Combine that with your description, you want to populate the Dropdown with the filtered Project result based on the current user. If so, you just to modify your formula as below.

Here is my SP list:

100802.png

Try to set the Items property of the Dropdown as below:

 

If(
    !IsEmpty(
        Filter(
            Projects,
            User().Email in Assignee.Email
        )
    ),
    Filter(
        Projects,
        User().Email in Assignee.Email
    ).Title
)

 

Note: 'Projects' is my SP list name. 'Assignee' and 'Manager' are Person columns within the 'Projects'.

101201GIF.gif

To display the corresponding users' mail to the projects, please try to set the Text property of the certain Label as below:

Concat(LookUp(Projects,Title=Title1.Text,Assignee.Email),Email&",")

Note: Title1 is the corresponding Label name to the Assignee column.

101203.png

Please check if the above formula could solve your problem.

Best Regards,

Qi

Best Regards,
Qi

View solution in original post

2 REPLIES 2
robdev
Advocate I
Advocate I

I got the following to work:

 

 

//Note: The column name in my SPO List that holds muliple Person or Group type values is called Assignees

Filter(Projects, CountIf(Assignees.Email,Email=User().Email))
Filter(Projects, !IsEmpty(Filter(Assignees, StartsWith(User().Email, Email))))
Filter(Projects, !IsEmpty(Filter(Assignees, Email=User().Email)))
Filter(Projects, LookUp(Assignees, Email=User().Email,Email))

// My favorite
Filter(Projects, CountIf(Assignees, Email=User().Email))

// If the value after the @ in the email addresses are not exactly the same (when on GCC) you can use the following just compare the values before the @:
Filter(Projects, CountIf(Assignees, First(Split(Email,"@")).Result = First(Split(User().Email,"@")).Result))

 

 

If the value after the @ in the email addresses are not exactly the same (when on GCC for example) you can use the following to just compare the values before the @:

 

Filter(Projects, CountIf(Assignees, First(Split(Email,"@")).Result = First(Split(User().Email,"@")).Result))

 

 

v-qiaqi-msft
Community Support
Community Support

Hi@Andres-san,

Based on the issue that you mentioned, do you want to filter the Dropdown based on the current user?

Could you please share a bit more about the scenario, is this 'Assignee' column a Person column that allows multiple select?

Combine that with your description, you want to populate the Dropdown with the filtered Project result based on the current user. If so, you just to modify your formula as below.

Here is my SP list:

100802.png

Try to set the Items property of the Dropdown as below:

 

If(
    !IsEmpty(
        Filter(
            Projects,
            User().Email in Assignee.Email
        )
    ),
    Filter(
        Projects,
        User().Email in Assignee.Email
    ).Title
)

 

Note: 'Projects' is my SP list name. 'Assignee' and 'Manager' are Person columns within the 'Projects'.

101201GIF.gif

To display the corresponding users' mail to the projects, please try to set the Text property of the certain Label as below:

Concat(LookUp(Projects,Title=Title1.Text,Assignee.Email),Email&",")

Note: Title1 is the corresponding Label name to the Assignee column.

101203.png

Please check if the above formula could solve your problem.

Best Regards,

Qi

Best Regards,
Qi

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,636)