Chances are you've landed on this blog post because you've searched for how to automatically refresh a rollup field. You're in the right place as this is what I cover in this this #WTF episode vlog.
What is a rollup field?
Rollup fields first came out in Dynamics CRM 2015 and there’s a couple of good blog posts by Jukka Niiranen, another long time Microsoft MVP in our community.
- CRM 2015 Rollup Fields: The Gotchas
- This talks about what a rollup field is.
- Analyzing Customer Behavior Data with CRM 2015 Rollup Fields
- This talks about about how it works underneath the hood.
So what is it?
It’s a field that is an aggregated value of child records in Dynamics 365 and Dataverse. The supported functions are SUM, COUNT, MIN, MAX and AVERAGE.
Now the key thing here as mentioned in Jukka’s blog post is that a rollup field is refreshed every hour, but also is mass calculated 12 hours from when the rollup field was created or updated. This means it’s not performed in real-time. There are some use cases though where you do need it to be refreshed in real-time.
If you've searched on how to automatically refresh rollup fields, most of the answers say to use plugins or even a custom workflow action which requires code. If you know code, no problem.
If you've searched on how to automatically refresh rollup fields, most of the answers say to use plugins or even a custom workflow action which requires code. If you know code, no problem.
With cloud flows, it’s straight forward to do and stick around till the end of this post because I’ll tell you whether it’s worth going through this effort with cloud flows.
Disclaimer
Now before we dive into how to refresh rollup fields with cloud flows, this is already blogged about by another person in New Zealand. His name is Boru Wang, go check out his blog. I’m still going to cover it in this episode with some differences to his blog post. I’m going to share two different methods using cloud flows that you can follow.
The CalculateRollupField function in the web API
The way to refresh rollup fields outside of plugins and custom workflow actions is to call the CalculateRollupField function in the Dynamics 365 and Dataverse API.
Quick break here to understand what is a function in Dynamics 365 and Dataverse,
Functions… represent re-usable operations you can perform using the Web API.. to perform operations that have no side-effects. These functions generally retrieve data and return either a collection or a complex type. Each of these functions has a corresponding message in the organization service.
Source: Microsoft Learn
There’s two parameters for the CalculateRollupField function.
The first one is Target, which is the table the rollup field lives in. This value is the EntitySetName of the table.
The second one is the FieldName, what is the logical name of the rollup field.
What this Microsoft Learn documentation doesn’t tell you, is that you also need to provide the GUID which is the global unique identifier of the row in the Target table, in order for the rollup field in that row to be refreshed.
This is found in another Microsoft Learn documentation where it explains the syntax to use in your get request.
This basically explains that because the calculaterollupfunction requires a reference to an existing row, you need to provide the GUID for that row in the table the rollup field lives in, hence the Target parameter required.
The GET request to call CalculateRollupField function
This is what the GET request will look like.
Request URI below:
[Organization URI]/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'EntitySetNameValue(GUID)'}&@FieldName='FieldNameValue'There’s the two parameters of Target and FieldName, and the GUID reference to the row in the table.
You have full flexibility on what labels you want to use for your parameters. I’m using
- EntitySetName for the Target parameter
- and FieldName for the FieldName parameter
Keep it simple and self explanatory.
Putting this altogether, this is what the actual request will look like.
https://orgfedadf52.crm6.dynamics.com/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'accounts(d465d691-1d87-ed11-81ad-000d3ad0f935)'}&@FieldName= 'ben_numberofcontacts'So let’s breakdown this GET request for your learning. To keep it simple for this WTF episode I created a rollup field for the Account table that performs the COUNT function to show the number of contacts related to the Account through the Account Name lookup field.
What we need to build our GET request to call the CalculateRollupField function is the following,
1. The Organization URI which is the Dataverse or Dynamics 365 environment organization URL.
2. The EntitySetName of the table.
You can find this by browsing to make.powerapps.com, view the table in your solution, navigate to Tools under Table properties and select API link to table definition. Then search for EntitySetName and you’ll find it.
3. The GUID of the table row for the rollup field to be refreshed in.
4. The logical name of the rollup field.
This can be found in the Advanced properties of your column that represents the rollup field in make.powerapps.com.
That's it. We’ll be using the in-built HTTP connector of cloud flows to perform the request that calls the CalculateRollupField function.
Scenarios
Now that we know how to form our GET request, the following scenarios is what I cover for the two methods of automatically refreshing a rollup field.
- Create a Contact where the Account Name lookup column is populated.
- Create a Contact where the Account Name lookup column is not populated.
- Modify a Contact by changing the Account Name lookup column from one account to another.
- Modify a Contact by clearing the Account Name lookup column so that it no longer related to an Account.
- Delete a Contact so that is no longer related to an Account.
Method 1 - single cloud flow
This is what my cloud flow looks like.
I have a trigger of when a Contact row is added, modified or deleted. In the Settings I also have a condition for this flow to only trigger when the Account Name field contains data. I don’t want this flow to trigger if the Account Name is not populated, in other words blank.
In the select columns field, I have the logical name of the Account Name field because I only want this flow to trigger when this field has been updated.
Next, I have an action that references my Environment Variable that retrieves my Azure Key Vault secret value from the Azure portal. This Azure Key Vault secret is used for the authentication in the HTTP request.
The following are a couple of links on how to use Environment Variable for an Azure Key Vault secret value.
The next actions is the HTTP request action where I'm using an application user which requires creating an app registration in Azure portal as the authentication for the API request to the web API. To learn how to do this, check out A Visual Guide To Power Platform Service Principal Setup by Matthew Devaney.
In my HTTP request I have specified the request method which is GET where we’re calling the CalculateRollupField function, followed by the URI that we walked through earlier, and then we have my authentication details.
I’m using another Environment Variable that contains the Environment URL for my Dataverse to use in the URI. The GUID of the account row/record is inserted as dynamic content, it's retrieved from the trigger response of the Contact, and the Azure Key Vault secret is inserted as a dynamic content value from my Environment Variable.
The following are references to what each of these authentication fields mean.
1 - Authentication is Active Directory OAuth to reference the app registration in Microsoft Azure portal.
2 - This is the Tenant ID from the app registration in Microsoft Azure portal.
3 - This is the environment URL of the Dataverse (or Dynamics 365) environment.
4 - This is the Client ID from the app registration in Microsoft Azure portal.
5 - The option to select is Secret.
6 - This is the Client Secret from the app registration and we're using the Environment Variable to retrieve the Azure Key Vault secret value.
Scenario 1 - Create a Contact record associated to an Account record
OK now we're ready to run through our scenarios.
This is what the Account looks like prior to the automation being executed, the Account only has 2 contacts and this is what is displayed in the rollup field.
Create a Contact and select an Account, then save.
This is what the Account looks like prior to the automation being executed, the Account only has 2 contacts and this is what is displayed in the rollup field.
Create a Contact and select an Account, then save.
When we
review the run history, the rollup field on the Account is refreshed.
The Number of Contacts rollup field has refreshed and is now correctly displaying 3.
Scenario 2 - Create a Contact record with no association to an Account record
Create a Contact, then save.
The cloud flow is not triggered which is correct due to the condition we specified, which is to only trigger if the Account Name lookup field contains data.
Method 2 - Using Parent flows and Child flow to make the automation reusable
Before we proceed with the remaining three scenarios, I want to switch over to Method 2 which is using multiple cloud flows. Shout out to George Doubinski for suggesting this method which I agreed is much better than Method 1.
We’re using the concept of Parent and Child flows to make the refresh of a rollup field reusable. In other words if you have more than one rollup field in your Dynamics 365 or Dataverse environment, this method enables the child flow to be called by multiple parent flows. You build it once and it is reused indefinitely. Genius.
To use a child flow, create it within a solution. Select the trigger, manually trigger a flow trigger, and enter in your inputs of EntitySetName, TargetID and FieldName.
These will come from the parent flows so that this child flow can be called multiple times to perform the refresh of rollup fields across tables and rows.
The next two actions are the same from Method 1 however the difference with the HTTP request is that I’m inserting the output values from the trigger as these will come from the parents flows.
The last two actions will send a response back to the parent flow that called it.
OK let’s check out the parent flow. Here's the parent flow.
The trigger is the same as Method 1 and the last action is run child flow, which calls the child flow that refreshes the rollup field based on the parameters provided. These parameters come from the input values that we defined in the trigger of the child flow.
Scenario 3 - Modify a Contact by updating the Account Name field to another Account record
Modify a contact record by updating the Account Name to another account.
We can now see the parent flow has triggered and see the child flow succeeded.
Navigate to the account record and the rollup field has automatically refreshed. The number correctly increments by 1.
The limitation with this scenario? Even though the account for the contact has been updated and the rollup field is refreshed in this account, the rollup field in the previous account the contact was associated to was not updated. I’ll talk more about this towards the end of the blog post.
Scenario 4 - Modify the Contact record by clearing the Account Name field
Clear the Account Name to another account.
The parent flow does not trigger.
The limitation with this scenario is that even though the account lookup field was cleared, the account rollup field has not refreshed.
Scenario 5 - Delete the Contact record
Delete the contact. The parent flow does not trigger.
The limitation with this scenario is that even though the contact has been deleted, the parent flow does not trigger because of the condition on the trigger. The parent flow will only trigger if the account name lookup column contains data.
But what if you remove the condition from the trigger?
Even if we removed the condition and deleted the contact record, the parent flow does trigger but it fails.
This is because the parameter value for the TargetID which is the GUID of the account record is missing in the trigger.
Is there a workaround to this limitation in order to refresh the previously associated account record?
So what is the underlying limitation with using cloud flows to refresh rollup fields? Well there’s functionality missing in the Dataverse connector as of when this WTF episode was published.
This missing jigsaw piece is the pre-image and context of the event. In other words the Dataverse trigger does not know what the previous value was for the account when updating the account name lookup field for the Contact or deleting the Contact. The trigger response only knows of the current value it was updated to. The pre-image and context functionality exists in the traditional SDK of Dynamics 365 and Dataverse but has not yet been exposed as a capability in the Dataverse connector.
Is it worth automatically refreshing rollup fields with cloud flows?
Now for the million dollar question – is it worth using flow to refresh a rollup field in near-time? If your requirement is for rollup fields to be refreshed on create, then yes, worth it ✔️
Otherwise until the pre-image and context is supported in the Dataverse connector, I don’t think it’s worth it ❌
Summary
I shared two methods with you in refreshing rollup fields.
- Method 1 - using a single cloud flow
- Method 2 - using Parent and Child flows to make the automation reusable
The following are the scenarios I went through with you for Method 1 and Method 2,
where only scenario number 1 was in my opinion an all round-success for refreshing the rollup field. Scenarios number 3, 4 and 5 were not yielding the behaviour I would have like since pre-image and context is not yet supported.
However when the product team does support it, then definitely Method 1 and Method 2 would work. Method 2 is the one that allows you to reuse the refresh rollup field automation by multiple parent flows.
I hope you learnt something new. Don’t forget to subscribe to my YouTube channel and till next time, Bye!