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

Clarification on Delegation

From what I have been reading on online articles and forum posts, delegation (or lack thereof) seems to be commonly associated with retrieving records in excess of the delegation threshhold (I keep hearing 500-2000). I'm starting to become concerned because I'm designing some SharePoint Online lists that may exceed 2000 records in the future.

 

For this example, I have to ask you to set aside any criticism of the design--I would never design anything like this; but illustrate this strictly for understanding the concept of delegation.

 

Lets say hypothetically that I have a table of 3000 products that have standardized names: "Toy 0001", "Toy 0002", etc. And let's say I set my delegation threshhold to 2000. If I were to do something nondelegable like this:

 

ClearCollect(myCollection,
  Filter(SortByColumns(mySPList, ProductName, Ascending),
Left(ProductName, 4) = "Toy "
) )

I would get the first 2000 items in order of their names (e.g. "Toy 0001", "Toy 0002"... "Toy 2000") (since SortByColumns is delegable) and I would fail to retrieve records "Toy 2001" to "Toy 3000".

 

What if I did this?

ClearCollect(myCollection,
  SortByColumns(mySPList, ProductName, Ascending)
)

Would I retrieve all 3000 records in memory?

 

If so, would my end-user encounter any data issues if the following were executed (not in mobile interface, but as a custom SharePoint form):

ClearCollect(myCollectionExhaustiveList,
  SortByColumns(mySPList, ProductName, Ascending)
);

ClearCollect(myCollection,
  Filter(myCollectionExhaustiveList, Left(ProductName, 4) = "Toy ")
);

Clear(myCollectionExhaustiveList)

 

1 ACCEPTED SOLUTION

Accepted Solutions
davidstone
Level 8

Re: Clarification on Delegation

I should say also that the threshold isn't the maximum PowerApps can hold in memory, only the maximum it can pull in from a datasource query.

 

So in theory (I haven't tried it), you should be able to collect your data in sections as you've suggested.

 

The added bonus is once you have your data in memory you don't need to worry about delegation, you can use all the comparators you want.

 

What I'm also not sure of though is what that would do to the performance.

View solution in original post

7 REPLIES 7
davidstone
Level 8

Re: Clarification on Delegation

Hi,

 

The threshold in PowerApps refers to the total number of records you can return with any query, so even if you tried to collect the entire 3000 records, you'd still only get 2000 (assuming you set your threshold to that).

 

That being said, delegation still allows you to search all of those 3000 records, provided the number of results returned by your query is fewer than 2000.

 

That's why you also need to be mindful of which column types and which operators are delegable.

KOM_4410
Level 8

Re: Clarification on Delegation

I'd say you understand the concept very well and your approach should work. I'm not sure about putting 

Clear(myCollectionExhaustiveList)

on the same property. I have found that the functions separated by ";" don't always run consecutively, so you could try to collect your new collection but the 'exhaustive' one is already empty. So maybe put that command somewhere else, like when navigating to the next screen.

 

Also, you would need to test the performance of collecting the 3000 items at the beginning. If users have a good connection it shouldn't be bad with that number but if you are thinking about 10000 it may be a bit slow.

davidstone
Level 8

Re: Clarification on Delegation

@KOM_4410, there is no way to pull in more records than the threshold.

 

Let's say you have a threshold of being able to carry 5 books from the library. Delegation let's you select from any of the millions of books in the library, but you can still only carry 5, no matter what.

Highlighted
KOM_4410
Level 8

Re: Clarification on Delegation

I believe you...good clarification! 

tommyly
Level 8

Re: Clarification on Delegation

@davidstone

Okay, that resolves one of the misconceptions. So what you are saying is that a ClearCollect will only collect the threshold-amount of items. I was under the impression that a ClearCollect would collect everything, but I guess that wouldn't make sense if you had a datasource with several hundreds-of-thousands of records (you'd crash your phone).

 

 

My specific issue involves a tagging system I've created that relies on Split and Concat. So a record has a column Tag that may have the values: "Technology|Education|Video Games". Users can use a multi-select comboBox whose Items properties parses out all possible tags by:

myComboBox.Items =
  SortByColumns(
Distinct( Split(Concat(myList, Tags, "|"), "|"),
Result
),
Result, Ascending
)

And then the resulting gallery or what-have-you has its item filtered like so:

myGallery.Items =
  Filter(myList,
Sum(Split(myList.Tags, "|"), Result in myComboBox.SelectedItems.Result) > 0
)

Obviously the myComboBox.Items would run into a delegation issue if there are more than 2000 records in myList. And then I would run into another delegation issue with myGallery as the "Sum" and "in" commands are not delegable.

 

Would it be an issue on a computer--as opposed to a mobile device--if I did something like this:

ClearCollect(myCollectionExhaustiveList,
  Filter(myList, ID >=0, ID<2000)
);
Collect(myCollectionExhaustiveList,
  Filter(myList, ID >=2000, ID<4000)
);
Collect(myCollectionExhaustiveList,
  Filter(myList, ID >=4000, ID<6000)
)
davidstone
Level 8

Re: Clarification on Delegation

I should say also that the threshold isn't the maximum PowerApps can hold in memory, only the maximum it can pull in from a datasource query.

 

So in theory (I haven't tried it), you should be able to collect your data in sections as you've suggested.

 

The added bonus is once you have your data in memory you don't need to worry about delegation, you can use all the comparators you want.

 

What I'm also not sure of though is what that would do to the performance.

View solution in original post

tommyly
Level 8

Re: Clarification on Delegation

@davidstone

Thanks for your response. I'm going to leave this open for a bit and see if there are other insights and solutions to this topic. If not I will accept this as a solution. Part of my apprehension is that I'm not at the threshold yet, but I don't want to design something that will become unusable (or unbearable per performance) when I hit the threshold.

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 (5,236)