cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
diegomarino
Helper III
Helper III

Create big Collections

Hi, it's a long time i don't use powerapps, so forgive me if this question is redundant (i made a search) or stupid.

 

i have a big sharepoint list of 47000 Ca records, i tried to add every of them with this formula

 

Set(varMaxID;First(Sort(qryFIliali;ID;Descending)).ID);;//ForAll(Sequence(varMaxID);Collect(qryFIliali;Value))

surely it was not good, my intentions was to add every record with id from 1 to the max value

thanks

4 REPLIES 4
EddieE
Solution Sage
Solution Sage

@diegomarino 

You need to add another column to your List that is of type Number. This is because SharePoint doesn't support '<' or '>' operators on the ID column, only '='.

 

You can add a column to your List called IDNumber then use Power Automate to get the ID of each row and add it to this new column. Once you have this setup, you can use this code to collect all of your data

// Note: change 'yourListName' to the name of your SP List

// Get the number of 2000 row batches needed to loop through
With(
    {
        wID:   First(Sort(yourListName,IDNumber,Descending)).IDNumber 
    },
    Set(vNum2000s,
        Switch(Mod(wID,2000),
            0,Round(wID/2000,0),
            RoundDown(wID/2000,0)+1
        )
    )
);

// Create Start-End values that each batch loops through using Sequence()
// Rename column "Value" (generated by Sequence) to "Start" to keep things
// consistent, and use AddColumns to attach the "End" column

ClearCollect(
    colSeq,
    AddColumns(RenameColumns(
            Sequence(vNum2000s,0,2000),
            "Value",
            "Start"
            ), "End", Start + 2000
    )
);

// Empty Collection that will be filled
// This is best practice before using ForAll to Collect() records

Clear(colSeqALL);

// Loop through the Start-End table collecting records
ForAll(
    colSeq As StartEnd,
    Collect(colSeqALL,
            Filter(yourListName,
                IDNumber > StartEnd.Start && IDNumber <= StartEnd.End
            )
    )
);

 

This code is from an article I wrote awhile ago.

 

I re-tested the code today and found it was much quicker then when I wrote that article. Back then a 3000+ item list took 6.5 sec to run this code, today it only took 1-2 secs. I'd be interested to hear how long a 47k list takes to load :).

 

Note: you don't need a new column called IDNumber if you already have a Text/Number type column setup with some sort of incremental ID number / text, other than the ID column.

diegomarino
Helper III
Helper III

thanks, give me some days and i'll try that...and i take the time it took

diegomarino
Helper III
Helper III

Sorry, don't you think we can make an addcolumn to avoid add a real column of rownumber?

@diegomarino 

No, that won’t work. You still need to segment the data source first somehow ie chop it up into 2000 record chunks then collect these chunks into collections.

 

Using AddColumns will only work on the first 2000 collected and not the rest - if that makes sense

 

The AddColumns I describe in the article still utilises an existing IDNumber column to collect the data

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,740)