Create big Collections

[diegomarino]

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

[EddieE]

@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]

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

[diegomarino]

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

[EddieE]

@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

[diegomarino]

Hi

sorry for necroposting but now i got the error "error when trying to retrieve data from the network".
It happened suddenly, i don't think i made some changes. The i deleted the sharepoint list and imported again but the same error

thanks

[diegomarino]

Found the problem, it is the threeshold limit. I tryed to make index column but it stucks and never ends. Some advice?
Thanks

[EddieE]

@diegomarino 

Once you've reached the threshold, it's generally too late to create an index column. Always best to create one when you create the list, as well as add any versioning that you need.

Having said that, you can try to delete records to get the number below 5,000 then try adding an index? I think this works, I've just never tried it. If you need to keep the records you are deleting, make a copy first.

[diegomarino]

hi, actually that was the problem, i deleted some rows (now at 4900 more or less) and tried to start the app, and worked. then i went to sharepoint, i made two indexed column  (i have another list of 6500 that never gave me problems), then i used MSaccess to readd the deleted rows and tried in the app. The same error unfortunely

[EddieE]

@diegomarino 

You've reached the depth of my knowledge on how to fix this issue. I'll call in a colleague of mine @WarrenBelz to see if he can assist you any further?

Warren, can you see a fix to this one?