cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilD
Level 8

Confirm this sort will scale to large SharePoint list and be delegated

I have an app that dispatches tasks that are routinely created and quickly completed in an hour or so. I'm using a SharePoint list to store the data with all plain text fields except a lookup to a location table and a lookup to an employee table (no SharePoint choice fields are used).

 

My questions relate to delegation. I believe I have this correct, but I wanted to confirm to be sure I am not missing something. Essentially, I just need to be able to return the last X number of latest records based on a date field in the SharePoint list. I don't need to match a date, I just need to sort the whole list and return the last X records.

 

My Gallery's Items property is shown below. My SharePoint list currently has around 600 rows. If I set the row limit for non-delegated queries down to a very low number like 4, this behaves as I would expect, returning 4 items with the most recent schedule dates in each of my six filtered categories (24 total). It appears this is delegating both the inner Sort for [ScheduleDate] and the equals for [TaskStatus].

 

Am I correct in assuming that the delegation warning shown below refers to the sorting of the items after they are returned from the source (which is fine)?

 

Also, will this continue to scale up if the list contains thousands of records? I have all the main category columns indexed, will passing the view threshold in SharePoint have any effect on the delegation of my inner sort shown below?

 

DelegationWarning.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Confirm this sort will scale to large SharePoint list and be delegated

@PhilD,

search() is not delegatable in SharePoint. It is recommended to use Filter(datasource,StartsWith(... instead and you can work with very large lists. If you want to use Search() with a collection of your datasource you can do it without a delegation warning. However, Collect() is also limited by delegation so you would need to Collect(colDS,  Sort(datasource, ID, Descending)) first then Search colDS. You would still hit a 2000 limit but it would work for the last 2000 items collected as described by @v-micsh-msft above.

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

if answers your question, please mark it solved.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Confirm this sort will scale to large SharePoint list and be delegated

Hi @PhilD,

 

If only the latest records are needed, would it be possible to sort the list through the ID field?

Based on what I know, SharePoint list has its own auto-generated ID column for the items created, which I think should be better used with the Sort function mentioned in your formula, to replace the ScheduleDate.

 

Further, for the number of rows in delegation, currently PowerApps allows 2000 rows at most. The number need to be adjust through the steps below:

  1. On the File tab, select App settings.
  2. Under Experimental features, change the Data row limit for non-delegable queries setting from 1 to 2000.

More information, see:

Delegation in PowerApps

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PhilD
Level 8

Re: Confirm this sort will scale to large SharePoint list and be delegated

Thanks @v-micsh-msft. The ID field is a good suggestion that may improve performance while rendering mostly the same result (new tasks would never be scheduled for a date more than a day or so out so the ID sort would yield almost the same set of 2000 items).

 

I just want to be sure that this will still work when there are thousands of items in the list which I think it will.

Super User
Super User

Re: Confirm this sort will scale to large SharePoint list and be delegated

@PhilD,

search() is not delegatable in SharePoint. It is recommended to use Filter(datasource,StartsWith(... instead and you can work with very large lists. If you want to use Search() with a collection of your datasource you can do it without a delegation warning. However, Collect() is also limited by delegation so you would need to Collect(colDS,  Sort(datasource, ID, Descending)) first then Search colDS. You would still hit a 2000 limit but it would work for the last 2000 items collected as described by @v-micsh-msft above.

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

if answers your question, please mark it solved.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,806)