Do you have a lot of data that you want to surface up in a gallery inside Power Apps? I’ll show you a trick to take that data and put it in a paginated format so you can reduce vertical scrolling.

If you want to see this in action and follow a walk-through in video format you can check out my how-to video on YouTube:

The finished product of the paginated gallery will look something like this:

Figure 1 – Paginated Gallery

OnStart Formulas

The first step in getting this to work is to take your data source that you want to surface up in your gallery and put it into a collection. Although you don’t have to put your data into a collection for this to work, I would highly suggest it. The pagination will be much slower if you have having to hit your data source directly each time you click the back and forward buttons. If you are interested in more Power Apps performance tips, check out the recording from my session at Microsoft ignite: https://youtu.be/UuIwB8YYKao

You can use the ClearCollect() Function in App OnStart Property to define the collection.

Next we need to get a count of the number of records in the data source which we can do with the CountRows() Function. The code needed in the App OnStart will look like this:

ClearCollect(
    inspections,
    SortByColumns(
        'Safety Inspections',
        "InspectionDate",
        Descending
    )
);
Set(
    totalrecords,
    CountRows(inspections)
)

Screen OnVisible Formula

To handle the pagination, we are going to utilize two variables. The first variable will serve as a counter to queue up records. The second variable will serve as a constant which will tell us how many records at a time to pull. We only want to return the maximum number of records which our gallery can hold on the screen without scrolling.

When the screen which contains the gallery is loaded, we want to set both of these variables to the gallery’s maximum number of rows. The formula for that is below:

UpdateContext(
    {
        pageCounter: RoundDown(
            GalleryInspections.Height / GalleryInspections.TemplateHeight,
            0
        )
    }
);
UpdateContext(
    {
        inspectionGalleryRowTotal: RoundDown(
            GalleryInspections.Height / GalleryInspections.TemplateHeight,
            0
        )
    }
)

Gallery Items Formula

Now that we have our data in a collection, the count value and the pageCounter and galleryRowTotal variables, we need to set the Items Property of our Gallery. We will use the FirstN and LastN Functions to handle querying our data source and returning the data in batches.

For more on the LastN and FirstN Functions see this documentation from Microsoft: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-first-last

We will point the FirstN Function to our data source collection and tell it to return the number that is in the pageCounter variable. We’ll wrap the FirstN function inside a LastN function so that we only return the last X number of items (which is determined by the galleryRowTotal variable) from the FirstN Function.

The formula for the Items Property is below. :

LastN(
    FirstN(
        inspections,
        pageCounter
    ),
   inspectionGalleryRowTotal
)

Next Button Formula

To handle the pagination you’ll need to add a Next and Previous button and/or icon to handle paging between the records. Let’s look at the Next button first. This button should only advanced forward if the pageCounter variable is great than the total number of records so we need to put in an If statement to check for this.

Next, we need to update the GalleryRowTotal variable and do a check to see if

If(
    pageCounter < totalrecords,
    UpdateContext({inspectionGalleryRowTotal:
With(
    {
        newCounter: pageCounter + inspectionGalleryRowTotal
    },
    With(
        {
            newRowTotal: newCounter - totalrecords
        }
    ,If(newCounter <= totalrecords,inspectionGalleryRowTotal, newRowTotal)
))
}));If(
    pageCounter < totalrecords,
    UpdateContext({pageCounter:
With(
    {
        newCounter: pageCounter + inspectionGalleryRowTotal
    },
    With(
        {
            newRowTotal: newCounter - totalrecords
        }
    ,If(newCounter <= totalrecords,newCounter,pageCounter + newRowTotal)
))
}));


//If(pageCounter<totalrecords,UpdateContext({pageCounter:If(pageCounter<totalrecords,pageCounter+inspectionGalleryRowTotal)}))

Previous Button Formula

For the previous button, we will be updating the GalleryRowTotal variable back to the default gallery row limit. We need an If Statement to check whether the pageCounter is less than 2 times the GalleryRowTotal and if so, we just set the pageCounter variable to the GalleryRowTotal. If it is not less than then we want to subtract the GalleryRowTotal from the pageCounter.

Here’s the formula for this part:

UpdateContext(
    {
        inspectionGalleryRowTotal: RoundDown(
            GalleryInspections.Height / GalleryInspections.TemplateHeight,
            0
        )
    }
);If(
    pageCounter < 2 * inspectionGalleryRowTotal,
    UpdateContext(
        {
            pageCounter: inspectionGalleryRowTotal
        }
    ),
    UpdateContext(
        {
            pageCounter: pageCounter - inspectionGalleryRowTotal
        }
    )
)

Page Identification Labels

The last piece that you might want to add is a label to show which page the user is on. To do this, just insert a label control on your app and set it’s Text Value to the following:

RoundUp(pageCounter/RoundDown(GalleryInspections.Height/GalleryInspections.TemplateHeight,0),0) & "/ " & RoundUp(
    totalrecords / RoundDown(
        GalleryInspections.Height / GalleryInspections.TemplateHeight,
        0
    ),
    0
)

Although there’s a few steps involved, it’s not too hard to configure pagination for your galleries. Happy Power Apping!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.