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?

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Super User
Super User

@WarrenBelz  thanks for the tag.

@haniel 

I was able to achieve this by doing these steps:

1. use Set() to setup the User variable

Set(vUser, User())

 

2. use ClearCollect to create collection

ClearCollect(
    myCollection,
    Filter(
        myList,
        vUser.Email in UserPerson.Email
    )
)

 

Let me know how you get on.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @haniel ,

The first thing to avoid delegation issues is to set a variable at App OnStart for the User's email

Set(vUserMail,User().Email)

Then

ClearCollect(
   myCollection, 
   Filter(
      SPlist, 
      vUserMail in Reviewers.SelectedItems
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Hi @WarrenBelz ,

 

Thanks for your prompt reply.  I did, in fact use a variable for the User().Email to avoid delegation.  As for the filter, the SelectedItems method does not exist on the People Picker field.

Ok Thanks @haniel ,

I was assuming a Combo-box as you mentioned multiple selected items.

What type of control are you using?

I'm trying to just load the items from my SharePoint list into a collection.  So, the trick here is to be able to filter the items in my SharePoint list based on the presence of the user name in a multi-select person field.  The only work-around I could think of is store all the user's emails in a separate field as a semicolon-delimited text field and then do a text comparison - not ideal though.

OK @haniel - I don't use these fields - I will tag a colleague @Eelman to see if he can assist

Eelman
Super User
Super User

@WarrenBelz  thanks for the tag.

@haniel 

I was able to achieve this by doing these steps:

1. use Set() to setup the User variable

Set(vUser, User())

 

2. use ClearCollect to create collection

ClearCollect(
    myCollection,
    Filter(
        myList,
        vUser.Email in UserPerson.Email
    )
)

 

Let me know how you get on.

View solution in original post

v-yutliu-msft
Community Support
Community Support

Hi @haniel ,

Actually, your this formula should be right:

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

But "in" and User() is not delegable in sharepoint list.

Could you tell me how many records in your sharepoint list?

Do you mean that you do not want to change non-delegable limit? You just want to set it to 500?

If your record in sharepoint list is less than 500, then delegation problem will not affect your app performance.

If your record is more than 500, then we need to figure out the problem of delegation.

 Since Concat is not delegable neither, so I not recommend you use this function.

 

I suggest you save sharepoint data in collection, then filter the collection.

Because collection does not have the problem of delegation.

Try this:

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

One collection could store more than 10000 records.

If your records are too large, you could consider saving data to several collections, then filter them one by one.

For example:

ClearCollect(collection1,Filter(SPlist,ID<=10000);
ClearCollect(collection2,Filter(SPlist,ID>10000),ID<=20000);
....  //save to multiple collections
Collect(myCollection1, Filter(collection1, User().Email in Reviewers.Email);
Collect(myCollection2, Filter(collection2, User().Email in Reviewers.Email);
....//filter based on different collections
Collect(alldata,myCollection1,myCollection2,....)
//save them together in one collection

 

Here's a table about delegable functions of sharepoint list:

Item Number Text Boolean DateTime Complex [1]
Filter Yes Yes Yes No [4] Yes
Sort Yes Yes Yes Yes No
SortByColumns Yes Yes Yes Yes No
Lookup Yes Yes Yes No Yes
= Yes Yes Yes No [4] Yes
<, <=,<>, >, >= Yes [2] No No No Yes
StartsWith - Yes - - Yes
IsBlank - No [3] - - No

Other functions are all not delagable (in,User(),Concat..)

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
haniel
Kudo Collector
Kudo Collector

Turns out that your solution does work.  Not sure why it didn't work on my original list.  Thanks for your help.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,479)