Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Building Power Apps / Collections code not b...
Power Apps - Building Power Apps
Answered

Collections code not bringing the correct result

(0) ShareShare
ReportReport
Posted on by MIA27

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

  • MIA27 Profile Picture
    MIA27 on at
    Re: Collections code not bringing the correct result

    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. 

     

     

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Collections code not bringing the correct result

    @MIA27 ,

    You have SortOrder.Ascending instead of Descending in _Limit

  • MIA27 Profile Picture
    MIA27 on at
    Re: Collections code not bringing the correct result

    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 146,702 Most Valuable Professional on at
    Re: Collections code not bringing the correct result

    @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 on at
    Re: Collections code not bringing the correct result

    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.

  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Collections code not bringing the correct result

    @MIA27 ,

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

  • MIA27 Profile Picture
    MIA27 on at
    Re: Collections code not bringing the correct result

    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

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Collections code not bringing the correct result

    @MIA27 ,

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

  • MIA27 Profile Picture
    MIA27 on at
    Re: Collections code not bringing the correct result

    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 146,702 Most Valuable Professional on at
    Re: Collections code not bringing the correct result

    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

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

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

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

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,702 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,026 Most Valuable Professional

Leaderboard

Featured topics

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

Product updates

Microsoft Power Platform Community release plans