Loop through a SP list in order to create a variable number of collections

[Sesese]

Hey guys,

I need your help for my project.

One of the main issue i'm currently facing is the 2000 max row limits during the clearcollect action cause I need an high number of rows to be available for the user.

Currently the solution is running with the formula on start where I'm creating collection 1 with rows from 1 to 2000, collection 2 with rows from 2001 to 4000 and then a final collection where I join both collections.

The issue here is that that once the last row will go from 4001+, they won't be detected by the solution.

What I want to ask is: is there a way to  automatically loop through the SP list and automatically create a variable number of collections based on the number of rows and then join in a final single one?

I mean:

- if there are only 1000 rows the system create only collection1

- with 3500 rows, it creates collection1 and collection2

- with 6400 rows, it creates 4 collections and join in a final collection

and so on

Many thanks

[WarrenBelz · Accepted solution]

Hi @Sesese ,

The issue you have is that the SharePoint ID is only Delegable for Equals = and not for Greater Than > or Less Than <, meaning you cannot "start" your subsequent collections at a defined point. I have a blog on how to collect all records in a list, however this requires a "shadow ID" numeric field on each record

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

[Sesese]

Hi @WarrenBelz 

thank you for your reply.

I've tried to follow the formula on your blog but it seems that it doesn't work.

My source is a SP list with 10k rows and a number column called Shadow ID

This is the formula:

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

It fails and says that the operation is prohibited because it exceeds the list view treshold

What can I do?

[WarrenBelz · Accepted solution]

Hi @Sesese ,

You need to index ShadowID in SharePoint.

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

[Sesese]

Hi,

I made the change and now I'm getting around 4k rows but still missing almost 8k

[WarrenBelz]

@Sesese ,

Assuming you have ShadowID on all fields, you should get the whole list - I have tested up to 10k, but it is very slow at that point. If you have a lot of fields in the list, this might affect it as well. Note that Power Apps was never desihned to do this.

[takolota]

@Sesese 

You can now also try this alternate method of working with a large number of Sharepoint items without delegation or collection limits:

https://powerusers.microsoft.com/t5/Community-App-Samples/Large-SP-List-Delegation-Workaround-App/td...