Update Parent Table Column based on Child change column

[e4omir]

Hello,

I want to create a behaviour to change Parent table values based on the Child update (change of a field value).

I know that I should use the "Data: execute an action on a set of rows" along with "Data: set the values of some columns in this row", but still struggling with the expressions.

So, I have a Parent table named "opportunities" and child table "quotes" with Ref column to the Parent table.

In both tables I have columns for Stage with ENUM list of choices. When the Stage value into the Child table is changed to "Sent" I want the Stage value to the Parent table to be changed respectively to "Feedback".

1. I created a behaviour ("Stage - Feedback")to the Parent table with action "Data: set the values of some columns in this row" with action: Stage = "Feddback" but how to tell to the behaviour section that this should work only when the Child Stage is "Sent"?

2. I created a behaviour ("Update Opportunity Stage") to the Child table "Data: execute an action on a set of rows" with

For a record of this table: Child table

Referenced Table: Parent table

Referenced Rows: I do not know how to create an expression for this  

Referenced Action: the child behaviour ("Stage - Feedback")

Could someone try to help with the 2 highlighted in bold sentences?

Thanks in advance for any help. 

[Suvrutt_Gurjar]

---

@e4omir wrote:

Referenced Rows: I do not know how to create an expression for this  

---

Please try the name of the reference column in the child table , so the expression will be

LIST([reference column name in the child table])

---

@e4omir wrote:

but how to tell to the behaviour section that this should work only when the Child Stage is "Sent"?

---

In the child table's action ( action 2 of type "Data: execute an action on a set of rows" ) please enter an expression as follows in the "Only if this condition is true" setting 

[Stage]= "Sent"

[e4omir]

Thanks Suvrutt_Gurjar,

Unfortunately it does not work.
To summarise, I created 2 behaviours:

1. On parent table Opportunity ("Stage - Feedback") with

* Do this: Data: set the values of some columns in this row

* Set these columns: Stage = Feedback

* behaviour: Only if this condition is true = true

2. I created 2nd behaviour on Child table Quotes ("Update Opportunity Stage") with:

* Do this - Data: execute an action on a set of rows

* Referenced Table: Opportunity

* Referenced Rows: LIST([opportunity_id]) - this is the ref column into the Child table

* Referenced Action: "Stage - Feedback" - the 1st behaviour (Parent)

*Behavior- Only if this condition is true: [status]= "Sent"

Maybe I am missing something, but even when I did assign into the Child Form_view under behaviour - Event Actions: Actions to take when events occur > "Update Opportunity Stage". After updating some child state to "Sent", nothing happened with the Parent Status.

[Suvrutt_Gurjar]

Thank you for all the details. All settings look good to me. 

One thing to note is in this reference action that updates parent from children, if there are multiple children and if any child has set [Status] as "Sent" then parent will be set to "Feedback" and it will not repeat for other child records of that parent. 

Also you may want to ensure there are no edit restrictions on the parent table's [Status] column.

[e4omir]

Thank you for the feedback. 

Both "Status" columns are editable. I tested the Parent behaviour as a button and it works just fine. The problem (I think) is with the second Child behaviour and especially the part with "Referenced Rows = LIST([opportunity_id])". This column opportunity_id is a ref column into the Child table. How this LIST function will know which exact Parent row to update? And the problem is that, nothings happened when I execute it.

[e4omir · Accepted solution]

In addition to my previous post - I think I solved it. The problem was with the second Child behaviour with the condition [status]= "Sent". With this condition enabled I tested it with a button and it worked (the button was visible only if the condition was true), but even that I attached it to the Child form_view action it did not do anything. Then I created an event with Updates Only to the Child table (Status = "Sent") and then created a bot with this event and with the Child behaviour as an action (Data: execute an action on a set of rows).

@Suvrutt_Gurjar Thanks again for your help.

[Suvrutt_Gurjar]

You are welcome and thank you for the update. Just wonder why the action worked with button and not on form save. My one guess is there could be different forms on the child table. As such event action does not execute in one of the views where you wish it to execute because the form is different than the form where event action is set up.

Anyway very good to know you moved forward and found a solution. All the best.

[e4omir]

I think it was not working due to the condition Status = Sent. Because when I tested it with a button, with this condition the button was missing (because the condition returned FALSE). When I removed it the button was there working properly. So I created an event for Update change and with BOT made it work. Those are only my thoughts, since I am pretty new in appsheet.

BR

[Suvrutt_Gurjar]

Okay got it. Thank you. I think the workflow will expect that the user first sets the status to "Sent" so that the action  becomes available and button becomes visible. This is so because [Status]="Sent" is the precondition for the action to invoke.

Even in form save event action, since the action invokes after saving the form, I believe with the condition of [Status]="Sent" , the event action should also trigger.

Anyway, as long as it is working for you now , you need not disturb the setup. Maybe later on you could further analyze.