cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndrewCarter
Level 8

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
AndrewCarter
Level 8

Re: Data Row Limits

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
Super User

Re: Data Row Limits

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
Level 8

Re: Data Row Limits

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

Super User
Super User

Re: Data Row Limits

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
Level 8

Re: Data Row Limits

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

Super User
Super User

Re: Data Row Limits

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
Level 8

Re: Data Row Limits

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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 439 members 4,542 guests
Please welcome our newest community members: