Data Row Limits

[AndrewCarter]

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

[timl]

Hi @AndrewCarter 

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?

[AndrewCarter]

@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.

[RezaDorrani]

Hi @AndrewCarter 

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

[AndrewCarter]

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

[RezaDorrani]

Hi @AndrewCarter 

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

[AndrewCarter · Accepted solution]

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