Filter on Multiselect Person field in SharePoint

[haniel]

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?

[WarrenBelz]

@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]

@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

[WarrenBelz]

@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)

[johnjohn123]

@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?

[WarrenBelz]

@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]

@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?

[WarrenBelz]

@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.