Dataverse Pagination Flow in Canvas Apps

From Creativity Spark, IT consultancy company, we are sharing this post to show you how to create a reusable Power Automate Flow to paginate your Dataverse records in a Canvas App.

Galleries and other controls automatically paginate the Dataverse records for you, as long as you set the reference to the table directly in the Items property of the control. But there may be scenarios where you need to manipulate your records before displaying them in a gallery, adding for example a calculated column.

Or maybe you may want to run a more complex query while keeping your ability to paginate results.

Or perhaps you want to perform aggregate queries in the entire dataset. An aggregate query is one that returns the data summarized, instead of the details of it.

An example could be the number of accounts per country. A query like this will return a table with a list of countries in one column, and a number in another, instead of a list of accounts.

summary table
Example of a summary table returned by an aggregate query
If you set the Table directly as a data source in the Gallery, the control will handle the pagination for you.
If you try to manipulate the input, your automatic pagination is gone.

The solution to all the problems above is to perform a fetch query in a flow, paginating them there and using the flow outputs as the data source in your gallery.

But once we create the flow, we can go a step further and make it reusable, so, if you have multiple queries to paginate, just reuse the same flow.

So, how do we do it?

And remenber if you need any help with your Power Platform apps, please don’t hesitate to contact us.

Steps

1. Is a good practice to use solutions when performing creating your Apps and Flows, so create a Solution, and add a new Flow to it. Call it Get Dataverse Records.

2. Use the PowerApps (V2) as the trigger, and add 2 parameters to it, as shown in the pic below.

3. Another good practice is to Initialize a variable for each of the input parameters. The reason for this is that when an input parameter is used in an expression, there is no clear reference to its name. Instead, a generic name is used, as shown below.

When you use the input parameter in an expression, you don’t see the name of the parameter.

Variables on the other hand have their names when they are used in the expression, so it makes it way more clear to understand what is going on in the expression.

So, create two variables, and set their initial value to the input parameters.

Matching each input parameter with a variable makes the expressions easier to read
Create a variable for each input parameter.

4. Add a Dataverse List rows activity to your flow, and set the parameters as shown in the picture below. There are only two parameters to set: The Fetch Xml Query parameter, and the Table Name.

The Dataverse List rows activity, with its two parameters.

5. Add a Compose activity to extract the Paging Cookie. The paging cookie is a value that Dataverse needs to paginate the records correctly.

The compose activity with a very long expression to extract the paging cookie value from the List Rows results.

The Compose expression will look like the one below. What this expression is doing, is checking if the List Rows has returned the paging cookie, and if it has, extracting it, using the substring function.

