Forum Discussion
PowerApps : Delegation Limit - cant filter on more than 2000 rows in a list
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...
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 :).
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 :).
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.
- 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.
- 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.
- 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.
