Unanswered

Using Collection to retrieve more than 2000 records

(1) Share

Report

Posted on by ddow2788

So this is the formula i am using on my Page is

Concurrent(ClearCollect(CollectionA,Filter('Person List',ID<2000)),ClearCollect(CollectionB,Filter('Person List',ID>=2000 And ID<4000)));ClearCollect(PLCollection,CollectionA,CollectionB)

Then i am using this formula in my datatable

Search(PLCollection,'Search Client'.Text,'Last Name','First Name')

The issue I am having now is it is only counting 2085 records. I tried to break it down into smaller collections but CollectionB is only collecting 85 records.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Michael E. Gernaey 53,335 Super User 2025 Season 2 on at

Like (1)

Report
Hi @ddow2788

ouch my eye balls lol... please use the </> code snippet

Concurrent(ClearCollect(CollectionA,Filter('PersonList',ID<2000)) ,ClearCollect(CollectionB,Filter('Person List',ID>=2000 And ID<4000)) ); ClearCollect(PLCollection,CollectionA,CollectionB));

I don't see anything in your code (at least what you showed) that is wrong.

Have you tried, add a button, just do your second line above (ID>2000 And ID<4000 and see how many you get?

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (2)

Report
Hi @ddow2788 ,

Your issue is that the ID field if (as I assume) you are using SharePoint is not Delegable except for equals = (it is not Delegable for greater/less than ><). If you have less than 4,000 records, you can do this (as per this blog of mine)

With( { _Low: Sort( 'Person List', ID ), _High: Sort( 'Person List', ID, Descending ) }, ClearCollect( PLCollection, _Low, Filter( _High, !(ID in _Low.ID) ) ) )

There are a few other options in the blog to split the list into smaller "chunks" as well.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
ddow2788 39 on at

Like (1)

Report
I ended up using this. Any issue you see in the foreseeable future with this?

Concurrent( ClearCollect(CollectionA, Filter('Person List', StartsWith('Last Name', "A"))), ClearCollect(CollectionB, Filter('Person List', StartsWith('Last Name', "B"))),ClearCollect(CollectionC, Filter('Person List', StartsWith('Last Name', "C"))),ClearCollect(CollectionD, Filter('Person List', StartsWith('Last Name', "D"))),ClearCollect(CollectionE, Filter('Person List', StartsWith('Last Name', "E"))),ClearCollect(CollectionF, Filter('Person List', StartsWith('Last Name', "F"))),ClearCollect(CollectionG, Filter('Person List', StartsWith('Last Name', "G"))),ClearCollect(CollectionH, Filter('Person List', StartsWith('Last Name', "H"))),ClearCollect(CollectionI, Filter('Person List', StartsWith('Last Name', "I"))),ClearCollect(CollectionJ, Filter('Person List', StartsWith('Last Name', "J"))),ClearCollect(CollectionK, Filter('Person List', StartsWith('Last Name', "K"))),ClearCollect(CollectionL, Filter('Person List', StartsWith('Last Name', "L"))),ClearCollect(CollectionM, Filter('Person List', StartsWith('Last Name', "M"))),ClearCollect(CollectionN, Filter('Person List', StartsWith('Last Name', "N"))),ClearCollect(CollectionO, Filter('Person List', StartsWith('Last Name', "O"))),ClearCollect(CollectionP, Filter('Person List', StartsWith('Last Name', "P"))),ClearCollect(CollectionQ, Filter('Person List', StartsWith('Last Name', "Q"))),ClearCollect(CollectionR, Filter('Person List', StartsWith('Last Name', "R"))),ClearCollect(CollectionS, Filter('Person List', StartsWith('Last Name', "S"))),ClearCollect(CollectionT, Filter('Person List', StartsWith('Last Name', "T"))),ClearCollect(CollectionU, Filter('Person List', StartsWith('Last Name', "U"))),ClearCollect(CollectionV, Filter('Person List', StartsWith('Last Name', "V"))),ClearCollect(CollectionW, Filter('Person List', StartsWith('Last Name', "W"))),ClearCollect(CollectionX, Filter('Person List', StartsWith('Last Name', "X"))),ClearCollect(CollectionY, Filter('Person List', StartsWith('Last Name', "Y"))),ClearCollect(CollectionZ, Filter('Person List', StartsWith('Last Name', "Z"))));ClearCollect(PLCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH,CollectionI,CollectionJ,CollectionK,CollectionL,CollectionM,CollectionN,CollectionO,CollectionP,CollectionQ,CollectionR,CollectionS,CollectionT,CollectionU,CollectionV,CollectionW,CollectionX,CollectionY,CollectionZ)

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

@ddow2788 ,

How many records do you have ? If it is over 4,000 you need to be aware that performance on Filters will slow and at some point end up glacial. There is a good reason that the Data Row Limit is limited to 2000.

Was this reply helpful? Yes No
ddow2788 39 on at

Like (1)

Report

I currently have about 2300 records.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

@ddow2788 ,

Until you get to 4,000, you can use the code I supplied - it will be much quicker to collect,

Was this reply helpful? Yes No