Solved: Re: Collection Limit - Page 2

Anonymous · ‎05-14-2017

I have a Sharepoint list with 2100 records in it. I have discovered the 500 record limit when using non-delegable functions but I performed the following and was wondering if this is normal? Connect my list as a datasource and set the Items property of a gallery to this datasource. I displayed the ID column and when scrolling in the preview mode I could see well over 1000 records in the gallery. I put a button on the screen and in the OnSelect I had a Collection created

ClearCollect(KPIs, KPIData); UpdateContext( { Counter: CountRows(KPIs) }).

When I set the Items property of the gallery to the new collection I only see 500 records and the Count only shows 500 as well.

Does this mean that there is a 500 record limit on collections?

Anonymous · ‎05-15-2017

@mr-dang with some additional testing I added a gallery and set its items directly to a filter of the data source and no records.

Filter(KPIData,ID>=1) produces first 500

Filter(KPIData,ID>=501) zero results in gallery.

Filter(KPIData,ID>=0 && ID<=501) produces first 500

Filter(KPIData,ID>=501 && ID<=1001) zero results in gallery.

Anonymous · ‎05-16-2017

@mr-dang I questioned whether the ID column was evaluating correctly so I created another Sharepoint list with two columns (Title, Column2). Column2 is a number field starting from 1 to 1200. I added a Gallery and set the items to the following and received the corresponding results.

Filter(NumberList, Column2>10 && Column2< 14 ) = 3 record

Filter(NumberList, Column2>=1 && Column2<=500 ) = records 1 - 500

Filter(NumberList, Column2>=500 && Column2<=502 ) = 1 record (500)

Filter(NumberList, Column2>=501 && Column2<=502 ) = Zero Records.

Filter(NumberList, Column2>500 ) = Zero Records

It appears that at least in my version of Power Apps that Sharepoint is not Delegable with Filter. Im going to start another thread and see if anyone else is seeing this.

panand99 · ‎02-28-2019

Hi @Anonymous
Do u find any solution for that issue. I am facing g the same issue as u were.

clau0289 · ‎03-07-2019

Same Issue here, did you get to solve the problem?

@Anonymous wrote:
@mr-dang I questioned whether the ID column was evaluating correctly so I created another Sharepoint list with two columns (Title, Column2). Column2 is a number field starting from 1 to 1200. I added a Gallery and set the items to the following and received the corresponding results.

Filter(NumberList, Column2>10 && Column2< 14 ) = 3 record

Filter(NumberList, Column2>=1 && Column2<=500 ) = records 1 - 500

Filter(NumberList, Column2>=500 && Column2<=502 ) = 1 record (500)

Filter(NumberList, Column2>=501 && Column2<=502 ) = Zero Records.

Filter(NumberList, Column2>500 ) = Zero Records

It appears that at least in my version of Power Apps that Sharepoint is not Delegable with Filter. Im going to start another thread and see if anyone else is seeing this.

Mr-Dang-MSFT · ‎03-08-2019

tl;dr: See #4.

There are practices that can be done, and practices that should be done.

The solution that I provided previously was in an effort to overcome delegation limits with the connections and conditions that existed at the time. Much has changed since then and I would encourage you to subscribe to the PowerApps blog to be on top of changes as they come along.

What I will be sharing in this post is a small part of a larger conversation on information architecture and how app experiences should differ from database experiences. First, I want to point you to a 3-part series written by @Drrickryp about designing your database:

I also invite you to read Mehdi's blog on performance considerations:

https://powerapps.microsoft.com/blog/performance-considerations-with-powerapps/

Assuming you've put some of those principles in place and understand the limitations brought up, please read on.

Here's how I think of app experiences vs. viewing data on the back end:

App experiences

fast: formulas are efficient and load time is short
minimal: only interacts with what you need at any given time, has few controls
curated: targets precise views and avoids showing everything

Backend database experiences

omnisicient: sees all your data and can pare down using malleable expression languages
omnipotent: can run any action against the data
function > form: what the data viewer does is more important than how it looks

Big idea: You should not bring in all of the data because apps are minimal.

Given that principle, here's my personal checklist on how to think of what data to pull in--others may differ here:

Filter your data against conditions that would pare them down below 500 records within the delegation rules and suggestions from Mehdi and @Drrickryp.
If you need > 500 records returned from a given filter, bump the limit up to 2000 in the App settings.
If you need > 2000 records returned from a filter and you're on SQL, create a SQL view for each experience you want to curate. Define it to a point to achieve minimalism and narrow scope. To do this right, you will result in < 2000 records.
https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/
If you need > 2000 records, you've tested the load time for the amount of records you need, and it is reasonable to your end-user and scenario, run a flow:
https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/
https://youtu.be/Tz67Deb_3RA?t=513
If you're on SharePoint and you have complex columns that you need to filter against, determine if you are going to be viewing the data in SharePoint more or in PowerApps more.

If you would be viewing the data in PowerApps, use text columns with the individual value instead. For example, instead of a Person column, use a text column containing just the email or other field that you need.
Page your data: bring in N records at a time with the same patterns above.
If you are still not able to overcome delegation limits, it is time to use the right tool for the right job and upgrade to a more robust datasource:
Common Data Service for Apps: supports aggregates, relationships, option sets
SQL: supports SQL views