Canvas App – Load More Than 10,000 Records from SharePoint

🔧 Prerequisites (Very Important)

Before using either solution, do the following in SharePoint:

1️⃣ Create a Numeric ID Column

• Create a Number column in the SharePoint list
• Example name: RecordId (or OID)
• This column should store a unique incremental number
  • You may copy the default ID value into this column using:
    • Power Automate
    • List formatting
    • One-time data update

2️⃣ Enable Indexing

• Go to List Settings → Indexed Columns
• Create an index on:
  • RecordId
• This is mandatory to avoid delegation and threshold errors

⚠️ Why This Is Needed

• SharePoint has a 5,000 item threshold
• Power Apps has delegation limits
• Using an indexed numeric field allows us to load data in batches

✅ Solution 1 – Manual Static Batching

🔹 When to Use

• When record ranges are known
• Small to medium lists
• Quick implementation

🔹 Limitations

• Hard-coded ranges
• Not scalable
• Must be updated when data grows

📌 Code (Static Ranges)

Clear(AllData);

ClearCollect(

    AllData,

    Filter(CallRecords, RecordId <= 5659),

    Filter(CallRecords, RecordId > 5659 && RecordId <= 7659),

    Filter(CallRecords, RecordId > 7659 && RecordId <= 9659),

    Filter(CallRecords, RecordId > 9659 && RecordId <= 11659),

    Filter(CallRecords, RecordId > 11659 && RecordId <= 13659),

    Filter(CallRecords, RecordId > 13659 && RecordId <= 15659),

    Filter(CallRecords, RecordId > 15659 && RecordId <= 16000),

    Filter(CallRecords, RecordId > 16000 && RecordId <= 18000),

    Filter(CallRecords, RecordId > 18000 && RecordId <= 20000),

    Filter(CallRecords, RecordId > 20000 && RecordId <= 22000)

);

✅ Pros

✔ Simple
✔ Easy to understand

❌ Cons

✖ Not dynamic
✖ Manual maintenance
✖ Not future-proof

🚀 Solution 2 – Dynamic Batching (Recommended)

🔹 When to Use

• Large datasets (10,000+ records)
• Growing SharePoint lists
• Production apps

📌 Code (Dynamic & Scalable)

// Get first record (lowest RecordId)

UpdateContext(

    {

        varFirstRecord: First(

            SortByColumns(

                CallRecords,

                “RecordId”,

                SortOrder.Ascending

            )

        )

    }

);

// Get last record (highest RecordId)

UpdateContext(

    {

        varLastRecord: First(

            SortByColumns(

                CallRecords,

                “RecordId”,

                SortOrder.Descending

            )

        )

    }

);

// Calculate number of batches (1999 per batch)

UpdateContext(

    {

        varIterationsNo: Max(

            1,

            RoundUp(

                (varLastRecord.RecordId – varFirstRecord.RecordId) / 1999,

                0

            )

        )

    }

);

// Create iteration table

UpdateContext(

    {

        varTableIterations: Sequence(

            varIterationsNo,

            0,

            1

        )

    }

);

// Clear destination collection

Clear(MainCo13);

// Load data in batches

ForAll(

    varTableIterations,

    With(

        {

            prevThreshold: Value * 1999,

            nextThreshold: (Value + 1) * 1999

        },

        Collect(

            MainCo13,

            Filter(

                CallRecords,

                RecordId > prevThreshold &&

                RecordId <= nextThreshold

            )

        )

    )

);

✅ Why Solution 2 Is Best

✔ Fully dynamic
✔ Works beyond 10,000 records
✔ No hard-coded values
✔ Safe under delegation limits
✔ Future-proof

🧠 Knowledge Base Notes

• Always use an indexed numeric column
• Keep batch size under 2,000
• Run this in:
  • App.OnStart
  • Screen OnVisible
  • Load button (recommended for performance)
• Avoid delegable columns like text filters