How to update calculated columns in Dataverse instantly with Power Automate

In my earlier blog post I introduced the powerful capabilities of Dataverse calculated columns. These allow you to define calculation logic to be performed automatically on the database side, rather than needing to always build it in the client side of Power Apps.

Rollup columns in particular are very covenient, as they’ll summarize data from related child rows onto the parent row, even across different Dataverse tables. There’s just one unfortunate gotcha: their values ​​are updated once an hour. Meaning often they don’t directly meet the requirements for app UI level features.

In this blog post I will show how you can overcome this limitation with the help of Power Automate cloud flows. But first, let’s take a look at the issues that the default behavior of rollup columns can cause.

The problem with rollup column update delay

For our example scenario, we’ll examine the non-alcoholic Beer Rating app we use with our FF colleagues. (You can check the details in our App Gallery.) This is what the Activities and Leaderboard pages of the canvas app contain:

The number of reviews (checkins) made by each user is calculated with the help of a rollup column in the user table. That way, it doesn’t have to be calculated in the canvas app.

The value of the column is the number of reviews (Beer rating) created by the user:

Great idea. However, this confuses the users of the application. The user’s ranking is not updated after adding a beer review. Well, it will eventually be updated. After one hour at the latest, the values ​​of the calculated columns are updated by a system job.

Naturally the user would expect to see his or her ranking updated right away. Can we force the calculated column to update programmatically in some convenient way?

We certainly can. This requires making one API call with a Power Automate cloud flow.

How to update rollup columns with Dataverse Web API

The calculated column can be updated with the CalculateRollupField function found in the Dataverse Web API. The documentation is unnecessarily sparse, so here are the steps to do the trick.

Let’s create a flow with Power Automate that starts when a new beer review is added:

Next, we make an HTTP call to force the calculated column to update. Elaiza Benitez’s blog has an excellent guide for this. We’ll go through the practical steps here needed to get our Beer Rating app to update the checkin count on the user table. There are 2 steps to this: 1) building the URL, and 2) creating the cloud flow.

1. Build URL for the request

The URL to use is as follows:

https://**myEnvUrlHere**/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'**EntitySetName(GUID)**'}&@FieldName='**FieldNameValue**'

We need to change the parts marked with an asterisk:

• Environment Address (myEnvUrlHere)
• An array with a calculated field of (EntitySetName )
• The identifier (GUID) of the record to be updated
• Calculated column name (FieldNameValue)

The necessary information can be found, for example, in the following places.

Address of the environment from the Power Platform admin center:

EntitySetName from the “Copy set name” found behind the EntitySetName Tools menu of the table:

The column’s FieldNameValue is the same as the Logical name found in the column’s properties:

The address to use is almost ready:

https://ffdemo1.crm4.dynamics.com/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'systemusers(**GUIDHERE**)'}&@FieldName='ff_checkins'

2. Request with Microsoft Entra ID (preauthorized) connector

Request is done with the Invoke an HTTP request function of the HTTP with Microsoft Entra ID (preauthorized) connector. This way, the flow does not need to contain any username and password information. They are stored in the user’s connection.

But what to use as the Base Resource URL and Azure AD Resource URI parameters?

When using the Dataverse API, we use the URL of the environment. That is the same address as in the request. The same one we just copied from the Power Platform admin center.

The request uses the GET method. We changed the Guid in the URLs to the identifier of the user who created the item.

And that’s it! We now have a cloud flow that can be leveraged wherever the specific rollup field requires an update.

Triggering the rollup column update from Power Apps

In our solution, the calculated column is updated when someone adds a new beer review. Specifically, when the flow’s trigger event is executed (When a row is added, modified or deleted). It is not synchronous, so a slight delay may occur.

A cloud flow can also be started from Power Apps. This has two advantages:

• The flow starts immediately.
• API calls caused by flow are handled by the end-user of the app, not the owner of flow. This is important if there are a lot of reviews.

For most scenarios, this is the preferred route to take. There is, however, another way available for executing custom logic in Dataverse.

Creating and invoking a custom action for rollup column update

Instead of calling a flow from our app, the update could be started from Power Apps using a Dataverse action by enabling a preview feature in the app settings:

For a detailed explanation of actions in Dataverse, see my earlier blog post on how to leverage custom actions to access the “before” values for Dataverse row updates.

For the Beer Rating app, let’s create an action (Force Recalculate user rollups) that gets the user identifier (CreatedByGuid) as a parameter:

After this, we create a flow that triggers when the action is performed. This flow updates the user’s rollup column received as a parameter:

We’ll need to add the Environments table as a data source to our app. The actions of the environment can be found from there:

We will launch an action in the app after adding a review. Execution of the action actually starts the flow:

Environment.ff_Forcecalculateuserrollups({CreatedByGuid:constDVUser.User})

However, using the action this way does not bring any added value:

• The flow starts even slower than if it started from updating a row in Dataverse (as we originally did).
• API calls still go to the flow owner.

If the use case requires updating the calculated column before editing/deleting the row that is the subject of change, this can be done neatly with the help of custom actions. However, in this case, the action is started from the classic workflow, not from Power Apps. That is because only the classic XRM workflows support synchronous execution of logic as part of the Dataverse transactions.

Conclusions

In this blog post, we have seen how to use Power Automate and Dataverse Web API to update calculated columns instantly, instead of waiting for the system job to run once an hour. This can improve the user experience and the accuracy of the data in Power Apps that rely on calculated columns.

We have also explored the option of using custom actions to trigger the update from Power Apps or classic workflows. Depending on the scenario, either of these methods can be useful for overcoming the limitations of rollup columns in Dataverse.