cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Data Row Limits

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

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Super User III
Super User III

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?

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

View solution in original post

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (3,493)