Collection only stores up to 5000 records

[rpersad]

I created a collection which in increments pulls the records from a SharePoint list, however, since going over the 5000 list threshold I the collection does not pull any records past 5000.

Would like to know if this a Collection limitation or could there be another issue that is occurring.

This is the iteration code:

Set(
    firstRecord,
    First('SP List')
);
Set(
    lastRecord,
    First(Sort('SP List',ID,SortOrder.Descending))
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500 + 1,
        0
    )
);
ClearCollect(
    iterations,
    Sequence(
        iterationsNo,
        0
    )
);
Clear(colSPList);
ForAll(
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                colSPList,
                Filter(
                    'SP List',
                    PowAppID > prevThreshold && PowAppID <= nextThreshold
                )
            )
        )
    )
);

[prajyot]

Hello ,

We can save upto 2000 records into collection. Please refer the below code to iterate through  more than 2000 records.

UpdateContext(
{
minUpdateID: First(
SortByColumns(
SPLIST,
"UPDATE_ID",
SortOrder.Ascending
)
).UPDATE_ID
}
);
UpdateContext(
{
maxUpdateID: First(
SortByColumns(
SPLIST,
"UPDATE_ID",
SortOrder.Descending
)
).UPDATE_ID
}
);
UpdateContext(
{
totalpasses: RoundUp(
(maxUpdateID - minUpdateID) / 2000,
0
)
}
);
ClearCollect(
colAllSPLIST,
Filter(
WebusersLoad_STG,
ID <= totalpasses
)
);
ClearCollect(
AllSPLIST,
Blank()
);
ForAll(
colAllSPLIST,
Collect (
AllSPLIST,
Filter (
SPLIST,
UPDATE_ID >= minUpdateID + ((ID - 1) * 2000) && UPDATE_ID <= minUpdateID + (ID * 2000)
)
);

)

Thank you ,

Prajyot Mahindrakar

[Anonymous_Hippo]

Hello @rpersad ,

Using the code below - created by Mr.Dang you can collect 2 Million records or more (hopefully you don't have to)

// --- PROCESS START ---
/*PRE-REQUISITE:
1. Set Data row limit for non-delegable queries to 2000
File > App Settings > Advanced Settings > Value: 2000
2. If using SharePoint, index your number column to avoid list view limitation.
*/
// not really required but-- Just In Case: Unload staging collections
Clear(colDummy);
Clear(colIterations);
Clear(colNumbersTable);
Clear(col_Sample);
Clear(Col_MasterData);
/*
Begin Generate Series
This can retrieve ~ 2 million records
Increase the count if you need additional records to be retrieved (hopefully not)
*/
ClearCollect(
    colNumTemp,
    [
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10
    ]
);
ForAll(
    colNumTemp,
    ForAll(
        colNumTemp,
        ForAll(
            colNumTemp,
            Collect(
                colDummy,
                {dummy: 1}
            );
            Collect(
                colNumbersTable,
                {Number: CountRows(colDummy)}
            )
        )
    )
);
// End Generate Series
// Obtain first record, last record
UpdateContext(
    {
        FirstRecord: First(
            Sort(
                'Assessment Tracker',  //replace with your sharepoint list
                ID,
                SortOrder.Ascending
            )
        )
    }
);
UpdateContext(
    {
        LastRecord: First(
            Sort(
                'Assessment Tracker',  //replace with your sharepoint list
                ID,
                SortOrder.Descending
            )
        )
    }
);
// Begin iteration capture
UpdateContext(
    {
        Iterations: RoundUp(
            (LastRecord.ID - FirstRecord.ID) / 2000,
            0
        )
    }
);
// Create a new collection to temporarily store our first ID to capture
ClearCollect(
    varCounter,
    {min_Num: FirstRecord.ID}
);
/*
Using a ForAll loop where column Number is less than or equal to the number of iterations
Update iteration collection with current iteration number, min, and max numbers
Update counter collection 
 */
ForAll(
    Filter(
        colNumbersTable,
        Number <= Iterations
    ),
    Collect(
        colIterations,
        {
            Number: Last(
                FirstN(
                    colNumbersTable,
                    CountRows(colIterations) + 1
                )
            ).Number,
            min_Num: First(varCounter).min_Num,
            max_Num: First(varCounter).min_Num + 1999
        }
    );
    Patch(
        varCounter,
        First(varCounter),
        {
            min_Num: Last(
                FirstN(
                    colIterations,
                    CountRows(colIterations) + 1
                )
            ).max_Num + 1
        }
    )
);
// End iteration capture
/*
Finally, with a ForAll loop let's collect all our items
** Cannot use ID column to do delegations! **
** Add a new number field in your data source 88
** The ID here must be a Number Field becasue of delegation issue** 
ID2 is our number field.
*/
ForAll(
    colIterations,
    Collect(
        col_Sample,
        Filter(
            'Assessment Tracker',  //replace with your sharepoint list
            ID2 >= min_Num && ID2 <= max_Num
        )//ForAll(Split(col_ Collect(Vineet2,{Test:})
    )
);

// And you should have all the data in your sharepoint list into the collection 

Cheers !

==========================================================================

Please click "Accept as Solution" if this post answered your question so that it is helpful for people having similar questions. If you found this post helpful hit that Thumbs up button !!! Thanks !!

[rpersad]

I could be wrong but the code looks like it works the exact same way as the code I have in my question. The only difference comes in with the code below which returns a blank collection

ClearCollect(
colAllSPLIST,
Filter(
WebusersLoad_STG, //I assume this suppose to be the name of my sharepoint list
ID <= totalpasses
)
);

[prajyot]

WebUsersLoad_STG is different sharepoint list with ID column (increment by 1) to maintain incremental counter for iterating through the records by 2k. e.g. if 10k records in your collection then ID will 10k/2k =5

[rpersad]

Hi @Anonymous_Hippo 

Seems like the code you suggested works similar to what I have in my question. However I tried it and the collection is returning 5000 rows. Not sure why it is capping at that number.

[prajyot]

The number of items in this list exceeds the list view threshold - SharePoint | Microsoft Learn

[rpersad]

Hi I have read this article and it only seem to tackle the issue on SharePoint and not when loading 5000+ records into into a PowerApps application.

[prajyot]

Could you please check your sharepoint listview, it's not showing more than 5k  that's why PowerApps also showing same.

[rpersad]

Can confirm given that the message below is displayed when I open the SharePoint list. I can confirm that I am also looking at the default view.