Help with automation: How to update parent record with child record adds/updates/deletes

[jrullan]

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.

[Austin_QREW]

Hi @jrullan,

Great question!  We have a Work Order App that uses a similar data structure to the one you have here! So you are off to a great start. 

The way our Work Order App is set up is to have a 'Virtual Column' that sums the total instead of having a static column that sums the total. 

Show More

Example: 

Copy & Customize: Work Order App

Live Webinar Walkthrough: Work Order App built with AppSheet

Hope this helps. Let me know if you have any other questions. 

Thanks,

Austin from AppSheet Training

[jrullan]

Thanks Austin! It seems a virtual column is the way to go for most cases. However, I was trying to update a real column in the Orders table so I could then use this column from within Sheets to create a report of all orders. 

[Austin_QREW]

For sure. Here is a quick walkthrough of how we set up our work order automation and template. Hope this helps! Let me know if you have any other questions!

Show More

Automation: 

Show More

Attachment Template: 

[Ryan_Mortimer]

Would I be correct in assuming that you want to:

1. SELECT() all relevant "Order Items"
2. SUM() the values of each [Sub Total]  of these items
3. Display/Enter/Update this value in the "Orders" table?

[jrullan]

Yes, exactly what I want,  when a new Order_Items row is added or updated. 

[Ryan_Mortimer]

So, I always think it's best to try and make people "work" for the answers because it strengthens the knowledge and the community as a whole - rather than just issue a straight answer.

My response was mainly helping you identify the steps and solutions you need to take. (Always the very first thing you should do - then move on to simplifying the process)

So, to start with, how do you plan to select all the RELEVANT Order Items?

1. First of all, you will need to SELECT() a "unique" value that appears in both Orders and Order_Items, which I would assume is [Order No]
2. Now you need to SELECT() based on this.

Show More

SELECT(
Order_Items[SubTotal],[Order No]=[_THISROW].[Order No])

If this works out, you want to add these values together.You can achieve this by simply "wrapping" the above code in SUM()

Show More

SUM(
SELECT(
Order_Items[SubTotal],[Order No]=[_THISROW].[Order No])
)

Now, seeing as you aren't utilising a virtual column, you may run into an issue where the total isn't updated as frequently as you want (or at all in some cases!) 

You can do one of two things here (or both!)

Option 1.

1. Use a virtual column for the above expression, called [TOTAL COST] (or something similar, whatever you want). This will update "automatically".
2. Add a standard column with an expression value of :

[TOTAL COST]​

Option 2 (PREFERRED).

Create an action/behaviour/automation that automatically updates the parent row whenever an edit on the child rows are made.

(I just create a "dummy column" and set an action to set a random value or something similar but that is for a later date). One problem at a time.

[jrullan]

@Ryan_Mortimer thanks for the suggestions. I do appreciate the answers you provided. 

Regarding Option 1:

I tried using the virtual and standard columns approach and it technically works, but it doesn't update when a child record is added. Only when the Orders record is updated it updates the standard column with the value of the virtual column. 

Option 2 is what I was trying to achieve from the beginning.

Where I think I am stuck is trying to understand the limitations of the automation features provided by Appsheet in terms of detecting an event in a child record and then updating it's parent record. It seems there is no straight forward way to do this, or maybe I'm not understanding how to use it correctly.

[jrullan · 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.  

[Ryan_Mortimer]

@jrullan this is a great example of multiple ways to achieve the same or similar result.

It's not up to you to decide which is most appropriate for the application/scenario and which utilises best practices for the application/scenario.

Also, taking the time to optimise each and every step is very important too - (ie. using a Filter or Select expression rather than a reverse reference)

[Ryan_Mortimer]

Most definitely there is:

Here is how I achieve this in my solutions for my customers, brace yourself it will be a longer read.

1. In the parent table, create a normal column (not a virtual one!) called [ActionTrigger]
   
   
2. Navigate to Behaviours > Actions
3. Create a new action called "AutoUpdate1" where it sets the value of [ActionTrigger] to "X"
4. Create a new action called "AutoUpdate2" where it sets the value of [ActionTrigger] to "XX"
5. Create a new action Called "AutoUpdateMain" with "Do This" set to Execute a sequence of actions. 
6. Set the Actions to the following 3, in this order
   Action 1 = AutoUpdate1
   Action 2 = AutoUpdate2
   Action 1 = AutoUpdate1
   
   

Start a new automation with

1. Event:
   Data Change
   Adds & Updates
   Table: "Order_Items",
    
2. Process: 
   Run a data action,
   Run Action on rows
   Referenced table: orders
   Referenced rows: [ORDER ID] = [_THISROW].[ORDER ID]
   Referenced Action: "Autoupdate"