Collection not collecting all rows

[ruth]

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

[CNT]

@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 · Accepted solution]

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

[ruth]

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.

[CNT]

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

[ruth]

@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

[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));

[CNT]

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

[ruth]

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.

[CNT]

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