web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Collections code not b...
Power Apps
Unanswered

Collections code not bringing the correct result

(0) ShareShare
ReportReport
Posted on by MIA27 2

I am developing app for teams within it I created tables. One of My table has 5374 records, which I imported from excel. Cross checked all, All going on fine till here.

 

As the number of records is high, I changed the setting Data Row limit to 2000, So I applied collection method to fetch all the records and then apply for combo or for gallery and for other requirement on the screen:

 

 

ClearCollect(colMyCollection, {RecoNo:0});
With( 
 {wLow: Sort(KMEmachineDataDV, RecoNo ), wHigh: Sort(KMEmachineDataDV, RecoNo, SortOrder.Descending ) },
 Collect( colMyCollection, wLow, Filter( wHigh, !(RecoNo in wLow.RecoNo) ) )
)

 

 

The result I get is 4001 records. Short of 1373 record. Result is not correct.

 

Then Applied it on the dropdown, by applying the following method, Not all the model get listed in drop down, some are still left  :

 

Sort(Distinct(KMEmachineDataDV,Model),Value)

 

 

Then Applied in the combo box based on another column , not getting all the records:

 

Sort(Distinct(KMEmachineDataDV,Serial),Value)

 

 

Then inserted a gallery and applied the below code without any filter or sort on the item property , which also does not bring all the result :

 

colMyCollection

 

 

After all test, It seems, that my collection code has some issue as I read several post mentions that SharePoint has these issues but Data verse Table does not such issues.

 

Please check and advise what correction I need to do in my collection code to get the correct result

Categories:
Building Power Apps
I have the same question (0)
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 153,034 Most Valuable Professional on at

    Hi @MIA27 ,

    One way can collect more than 4,000 records is with a unique numerical identifier as per this blog of mine. If is also possible if you have another identifier where you can collect "batches" of under 2,000 records each, you can do it this way.

     

    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

  • MIA27 Profile Picture
    MIA27 2 on at

    Dear Mr. Warren,

    Thank you for your guide, I read the website contents, try to implement by doing the changes as mentioned there, but getting error in red color lining, could not understand the mistake, once this get fixed, then only I will be able to check the records count and others, 

     

    Note : As a alternate sequence, I always maintain that in every database, in this case RecoNo Field is numeric field and has no duplicate as well.

     

    Please check the below and advise.

     

     

    The code after changes applied :

     

    Clear(colAllList);
With(
 {
 _Sets: 
 With(
 {
 _Limits: 
 With(
 {
 _Limit: 
 Sort(
 KMEmachineDataDV,
 RecoNo,
 Descending
 )
 },
 RoundDown(
 First(_Limit).RecoNo / 2000,
 0
 ) + 1
 )
 },
 AddColumns(
 RenameColumns(
 Sequence(
 _Limits,
 0,
 2000
 ),
 "Value",
 "LowID"
 ),
 "HighID",
 LowID + 2000
 )
 )
 },
 ForAll(
 _Sets As _MaxMin,
 Collect(
 colAllList,
 Filter(
 KMEmachineDataDV,
 RecoNo > _MaxMin.LowID && RecoNo <= _MaxMin.HighID
 )
 )
 )
)

     

     

    Below are the lines are which are getting highlighted in red, which I tried to capture via print screen, to show you the error lines

    MIA27_0-1708329668871.png

     

     

     

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 153,034 Most Valuable Professional on at

    @MIA27 ,

    It needs SortOrder.Descending - I did that blog some time ago when this was not needed.

  • MIA27 Profile Picture
    MIA27 2 on at

    I changed to the below , the red color fixed from the top row codes, still red color in the last paragraph code 

     

     

    Clear(colAllList);
With(
 {
 _Sets: 
 With(
 {
 _Limits: 
 With(
 {
 _Limit: 
 Sort(
 KMEmachineDataDV,
 RecoNo,
 SortOrder.Ascending
 )
 },
 RoundDown(
 First(_Limit).RecoNo / 2000,
 0
 ) + 1
 )
 },
 AddColumns(
 RenameColumns(
 Sequence(
 _Limits,
 0,
 2000
 ),
 "Value",
 "LowID"
 ),
 "HighID",
 LowID + 2000
 )
 )
 },
 ForAll(
 _Sets As _MaxMin,
 Collect(
 colAllList,
 Filter(
 KMEmachineDataDV,
 RecoNo > _MaxMin.LowID && RecoNo <= _MaxMin.HighID
 )
 )
 )
)

     

    MIA27_0-1708331309176.png

     

    Please advise

  • WarrenBelz Profile Picture
    WarrenBelz 153,034 Most Valuable Professional on at

    @MIA27 ,

    What type of field is RecoNo ? The code works perfectly here with a numeric column in SharePoint.

  • MIA27 Profile Picture
    MIA27 2 on at

    Its is Numer Field, below image of the property of RecoNo field.

     

    Note: As it was upgraded from MS Access Table, (there is was Field Type : Number and Field size Double.

    When updgraded in Dataverse for Teams table, I found it has upgraded to Float

     

    Kindly review and advise

     

    MIA27_0-1708338979555.png

     

     

    I doubt of spaces in between the code, but not sure, what can be mistake.

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 153,034 Most Valuable Professional on at

    @MIA27 ,

    The error you are getting suggests that this field is not being dealt with as a number

    RecoNo > _MaxMin.LowID && RecoNo <= _MaxMin.HighID

     as both LowID and HighID in _Sets are numbers based on the Sequence of the ForAll

    I can assure you the code works if a unique number value is used.

  • MIA27 Profile Picture
    MIA27 2 on at

    Could not understand what correction I did, just copied the code again from the above, delete above and pasted back, changed the screen and back, it worked, no red line error.

     

    Now the code worked, then I tested on label text by the below code

    CountRows(colAllList)
    Its brings result 2000 only (while the records is 5374)
     
    Then applied the collection for the gallery and added a label to check how records the gallery has fetch by the below code
    CountRows(Gallery1.AllItems)
    same result of 2000
     
    Now how to move further, for the complete records, It is important 1 gallery and 3 combo will be based on this collection.
    I noted you advised to obtain by batches of 2000, but it not getting clear to me how?.
     
     

    The final code applied is :

     

    Clear(colAllList);
With(
 {
 _Sets: 
 With(
 {
 _Limits: 
 With(
 {
 _Limit: 
 Sort(
 KMEmachineDataDV,
 RecoNo,
 SortOrder.Ascending
 )
 },
 RoundDown(
 First(_Limit).RecoNo / 2000,
 0
 ) + 1
 )
 },
 AddColumns(
 RenameColumns(
 Sequence(
 _Limits,
 0,
 2000
 ),
 "Value",
 "LowID"
 ),
 "HighID",
 LowID + 2000
 )
 )
 },
 ForAll(
 _Sets As _MaxMin,
 Collect(
 colAllList,
 Filter(
 KMEmachineDataDV,
 RecoNo > _MaxMin.LowID && RecoNo <= _MaxMin.HighID
 )
 )
 )
)

     

     

     

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 153,034 Most Valuable Professional on at

    @MIA27 ,

    You have SortOrder.Ascending instead of Descending in _Limit

  • MIA27 Profile Picture
    MIA27 2 on at

    Thank you so much for your time and advise.

    It worked for gallery as well as direct count from collection.

     

    Good learning for me, as it has requirement for several tables. 

     

     

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

News and Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 717 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 329 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 268

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans