Getting data into parent table from child table

[SITECH_Mid-Cana]

Hello All,

This seems like a basic function but I am struggling with this. How do I copy data in a row from Child to Parent table? Example my Parent and Child table both have status column. How do I copy the status from child to parent? What is the expression that i need to use?

[Joseph_Seddik]

Does each parent have only one child?

[SITECH_Mid-Cana]

Yes only 2 tables in total

[Suvrutt_Gurjar]

Hi @SITECH_Mid-Cana 

I think @Joseph_Seddik 's question is whether there is only one child record for each parent record or there can be multiple children records for each parent record.

[SITECH_Mid-Cana]

Not sure I completely understand the question. But I literally just want to replicate the Status cell in the child( Service Report) to parent(Service Request). The status column is Enum type that has 3 possible entries: Initial, Complete, waiting for parts. So whatever the selection is in Child I want the parent to have the same thing in its Status cell for that particular ticket

[Suvrutt_Gurjar]

In one to many table relationships, a parent can have many children records and it is very common to have many children records for a single parent record.

So I believe the question is , if there is only one child record, it is easier expression  to take the status of child record to the parent.

But you have say three children records, for a single parent record and the children records have different statuses , one initial,  the second complete and the third waiting,  then which of these 3 statuses should reflect in the parent.

[SITECH_Mid-Cana]

What expression would I use if it is one to one? I totally see your point if there are multiple report statuses per one request status. I would have to figure out how to sort child entries by date and pick the status of a latest one.

[Suvrutt_Gurjar]

For a single child record, the expression can be something like 

INDEX([Related Children][Status], 1) in a VC 

Here [Related Children] is the system generated reverse reference column in the parent table and [Status] is the column in the child table.

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

[SITECH_Mid-Cana]

Thanks, that got me a step closer. I am getting status copied from Child to Virtual Column in parent. I now have to figure out how to copy from VC to Status in Parent.

I tried using Status app formula =[VC Status]  There are two issues.

1. The Status column does not update on sync. I have to edit and save the form for Status to take the value from VC. 

2. Status is now not editable obviously because I am using the app formula. I would like to set the Status from parent. 

I am guessing I should be using automation tasks to do this. Or is there a better way/expression that I can use to have both table mirror each other's status?

[SITECH_Mid-Cana]

And its one report per request so if I understand this correctly its one child to parent. 

Also if its not a big ask how would you do it if there were multiple reports per request? How would you take the Status of the latest one?

Thanks

[Suvrutt_Gurjar]

---

@SITECH_Mid-Cana wrote:

How do I copy the status from child to parent? What is the expression that i need to use?

---

---

---

I  responded to your earlier requirement pf copying status from child to parent. If you wish to set a real column in the parent table, please make use of reference actions. A sample app frpm AppSheet sample app repository below demonstrates updating child table from the parent table in the above requirement you need to do the other way but that is possible with the reference actions or yes even by using automation BOT.

Reference Actions - AppSheet

---

@SITECH_Mid-Cana wrote:

I would like to set the Status from parent. 

---

This is a significantly different or rather somewhat opposite requirement from the earlier one. Please elaborate what exactly you wish to do - you have mentioned both- setting parent from child earlier and now setting status from the parent as well.

[SITECH_Mid-Cana]

Sorry if I am not precise on this. I will try to explain. I have a Service Request Table that is a parent with a real column [Status] that is editable and type Enum with the following options Initial, complete, Waiting for parts, Follow up. Office looks at the service requests in the table and dispatches a technician. Technician creates a service report in a child talbe Reports. Based on the said report the office needs to assign the Status of the request. This is a bad situation as there is no real trigger for the office to change status. Stuff gets missed. In the perfect world I would automate the status change in Request based on logical selections in the Report. For now I would rather create a real Status column in the  report and have the technician change the status of  the report to Complete, Follow up etc. I would like the Parent Request table to be updated with the new status from the Child Report table without loosing the ability to set the status manually in both tables. (so no app formulas I guess). Does that make sense? 

[SantiagoU]

Hi @SITECH_Mid-Cana it may be simpler to bring the PARENT data to the Child and use the Child data instead. It seems that the most actionable data should be in the child and it will be a 1:1. To bring parent data to the Child, you can use de-references (https://support.google.com/appsheet/answer/10107396?hl=en) so bring the Service request data to the child and take all actions and automations on the child row. 

Conversely, if it's 1:1, you may want to consider keeping it all in a single table instead?

[RustamSap]

You are correect on this:

---

@SantiagoU wrote:

 it may be simpler to bring the PARENT data to the Child and use the Child data instead. It seems that the most actionable data should be in the child and it will be a 1:1.

---

But I still need the data to be transferred back to parrent. Like in my specific situation I need to track the status of request. The request status may change BEFORE a service report ever created, like Waiting for Parts status. So i cant eleiminate it from requests. 

Having everything in one table and using slices for the views of requests and reports  might have been viable at design stage. The app has been in use for years now. It might come to this but for now is there realy no way of mirroring status column in both tables and not losing the ability to edit it manually?

[SantiagoU]

Hi there!

Got it. In that case, I would follow the same guidance from @Suvrutt_Gurjar but add the expression in an Action and then I would enable an automation that triggers that action. Here's how I got it to work. I think these patterns are best solved via automation.

Objective: Bring a value from a child row to the parent row. Assume only one child row per parent

1. Create action in the parent table (mine is called Pseudo Memegen): Set the value of a column. Select the Status column, include the INDEX() expression

2. Create automation. You set the rules of when it triggers, it may be when any change happens in the Child table (mine is called Comments).  Select the RUN ACTION ON ROWS option. You want to find a set of rows (only one in this case) and then execute an action. To identify Row you want to change, make a list of one by bringing up the column that has the parent value (the column with the REF in the Child table) in my case is called Meme. Then select the action that you created in step 1. 

3. If everything is set up correctly and the app is published, you should have an action that updates the parent with a value from the child table. Again, this works on a 1:1 relationship, if you have many child rows for one parent, you need to change the strategy on how to select the row value to update. But the strategy with automation would be the same. 

Let me know if you were able to test. 

[SITECH_Mid-Cana]

Thank you so much! I will give it a shot and Post back.