cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruth
Helper IV
Helper IV

Collection not collecting all rows

I'm creating a very basic collection from a contacts sharepoint list. the list contains approximately 10k records. For some reason, not all the rows are being "collect". The records that are not being included are random. I know there are issues when working with a table greater than 2000 rows, but my understanding is that collections do not have the same limations.  

 

My code is in fact basic - ClearCollect(ccContact, tblContact)

 

Any ideas?

 

ruth

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi @ruth,

Based on the issue that you mentioned, do you mean that the ClearCollect() function could not collect all the records from your SP list?

Could you please share a bit more about the scenario?

Actually, the point is "ClearCollect()" is non-delegable, so you could not collect all the records that exceeds 2000 already.

As an alternative solution, the data source can be segmented and saved in batches to a collection which means that you could save records in batches.

Concurrent(
ClearCollect(CollectionA,Filter(NameofTable,ID<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID >=4000 And ID<6000)),

ClearCollect(CollectionD,Filter(NameofTable,ID >=6000 And ID<8000)),

ClearCollect(CollectionE,Filter(NameofTable,ID >=8000 And ID<10000)),

ClearCollect(CollectionF,Filter(NameofTable,ID >=10000 And ID<12000)),
...
)

Then collection all these collection into a collection as below:

ClearCollect(YourCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF)

Check here for more details about the Concurrent function.

Regards,

Qi

Best Regards,
Qi

View solution in original post

@ruth Yea, I tested it and can confirm that when Collecting from a SP List based on the Built in ID it returns only the maximum number of records set in the Data Row Limit, even when collected in batches. But when we use another column (our own ID) to collect the records, we can go past the Data Row Limit and get all the records in batches.

 

To me this looks like a BUG. If possible, try and raise a ticket with Microsoft.

View solution in original post

21 REPLIES 21
CNT
Super User
Super User

@ruth the 2K limit is called Delegation. Irrespective of the datasource PowerApps will collect only a maximum of 2K records. This is to keep the App performant. To circumvent delegation normally App builders try to filter the Data at the source and bring in only <2K records. But in your case if for some reason you need all the records, you will have to collect sets of 2K records manually.  You'll have to find the ID of the Last record and then load 1-2000 and then 2001-4000 and so on into your collection.

Have you tried filtering your data at the source? Can you share u'r requirement so that we can try and avoid delegation issues.

v-qiaqi-msft
Community Support
Community Support

Hi @ruth,

Based on the issue that you mentioned, do you mean that the ClearCollect() function could not collect all the records from your SP list?

Could you please share a bit more about the scenario?

Actually, the point is "ClearCollect()" is non-delegable, so you could not collect all the records that exceeds 2000 already.

As an alternative solution, the data source can be segmented and saved in batches to a collection which means that you could save records in batches.

Concurrent(
ClearCollect(CollectionA,Filter(NameofTable,ID<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID >=4000 And ID<6000)),

ClearCollect(CollectionD,Filter(NameofTable,ID >=6000 And ID<8000)),

ClearCollect(CollectionE,Filter(NameofTable,ID >=8000 And ID<10000)),

ClearCollect(CollectionF,Filter(NameofTable,ID >=10000 And ID<12000)),
...
)

Then collection all these collection into a collection as below:

ClearCollect(YourCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF)

Check here for more details about the Concurrent function.

Regards,

Qi

Best Regards,
Qi

Thanks, I did do a bit more research and found that I had to load in 2k chunks. So this does help.  This is my first powerapp w dealing w large datasets. Are there any best practices or preferred approaches to loading all the data via a collection and working off the collection vs. building dynamic filters on the data - ie, retrieving contacts based on types, name search, etc.

@ruth Though it is possible to load large sets of data into a collection, it is not advisable as u'r App will not be performant. So you must try and let the datasource do the heavy-lifting. If you can filter the records at the datasource and bring in only what is necessary into the App, u'r better off.

Hope this helps!

@CNT , I've been trying to filter my datasource. I don't need more than 2000 rows, in fact any given filter is probably less than 500 rows. The problem I'm still having is accessing any of my data over 2000.  For example, I have a table of 5000 rows, a simple filter on a particular Event type should return 122 rows.  I'm only retrieving 63 rows which are the 63 that match within the first 2000 rows.  it seems as if every filter I issue has a delegation warning.

 

I event tried to batch the datasource into a collection based on @v-qiaqi-msft response above but I'm still on accessing 2000 rows. I'm at a complete loss as to how to access any rows over the 2000 limit.

 

ruth

@v-qiaqi-msft , this solution isn't working.  I'm still only retrieving 2000 rows.  here is my code. When I filter cEventLog, I'm only retrieving values from the first 2000 of the original datasource eventlog. any idea what I might be doing wrong?

 

ClearCollect(
cEventLog1,
Filter(tblEventLog,ID<2000)
);
ClearCollect(
cEventLog2,
Filter(tblEventLog,ID >=2000 And ID<4000)
);
ClearCollect(
cEventLog3,
Filter(tblEventLog,ID >=4000 And ID<6000)
);
ClearCollect(cEventLog,cEventLog1,cEventLog2,cEventLog3));

@ruth 

Add a button and place this code in the OnSelect property ,

Concurrent( 
    ClearCollect(cEventLog1, Filter(tblEventLog,ID<2001)), 
    ClearCollect(cEventLog2, Filter(tblEventLog,ID >=2001 && ID<4001)),
    ClearCollect(cEventLog3, Filter(tblEventLog,ID >=4001 && ID<6001))
)
ClearCollect(cEventLog,cEventLog1,cEventLog2,cEventLog3);

 

Place this code in the Text property of the Button,

CountRows(cEventLog)

 

What do you see when you click the Button?

2000

It won't allow me to collect more than the first 2000 rows. again, my final result set in all situations will be only a few hundred rows but of course I need access to the rows beyond 2k.

@ruth What is your data your datasource? You had mentioned earlier that you have about 10K records. Is there an unique ID column ranging from 1 to 10000 for these records?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (4,180)