LinkedIn
Twitter
Hello All,
I have the following three related tables Orders, Items and Order_Items.
I want to update an order's Total each time I add or update a new Order_Items entry.
I can't find a way to do it using a bot that detects the Add/Update event on the Order_Items table. Perhaps there is another way of doing this? Any help is appreciated.
Solved
1 ACCEPTED SOLUTION
Ok.... I think I got it.
Thanks to @Ryan_Mortimer 's suggestions I fiddled a bit and got it to work. Let me try to explain how:
1. Added a "Virtual Total" to the Orders data
2. Created an action Update Total for Orders data to copy the virtual column value to the standard (real) column Total
3. Added an event Child_Change for all changes to child records
4. Added a process Update_Parent for the table Order_Items with a step Update_Order_Total to run the action on a set of rows of the referenced table Orders using this filter
Filter(Orders,[ID]=[_Thisrow].[Order_ID])
5. Finally, tied it all down with a bot:
So the bot is being triggered by the Child_Change event on the Order_Items data. When the event is detected the bot calls the Update_Order_Total process that runs the Update Total action on the row that matches the filter criteria that selects the parent record in Orders. The Update Total action simply copies the virtual column value to the standard column.
The key to make it work is step 4, having a data action that Runs an action on a set of rows. This allows the action to be performed on a different data set, in this case Orders, even though the event was detected on the Order_Items data set.
