I have a SharePoint list where each item has a person field identifying whose item it is. When displaying these items in a gallery, the filter expression below is not delegated but if use a plain text field like 'Title' in the comparison it is delegated. To clarify, the filter expression works fine in either case but I presuming the non-delegated method will not scale up and will fail to be accurate the moment the 501st record is added to the list.
SortByColumns(Filter(Goals, Employee.DisplayName = varSelectedUser),"Title", Ascending)
SortByColumns(Filter(Goals, Title = varSelectedUser),"Title", Ascending)
Being able to display records associated with a specific user seems like basic functionality, even if the list exceeds 500 entries.
Would it make sense to simply use a flow to write the employee's display name to a plain text field in the SharePoint list and use that?
I have read about some fantastically complex methods of overcoming the delegation issue but I'm confused to whether they apply in this situation. Is providing a way to filter a gallery by person really this unusual?
What is actually the correct way to simply filter gallery items from a SharePoint list where the person column (Employee.DisplayName) is equal to a given value?
You said you are presuming it won't work for the 501st record. how did you check now that it's not delegating?
Also, the number of records for non delgable queries has been increased to 2000 now. Check this out - https://powerapps.microsoft.com/en-us/blog/powerapps-data-row-limit-for-non-delegable-queries/
One more thing , which I am not sure could be a possible issue is, use of "." in the column name Employee.DisplayName. I hope that doesn't create some kind of issue with how powerapps interprets "."
I was going by the blue underling of that part of the expression but it is at least good to know that the limit is higher than previously thought.
Regarding Employee.DisplayName, the period is not part of the column name but is a reference to property. The SharePoint person field returns an object with the following properties that show up in PowerApps.
If I filter on a column that returns a text value, no blue dot so I'm thinking the lack of delegation has something to do with processing the more complicated return of the people picker object - just no sure what the best way to address it
Yes you are correct, PA/SP cannot currently delegate on selected items in choices, properties of non-text items (i.e People Picker) and logic operators <=> against any non-text or non-number columns.
I would suggest a solution like what you described - if your source data is already filled with all of the items, then create a text column and use Flow to fill it with Employee.Displayname.
Create a text column Maybe EmployeeDisplayNameValue and include it in the gallery as a hidden field, then have your edit/new screens set the textbox within the datacard to Text(Employee.DisplayName) so that the App will write back this data with each new or edited entry.
If you have not filled in all data yet, use the above fields and just edit all your existing entries.
Items = to:
SortByColumns(Filter(Goals, EmployeeDisplayNameValue = varSelectedUser),"Title", Ascending)
This should now be Delegable and any new items will fill the delegable field so will also be searchable 🙂
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.