How can I use Power Automate to sum child record values in Dynamics 365?
Rollup fields are great for aggregating the values of child records. However there are limitations with Rollup fields; most notably that they only ‘rollup’ once every hour and you can only have 10 per table.
Applies to:
Dynamics 365 On-Prem
Dynamics 365 On-line
Power Automate
Using a simple Power Automate process we. can use it to update the values of a parent record whenever the child record has been updated.
In this example, we have a parent record and multiple child records. This would be similar to a sales order and sales order detail tables.
The Child record has a Cost and Retail field (Type Currency) as well as a lookup to the parent record.
The Parent record has totals columns to hold the total costs and the total retail values
We’re going to add an automated flow to fire on add or modify of the child record. When triggered, the flow should get all of its sibling records (other child records attached to the same parent record) and sum the cost value and the retail values for all children, then write those two values back to the parent.
Setup the FetchXML
First we’re going to need to get some FetchXML code setup. The easiest way to do this is to create it in Advanced Find, then export the code.
So open Advanced Find and select the Child record table. Ensure that you add a criteria to filter based on the parent lookup.
Add the two columns that we want to aggregate/sum in the column selector and remove all others.
Download the Fetch XML
Open the Fetch XML in an editor and make the following changes;
Add the following to the header
aggregate=”true”Remove the Order By line
Add aggregate=”sum” alias=”<name>” to the attributes/columns we need to aggregate. Ensure that you set an alias name for each line so that we can refer to it later in the resultset.
Remove any other attribute lines that are not needed (ie. not aggregated)
Note the ‘Value’ statement for the lookup filter. we will be using this shortly.
Save this XML for use.
Building the Power Automate Flow
Add a new automated flow to your solution
Select the ‘When a Row is Added/Modified’ from the Dataverse options
Click Create
In the filters, add the Schema names for the two fields that we’d like to trigger on - in this case newcost and new_retail.
Add a new dataverse step action for List Rows - here we’ll use our previously made FetchXML and add the Parent Lookup value from the trigger step as the value in the fetchxml code.
This step is essentially searching for all ‘child’ rows where the parent is the same as the one that was just modified - and returning a sum of the Cost and the Retail.
Next we need to write this back to the parent record so that it has the sum values.
We’re going to do something a little funky here - IF we just try to use the output of the List Rows step, Power Automate will interpret this as a multi-line response and force an Apply To Each process - because Power Automate is expecting to get multiple results back as part of a List Rows step. We know that we’re asking for aggregate values and therefore will only ever get one result set.
So to get the data, we’re going to directly access the result set in the array using the body command.
Select the Cost field and in the expression tab, enter the following;
body(‘List_Rows’)?[‘Value’]?[0]?[‘cost‘]
What does that even mean?
Broken out, this means;
body(‘List_Rows’)
Use the Body data from the step ‘List Rows’. (note that spaces are replaced with an under-score)
?[‘Value’]
Get the node called Value
?[0]
Get the first element of the array
?[‘cost‘]
Get the field called ‘cost’ - which you will remember that you set in the fetchxml alias earlier.
I have pasted a copy of the output on the right so that you can see how the body() code refers to the output of the List Rows.
{
"@odata.context": "https://dominicsystems.crm.dynamics.com/api/data/v9.1/$metadata#new_children",
"@Microsoft.Dynamics.CRM.totalrecordcount": -1,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
"value": [
{
"retail@OData.Community.Display.V1.AttributeName": "new_retail",
"retail@OData.Community.Display.V1.FormattedValue": "$1,275.00",
"retail": 1275,
"cost@OData.Community.Display.V1.AttributeName": "new_cost",
"cost@OData.Community.Display.V1.FormattedValue": "$870.00",
"cost": 870
}
]
}
We’re going to write this info to the parent record.
Add a new action step to update a row.
For the record ID, select the Parent Lookup from the initial trigger step. In the Cost and Retail values, use the code from above to directly use the List-Rows aggregate sum values.
When flow runs, you can see here, that we have four child records, which are then aggregated and saved on the parent record.
Related Content: