Unable to fetch more than 2000 records from Sharepoint list

[Sandeep8]

I have a Sharepoint list with 50000+ records. In my powerapps form, i want to populate few fields in the form from this Sharepoint list  based on the ID entered in the textbox. I used collections but does not seem to be working. Below is the code i used to retrieve until 18000 rows and this has been added to onVisible function of the screen. RowNo is a column in SP list i have added to include sequential numbering.

Concurrent(

ClearCollect(Collection1,Filter(SPList, RowNo <2000)),
ClearCollect(Collection2,Filter(SPList, RowNo >=2000 And RowNo <4000)),
ClearCollect(Collection3,Filter(SPList ,RowNo >=4000 And RowNo <6000)),

ClearCollect(Collection4,Filter(SPList, RowNo >=6000 And RowNo <8000)),

ClearCollect(Collection5,Filter(SPList, RowNo >=8000 And RowNo <10000)),

ClearCollect(Collection6,Filter(SPList, RowNo >=10000 And RowNo <12000)),

ClearCollect(Collection7,Filter(SPList, RowNo >=12000 And RowNo <14000)),

ClearCollect(Collection8,Filter(SPList, RowNo >=14000 And RowNo <16000)),

ClearCollect(Collection9,Filter(SPList, RowNo >=16000 And RowNo <18000))

);
ClearCollect(MainCollection,Collection1,Collection2,Collection3,Collection4,Collection5,Collection6,Collection7,Collection8,Collection9)

Using lookup to retrieve field data as below

LookUp(MainCollection,DataCardValue1.Text = 'ID').Name

It returns blank value.

Any help here would be great!

[WarrenBelz · Accepted solution]

Hi @Sandeep8 ,

Firstly, this may improve your collection (this will collect all records in a list)

Clear(MainCollection);
With(
   {
      wSets: 
      With(
         {
            wLimits: 
            With(
               {
                  wLimit: 
                  Sort(
                     SPList,
                     RowNo,
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit).RowNo / 2000,
                  0
               ) + 1
            )
         },
         AddColumns(
            RenameColumns(
               Sequence(
                  wLimits,
                  0,
                  2000
               ),
               "Value",
               "LowID"
            ),
            "HighID",
            LowID + 2000
         )
      )
   },
   ForAll(
      wSets As MaxMin,
      Collect(
         MainCollection,
         Filter(
            SPList,
            RowNo > MaxMin.LowID && RowNo <= MaxMin.HighID
         )
      )
   )
)

However if you are putting the ID of the required record in DataCardValue1, you would need

LookUp(
   MainCollection,
   ID = Value(DataCardValue1.Text)
).Name

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.

Visit my blog Practical Power Apps

[Sandeep8]

@WarrenBelz Thanks so much for your help. It worked like magic 🙂

[Sandeep8]

@WarrenBelz anyway i can turn off the notification message on top when searching the records. This gets triggered at this point. Please see attached screenshots.

wLimits: With(
                    {
                        wLimit: Sort(
                            'Cleaned MMS_1',
                            RowNo,
                            Descending
                        )
                    },

[WarrenBelz · Accepted solution]

@Sandeep8 ,

You need to Index RowNo in your SharePoint list.

[Sandeep8]

I have indexed the RowNo column in Sharepoint. I still get this notification.

[Sandeep8]

Well there was a cacheing issue with my system. Now its fine and i dont get the notification anymore. Thanks @WarrenBelz for your help 🙂

[Sandeep8]

@WarrenBelz Request your help here.. 

1. Anyway to come out of the loop the moment ID is found. Currently it scans through all 50000+ rows even after finding the ID entered making the app slow.
2. The rowcount in MainCollection is twice the no of rows in my database after executing the codes for each row. Unable to figure out why the spike in the row count. Is there anyway we can control this? This is making the app significantly slow.

[WarrenBelz]

@Sandeep8 ,

You can choose what rows (forward or reverse) to collect with this code from my blog on Delegation. You should not have double to rows however unless you ran that code twice.

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.

Visit my blog Practical Power Apps

[Sandeep8]

Thanks.. will follow the steps in your blog and see if it solves.