Collection Limit

[Anonymous]

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]

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

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

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

[clau0289]

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]

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:

• Database design and PowerApps – Step 1 Planning
• Relational database design and PowerApps – Step 2 Data Gathering, Constructing & Normalizing Tables
• Relational Database Principles and PowerApps - Step 3 Keys and Relationships

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:

1. Filter your data against conditions that would pare them down below 500 records within the delegation rules and suggestions from Mehdi and @Drrickryp. 
   
   
2. If you need > 500 records returned from a given filter, bump the limit up to 2000 in the App settings.
   
   
3. 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/
   
   
4. 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
   
   
5. 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.
   
   
6. Page your data: bring in N records at a time with the same patterns above.
   
   
7. 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