How to Load 2000+ Records in Canvas PowerApps from SharePoint List using collection

Summary:

Loading large datasets into Canvas PowerApps from a SharePoint List can be challenging due to PowerApps’ delegation limits and performance constraints. In this blog post, we explore effective techniques to efficiently handle and load over 2000 records. We’ll discuss the importance of optimizing your SharePoint list and using collections to manage data. Our approach focuses on loading data using collections to enhance performance and ensure a smoother user experience.

Step1:

Create a SharePoint list and use the default ‘Title’ column to add data. Populate the list with approximately 5,000 records.

 

 

 

Step2:

Create canvas PowerApps from app.powerapps.com.

 

 

Step3:

Add the SharePoint List as data source ‘Records List’.

 

 

Step4:

Add a ‘Vertical Gallery’ and insert two labels. Set the data source to SharePoint List. Set the values to ThisItem.Title and ThisItem.ID. Rename the headers in the gallery to ‘Title’ and ‘Index’.

 

 

Step5:

Add a label to display the number of records loaded in the app.

Code:

“Total Number of Records: ” & CountRows(Gallery1.AllItems)

 

 

Step6:

Create an additional screen to handle redirection, set the collection, and load data into it. This logic can be implemented in various places within the app, such as the App.OnStart event, a Button click event, or the Screen Visible property. In this case, we will write the logic in the Screen Visible property so that it executes automatically during screen transitions.

• Currently, we have approximately 5000 records in our SharePoint list, and the PowerApps “Data row limit” is set to 2000 records. Therefore, the total number of records in the SharePoint list (5000) divided by the data row limit (2000) equals approximately 2.5, which rounds up to 3. Based on this, our logic involves looping through 3 iterations to insert records into the “colRecords” collection.

Code: Clear(colRecords);ForAll(Sequence(Round(First(Sort(‘Records List’,Index,SortOrder.Descending)).Index/2000,0),1,1),With({_firstID:(ThisRecord.Value-1)*2000,_lastID:ThisRecord.Value*2000},Collect(colRecords,Filter(‘Records List’,Index>_firstID&&Index<=_lastID))))

 

 

Step7:

Change the data source name in the Gallery “Items” property to the collection name “colRecords”.

 

 

Step8:

Now, switch from Screen1 to Screen2 and check the “Total Number of Records” display; it should show a total of 5000 records.

 

 

Step9:

Now you can use the items from this collection throughout the application.

 

 

Conclusion:

In conclusion, efficiently loading over 2000 records in Canvas PowerApps from a SharePoint List requires a strategic approach. By optimizing the SharePoint list and leveraging collections, you can work within PowerApps’ delegation limits while ensuring a smoother user experience. The outlined steps, from setting up the canvas and gallery to implementing mathematical logic for data retrieval, enable you to manage large datasets effectively. By breaking down the data into manageable chunks and utilizing collections, you enhance performance and responsiveness, ultimately delivering a more efficient application for users.