PowerApps, Power Automate, SharePoint, Dynamics 365, Azure, C#: PowerApps: Pagination In Gallery

Hello Friends,

Welcome back with another post on PowerApps. Have you ever encountered a scenario where you have hundreds or thousands of records in PowerApps gallery. The most tedious job at that time is to view all records by scrolling. It would be great if we have a pagination kind of functionality similar to other technologies. Unfortunately, PowerApps doesn't provide any pagination concept in Gallery as well as in Data table. Let's try to do with Gallery.

Here, we will first fetch all records from SharePoint List. For this will take help of my earlier article PowerApps: Getting More Than 2000 Items (Using Sequence).
Then we will try to create pagination upon gallery with search feature as well.I had created a list having 4 column with around 25000 items.
Now open the PowerApps maker portal by clicking here.
You may also go to PowerApps maker portal by clicking on Integrate >> PowerApps >> See all apps.
Create a new PowerApps app by clicking "+ Create" >> "Canvas app from blank".
Give in the name. Choose format as Tablet. Click on Create. It will take couple of minutes to create a blank canvas app. The moment, it creates and shows you the blank canvas screen to design, the first most important task you need to do is to Save the app. Click on File >> Save to save the app. The benefit of doing so is that until you save it, your app will remain in browser memory and if anyhow, browser gets closed, your hard work will get vanished. But, if you have saved the app at very beginning, then it gets saved on cloud and PowerApps maker portal start saving the changes every 1-2 minutes automatically.
Let's move on. You will get Screen1 by default. Let's rename it ScreenGalleryPagination by clicking on 3 dots just next to the Screen1 name.
Now we will add a SharePoint connection. For this click on cylinder icon (baiscally it is a database icon). Click on "+ Add data". Search for SharePoint and then click on SharePoint connection.
It will show you the connection with your login. Click on same. Then it will show you the possible sites at right side. Select your SharePoint site. Then it will show you the SharePoint lists. Choose the list and click on Connect.
Now we will fetch the items from this SharePoint list to PowerApps collection. For this click on Apps and then choose "OnStart" property.
Now click on function box and input below code-

ClearCollect(
    Coll_AllItems,
    []
);
ClearCollect(
    DataSourceFiltered,
    []
);
Set(
    FirstRecord,
    First(TestListSJ)
);
Set(
    LastRecord,
    First(
        Sort(
            TestListSJ,
            ID,
            Descending
        )
    )
);
Set(
    BatchSize,
    500
);
Set(
    MaxIteration,
    RoundUp(
        (LastRecord.ID - FirstRecord.ID) / BatchSize,
        0
    )
);
ClearCollect(
    TotalIterations,
    AddColumns(
        AddColumns(
            ForAll(
                Sequence(MaxIteration),
                Value
            ),
            "min",
            (FirstRecord.ID + (Value - 1) * BatchSize)
        ),
        "max",
        (FirstRecord.ID + Value * BatchSize)
    )
);
ForAll(
    TotalIterations,
    Collect(
        Coll_AllItems,
        Filter(
            TestListSJ,
            ItemID >= min && ItemID < max
        )
    )
);
ClearCollect(DataSourceForGalleryItems,Coll_AllItems);

Let's explain one by one. Please match with color-

Coll_AllItems: It is a collection to hold all items fetched from SharePoint list.
DataSourceFiltered: It is a collection to hold the result obtained when we click on "Search Record" button.
FirstRecord / LastRecord: These are variables to hold the First item and the Last item of SharePoint list (Sort By ID).
BatchSize: It is the number of records we will fetch in each call to SharePoint.
MaxIteration: It is the number of total calls we need to made to SharePoint in order to fetch all records.
TotalIterations: It is a collection which is holding the starting ID and the ending ID which will be passed in Filter call to SharePoint. (i.e. 0-500, 501-1000, 1001-1500, ...)
ForAll: It is a function call using which we will fetch all records from SharePoint
ClearCollect: This command we are using to make clone of Coll_AllItems collection. This clone will be used to assign back if we had made a call of Search Record and then we are making call of Search Page / Reload.

With this, we have fetched all records from data source.
Now, click on screen "ScreenGalleryPagination" and add a Gallery (Vertical). Name this gallery as "GalleryListData". Set-

