Refresh calculated fields in parent table when a child record is saved

[BDTG]

I am building an order entry app which has a parent table (Orders) for storing the general order info (Customer, Order Date, etc.) and a child table (Order Details) for storing the lines that are on the order. The Order Details table has a REF field pointing to the Order# key field of the parent table. This is working fine.
In the Orders table, I have a calculated field called “Line Total” to sum all the associated lines in the Details table. For example, if I add two lines to the order for $10.00 and $5.00, the Line Total on the parent should calculate to $15.00. My problem is that when I add lines to the Order, they show up correctly as related lines in the View, but the “Lines Total” field on the Order table is not automatically refreshing with a new total value when I save the Detail row. The only way I can get it to refresh the calculated value is to edit the Order line and then Save it. Then the calculated values are correct. Manually doing this each time is a pain.
Does anyone know how to trigger the update of the parent record based on saving a child record? OR is there an Action or Workflow I can use to automatically do this? Any other ideas?

Thanks!

[Austin]

This idea is not optimal but should work to send an update to the parent. Create a workflow that triggers on add for Line Items, this workflows action will be an execute action on set of rows, this action should execute on the parent row. You can just create an action that updates a column to itself thus triggering a re-calculate of the formula or you can have your re-calculating of the line items be the action itself. Either way will cause the Line Total to update.

[BDTG]

I’ve never attempted a workflow, so bear with me. I added a new workflow and named it.

WHEN THIS HAPPENS…
TARGET DATA: Order Detail
UPDATE EVENT: Adds_Only

IF THIS IS TRUE
CONDITION:

DO THIS
REACTION: Change Data?? (this is the only choice that seems relevant)
NAME: Save Parent record
DATA CHANGE ACTION NAME: <The only choice in the dropdown is “Delete”>??

Not sure if I’ve missed something or if I can create new Data Change Actions to populate this dropdown?

thanks again.

[furkizio]

Same problem here with same type of application.
Following the topic

[BDTG]

I think I’ve found a solution that works. Instead of creating a workflow, I just added an Action on the Orders table. I created a new field called LastUpdated and the set the action to update this field value with “NOW()”. I placed this action on the “Line Total” field and it now shows up on the View as a button I can click and it updates the Order record.

[Aleksi]

It doesn’t update it because you are using a normal column. If you want it will happen dynamically, you should use virtual column. Then the syntax should be…
SUM([Related Order Details][Price])

[Harriswe]

Hi @Aleksi I have the same requirement to update a record (usually a calculation) in a parent table and using a virtual column is FAR from ideal, simply because of the synchronisation overheads. Far-better to update the parent only if an event occurs requiring an update, rather than every time the app synchronises.

I feel this is an area where the workflow functionality is a bit of a let-down. Are there any plans to change the status quo?

[Rafael_ANEIC-PY]

Hello friends, im working on a very similar app, and i’ve come up with this:

Step 1: Create an action that recalculates the [Line Total] indirectly by changing the value of a trigger column (could be a “Last Updated”, or just a random “Trigger” hidden column) in your parent table.

We’ll call this action “Recalculate”

Step 2: Create an action on the child table, this action should be of the type “Execute an action on a set of rows”, from the child table pointed to the parent table.
For the “referenced rows” field use this expression:

LIST([Column reference to the parent table])

The referenced action will be the first action we made, which we called “Recalculate”.

We’ll call this second action “Run recalculation”

Step 3: Create a workflow that acts on ALL CHANGES to your child table (users could well add, update or delete their line items).
In “Do This” we pick “Change Data”, and our action of choice shall be “Run recalculation”.

And that’s about it, if you have more than 1 child table linked to a parent table that needs constant updating, this process can be scaled following the same method.