I was wondering if anyone faced the following scenario I'm trying to solve:
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?
Solved! Go to Solution.
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.
@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
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?
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.
@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?
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us for the next call on June 15, 2022 at 8am PDT.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.