Answered

Data Row Limits

(0) Share

Report

Posted on by Community Power Pla...

OK I thought I had a handle on this, but it seems not to be the case.

My APP settings limits are set to 500 (default)..

In my APP I have a connection to a SharePoint list with 1000+ items.. When I create a collection using a filter expressions (which does give me a delegation warning) I am expecting this to query SharePoint to return my Items I expect by the filter expression;

this returns 34 items.. which I know to be incorrect.

If I then amend the APP settings limits to say 2000 and run my query again I get the full number of items I expect (currently 336)

My question is, am I actually querying my SharePoint List, or some cached copy..

I have tried to mitigate the delegation warning by creating a string column for my lookup, but as I am looping through another collection to get the associated child items I get the delegation warning on this lookup value

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

timl 36,393 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Anonymous

In general, you'll be querying your SharePoint list, as opposed to a cached copy.

Let's say your SharePoint list contains 1000 items. With the data row limit set to 500, PowerApps will execute a non-delegable query by retrieving records 1-500 onto the local device. It then retrieves the records that match your filter expression from within that 1-500 subset of records. Therefore, if there were matching records within the 500-1000 range of records, those records would not appear in your search result.

Does that help explain the behaviour, or is there something more specific that you're looking for?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@timl ,
Unfortunately, yes this is what I thought happend.. so therefore PowerApps is not really useful in a real world scenario where a list has more items within it than the max local cached copy I.E. 2000.
This is very frustrating as I know that the Items I want are a small (<500) subset of the list data.
So on delegable queries where everything can be offset to the datasource I.E. SharePoint that is a direct query. I am therefore unsure how I can mitigate my query any further as I am querying against a string column for an ID within another collection.
I can't even use UpdateContext within the ForAll to create a local variable, as this is unsupported.

Was this reply helpful? Yes No
RezaDorrani 12,143 on at

Like (0)

Report

Hi @Anonymous

The local cached copy (collection can hold n number of records)

however you would have to keep collecting in a batch style into that collection

example Collect(colData, query list for 2k items)
Collect(colData, query list for NEXT 2k items)
your collection will keep storing as much data you provide

to know more about delegation - check my delegation vlog series

https://www.youtube.com/watch?v=gwiErbYtRdA (what is delegation)
https://www.youtube.com/watch?v=eCMuXPI1Qok delegation with SP date column
https://www.youtube.com/watch?v=pn50AKn3Q1Y delegation with choice, lookup and yes/no fields
https://www.youtube.com/watch?v=44j2VRbdWjk delegation when querying using multi select fields

Regards,
Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Many Thanks @RezaDorrani ,
I am not too sure that I can get batched items from SharePoint, or want to bring that much data back to the APP.
On start the APP loads all the Outstanding Header Items into a collection. I then want to get all the associated child items for each of the outstanding header items.
I have to do everything into collections as I want to store data locally as connectivity is not always available.
Now as I read Delegation Collect & ClearCollect are non delegable, Filter is delegable but the expression being used is being classified as non-delegable.
ForAll( sortedHeaderItems, Collect( colSiteItems, Filter( WorkItemDetails, ParentID = sortedHeaderItems[@ID] ) ) )
Both ParentID and sortedHeaderItems.ID are number columns so are delegable so I am not too sure why I am getting an error

Andrew

Was this reply helpful? Yes No
RezaDorrani 12,143 on at

Like (0)

Report

Hi @Anonymous

Collect will only hold 2k records
collect(query to data source - even if source returns 5k) - it will hold on 2k

you would have to query again and collect in same collection to load set of next 2k
this is by design

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
In case anyone else wasa contemplating doing this I think I have the solution..

Clear(test); ClearCollect(parentIDCol, RenameColumns(ShowColumns(sortedHeaderItems,"ID"),"ID","HeaderID")); ForAll(parentIDCol,Collect(test, Filter(WorkItemDetails, ParentID = HeaderID))); UpdateContext({PID:Max(test,ID)});
For the above is my Test.. I had to create an array / collection of my HeaderID's, and then use this in the ForAll to loop through and retrieve the Details into my collection.
This does not thow a delegation warning even though they are they same data, I guess it is structured differently..
Regards,
Andrew

Was this reply helpful? Yes No
Suggested answer

drummerboy 35 on at

Like (0)

Report

I have a number of apps in which I access 8k, 15k records, etc. The collect is not the issue, it is the calls to the DB that only get back 2000 at a time. So you have to design your filters so they actually work to look beyond/search beyond the 2000th record (meaning it's delegatable). This may require some experimention. I had to add a field which contains an halfyearid such 202201 (for first half of the year 2022) vs 202202 (for 2nd half of the year). I show you how I use that field in a moment. Basically you have to collect all the records you want into an internal collection. Design your galleries around this collection. I use a temp collection to gather the data then copy it into the real collection used by any gallery so the gallery isn't trying to update itself as records are being added.

In this app, I use a key prefix and the users can choose to include archived records or not. Here's some code that works to load 5k + records... Startswith is definitely delegatable. As long as there's < 2000 per year, this works. There's also a combined into an allrecs collection at the end which combines a few files worth of data. Oh and by the way, all this data is contained in the callable ResetForm function of a dummy form.

If (

varIncludeArchivedLogbooks,

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-18"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-19"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-20"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-21"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-22"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-23"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-24"

)

)

);

Collect(

colArchivedV2,

Filter(

'Operations Logbook Archived',

StartsWith(

LogbookNumber,

"LB-25"

)

)

);

Clear(colAllRecs);

Collect(

colAllRecs,

'Operations Logbook Tracking',

'Operations Logbook Complete-Cancel',

colArchivedV2

);

,

Clear(colAllRecs);

Collect(

colAllRecs,

'Operations Logbook Tracking',

'Operations Logbook Complete-Cancel'

);

);

Was this reply helpful? Yes No