Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Building Power Apps
Unanswered

Using Collection to retrieve more than 2000 records

Like (1) ShareShare
ReportReport
Posted on 12 Jul 2024 18:04:01 by 39

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. 

  • WarrenBelz Profile Picture
    146,810 Most Valuable Professional on 16 Jul 2024 at 03:07:29
    Re: Using Collection to retrieve more than 2000 records

    @ddow2788 ,

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

  • ddow2788 Profile Picture
    39 on 15 Jul 2024 at 22:27:30
    Re: Using Collection to retrieve more than 2000 records

    I currently have about 2300 records. 

  • WarrenBelz Profile Picture
    146,810 Most Valuable Professional on 15 Jul 2024 at 21:36:08
    Re: Using Collection to retrieve more than 2000 records

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

  • ddow2788 Profile Picture
    39 on 15 Jul 2024 at 18:28:56
    Re: Using Collection to retrieve more than 2000 records

    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)
  • WarrenBelz Profile Picture
    146,810 Most Valuable Professional on 14 Jul 2024 at 08:17:50
    Re: Using Collection to retrieve more than 2000 records

    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

  • Michael E. Gernaey Profile Picture
    41,886 Super User 2025 Season 1 on 12 Jul 2024 at 19:55:46
    Re: Using Collection to retrieve more than 2000 records

    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?

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
MS.Ragavendar Profile Picture

MS.Ragavendar 27

#2
mmbr1606 Profile Picture

mmbr1606 14 Super User 2025 Season 1

#3
Michael E. Gernaey Profile Picture

Michael E. Gernaey 13 Super User 2025 Season 1

Overall leaderboard
Loading started