Question with copying parent and children to new tables

AppSheet AppSheet Q&A
1

Hi, I have been trying to follow the steps below through actions but I am struggling to link a new child table to the new parent table.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Copy-parent-record-together-with-child-records-Action-Only-No/m-p/354447

A bit about the app.

I have a Recipe_Table, an Ingredients_Table and a Recipe_Ingredients Table.

The user builds a recipe out and then based on the yield desired it adjusts the ingredient quantities accordingly.

The user then wants to push those recipes to a Recipe plan for the week which will then tie up to the stock on hand to determine whether everything is in stock.

So because the recipes are changing I want to push the Recipe Table and Recipe Ingredients Tables to a Recipe_Plan_Ingredients and Recipe_Plan table.

I have managed to get the information from the Recipe_Table copied to the Recipe_Plan table but I am struggling to get the child records to go along with the copy.

I am copying the ID of the Recipe_Table to the Recipe_Plan table.

Is this the value I use to link, and if so how do I set up the Ref column in the Recipe_Plan_Ingredients?

Is the Recipe_Plan_Ingredients a part of the Recipe_Plan table or the Recipe Table or should it have a Ref to both?

2
  • Add a column to the Parent Table, example: CopyID column name
  • Step 1 : Create an Action with (Data: add a new row to another table using values from this row )

Action Name: CopyXParentTableName0001-01
For a record of this table : ParentTableName
Do this : Data: add a new row to another table using values from this row
Table to add to : ParentTableName
In the Set these columns : set the relevant columns.
COPYID Column: [_THISROW].[ParentID]&“-”&CONTEXT(“Device”)
(You can replicate this with expressions like useremail().)

Prominence : Do NoT Display

  • Step 2 : Create an Action with (Data: add a new row to another table using values from this row )

Action Name: CopyXParentTableName0001-02
For a record of this table : ChildTableName
Do this : Data: add a new row to another table using values from this row
Table to add to : ChildTableName
In the Set these columns : set the relevant columns.
Prominence : Do NoT Display

  • Step 3 : Create an Action with (Data: execute an action on a set of rows )

Action Name: CopyXParentTableName0001-03
For a record of this table : ParentTableName
Do this : Data: execute an action on a set of rows
Referenced Table : ChildTableName
Referenced Rows : SELECT(ChildTableName[ChildID],[ParentID]= LEFT(INDEX( Select(
ParentTableName[CopyID],[CopyID]=CONCATENATE([_THISROW].[ID],“-”,Context(“Device”)) ),1),8))

Referenced Action : CopyXParentTableName0001-02

Prominence : Do NoT Display

  • Step 4 : **Create an Action with (Data: set the values of some columns in this row ) —**To avoid duplication and to keep track of which ID it was copied from.—

Action Name: CopyXParentTableName0001-04
For a record of this table : ParentTableName
Do this : Data: set the values of some columns in this row
Set these columns : CopyID = “Cp”&[_THISROW].[PARENTID]

Prominence : Do NoT Display

  • Step 5 : Create an Action with (Data: execute an action on a set of rows )

Action Name: CopyXParentTableName0001-05
For a record of this table : ParentTableName
Do this : Data: execute an action on a set of rows
Referenced Table : ChildTableName
Referenced Rows : SELECT(ParentTableName[ID],[CopyID]=CONCATENATE([_THISROW].[ID],“-”,Context(“Device”)))

Referenced Action : CopyXParentTableName0001-04

Prominence : Do NoT Display

  • Step 6 : Create an Action with ( App: go to another view within this app ) — If you want to edit the Copied Recording immediately

Action Name: CopyXParentTableName0001-06
For a record of this table : ParentTableName
Do this : If you want to fix the Copied Recording immediately
Referenced Table : LINKTOROW(MAXROW(“ParentTableName”,“_ROWNUMBER”),“Parent_FormName”)

Prominence : Do NoT Display

  • Step 7 : Create an Action with ( Grouped: execute a sequence of actions)

Action Name: CopyXParentTableName0001-00
For a record of this table : ParentTableName
Do this : Grouped: execute a sequence of actions
Referenced Table : LINKTOROW(MAXROW(“ParentTableName”,“_ROWNUMBER”),“Parent_FormName”)

Action: CopyXParentTableName0001-01 ,

CopyXParentTableName0001-03 ,

CopyXParentTableName0001-05 ,

CopyXParentTableName0001-06

Prominence : Display Ä°nline

Attach Column : CopyID

2 Likes
3

@HBT thank you very much for this detailed reply. I’m very sorry for the late response but I wasn’t able to get to back to this until now.

I have been working through this but when I arrive at CopyXParentTableName0001-05 I am not able to see the reference action in the dropdown, I only see the CopyXParentTableName0001-02 action.

If Invert the “For a record of this table” to be the “ChildTableName” then I am able to view that action but the SELECT statement does not work.

Should the “ChildTableName” also have a CopyID column? And should Step 4 be amended?