Layout: Title, Subtitle, and body
TemplateSize: 50
Height: Parent.Height-GalleryListData.Y-100
Width: Parent.Width

Title1 label-

Rename it to lblID
X: 0
Y: 0
Width: 100
Height: GalleryListData.TemplateHeight

Subtitle1 label-

Rename it to lblTitle
X: lblID.X+lblID.Width
Y: 0
Width: 400
Height: GalleryListData.TemplateHeight

Body1 label-

Rename it to lblGUID
X: lblTitle.X+lblTitle.Width
Y: 0
Width: 600
Height: GalleryListData.TemplateHeight

I have renamed according to my list columns. You may rename according to your requirements. Other cosmetic surgeries is up to you. 😊
Set GalleryListData >> DataSource to DataSourceForGalleryItems
Now click on Edit link just ahead of Fields property and set the which field is to be displayed in which label.
Now, click on ScreenGalleryPagination and set it's "OnVisible" property. Here we will set 2 variables-

PageSize: It the number of items to be displayed in gallery at a time.
intFilterRecordsFromGallerySource: It is a number which will be used to filter the number of such items from entire collection using FirstN so that we can then use LastN with PageSize to get the actual items to be displayed on screen when user will click on Next/Previous icon.

The code is-

UpdateContext({PageSize: 0});
UpdateContext({intFilterRecordsFromGallerySource: 0});
UpdateContext(
    {
        PageSize: RoundDown(
            GalleryListData.Height / GalleryListData.TemplateHeight,
            0
        )
    }
);
UpdateContext({intFilterRecordsFromGallerySource: PageSize});

Now, click on GalleryListData gallery and choose Items property and assign the below code.

LastN(
    FirstN(
        DataSourceForGalleryItems,
        intFilterRecordsFromGallerySource
    ),
    If(
        Mod(
            intFilterRecordsFromGallerySource,
            PageSize
        ) = 0,
        PageSize,
        Mod(
            intFilterRecordsFromGallerySource,
            PageSize
        )
    )
)

This code is what we were talking about in above step. The moment you add this code, you may find that the gallery becomes blank. This is due to non initialization of variable. So, save the app and play it once.
With this, we have completed the basic display part. Now the actual work starts where we will implement the pagination and will see, how it works.
Let's add a reload button at top. For this, add a Reload icon at top and update it's "OnSelect" property as-

ClearCollect(DataSourceForGalleryItems,Coll_AllItems);

The other changes are-

Rename it to IconReload
Height: 40
Width: 40
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: 0
X: 1326 (or set it accordingly to make it extreme right)

Now, we will add Previous / Next button. For this, add Left icon and update it's "OnSelect" property as-

If(
    intFilterRecordsFromGallerySource < 2 * PageSize,
    UpdateContext({intFilterRecordsFromGallerySource: PageSize}),
    UpdateContext(
        {
            intFilterRecordsFromGallerySource: intFilterRecordsFromGallerySource - If(
                intFilterRecordsFromGallerySource = CountRows(Coll_AllItems),
                If(
                    Mod(
                        CountRows(Coll_AllItems),
                        PageSize
                    ) = 0,
                    PageSize,
                    Mod(
                        CountRows(Coll_AllItems),
                        PageSize
                    )
                ),
                PageSize
            )
        }
    )

);

The other changes are-

Rename it to IconPrevious
Height: 40
Width: 80
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: GalleryListData.Y+GalleryListData.Height
X: 0

Now, add right icon and update it's "OnSelect" property as-

