Mastering Pagination in Power Apps Canvas Applications for SharePoint Online Lists

Pagination is a technique that allows users to divide large datasets into manageable chunks, making it easier to navigate, search, and interact with data. In the context of Power Apps canvas applications for SharePoint Online lists, mastering pagination is essential for enhancing the user experience and ensuring optimal performance.

In this article I would like to introduce one of the possible technique to achieve pagination in Power Apps canvas application.

First of all let's touch a bit of theory. SharePoint connector is default connector within Power Apps canvas application (and requires no premium license) and is based on REST API calls to SharePoint Online lists. Default item limit for REST call response is set to 100 elements. As addition to this Sharepoint Online has got strick limitation per list view set to 7000 (which could not be midified opposite to SharePoint on-prem environment).

So, beacuse of these SPO "connection features" your Power Apps gallery will not dispay all the items retrived from some SharePoint list. Instead you will experiene loading spinner once you will scroll down the gallery. This would not be a problem at all, but I would point at least two facts why such a behaviour might be problematic:

1. the gallery is not loading all items at once and this might cause some issues in case you have some filtering or searching functionality atached to the gallery. In simple words, once you filter the gallery you might not receive all the items, because they just were not loaded.
2. in most cases data load is quite fast, however time to time I face with some delays. And this is a bit annoying.

The other importan fact is that Power Apps is not able to retrieve more than 2000 items (default setting is set to 500 items).

And this the moment for pagination to step in and help us to avoid these inconviniences.

Below is my simple canvas app consisting of:

• some search and filtering controls;
• vertical gallery and container with text labels to act as table headers;
• dropdown to select number of items per page;
• pagination navigation

First of all we have to specify the source of the gallery. In my case "Items" property of my gallery looks like below:

Items: With(
	{allRequests:
		LastN(
			FirstN(
				Sort(
					Filter(OnBoarding, 
						Status.Value = ComboBox_FilterStatusOptions.Selected.Value || IsBlank(ComboBox_FilterStatusOptions.Selected.Value), 
						ProcessingType.Value = ComboBox_FilterRequestTypeOptions.Selected.Value || IsBlank(ComboBox_FilterRequestTypeOptions.Selected.Value)
					),
					ID, SortOrder.Ascending
				),
				currentPageVar * Dropdown_PageFrame.Selected.Value
			),
			Dropdown_PageFrame.Selected.Value
		) 
	}, 
	SortByColumns(
		Filter(allRequests, TextInput_SearchBox.Text in FullName || IsBlank(TextInput_SearchBox.Text) ), 
		"StartDate", effectiveDateSortingVar, "Modified", lastUpdateSortingVar
	)
)        

I am using "With" statement because I need to search thought the gallery, but unfortunately it is not possible to have "in" operator in "Filter" formula because of the delegation issues (SharePoint delegation does not support "in" operator as well as "Search"). So, I retrieve SPO list items prefiltered by "status" and "precessing type" fields. And then I am able to peform search and sorting.

But the main interest for us is the combinations of "LastN" and "FirtsN" formulas. The logic is pretty straight forward:

• with "FirstN" I retrive first "currentPageVar Dropdown_PageFrame.Selected.Value" elements (e.g. in case it is set to display 10 items per page and I am currently on page 9, I need to get first 10 * 9 = 90 items); and display last 10 items)
• With "LastN" I display last "Dropdown_PageFrame.Selected.Value" (in our example last 10 items out of 90 loaded on previous step).

If per page items drop down value would be changed (let's say to 20 items per page) the gallery will automaticall be reloaded and needed number of records would be displayed.

This all is good and looks really simple. True! The main problem appears once we come to the second part of pagination solution - pagination navigation.

According to initial design approved by client the app must provide ability to navigate to prev/next page as well as to first and last.

To navigate to the very first page I've just created a "<<" button and assigned "OnSelect" property of it to:

OnSelect: Set(currentPageVar, 1)        

"<" - previous page button is also very clear:

OnSelect: Set(currentPageVar, currentPageVar - 1)
DisplayMode: If(currentPageVar = 1, DisplayMode.View, DisplayMode.Edit)        

On note, don't miss to set "DisplayMode" property to avoid getting pages less than 1 :)

Next page page ">" button is also simple:

OnSelect: Set(currentPageVar, currentPageVar + 1)        

So, done all those tree buttons I've come to ">>" - last page button. It must be easy - get the total number of list items, devide by "items per page" number and assign this value to the button. But how to get the number of all list items??? In case SPO list contains more than 5000 items it would not be possible to get it from Power Apps, as well as in case of 7K+ items SPO would not return all the items.

The solution is unexpectedly simle. There is a REST endpoint returning list item count:

/_api/web/lists/getbytitle('OnBoarding')/ItemCount        

Now all I need is to create simple Power Automate flow to get list items count and pass it to Power Apps canvas application.

And this is it. We can finalize the solution. Last page button will look like this:

OnSelect: Set(currentPageVar, RoundUp(totalItemsVar.itemcount/Dropdown_PageFrame.Selected.Value,0))

DisplayMode: If(currentPageVar = RoundUp(totalItemsVar.itemcount/Dropdown_PageFrame.Selected.Value,0), DisplayMode.View, DisplayMode.Edit)        

Same validation condition was assigned to next page button in order not to get outside the last page.

Hope, this article would help you to create pagination wihtin your Power Apps canvas applications.

All the best and stay safe :)