Paging Large Datasets in Canvas Apps

A lot of the tables I work with contain many thousands of records and the methods shown use Power Fx only no Flows required. In these posts we will create pagination for two examples;

Example 1 - Paging large collections in a gallery (This post).
Example 2 - Connecting directly to Dataverse tables and providing custom paging for the DataList component from the PowerCat Creator Kit, as currently paging in the component is limited to 500 records.

Example 1 - Paging large collections in a gallery

This post will show how the paging was created in the Icon Menu Builder, working with collections of over 9000 items.

Paging a collection in action

The first example is from the Icon Menu Builder, the datasources are loaded as static datasources from Excel files.

The following screen shows the Fluent UI collection has a total of 9706 records, loading the whole collection in a gallery will break Power Apps, time out and generally be unusable.

Fluent UI Large Collection

The gallery items uses the collection col_icons which is created for the Fluent UI Icon set with the following code:


ClearCollect(
        col_icons,
        FluentUI
    )

The reason this collection is created is dependant on the chosen icon set, so we don’t use the datasource directly.
The following method will work on any collection however you create it.

Setup Variables

We need to 3 variables to successfully page the collection:

varPageNumber - tracks the current page.
ddPageSize.selected.Value - The combo box selected page size
iconSort - Provides the ability to sort the collection Ascending or Descending

To provide the paging size a combo box is used to allow the user to chose the number of records in a page. Shown on the right handside of the paging controls.

Page Size Combo Box

Name: ddPageSize

Items:

["50","70","80","100","500","1000"]

Now we have the page size we need a context variable to track the current page number and the sort order.

Page OnVisible Property

In the page OnVisible property we create the context variable varPageNumber and iconSort with the following code:

UpdateContext(
    {
        varPageNumber: 1,
        iconSort: Ascending
    }
);

Configure Gallery Items

Now we have the variables and collection set up we add the following to the gallery items property to filter the collection with the number of items for the selected varPageNumber.

If(
    varPageNumber = 1,
    FirstN(
       SortByColumns(col_icons,"Set",iconsort,"Name",iconsort),
        ddPageSize.SelectedText.Value * varPageNumber
    ),
    LastN(
        FirstN(
             SortByColumns(col_icons,"Set",iconsort,"Name",iconsort),
            ddPageSize.SelectedText.Value * varPageNumber
        ),
        ddPageSize.SelectedText.Value * 1
    )
)

The code filters the collection we the correct number of records for the current page. Next to configure paging controls:

Paging Controls

The paging controls modify the page the varPageNumber variable.

Paging Controls

Number of Pages

Paging Controls

A label control is used to display the current page number and the total number of pages, the Text property is set to:

varPageNumber &" of " & RoundUp(CountRows(col_icons)/ddPageSize.SelectedText.Value,0)

Next page Icon

Paging Controls

Our next icon will update the varPageNumber context variable.

OnSelect property

UpdateContext({varPageNumber: varPageNumber + 1})

Image property

If(
    img_next_last.DisplayMode = DisplayMode.Disabled,
    "data:image/svg+xml," & EncodeUrl("<svg width='24' height='24' viewBox='0 0 24 24' fill='none' xmlns='http://www.w3.org/2000/svg'> <path d='M3 4.753C3 3.34519 4.57781 2.51363 5.73916 3.30937L16.2376 10.5028C17.2478 11.1949 17.253 12.6839 16.2477 13.3831L5.7492 20.6847C4.58887 21.4917 3 20.6613 3 19.248V4.753ZM4.89131 4.54677C4.7254 4.43309 4.5 4.55189 4.5 4.753V19.248C4.5 19.4499 4.72698 19.5685 4.89274 19.4532L15.3912 12.1517C15.5348 12.0518 15.5341 11.8391 15.3898 11.7402L4.89131 4.54677ZM20.9999 3.75C20.9999 3.33579 20.6641 3 20.2499 3C19.8357 3 19.4999 3.33579 19.4999 3.75V20.25C19.4999 20.6642 19.8357 21 20.2499 21C20.6641 21 20.9999 20.6642 20.9999 20.25V3.75Z' fill='#383C84'/> </svg>"),
    "data:image/svg+xml," & EncodeUrl(
        "<svg width='24' height='24' viewBox='0 0 24 24' fill='none' xmlns='http://www.w3.org/2000/svg'> <path d='M3 4.753C3 3.34519 4.57781 2.51363 5.73916 3.30937L16.2376 10.5028C17.2478 11.1949 17.253 12.6839 16.2477 13.3831L5.7492 20.6847C4.58887 21.4917 3 20.6613 3 19.248V4.753ZM4.89131 4.54677C4.7254 4.43309 4.5 4.55189 4.5 4.753V19.248C4.5 19.4499 4.72698 19.5685 4.89274 19.4532L15.3912 12.1517C15.5348 12.0518 15.5341 11.8391 15.3898 11.7402L4.89131 4.54677ZM20.9999 3.75C20.9999 3.33579 20.6641 3 20.2499 3C19.8357 3 19.4999 3.33579 19.4999 3.75V20.25C19.4999 20.6642 19.8357 21 20.2499 21C20.6641 21 20.9999 20.6642 20.9999 20.25V3.75Z' fill='" & Mid(
            Color3TextJSON,
            2,
            7
        ) & "'/> </svg>"
    )
)

