cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AsgeirAlbretsen
Level: Powered On

Delegation warning. The highlighted part of this formula might not work correctly with column "Name" on large data sets

After implementing a simple search function for browsing XLSX files in SharePoint, I encountered this error:

 

"Delegation warning. The highlighted part of this formula might not work correctly with column "Name" on large data sets"

 

My formula looks like this:

 

Filter(Documents, TextInput_ProjectNumSearch.Text in 'Name', ".xlsx" in 'File name with extension' Or ".xlsm" in 'File name with extension')

 

My data set has tens of thousands of files, so delegation issues can be a major problem. The explanation for the warning tells me that the data source might return incomplete results. Does that mean it has no issues finding all the files, but it just can't return more than 500 to 2000 at once? Each search should usually return about 10 results, so if it's still able to find every single file, it shouldn't be an issue. I just want to make sure every single file can be found. 

 

 

3 REPLIES 3
shailendra74
Level 8

Re: Delegation warning. The highlighted part of this formula might not work correctly with column "Name" on large data sets

Hi @AsgeirAlbretsen 

 

Here is the explanation given in Non-delegable limits

 

For example, consider a Filterfunction with a selection formula that can't be delegated, over a data source that contains a million records. Because the filtering is done locally, only the first 500 records are scanned. If the desired record is record 501 or 500,001, it isn't considered or returned by Filter.

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

Thanks.

Community Support Team
Community Support Team

Re: Delegation warning. The highlighted part of this formula might not work correctly with column "Name" on large data sets

Hi @AsgeirAlbretsen ,

Delegation is PowerApps itself limit.

It means that PowerApps can deal with at most 2000 records locally, the other records need to be delegabled to data source to be dealt with. So if your records is larger than 2000, you need to use delegable data source and delegable functions. Or else, data can not be delegabled to data source. In this situation, PowerApps will only return the result of data that is dealt with locally which means that you app will not perform well.

This is regardless of how many data will return. It is related to how many data will be dealt with.

I suggest you use delegable data source(Common Data Service/SharePoint/SQL Server) and delegable functions(modify your formula).

Here are docs about delegable functions for your referenceSmiley Saddifferent data source have different delegable functions)

What's more, do not forget to change your non-delegable limit(up to 2000):

File->app settings->advanced settings7124.PNG

 

 

Best regards,

Community Support Team _ Phoebe Liu

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.
Highlighted
Super User
Super User

Re: Delegation warning. The highlighted part of this formula might not work correctly with column "Name" on large data sets

Hi @AsgeirAlbretsen 

As @shailendra74  and @v-yutliu-msft  said, you cannot filter using "in" with large SharePoint lists.  However, if all of your items that you want to filter end  ".xlsx" or ".xlsm",  then perhaps your can use the EndsWith() function to filter your list.  Both StartsWith() and EndsWith() are delegatable functions so the number of items in your list should not be a problem.  See https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-startswith in the documentation for more information. You will need to use a logical "Or" to obtain both xlsx and xlsm in your filter.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 60 members 4,821 guests
Please welcome our newest community members: