Have you ever tried to use Power Automate to make an HTTP request to calculate a rollup field? If you are like me working as a functional consultant who does not know much about coding, this solution would be perfect for you!
Acknowledgement
Before starting this article, I would like to appreciate my mentor, Bradley Festraets, who is an exceptional Dynamics 365 and Power Platform solution architect. He has been very generous spending time with me to help me exploring new ideas. I would like to share this idea with you now and hopefully it will help with your solution design.
Rollup Fields in Dynamics 365
Rollup fields in Dynamics 365 are designed to help users obtain insights into data by monitoring key business metrics. For example, you would like to know how much worth of the deal your company have made with a specific account. A rollup field can help you to calculate the aggregate value over the records related to a specified record, which is total value of the past orders related to a business account in this case.
A rollup field can not only aggregate data by using summation (SUM) functions, but can also aggregate data by using counting (COUNT), minimize (MIN), maximize (MAX), and averaging (AVG) functions.
There are a few examples of rollup fields capabilities that Microsoft Doc has listed:
Total estimated revenue of open opportunities of an account;
Total estimated revenue of open opportunities across all accounts in a hierarchy;
Total estimated revenue of an opportunity including child opportunities;
Total estimated value of qualified leads generated by a campaign;
Number of high priority open cases across all accounts in a hierarchy;
Earliest created time of all high priority open cases for an account.
Limitation of Default Rollup Fields
Although rollup fields are so helpful to business users, there are a few limitations of default rollup fields feature:
You can define a maximum of 100 rollup fields for the organization and up to 10 rollup fields per entity;
A workflow can’t be triggered by the rollup field updates;
A workflow wait condition cannot use a rollup field;
A rollup over the rollup field is not supported;
A rollup can't reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity;
The rollup can only apply filters to the source entity or related entities, simple fields or non-complex calculated fields;
A rollup can be done only over related entities with the 1:N relationship. A rollup can’t be done over the N:N relationships;
A rollup can’t be done over the 1:N relationship for the Activity entity or the Activity Party entity;
The values of the rollup fields are calculated by system jobs that run asynchronously in the background every hour. Mass updates run every 12 hours.
Because of the limitations, we probably need to think of the business scenarios for an extra mile to the end users. What if the business users want to have near real time calculation of the rollup fields? As a functional consultant, I would like to keep the solution low code or no code. I will introduce you the method of using Power Automate to call an HTTP request to calculate the rollup field in Power Platform model driven apps.
Example Scenario
To better illustrate the method, an example scenario will be super helpful. Imagine there is a pet management solution designed for an animal rescue organization. The organization have multiple offices across the country. The stuffs rescue animals and provide them treatment and forester at the nearest office location. Whenever the staffs rescued an animal, there will be a new detailed animal record to be added in Dynamics 365. There is a 1:N relationship between locations and animals table. The manager would like to check the total number of active animals in each location so the management team can better manage the budget and allocate the resources. Please check the simplified ERD below.
Microsoft identity platform and the OAuth 2.0 client credentials flow
To make the HTTP request in Power Automate, you must first expose the app roles in the API's app registration in the Azure portal. Then, configure the required app roles by selecting those permissions in your client application's app registration. If you haven't exposed any app roles in your API's app registration, you won't be able to specify application permissions to that API in your client application's app registration in the Azure portal. If you would like to know more about OAuth 2.0 client credentials flow, please click here.
Trigger Rollup Field Recalculation using Dynamics 365 Web API through an HTTP request
Based on Web API reference, there is a function, CalculateRollupField, which calculates the value of a rollup attribute. We need to pass a reference to a table locating a record, and then perform the action.
To perform an HTTP request action in Power Automate would require a token for authorization purpose. Well, how to get the token? That would need us to register the application in Azure portal. We would also need a key vault for the Power Automate to get authorized and then get the secret to get the token performing the action. Please follow the next two sections for detailed steps of registering an application and creating a key vault in Azure.
Registering an application in Azure Portal
First of all, you would need to login to Azure Portal. From the search bar on the top, search "App registrations".
Once navigate to App registrations page, go ahead and create a new app registration.
Fill out the details and select the right option as the screenshot below. Click on "Register".
After registering the application, click into the registration record. From the left pane, click on "Authentication" and add the redirect URLs as screenshot below.
Click on "API permissions" to add Dynamics CRM to the configured permission list.
Search "Dataverse" from the API list and click on "Add permissions".
Click on "Certificates & Secrets" on the left pane, and create a client secret.
Please note that you have to manually save the value of the client secret right after you creating it. It will be used later and you will not able to read it from the portal because it will be encrypted. If you accidently forgot to save the value, you would need to create a new version of the client secret.
Creating a key vault in Azure Portal
To create a key vault, you would need to create a resource group first. Search "Resource group" from the search bar on the top of Azure portal. Once navigate to Resource group page, click on "+ Create". You would need to select a Visual Studio Enterprise subscription and name this resource group. Click on "Review + Create".
Now, you can go ahead and create a key vault. In the resource group just created, click on "+ Create". It will navigate to "Create a resource" page. Search "Key vault" in the search bar and add it to the resource group.
Click on "Create".
Fill out the details for the key vault. Please make sure it makes sense to you.
Click on "Review + Create". You should be able to see your key vault has been validated.
Click on "Create" to deploy the key vault.
Navigate to the Key Vault Page just created, and find Secret on the left pane.
Create a secret in this key vault. Make sure the value of the secret is the same value as the client secret value you saved previously when you register the application.
Now you should be able to see the secret has been created successfully.
If you would like to share the key vault with someone else in your organization, you can add them in access policies.
If you have followed through here, then congratulations! You have successfully complete the part of configurations in Azure.
Add the application user and assign the security role in Power Platform Admin Center
Since we have registered the application in Azure portal, it would be available to be used as a service account in Power Platform. All we need to do now is to add this service account as an application user and assign a proper security role.
There is something needs to be emphasized about security role. To calculate a rollup field within a table, the account needs to have read and write privileges of that entity. It does not need to have any privileges of the related tables. Because this is an application user, you need to carefully grant the security role which is just enough to perform the actions.
When navigate to Power Platform Admin Center, find the environment and click on "Settings".
In the "Users + permissions" section, click on "Application users".
You can add the application user and assign the security role at the same time. The name of the application user should be the same as the app registration name in Azure.
Click on "Save". Congratulations! Now you can start to build the flow in Power Automate!
Create an HTTP request in Power Automate
Let's have a look at the overall structure of my demo flow. Your logic might be different from mine which is totally fine. However, we will all need a trigger, the authorization details and finally perform the HTTP request to calculate the rollup field.
Here is a checklist for all necessary information you would need for this HTTP request:
Web API Environment Name
Base URL of the Web API Endpoint
Tenant ID (Directory ID in Azure Portal)
Client ID (Application ID in Azure Portal)
Secret from Azure Active Directory Key Vault
To find your Web API Environment Name and Base URL of the Web API Endpoint, you can navigate to developer resources of make.powerapps.com.
Click on "Developer resources".
To find the client ID and tenant ID, you can navigate to the app registration page in Azure Portal.
The secret is the same value as the client secret previously saved when you register the application.
Cool, now you are pretty close the final step, which is to perform the HTTP request action in Power Automate. Please follow the screenshot below to setup your HTTP request action. The key is how to use the CalculateRollupField function. Please note that the parts I highlighted below are dynamic values, which need be matched with your data.
BASE URL CalculateRollupField(Target=@tid,FieldName=@fn)?@tid={'@odata.id':'TABLE NAME(UNIQUE IDENTIFIER)'}&@fn='ROLLUP FIELD'
Congratulations! This is the end of this article! Thanks for following through and hopefully it helps with your solution! Please feel free to share with others and leave a comment below if you need to discuss.