DisplayMode property

If(

    ddPageSize.SelectedText.Value * varPageNumber < CountRows(col_icons),
    DisplayMode.Edit,
    Disabled
)

Last Icon

Paging Controls

The last icon updates the varPageNumber to the last page.

OnSelect property

UpdateContext(
    {
        varPageNumber: RoundUp(
            CountRows(col_icons) / ddPageSize.SelectedText.Value,
            0
        )
    }
)

Image property

If(
    img_next_last.DisplayMode = DisplayMode.Disabled,
    "data:image/svg+xml," & EncodeUrl("<svg width='24' height='24' viewBox='0 0 24 24'  xmlns='http://www.w3.org/2000/svg'> <path d='M11.5 5.50289C11.5 4.22384 12.9981 3.5316 13.9722 4.36058L21.3819 10.6668C22.203 11.3656 22.203 12.6334 21.3819 13.3322L13.9721 19.6381C12.9981 20.4671 11.5 19.7748 11.5 18.4958V14.5091L5.47215 19.639C4.4981 20.4679 3 19.7757 3 18.4966V5.50373C3 4.22468 4.49813 3.53244 5.47218 4.36142L11.5 9.49148V5.50289ZM11.5 11.4612L4.5 5.50373V18.4966L11.5 12.5395V11.4612ZM20.4098 11.8091L13 5.50289V18.4958L20.4098 12.1899C20.5271 12.0901 20.5271 11.9089 20.4098 11.8091Z' fill='#383C84'/> </svg>"),
    "data:image/svg+xml," & EncodeUrl(
        "<svg width='24' height='24' viewBox='0 0 24 24'  xmlns='http://www.w3.org/2000/svg'> <path d='M11.5 5.50289C11.5 4.22384 12.9981 3.5316 13.9722 4.36058L21.3819 10.6668C22.203 11.3656 22.203 12.6334 21.3819 13.3322L13.9721 19.6381C12.9981 20.4671 11.5 19.7748 11.5 18.4958V14.5091L5.47215 19.639C4.4981 20.4679 3 19.7757 3 18.4966V5.50373C3 4.22468 4.49813 3.53244 5.47218 4.36142L11.5 9.49148V5.50289ZM11.5 11.4612L4.5 5.50373V18.4966L11.5 12.5395V11.4612ZM20.4098 11.8091L13 5.50289V18.4958L20.4098 12.1899C20.5271 12.0901 20.5271 11.9089 20.4098 11.8091Z' fill='" & Mid(
            Color3TextJSON,
            2,
            7
        ) & "'/> </svg>"
    )
)

DisplayMode property

If(
    ddPageSize.SelectedText.Value * varPageNumber < CountRows(col_icons),
    DisplayMode.Edit,
    Disabled
)

Previous Icon

The last icon updates varPageNumber - 1.

OnSelect property


UpdateContext({varPageNumber: varPageNumber - 1})

Image property

