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?

[mr-dang]

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

[Anonymous · Accepted solution]

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

[mr-dang]

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/

[Anonymous]

Thanks for your info

[Anonymous]

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

[mr-dang]

@Anonymous,

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

[Anonymous]

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

[mr-dang]

---

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

[Anonymous]

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