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

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!

1 Like

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.

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

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.

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.

4 Likes

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])

2 Likes