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!
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.
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.
Same problem here with same type of application.
Following the topic
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.
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])
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?
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.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
16 |