If(
    img_prev.DisplayMode = DisplayMode.Disabled,
    "data:image/svg+xml," & EncodeUrl("<svg width='24' height='24' viewBox='0 0 24 24' fill='none' xmlns='http://www.w3.org/2000/svg'> <path d='M20.9999 4.753C20.9999 3.34519 19.4221 2.51363 18.2607 3.30937L7.76228 10.5028C6.7521 11.1949 6.74691 12.6839 7.75223 13.3831L18.2507 20.6847C19.411 21.4917 20.9999 20.6613 20.9999 19.248V4.753ZM19.1086 4.54677C19.2745 4.43309 19.4999 4.55189 19.4999 4.753V19.248C19.4999 19.4499 19.2729 19.5685 19.1072 19.4532L8.60869 12.1517C8.46507 12.0518 8.46581 11.8391 8.61013 11.7402L19.1086 4.54677ZM3 3.75C3 3.33579 3.33579 3 3.75 3C4.16421 3 4.5 3.33579 4.5 3.75V20.25C4.5 20.6642 4.16421 21 3.75 21C3.33579 21 3 20.6642 3 20.25V3.75Z' fill='#383C84'/> </svg>"),
    "data:image/svg+xml," & EncodeUrl(
        "<svg width='24' height='24' viewBox='0 0 24 24' fill='none' xmlns='http://www.w3.org/2000/svg'> <path d='M20.9999 4.753C20.9999 3.34519 19.4221 2.51363 18.2607 3.30937L7.76228 10.5028C6.7521 11.1949 6.74691 12.6839 7.75223 13.3831L18.2507 20.6847C19.411 21.4917 20.9999 20.6613 20.9999 19.248V4.753ZM19.1086 4.54677C19.2745 4.43309 19.4999 4.55189 19.4999 4.753V19.248C19.4999 19.4499 19.2729 19.5685 19.1072 19.4532L8.60869 12.1517C8.46507 12.0518 8.46581 11.8391 8.61013 11.7402L19.1086 4.54677ZM3 3.75C3 3.33579 3.33579 3 3.75 3C4.16421 3 4.5 3.33579 4.5 3.75V20.25C4.5 20.6642 4.16421 21 3.75 21C3.33579 21 3 20.6642 3 20.25V3.75Z' fill='" & Mid(
            Color3TextJSON,
            2,
            7
        ) & "'/> </svg>"
    )
)

DisplayMode property

If(varPageNumber = 1, Disabled, Edit)

First Icon

The last icon updates the varPageNumber to the first page.

OnSelect property

UpdateContext({varPageNumber: 1})

Image property

If(
    img_prev_first.DisplayMode = DisplayMode.Disabled,
    "data:image/svg+xml," & EncodeUrl("<svg width='24' height='24' viewBox='0 0 24 24'  xmlns='http://www.w3.org/2000/svg'> <path d='M12.5007 5.50338C12.5007 4.22433 11.0026 3.53209 10.0285 4.36107L2.61872 10.6673C1.79765 11.3661 1.79767 12.6339 2.61875 13.3327L10.0285 19.6386C11.0026 20.4676 12.5007 19.7753 12.5007 18.4963V14.5086L18.5295 19.6392C19.5035 20.4682 21.0016 19.7759 21.0016 18.4969V5.504C21.0016 4.22495 19.5035 3.53272 18.5295 4.3617L12.5007 9.49258V5.50338ZM12.5007 12.5389V11.4623L19.5016 5.504V18.4969L12.5007 12.5389ZM3.5909 11.8096L11.0007 5.50338V18.4963L3.5909 12.1904C3.47361 12.0905 3.4736 11.9094 3.5909 11.8096Z' fill='#383C84'/> </svg>"),
    "data:image/svg+xml," & EncodeUrl(
        "<svg width='24' height='24' viewBox='0 0 24 24'  xmlns='http://www.w3.org/2000/svg'> <path d='M12.5007 5.50338C12.5007 4.22433 11.0026 3.53209 10.0285 4.36107L2.61872 10.6673C1.79765 11.3661 1.79767 12.6339 2.61875 13.3327L10.0285 19.6386C11.0026 20.4676 12.5007 19.7753 12.5007 18.4963V14.5086L18.5295 19.6392C19.5035 20.4682 21.0016 19.7759 21.0016 18.4969V5.504C21.0016 4.22495 19.5035 3.53272 18.5295 4.3617L12.5007 9.49258V5.50338ZM12.5007 12.5389V11.4623L19.5016 5.504V18.4969L12.5007 12.5389ZM3.5909 11.8096L11.0007 5.50338V18.4963L3.5909 12.1904C3.47361 12.0905 3.4736 11.9094 3.5909 11.8096Z' fill='" & Mid(
            Color3TextJSON,
            2,
            7
        ) & "'/> </svg>"
    )
)

DisplayMode property

If(varPageNumber = 1, Disabled, Edit)

And that’s it you have all the functions needed to page a collection in a gallery.

I hope you found this useful, in example 2 we use the PowerCat Creator Kit controls

Paging Large Datasets in Canvas Apps

Working with datasets of more than 2000 records. Example 1 - Paging large collections in a gallery.

Example 1 - Paging large collections in a gallery

Setup Variables

Page Size Combo Box

Page OnVisible Property

Configure Gallery Items

Paging Controls

Number of Pages

Next page Icon

OnSelect property

Image property

DisplayMode property

Last Icon

OnSelect property

Image property

DisplayMode property

Previous Icon

OnSelect property

Image property

DisplayMode property

First Icon

OnSelect property

Image property

DisplayMode property

On This Page

FEATURED TAGS