Pagination in Canvas App
Pagination in Canvas App
Let's First Understand What is Pagination? 📚
Pagination is the process of dividing a large dataset into smaller, more manageable chunks, allowing users to navigate through the data efficiently. Instead of loading all records at once, pagination retrieves and displays only a limited set of data per page, improving performance and usability.
How Pagination Works?
The dataset is divided into pages, with each page containing a fixed number of records (e.g., 10, 20, or 50 records per page).
Navigation controls (Next, Previous, Page Numbers) enable users to move between pages.
In databases like SQL Server, pagination uses commands like
OFFSET&FETCH NEXTto retrieve only the required records. (In Canvas App this doesn’t work – PowerApps doesn’t support native row-based pagination)
Is it Possible to implement a Proper Pagination in Canvas App? 🤔
| Tech | Pagination Method | Performance with Large Data | Delegation Support | Customization |
|---|---|---|---|---|
| Canvas Apps | Client-side (Collection-based) | Struggles with datasets >2000 records due to delegation limits | Limited (Delegable filtering only for certain data sources) | Basic manual implementation |
| SQL Server | Server-side (OFFSET & FETCH NEXT) | Scales efficiently to millions of records | Fully delegable (Optimized queries) | Highly customizable |
| React | Server-side with APIs (Lazy loading, Infinite scroll) | Super fast using paginated APIs | Full control via backend APIs | Highly customizable |
Why Proper Pagination is Hard in Canvas Apps? 😴
Delegation Limits – Can’t fetch more than 2000 records without Power Automate or loading all data.
No Native Offset/Limit Features – Unlike SQL Server, PowerApps can’t fetch records dynamically from a large dataset without loading everything first.
Filtering Constraints – Delegation works for indexed columns like
"Created Date", but not for ID (causing pagination issues).Manual Workarounds – Requires collections or filtered subsets to mimic pagination, which is not efficient for massive datasets.
Why do we Pagination in Canvas App, when its not Proper? 😮💨
✔️ Prevents UI Overload – Even if all data is fetched, displaying thousands of rows at once would lag the app. Pagination keeps things manageable.
✔️ Improves Navigation – Users expect structured navigation instead of endless scrolling. Even if it’s not perfect, pagination helps.
✔️ Works Around Delegation Limits – Since delegation caps data retrieval at 2000 records, pagination is used inside collections to make the experience smoother.
✔️ Enhances Filtering & Searching – Helps users focus on relevant records instead of digging through massive lists.
Now that we know Pro's and Con's of Pagination in Canvas App, Lets implement it!!
First thing first, load all the data to Collection. You ask how?
Click here to open the blog post where I have shown how to to load all data to collection.
Once you have all the data loaded to the collection. Follow below steps:-
Step 1:
Create UI – I am using a Table control and a ComboBox, 2 Icons (Back and Next) and one Label to display on which page we are on.
Step 2:
1. Page Size Selection
✔️ The user selects how many items per page (20, 30, 50, or 100).
✔️ Default selection is 20 items per page.
// ComboBox1 -> Items Property
[20,30,50,100]
// ComboBox1 -> DefaultSelectedItems
[20]
✔️ varitr (current page index) is initially set to the selected page size.
// Screen -> OnVisible Property
UpdateContext({varitr:ComboBox1.Selected.Value})
2. Pagination Controls: Next & Previous
✔️ Clicking Back decreases varitr, moving to the previous set of items.
✔️ Disables Back button if reaching the first page:
✔️ Clicking Next increases varitr, loading the next set of items.
✔️ Disables Next button if reaching total dataset size:
✔️ Ensures smooth navigation within valid page range.
// Back Icon -> OnSelect Property
UpdateContext({varitr: varitr - ComboBox1.Selected.Value})
// Back ixon -> DisplayMode Property
If(varitr = ComboBox1.Selected.Value,DisplayMode.Disabled, DisplayMode.Edit)
// Next Icon -> OnSelect Property
UpdateContext({varitr: varitr + ComboBox1.Selected.Value})
// Next Icon -> DisplayMode Property
If(varitr >= CountRows(col_5000_Products),DisplayMode.Disabled, DisplayMode.Edit)
3. Page Indicator
✔️ Displays current page number vs. total pages.
✔️ Dynamically updates based on varitr & total dataset size.
// Label Control -> Text Property
"Page No: " & varitr/ComboBox1.Selected.Value &"/"&RoundUp(CountRows(col_5000_Products)/ComboBox1.Selected.Value,0)
4. Filtering Table Data Per Page
✔️ Ensures pagination pulls records sequentially by ID.
✔️ Retrieves the first varitr records, including previous pages.
✔️ LastN() ensures only the relevant records appear for the user’s selected page
✔️ Prevents pagination issues when reaching the last records.
✔️ Ensures pagination logic doesn’t exceed dataset size.
// Table Control -> Items Property
//Note yove to give your collection nmae
If(varitr <= CountRows(col_5000_Products),
LastN(FirstN(Sort(col_5000_Products,ID,SortOrder.Ascending),varitr), ComboBox1.Selected.Value),
LastN(FirstN(Sort(col_5000_Products,ID,SortOrder.Ascending),varitr), CountRows(col_5000_Products) + ComboBox1.Selected.Value - varitr)
)
