SharePoint Delegation Cheat Sheet For Power Apps

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 SharePointPower Apps Functions With Partial Delegation Support In SharePointKnown Workarounds For Non-Delegable Power Apps Functions In SharePointIsBlankSearchCreate Collections Over 2,000 RowsDouble Collection Size To 4,000 RowsForAll + Filter LoopColumn 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.

Filter
LookUp
Sort [1]
SortByColumns [1]
StartsWith
And, Or [2]
=, <, <=, <>, >, >= [3]


Notes
  1. Sort & SortByColumns function does not support delegation on complex column types: Choice, LookUp, Person, etc.
  2. Not operator does not support delegation
  3. 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.

AddColumns
ClearCollect
Collect
DropColumns
RenameColumns
ShowColumns



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.



IsBlank Function

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


Search Function

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.





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.

Matthew Devaney

Subscribe
Notify of
guest

39 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Manoj
Manoj
1 year ago

This is real helpful. Thanks for this article.

Amber Davies
Amber Davies
10 months ago

Yes please! Sounds awesome 👏🏼

Amber Davies
Amber Davies
10 months ago

Thank you 👏🏼

Johany
1 year ago

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?

Johany
1 year ago

Hi Matthew, I am attaching the screenshot, no warning at all

Delegation.PNG
Erick
Erick
1 year ago

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?

Thanks!

delegationFilter.png
Erick
Erick
1 year ago

Multiline

Erick
Erick
1 year ago

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. 

MARTIN BAPTISTE
1 year ago

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 !

Baptiste ⚡

Jenny
Jenny
1 year ago

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.

Jenny
Jenny
1 year ago

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!

Reiner Knudsen
1 year ago

That is so very helpful (again). Yet, a SharePoint list with 4,000 entries sounds like the ultimate nightmare😀 Thank you for sharing.

Kurt Henderson
Kurt Henderson
1 year ago

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)

Cake
Cake
1 year ago

Hi, Matthew. Thanks for amazing post as usual. But i have one thing to ask you since I experienced this problem, why the file sent to my email is not converted to pdf and it keeps sending me emails with docx file? (I have followed your steps but maybe I missed something).

Best Regards

SS.PNG
Akshat
Akshat
1 year ago

Hi Matthew,

I am having a doubt. I am using SharePoint Online list as my data source in canvas app.
I have added a combo box control and the Items property is configured as: Filter(<SPList>, <Single line of text type column_1> = “<text>”). In the fields property, I have set another single line of text column as PrimaryField and SearchField.
For testing, I have set Data Row Limit as 1 in Settings.

My question is: If Filter query is fully delegable to SP, why am I getting only one result in combo box dropdown?

David
David
11 months ago
Reply to  Akshat

Hello Akshat,

That is because the Data Row limit is not limited (no pun intended) to non-delegable queries. The data row limit is a limit to how many rows can be requested for all types of queries in all controls with one exception.

Galleries, Matt mentions in the other reply, are actually the only exception. Queries there may not be affected by the Data Row limit if and only if the query is fully delegable.

David
David
11 months ago
Reply to  David

One last thing, Akshat,

This is also why setting the Data Row limit to 1 as a means to confirming if your query is delegable is bad because delegable queries are also affected and it only works with galleries (in some cases).

Use the monitor function to check if your queries are delegable.

Elizabeth
Elizabeth
10 months ago

I have recently discovered a little hack that has really improved my app. I have a few galleries that have simple, delegable filters that drastically reduce the number of rows needed but they also require non-delegable filters (dates with ranges). I have found that nesting the delegable Filter function within the non-delegable Filter function really improves load speed and future proofs my app for when the lists will exceed 2000 rows.

Jason
Jason
7 months ago

Hi, I recently started getting into Power Apps development and I must say your content has been an amazing help so thank you. I do have one question in terms of my understanding of delegation. I am doing a look up based on a date picker selection to match with a date column in SQL Server.

Set(
  varCurrentRecord,
  LookUp(
    (table_name),
Text(Date, DateTimeFormat.ShortDate )= Text(dpSearchDate.SelectedDate,DateTimeFormat.ShortDate )
  )
);

The delegation warning refers to the Text (using this to ensure format for both dates are the same to match) part of the code and indicates it will not work properly on large data sets. However if I am returning a few rows, in this case only one row as each date is only related to one row in the table, then can I safely ignore this error? Or is it that even though I am only returning one row if the table in the SQL database has more than 500 rows then it is possible that the row returned can be incorrect?

I do hope I phrased my question correctly and would appreciate any response. Thank you

Michael Damaschke
Michael Damaschke
4 months ago

HAPPY, some new delegational function are available:

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview

These lists will change over time. We’re working to support more functions and operators with delegation.”

Dave
Dave
4 months ago

Great article, but I’m still having issues with some of the Sharepoint things that are supposed to have full delegation support. I’m trying to loop through a few ‘page sets’ of 500 rows from a large SharePoint list, but I can’t do any comparisons between my ShadowID field, (which is a number column in SharePoint – a straight manual copy of the SharePoint ID field, which I know has delegation issues) and my counter. It doesn’t want to let me use &&, < or * in the Filter. So while the paging works if I set them to smaller values, it still cuts off when it reaches 500 in total.

Does anyone have any thoughts on what I’m doing wrong?

Thanks

Clear(colAllStoriesDataRaw);
ForAll(
    colNumbersTable As iterationCounter,
    Collect (
        colAllStoriesDataRaw,
        Filter (
            'Site Pages',
            And(ShadowID > (iterationCounter.Value - 1) * 500,ShadowID <= ((iterationCounter.Value - 1) * 500) + 500)
        )
    );
    
);
Patrice B
Patrice B
4 months ago

Is there a fully delegable way to display SharePoint Document Sets (either just the sets, not the contents, or just the contents not the sets) in a PowerApps gallery since IsFolder and Path are not fully delegable?

Pascal
Pascal
3 months ago

Dear Matthew,

why do I get a delegation warning here:

Filter(‘SPList’, Channel.Value=DropDownChannel.Selected.Value)

I understood that Filter is delegable for choice columns? The dropdown contains the choices (SPList, Channel column).

Thanks!

JoK
JoK
1 month ago

Hey Matthew, great article that I think has me pretty close to resolving my issue. I am attempting to extend the ForAll/Filter method to include a StartsWith instead of equals.

If I hardcode the value, foregoing the ForAll, I get the expected number of results back.
If i use the ForAll with Value, I only get a handful of results back from the main set. Any thoughts on how to handle that? My goal is to add more values to the Forall but need to get this to work first. Thank you!

Clear(colMyCollection);
ForAll(
  [“t”],
  Collect(colMyCollection,
  Filter(SPSource, StartsWith(UserDisplayName, Value)))
);

Clear(colMyCollection);
Collect(colMyCollection, Filter(SPSource, StartsWith(UserDisplayName, “t”)));