Calculated or roll up best practice

[ckserra33]

Hi everyone.  I have a parent and a child table where the parent table is the headers for a parts order, and the child table lists the items purchased.  Ideally, I want a column in the Parent table that sums up the corresponding columns in the child table dynamically.  Currently I have it working statically, but I need to change it so if the qty sold changes before the billing cycle, the price will update dynamically on the parent table.

Can someone tell me the best method to accomplish this?  I looked at calculated columns today, but it said that they were being retired and needed to use the formula column, but I figured I would ask here before moving forward.

Thanks!

Chris

[Stevieed]

Hi,

There are several ways to achieve this.

Calculated column does not provide the feature you required. The feature you are looking for are rollup column or custom method using power automate or c# dataverse plugin.

Rollup column has been around for many years and they are the basic method for calculating sum. However rollup column has a lot of limitations. Refer to this article: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-rollup-fields

I would strongly advised you not to try rollup column because you may in the future encountered a limitation like a brickwall and you never be able to work around it and you have to start consider other method of implementation.

Instead I strongly recommend you use power automate for this task. The optimum way of doing this is:

1. Power automate trigger: When a child item changed its value

2. Using list row dataverse action, and in that action, use fetchxml language to write a query to retrieve the sum of a selected list of record. You can construct fetchxml using the fetchxml builder in Xrmtoolbox: https://www.xrmtoolbox.com/plugins/Cinteros.Xrm.FetchXmlBuilder. Step to get this tool: download  Xrmtoolbox, connect it with your environment and install fetchxml from its tool store. This tool is so straightforward and it allow you to construct query directly by selecting table and fields from a drop down list, it's very easy to use.

3, once completed the fetchxml query, you will want to put the parent record's unique identifier (guid) column inside the fetchxml query text, as a dynamics variable in your power automate flow, which means "the flow will aggregate the related child records to whichever parent record is triggering the flow".

4, add a update roll dataverse action to retrieve the aggregate result from previous action and put it in for update. Note fetchxml list row action returns results in a slightly counter intuitive way, we need to check null and check if result exist before put it in for update. Use below formula for this purpose: 

if(contains(outputs('List_rows')?['body/value'][0],'<your fetchxml result alias name>'),outputs('List_rows')?['body/value'][0]['<your fetchxml result alias name>'],0)

Refer to attached picture for my fetchxml power automate flow for your reference.

 

Fetchxml and power automate can become very versatile as you can make them into parent and child flow and reuse aggregation logic everywhere.

Another method to calculate sum is to use c# dataverse plugin however it will require writing c# codes and a lot more overhead such as checking null value and checking if puts are valid, etc etc, and it can only aggregate changes from one field from one sdk message for each plugin.