cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
haniel
Kudo Collector
Kudo Collector

Filter on Multiselect Person field in SharePoint

Hi all,

 

I was wondering if anyone faced the following scenario I'm trying to solve:

 

  1. I have a SharePoint list with over 500 items in it.
  2. The SharePoint list has a multi-select person field, called Reviewers.
  3. The app is limited to 500 items for performance reasons.

CHALLENGE:  I am trying to load only items, where the current user appears in the Reviewers field using the Filter functionality as I am filtering on other fields as well.

 

I tried the following scenarios with no luck:

 

Collect(myCollection, Filter(SPlist, User().Email in Reviewers.Email)

Collect(myCollection, Filter(SPlist, User().Email in Concat(Reviewers.Email,Email&";"))

 

Any ideas how I can achieve what I'm trying to do?

16 REPLIES 16

@GET123 ,

This is an old post and one thing has changed since (User() is Delegable), however if you are trying to filter a 47,000 item list for a value inside a multi-select field, this is not possible as you need to use the in Function which is not Delegable.

johnjohn123
Super User
Super User

@WarrenBelz  so what we need to do if we want to filter a SharePoint multi-select People/Group field ? i am facing this issue inside my project.. any advice?

 

Is for example creating a separate SharePoint list to store the single Person email + the existing SharePoint list item ID? in other word instead of managing the info of the people inside a People/Group SharePoint field inside the existing list to store this info inside a separate list?

the new list will have this data

 

ID | People/Group | ExsistingItemID

1  | userA               | 100

2  | userB               | 100

3  | userC               | 200

4  | userA               | 300

@johnjohn123 ,

It would be better to post a new thread on this. I totally agree that people fields should be avoided if you have an alternative (I do not use them at all)

@WarrenBelz what are the available approaches then? can you advice? is using a separate list as i mentioned a valid approach to go with? and you mentioned (I do not use them at all) so how you implement a scenario where you want to store multiple users for an item? and be able to filter based on this field without getting delegation warning?

@johnjohn123 ,

My comment was around the field type - I use multi-choice fields and would store the user's name or email in them. You can use Office365Users if you want other detail (which you will find is infrequent generally - although I have a reference list of my users as I store a lot of other details not available in Person fields).

As far as the Delegation warning however the in filter is not Delegable, so you are still in the same position.

johnjohn123
Super User
Super User

@WarrenBelz so how you use multi-choice fields to store users? can you explain this in more details please? also as far as i know, also filtering a SharePoint choice field which allow multiple selection will not work either (it will raise a delegation error)..

 

my second question is; for the approach i proposed of defining a separate list to store the ExsistingItemID and the userEmail; is that if i want to know the ExsistingItemID for the related userEmail >> i will query this new list and get all the ExsistingItemID related to a useremail.. then i will build an array of ExsistingItemID and then i will query the ExsistingsarePoint list based on the array of ids i have.. so finally i can get all the ExsistingItem details for a specific userEmail.. am i correct?

@johnjohn123 ,

I thought I answered that - I only store the name or email address in the field and secondly, it does not solve the Delegation issue as in is not Delegable. There is a possible workaround to Delegation with a Collection I answered on this post today.

From what I can understand from your second question, as long as you make a collection with your reference list, it should take care of the potential Delegation issue with the Relational Query (which is not Delegable).

I trust we can close this old post now.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (4,403)