Calculating Sub-Totals from child records have always been interesting. Prior to Power Automate, the only options where:
- Use a Rollup field
- Use Code (JSCRIPT or a .NET Plugin)
- Use a custom workflow activity
Although Rollups are a suitable way to accomplish this, it has its limitations (mainly that they don’t get calculated in real time, and you can’t trigger events off of them). The other options require developers, which then lead to several considerations like how quickly will the deployment be, how much will it cost to make changes or to maintain it, and how complicated will be the monitoring, testing, and troubleshooting of such solutions.
Our preference has always been to use a low code approach, and use as much out-of-the-box features as possible. Now, while Power Automate may not be considered 100% out-of-the-box (it is considered a Customization), it can still be less complicated than coding a solution.
The Requirement for this Tutorial
For this Tutorial, we will assume that there is a parent entity that tracks total amounts of records from a child entity. Every time a new child record gets added, updated, or deleted, the parent total needs to be calculated.
We created three Flows in Power Automate:
- A Child Flow that does the calculation of the sub-total by looping over an array of records
- A Flow that triggers when the child record gets created or updated
- A Flow that triggers when a field on the parent entity is updated, and set to “yes”.
- This field is in turn updated by a Dynamics 365 Workflow that is triggered by the Delete event of the child entity
Why did we do this approach?
Power Automate can calculate the subtotals easily. The problem arises when a record is deleted. Although the new Common Data Service adapter triggers on the Delete event, when it does, it doesn’t keep any data element of the record deleted. Therefore, you can’t use it going on forward to get the rest of the records. So we had to get creative and use an alternate approach.
Enjoy the video below, and please subscribe to our channel. We will be posting Tutorials on a Regular basis.