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

Collection Limit

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Collection Limit

@mr-dang I have seen your article and thanks. So am I understanding correctly that No matter the data source there is a 500 limit? This is not clear in the documentation I have seen.

View solution in original post

14 REPLIES 14
Highlighted
Community Champion
Community Champion

Re: Collection Limit

Hi,

Collections can hold as many records as you want.

 

In your case, although you scrolled down and revealed 1000 records in the Gallery, PowerApps still refers to the original output of the Items property of the gallery when collecting. Since Gallery.Items was linked to the datasource and was already capped to 500, your collection assumed the same cap.

 

There's a few solutions on these forums about how to overcome the 500 record limitation by using a local collection. Here's mine:

https://powerusers.microsoft.com/t5/PowerApps-Forum/500-item-limit-in-CDM-entity-search-filter-need-...

Microsoft Employee
@8bitclassroom
Highlighted
Anonymous
Not applicable

Re: Collection Limit

@mr-dang I have seen your article and thanks. So am I understanding correctly that No matter the data source there is a 500 limit? This is not clear in the documentation I have seen.

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Collection Limit

 

All datasources can return at most 500 records at this time. Some cannot even analyze records beyond the first 500 unfiltered records.

 

So a datasource in OneDrive/Dropbox can have more than 500 records, but you can't read any records after the first 500 even if you tried--it's like they don't exist. Premium connectors like Common Data Service can read all records and Filter among all records, but it can still only return the first 500 records that match a given condition.

 

The site below can tell you what functions and operators are okay:

https://powerapps.microsoft.com/en-us/tutorials/delegation-list/

Microsoft Employee
@8bitclassroom
Highlighted
Anonymous
Not applicable

Re: Collection Limit

Thanks for your info

Highlighted
Anonymous
Not applicable

Re: Collection Limit

@mr-dang

 

 

I tried the following and encountered odd behavior...

 

 

ClearCollect(HundredChart, [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100]);
UpdateContext({firstrecord: First(KPIData)});
UpdateContext({lastrecord: First(Sort(KPIData,ID,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.ID-firstrecord.ID)/500,0)});

ClearCollect(iter,
AddColumns(AddColumns(Filter(HundredChart,Value<=maxiter),"min",(Value-1)*500),"max",Value*500)
);

Clear(datasource_temp);
ForAll(iter,
Collect(datasource_temp,
Filter(KPIData,ID>=firstrecord.ID+min && ID<firstrecord.ID+max)
)
);
UpdateContext({lastrecord: First(Sort(datasource_temp,ID,Descending))})

 

 

I have this in the onselect of a button and I have a label set to the lastrecord variable. When the pull is completed I see 2165 in the label and then is quickly changes to 500 in preview mode. In app mode I only see 500.  I put a gallery on the screen showing the id of the datasource_temp collection and it only goes up to 500 in the preview. 

 

Any idea what is going on here?

Highlighted
Community Champion
Community Champion

Re: Collection Limit

@Anonymous,

Is the ID column of KPIData a numerical value or is it text?

Microsoft Employee
@8bitclassroom
Highlighted
Anonymous
Not applicable

Re: Collection Limit

@mr-dang Im pretty sure it is. I added it as a source to a Gallery and did ThisItem.ID + ThisItem.ID and the math was successful. The ID is an internal Sharepoint data type but it appears that Powerapps is interpreting it correctly.

Highlighted
Community Champion
Community Champion

Re: Collection Limit


@Anonymous wrote:

@mr-dang

 

 

I tried the following and encountered odd behavior...

 

 

ClearCollect(HundredChart, [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100]);
UpdateContext({firstrecord: First(KPIData)});
UpdateContext({lastrecord: First(Sort(KPIData,ID,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.ID-firstrecord.ID)/500,0)});

ClearCollect(iter,
AddColumns(AddColumns(Filter(HundredChart,Value<=maxiter),"min",(Value-1)*500),"max",Value*500)
);

Clear(datasource_temp);
ForAll(iter,
Collect(datasource_temp,
Filter(KPIData,ID>=firstrecord.ID+min && ID<firstrecord.ID+max)
)
);
UpdateContext({lastrecord: First(Sort(datasource_temp,ID,Descending))})

 

 

I have this in the onselect of a button and I have a label set to the lastrecord variable. When the pull is completed I see 2165 in the label and then is quickly changes to 500 in preview mode. In app mode I only see 500.  I put a gallery on the screen showing the id of the datasource_temp collection and it only goes up to 500 in the preview. 

 

Any idea what is going on here?


 

The part in red is extra. You can delete it. It explains why the number jumps. Can you place CountRows(datasource_temp) in a label and see it count up as the formula imports more records?

Microsoft Employee
@8bitclassroom
Highlighted
Anonymous
Not applicable

Re: Collection Limit

@mr-dang Removed the red and put the CountRows(datasource_temp) in the Text of a label...after it finished it showed 500. Do you not get the same result?

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,005)