SharePoint Delegation Cheat Sheet For Power Apps
This SharePoint delegation cheat sheet is the quickest way to check which Power Apps functions support delegation. When you get a delegation warning like this: “the Filter part of this formula may not work correctly on large data sets”, change your code to only use functions found on this page. It will remove the delegation warning.
Table Of Contents: • Power Apps Functions With Full Delegation Support In SharePoint • Power Apps Functions With Partial Delegation Support In SharePoint • Known Workarounds For Non-Delegable Power Apps Functions In SharePoint • IsBlank • Search • Create Collections Over 2,000 Rows • Double Collection Size To 4,000 Rows • ForAll + Filter Loop • Column Indexing For Large Datasets Over 5,000 SharePoint List Items
Power Apps Functions With Full Delegation Support In SharePoint
When these functions are used to get data from SharePoint the full result is returned.
- Sort & SortByColumns function does not support delegation on complex column types: Choice, LookUp, Person, etc.
- Not operator does not support delegation
- SharePoint ID column only supports the = operator. Does not support <, <=, <>, >, >=
Power Apps Functions With Partial Delegation Support In SharePoint
When these functions are used to get data from SharePoint only the first 500 records are returned by default.
This can be increased to 2,000 records by changing the data row limit in the Power Apps advanced settings menu.
Known Workarounds For Non-Delegable Power Apps Functions In SharePoint
Delegation is not supported for these Power Apps functions in SharePoint.
The IsBlank function does not support delegation in SharePoint.
Filter('SharePoint List', IsBlank(Title))
We can use the Blank function to achieve the same result.
Filter('SharePoint List', Title=Blank())
The Search function does not support delegation in SharePoint.
Search('SharePoint List', "Cat", "Title")
We can use the StartsWith function to get all results where the start of the word matches the search terms.
Filter('SharePoint List', StartsWith(Title, "Cat"))
A full delegation workaround for the Search function is possible by performing the search in a Power Automate flow and sending the result back to Power Apps.
Create Collections Over 2,000 Rows
The ClearCollect function supports partial delegation in SharePoint. However, there are ways to create collections over 2,000 rows.
ClearCollect(colCollectionName, 'SharePoint List')
Double Collection Size To 4,000 Rows
We can use this Power Apps code to load up to 4,000 rows into a collection. First, we load the list data into 2 temporary collections. The 1st temporary list is sorted in ascending order and the 2nd temporary list is sorted in descending order. Then we merge both temporary collections into a single collection and remove the duplicates.
This technique only returns the full result when the filtered SharePoint list contains less than or equal to 4,000 items. For more information check out this article.
Concurrent( ClearCollect( colChunk1, Sort('SharePoint List', ID, Ascending) ), ClearCollect( colChunk2, Sort('SharePoint List', ID, Descending) ) ); ClearCollect( colYourCollectionName, colChunk1, Filter(colChunk2, Not(ID in colChunk1.ID)) ); Clear(colChunk1); Clear(colChunk2);
ForAll + Filter Loop
The ForAll function can also be used to exceed the delegation limit. It can loop over a set of single column table of values an return the results into a collection. The only limitation is each iteration of the loop can only return up to 2,000 results. For a full example of the technique check out this article.
Clear(colYourCollectionName); ForAll( ["New", "Submitted", "Approved"], Collect( colYourCollectionName, Filter('SharePoint List', SubmissionStatusColumn=Value) ) );
Column Indexing For Large Datasets Over 5,000 SharePoint List Items
When the number of items in a SharePoint list exceeds 5,000 items the following warning appears. The Power Apps Filter function and LookUp function will not evaluate any items past the 5,000th item in the list until column indexing is turned on. Each column used in the filter criteria must be indexed to return the full result.
|“The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.”|
To enable column indexing open the SharePoint list settings…
…and select Indexed columns.
Choose the column to be indexed from the dropdown menu and click Create.
There can be up to 20 indexed columns on the same SharePoint list.
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
Questions About Delegation In SharePoint?
If you have any questions about Power Apps + SharePoint Delegation Cheat Sheet please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
This is real helpful. Thanks for this article.
I created it because the information was buried too deep within the Microsoft documentation. Hopefully it will help you and I both get what we need faster.
Also, I have a solution for multiple person, multiple choices and multiple lookup columns. Maybe I will publish that next.
This is awesome, I have an issue now that I haven’t found a solution Matthew perhaps. you can give me a hand
I need to put on a combo box items something like “SharePoint List”.Title without delegation, my SP List has around 1000 items.
Any idea legend?
Can you please share a screenshot of the code that has the delegation warning?
Hi Matthew, I am attaching the screenshot, no warning at all
Where is the delegation warning? I was hoping to see it in your screenshot.
Sensational Matthew, once again, congratulations on the content.
Now, look how interesting. I use the filter plus the = sign and still get the delegation alert. Should I ignore this alert or use a solution with power automate to get around it?
What SharePoint list data type is GUID_Projecto?
Multiline text is a non-delagable data type. I will add it to my guide.
Why are you using a multiple line text column to store what looks like a GUID value? Shouldn’t it be single-line text?
Thanks Matthew for clearing that up, the fact that the data type is multiline makes perfect sense that it is not delegable. I really made the wrong decision when I started my application. It was a learning experience.
Great article Matthew (as usual 😉),
Are you sure that = operator cannot be used on the ID column?
On the Microsoft documentation it is:
The SharePoint ID field of a table is a numeric field in Power Apps. However, SharePoint only supports the equal operation (‘=’) for delegation on an ID field.
SharePoint – Connectors | Microsoft Learn
Or did I misunderstand?
Have a nice day !
You are correct. I have fixed the error. Thank you for contributing to my blog. Comments like yours help ensure the quality of my work remains high.
I used a flow to copy the ID column to a number-type column. That’s how I’ve been getting around delegation for the ID.
Thank you for this post! When you use a flow to pull in a large dataset, how do you deal with the time lag? I tried this and I found that the loading time seemed prohibitive. Thanks!
Limiting the number of columns and rows returned to the app helps. A SharePoint list will return many unused columns by default.
That is so very helpful (again). Yet, a SharePoint list with 4,000 entries sounds like the ultimate nightmare😀 Thank you for sharing.
Yes. It is. SQL or Dataverse is ideal at this point. But then again, SharePoint is included in the standard license. So many must make due with the only thing they have. *shrug*
Hi Mathew, this article is very helpful and I have it bookmarked. Thank you. One thing you state in the article is that Filter function supports the “=” sign when used with the ID column. I have a filter statement (below) which is using the ID field but is a lookup. The scenario is I have a parent table “Requests” and a child table “Request Notes” and the child table has a lookup column to the parent table called “Request Notes.Associated Request”. I’m using this in the items property of a gallery to filter the notes for a particular request. I’m sure this is a very common thing to do but I will always get a delegation warning on the following filter function. Any suggestions?
Filter(‘Request Notes’,’Associated Request ID’.Id=varCurrentRequest.ID)