UpdateContext(
    {
        intFilterRecordsFromGallerySource: intFilterRecordsFromGallerySource + If(
            intFilterRecordsFromGallerySource + PageSize <= CountRows(Coll_AllItems),
            PageSize,
            If(
                intFilterRecordsFromGallerySource = CountRows(Coll_AllItems),
                0,
                Mod(
                    CountRows(Coll_AllItems),
                    PageSize
                )
            )
        )
    }

The other changes are-

Rename it to IconRight
Height: 40
Width: 80
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: GalleryListData.Y+GalleryListData.Height
X: Parent.Width-IconRight.Width

Another section has been completed. Now save the app and play it. Click on Next / Previous buttons. The gallery is getting loaded accordingly.
Now, we will try to show the

Page Number / Total Pages
Total Records
First Page
Last Page
Search By Page Number
Search By Record Title

Let's add Page Number / Total Pages. For this, add a label.

Rename it to lblCurrentPage
Width: 75
Height: 40
X: 0
Y: Parent.Height-lblCurrentPage.Height
BorderThickness: 1
Text: RoundUp(intFilterRecordsFromGallerySource/PageSize,0)
Align: Center

Add another label.

Rename it to lblSeparatorSignLabel
Width: 30
Height: 40
X: lblCurrentPage.X+lblCurrentPage.Width
Y: Parent.Height-lblSeparatorSignLabel.Height
BorderThickness: 1
Text: /
Align: Center

Add another label.

Rename it to lblTotalPages
Width: 80
Height: 40
X: lblSeparatorSignLabel.X+lblSeparatorSignLabel.Width
Y: Parent.Height-lblTotalPages.Height
BorderThickness: 1
Text: RoundUp(CountRows(DataSourceForGalleryItems)/PageSize,0)
Align: Center

This way Page Number / Total Pages is done.
Now we will Total Number of Records.
Add a label.

Rename it to lblTotalRecordsText
Width: 130
Height: 40
X: lblTotalPages.X+lblTotalPages.Width+20
Y: Parent.Height-lblTotalRecordsText.Height
Italic: true
Text: Total Records:

Add another label.

Rename it to lblTotalRecordsValue
Width: 75
Height: 40
X: lblTotalRecordsText.X+lblTotalRecordsText.Width
Y: Parent.Height-lblTotalRecordsValue.Height
Text: CountRows(DataSourceForGalleryItems)

This way, Total Records display gets done.
Now we will work upon First Page & Last Page. Let's Add Last Page button.
Add a button-

Rename it to btnLastPage
Width: 125
Height: 40
X: Parent.Width-btnLastPage.Width
Y: Parent.Height-btnLastPage.Height
Text: Last Page
OnSelect: UpdateContext({intFilterRecordsFromGallerySource:CountRows(DataSourceForGalleryItems)});

Add another button-

Rename it to btnFirstPage
Width: 125
Height: 40
X: Parent.Width-btnLastPage.Width-btnFirstPage.Width-20
Y: Parent.Height-btnLFirstPage.Height
Text: First Page
OnSelect: UpdateContext({intFilterRecordsFromGallerySource:PageSize});

This way First Page & Last Page also done.
Now, we will implement Search Page / Search Record functionality. For this-
Add a button-

Rename it to btnSearchPage
Width: 150
Height: 40
X: lblTotalRecordsValue.X+lblTotalRecordsValue.Width+20
Y: Parent.Height-btnSearchPage.Height
Text: Search Page
OnSelect:

If(
    Value(txtSearchBox.Text) <= RoundUp(
        CountRows(DataSourceForGalleryItems) / PageSize,
        0
    ),
    UpdateContext({intFilterRecordsFromGallerySource: Value(txtSearchBox.Text) * PageSize})
)

The moment, you add the OnSelect code, it will error because, till now, we haven't added textbox, from which it will pick the data. So don't worry,, we are going to add it next.
Add a textbox (Text input).

Rename it to txtSearchBox
Default: "" (Blank)
Width: 250
Height: 40
X: btnSearchPage.X+btnSearchPage.Width+20
Y: Parent.Height-txtSearchBox.Height

Add another button

Rename it to btnSearchRecord
Width: 150
Height: 40
X: txtSearchBox.X+txtSearchBox.Width+20
Y: Parent.Height-btnSearchRecord.Height
Text: Search Record
OnSelect:

ClearCollect(
    DataSourceFiltered,
    Search(
        Coll_AllItems,
        txtSearchBox.Text,
        "Title"
    )
);
ClearCollect(
    DataSourceForGalleryItems,
    DataSourceFiltered
);
UpdateContext({intFilterRecordsFromGallerySource: PageSize});

This way, we have completed our functionality. Below are the screenshots.
Home Screen-
Next Button-
Previous Button-
Search Page-
Search Record-
Reload-
Last Page-
First Page-
This way you can implement pagination.
You may also implement numeric pagination. For this, you need to visit my next post. PowerApps: Pagination In Gallery (Implement Numeric Pagination).