cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

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

@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
Community Support

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.

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.

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,521)