Power Apps SharePoint Pagination: Load 500 Records at a Time Using Index_Id

When working with large SharePoint lists in Power Apps, you may hit the delegation limit if you try to load more than 2,000 records at once. To build an efficient app that scales beyond this, we can use a custom pagination technique — loading data 500 records at a time using a numeric Index_Id column.

In this post, I’ll walk you through a fully working solution that loads SharePoint list items in chunks, supports “Next” and “Previous” buttons, and avoids delegation issues by leveraging indexed fields.


Requirements

  • A SharePoint list connected to Power Apps (e.g., 'Test Data for sp')
  • A numeric Index_Id column in SharePoint — must be indexed
  • Items are assumed to be ordered sequentially by Index_Id

Pagination Strategy

We:

  • Use a local collection (NewData) to hold 500 records at a time
  • Track the last shown Index_Id using a variable (lastID)
  • Control records per page using a variable max1 (e.g., 500)

This method:

  • Avoids delegation warnings
  • Improves performance
  • Scales to thousands of records safely

Implementation Steps

1. Screen OnVisible

ClearCollect(NewData, FirstN(Sort('Test Data for sp', Index_Id, Ascending), 500));
Set(lastID, Last(NewData).Index_Id);
Set(max1, 500);

 This initializes the gallery with the first 500 records, sorted by Index_Id

2. Next Button OnSelect

If(
    IsEmpty(Filter('Test Data for sp', Index_Id > lastID)),
    Notify("No more records.", NotificationType.Information),
    ClearCollect(NewData,
        Filter(
            'Test Data for sp',
            Index_Id > lastID && Index_Id <= lastID + max1
        )
    );
    Set(lastID, Last(NewData).Index_Id)
)
Loads the next 500 records based on the current lastID.

3. Previous Button OnSelect

Set(tempID, First(Sort(NewData, Index_Id, Ascending)).Index_Id);
If(
    tempID <= 1,
    Notify("You're already at the first page", NotificationType.Information),
    ClearCollect(NewData,
        Filter(
            'Test Data for sp',
            Index_Id <= tempID - 1 && Index_Id > tempID - max1 - 1
        )
    );
    Set(lastID, Last(NewData).Index_Id)
)

Loads the previous 500 records by recalculating the window using the lowest currently loaded Index_Id.

Tips

  • Use a number column (not calculated column) for Index_Id

  • Ensure Index_Id is indexed in SharePoint to enable delegation

  • Add variables like CurrentPage if you want to show page numbers



Comments

Post a Comment

Popular posts from this blog

Step-by-Step Guide: Power Automate Custom Connector Using Graph API from Azure App Service

Image
Step 1: Open Power Automate, navigate to the Custom Connectors tab, and select Create from blank as shown in the image below. custom connector(click image for better resolution) Step 2: In the General tab, upload an icon (less than 1MB), add a description, select the correct scheme (in my case, HTTPS ), and provide the host name. For example, if your API URL is https://graph.microsoft.com/v1.0/users/{email} , the host name will be the highlighted part — graph.microsoft.com . Step 3: Go to the Security tab. Here, you need to select the authentication method. Choose OAuth 2.0 as the authentication type, as shown in the image below. Step 4: Select the Identity Provider as Azure Active Directory . Check the box Enable Service Principal Support . This option allows you to create a connection using a Client ID and Client Secret instead of using the user's own authentication. Next, provide the Client ID , Client Secret , Tenant ID , and other required details as shown in t...
Read more

Calling Microsoft Graph API from Power Automate Using Azure App Services – Step-by-Step Guide

Image
Step 1: You should have an Azure subscription, or if you have access to the Microsoft Entra Admin Center, you can create an app from there. This app can then be used in Power Automate to connect to Microsoft Graph API. Step 2: Click on "New registration" , provide a name for your app, assign the required API permissions (such as Microsoft Graph permissions), and then click "Register" to complete the app registration process. Step 3: After registration, you'll be redirected to the app's overview page. From there, navigate to the API permissions tab and click "Add a permission" to assign the necessary Microsoft Graph API permissions. Step 4: Select Microsoft Graph as the API, and based on your requirements, choose the appropriate permissions. In my case, I selected Application permissions , which do not require user sign-in and are suitable for background services or automation. Step 5: Select the required Microsoft Graph API permissions. Yo...
Read more

Step-by-Step: Give Unique Permissions to OneDrive Files Using Power Automate and Graph API (No Premium License Needed)

Image
We are uploading files through Power Apps using the Attachment control. In the backend, a Power Automate flow uploads these files to OneDrive and assigns unique permissions . Since Power Automate does not have a built-in connector to directly assign unique permissions in OneDrive, we are handling this through the Microsoft Graph API (without using a custom connector). Step 1: Create a Blank Power Apps Application Open Power Apps Studio. Select New app > Start with a page design as shown below. Step 2 :  select Blank Canvas  Step 3: Add an Attachment Control In Power Apps, the Attachment control is not available directly from the standard controls list. To get the Attachment control: Add an Edit Form to your app. Connect the form to a SharePoint list . Once connected, the form will automatically include the Attachments field. You can then use this Attachment control to upload files. Step 4: Keep Only the Attachment Control Since we only need th...
Read more