How To Automatically Calculate a Rollup Column with Power Automate

In a recent Pragmatic Works tutorial, the presenter explores how to automatically recalculate rollup columns in Dataverse using Power Automate. Rollup columns are a powerful feature, but they only recalculate hourly by default, making real-time data reporting challenging. This guide walks through how to automate rollup column calculations on demand.

What Are Rollup Columns?

Rollup columns in Dataverse automatically calculate aggregated values from related tables, such as the total number of job applicants for a job opening. However, they refresh only once per hour by default, which can be a limitation when real-time reporting is required.

The Challenge

In the scenario presented, a customer needed the rollup column for the number of job applicants to update immediately after a new applicant was added. Waiting an hour was not an acceptable solution for their reporting needs. Power Automate provides a way to trigger an instant recalculation.

Using Power Automate to Trigger Rollup Recalculation

The tutorial demonstrates how to create a Power Automate flow that recalculates a rollup column automatically whenever a new record is added.

Step 1: Setting Up the Power Automate Flow

1. Create a New Flow:
   • Navigate to the Power Automate portal and create a new automated cloud flow.
   • Set the trigger as “When a row is added, modified, or deleted” for the job applications table.
2. Define the Trigger Conditions:
   • Configure the flow to trigger when a new job application record is added.
   • Set the scope to the organization level to ensure comprehensive coverage.

Step 2: Configuring the HTTP Request Action

To force a rollup column recalculation, the presenter uses the Dataverse Web API with Power Automate. This involves making an HTTP request using a pre-authorized Microsoft Entra connector.

1. Add the HTTP Action:
   • Search for “HTTP with Microsoft Entra” in Power Automate.
   • Select the pre-authorized connector to simplify authentication.
2. Configure the HTTP Request:
   • Set the method to GET.
   • Paste the environment’s web API endpoint (found under Power Apps settings > Developer Resources).

Step 3: Constructing the API Request URL

The API request requires three components:

• Base URL: Retrieved from the developer resources panel in Power Apps.
• Entity Set Name: The pluralized logical name of the table.
• Field Name: The logical name of the rollup column being calculated.

The presenter demonstrates how to locate the entity set name and rollup column name using Power Apps advanced tools.

Step 4: Adding Dynamic Content to the Flow

The final step involves dynamically passing the record ID (GUID) into the API call.

• The Opening field is selected from the job applications table.
• The flow dynamically retrieves the record ID and calculates the rollup column for the corresponding job opening.

Step 5: Testing the Flow

The presenter tests the automation by submitting a new job application. Within seconds, the rollup column updates automatically, reflecting the new applicant count without waiting for the default hourly refresh.

Key Benefits of This Approach

• Real-Time Data Updates: Rollup columns update instantly after a record change.
• Automated Workflow: No manual intervention required for recalculations.
• Simplified API Integration: The pre-authorized Entra connector reduces authentication complexity.

Conclusion

This Power Automate solution is ideal for scenarios where real-time data updates are essential, such as job applicant tracking. By using Dataverse's Web API and the pre-authorized Entra connector, organizations can automate rollup column recalculations efficiently. 

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.