PowerApps : Delegation Limit - cant filter on more than 2000 rows in a list

[Jason Brownhill]

Can anyone help please?! We have a look up on a data source in PowerApps that we are processing by delegation as the form is loading and cant filter on results due to the data source being above 2000 rows from a SharePoint list. We've used this approach to get around a performance issue when using the app, but then this method has then struck us with the maximum amount of data that PowerApps can look at before then filtering based on our query. Desperately need a workaround for this please?!

The only 'fudge' we have is to remove completed items from the list itself, but this defeats the object of the solution and having reportable data in one place.

Thanks,

Jason.

[#2]

So the 2000 records you're pulling in, what's that data look like? Is there a unique identifying in it? Also what is the data source. You can pull more than 2000 records, you will just have to build a timer job to basically do it in batches into a collection. So do 2000, then do then next 2000 etc. But you need a way to filter in batches of less than 2000 so you would collect(yourcollection,filter('datasource', id < variablenumber * && id < previousvariablenumber));set(previousvariablenumber,variablenumber);set(variablenumber,Variablenumber+2000)

So basically you would find a way to connect this to a time job that runs up till your max records, so you need to calculate ahead of time how many times the timer runs, then for variable would be the chunk of records so start at 2000, then add 2000 to it. and each time you run you get a back of 2000 into your collection. So if you calculated you had 5000 records you would get a 3 to run the timer or loop 3 times, first time you would get 0-2000, 2nd 2000-4000 3rd time 4000-5000.

Anyway, that's high level how you could approach that but it should work :).

[#3]

The other issue you will run into is Max() is not delegate which is annoying, but depending on your source, if it's SQL, you can do a view in sql and have it calculate your max for you and reference that to set your variable.

[#4]

Hi Chris,

 Thanks for that fast response! We're calling in data from a SharePoint list - probably not optimal/ideal, but what we had to go with in the time available. I'm assuming this approach is still viable? We'll do some research into taking that approach if it is.

Thanks again,

Jason.

[#5]

Yeah SharePoint makes it much harder due to the fact that you can't use the ID field direct for < or > in my example. What you might be able to try thou is creating another calculated column and just pull in the [ID] which might be able to use > or < without delegation. You'll want to just manually setup a gallery, using a filter('data',calcid > 2000) to test for delegation, if that works then you'll be able to use that method, if not and it delegates might have to see if there is some other way to rig it up.

[#6]

Hello Chris,
I have a SharePoint list having more than 10000 items. I had created a column FormattedInDate (Numeric) in that list. I had created Index on this column as primary and CreatedBy as secondary.
However, when I am trying to fetch all records in PowerApps, using Filter, it is returning only first 500 records. The command I am using is:-
ClearCollect(ConsolidateData,Filter(DailyTaskSheetForPowerApps,FormattedInDate >= 20000101, FormattedInDate <= 20210602,'Created By'.Email = User().Email));

Here ConsolidateData is a collection created in SharePoint while DailyTaskSheetForPowerApps is the SharePoint list having more than 10000 items.
Additionally, I am saving the CreatedDate in yyyyMMdd format so that I can make sorting/filtering on it.
Please suggest what I am missing to get all records from SharePoint.

[#7]

This is normal. You can only ever return 500 (2000 with setting changes) records in a query regardless of delegation. Delegation let’s you actually filter down those 500 records. If you use something not delegable you return the first 500 records in the days first then filter so you always get the same results regardless of the filter.

There are for loop hacks out there you can use to return more records by getting records filter by ID in 500 batches but you can’t just filter and get more than the max in a single query.