if(
    or(
        empty(outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']),
        equals(indexOf(outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'],'pagingcookie="'),-1),
        equals(indexOf(outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'],'" istracking='),-1)
    ),
    '',
    substring(
        substring(
            outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'],
            0,
            indexOf(outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'],'" istracking=')),
            add(indexOf(outputs('List_rows')['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'],'pagingcookie="'),14)
    )    
)

6. Add a Response to a PowerApp or Flow activity to return the records. The response will return three parameters: Data, Paging Cookie, and More Records. The first and the last are from the outputs in the List Records and the Paging Cookie parameter comes from the previous Compose activity.

The Response activity used to return the values to the calling Canvas App

Save the flow, and you are ready to go. Now is the time to use it in a Canvas App.

Canvas App Steps

I will assume you already have a Canvas App where you want to use the flow, so I’ll skip the app creation steps process.

  1. We will rely on the ParseJSON function to make the flow reusable, so enable this new feature in the Canvas App settings.
To use the ParseJSON function you have to enable it on the canvas app settings

2. Add a reference to the flow you just created.

2. Add a button, and call it btnGetRecords. This button will encapsulate the code to call the Flow. It will be later used as a function. You may even want to hide it later.

3. Add a text input control called txtPageSize to configure the number of records you want to return on each page.

4. Add a label to show the current page. Set its Text property to:

$"Page: {CurrentPage}"

5. Add a couple of buttons to move back and forth between pages. Call them btnPreviousPage and btnNextPage.

6. Add the following code to the OnSelect property of the btnGetRecords button. You’ll find the explanation of each part below.

// 1. Save the current Page Size in the context
UpdateContext({PageSize: Value(txtPageSize.Text)});

// 2. If there is a paging cookie returned by the previous call of the the flow, make it available in the context
UpdateContext(
            {PagingCookie: If(
                Not IsError(QueryResponse) And Not IsBlank(QueryResponse.pagingcookie),
                $"pagingcookie='{QueryResponse.pagingcookie}'",
                ""
            )});

// 3. Build the query that will be executed, setting the 
// current page, the number of records per page and 
// the paging cookie returned by a previous call
UpdateContext({FetchXml: 
          $"<fetch page='{CurrentPage}' count='{PageSize}' {PagingCookie} >
             <entity name='account'>
                <attribute name='address1_city' />
                <attribute name='name' />
                <attribute name='telephone1' />
             </entity>
             </fetch>"});

// 4. Run the Flow, passing the fetch query as a parameter
UpdateContext({ QueryResponse: GetDataverseRecords.Run(FetchXml,"accounts")});

// 5. Parse returned records
ClearCollect(
    PaginatedRecords,
    ForAll(
        ParseJSON(QueryResponse.data),
        {
            accountid: Text(ThisRecord.accountid),
            name: Text(ThisRecord.name),
            rownumber: 0// Initialize a default value. This will be updated next
        }
    )
);

Part 1 extracts the value from the page size input box, converts it to a number using the Value function and adds it to the context.

Part 2 checks if a previous call of the flow returned a paging cookie. This paging cookie enables pagination, the same way the flux capacitor makes time travel possible. If there is a paging cookie, this part makes it available in the context.

Part 3 is where you should set the query you want to run. To generate one, you can use the almighty Fetch Xml Builder, or the download fetch xml button in the advance find function of any model-driven app. An extra step before copying and pasting the query is to replace all the double quotes with single quotation marks.

There is a Download FetchXML button available under the Edit Filters button on any view on any Model Driven App.

Part 4 is where we run the Flow and save its results to a context variable. A good practice would be to add some level of error handling, using the IfError function. For simplicity, we will skip this part.

In Part 5 we read the data output, which contains the results returned by List Rows activity in the Flow, and we convert it to a table using the ParseJSON function. The results will be left in a collection called PaginatedRecords.

7. On the OnSelect of the btnPreviousPage, add the following expression:

If(
    CurrentPage > 1,
    UpdateContext({CurrentPage: CurrentPage - 1});
    Select(btnGetRecords)
)

This expression is checking if there is a previous page to go to, and then decreases the value of the Current Page in one. The Select function is how you execute the handler of a button in your app. This is AKA function button.

8. On the OnSelect of the btnNextPage, add the following expression:

If(
    Boolean(QueryResponse.morerecords),
    UpdateContext({CurrentPage: CurrentPage + 1});
    Select(btnGetRecords)
)

Similar to the previous button, but in the opposite direction. We check if the last execution of the flow indicates if there are more pages with records available.

And that’s it! You probably already have a gallery or a grid on your app, but if you don’t add a gallery and set its Data Source property to the PaginatedRecords collection created in step 6 part 5. Set a couple of labels to display the fields from the query.

The end result should look like the one below.

Bonus

What is the magic trick you are using to display the row number in the gallery you may ask?
As everything in Canvas Apps, is just a Power FX expression.

// Update the rownumber
ForAll(
    Sequence(CountRows(PaginatedRecords)) As CurrentIndex,
    Patch(
        PaginatedRecords,
        Index(
            PaginatedRecords,
            CurrentIndex.Value
        ),
        {rownumber: (PageSize * (CurrentPage - 1)) + CurrentIndex.Value}
    );
    
);

We are iterating through all the values in the PaginatedRecords collection using an index. The trick to making this happen is the combination of the Sequence and the Index function.

Sequence returns a sequence of numbers whose length matches the number of items in the PaginatedRecords collection. Then I use the Index function to get the record belonging to that index in the collection, and update its rownumber value using the Patch function. The value set is the result of a small multiplication to calculate the index on the current page.

Conclusion

This is a small trick I used on a project lately. I hope you find it useful. If you have any comments or queries, please leave them below. And if you need any help with your Power Platform apps, please don’t hesitate to contact us.

Further reading

Subscribe to our newsletter