Copy Row along with child records

[Koichi_Tsuji]

It should have been recognized that the coping row including the child table/rows are currently not supported and possible.

However, I did find one way to do it. It works perfectly.

Before you read this post, make sure a prerequisite to use this tip is that you are on the business plan holder where you have access to Appsheet API.

---

What is behind the trick?

Basically, the trick is to retain the row ID of the original row and keep this id within the new row which was generated after the action.
Dont select “Copy row and edit” for action. Just select “Data : Add a new row to another table using values from this row/” During this process, new row will contain the id of original row.

Set workflow to generate new set of the child rows originated from. Rather than “copy” the set of child row, add row by Appshee API.

Now we are able to select the set of the row in the child table using the “OriginalIDCopiedFrom” within the condition for select expression.

After selecting the set of original child records, then loop it though within Body template for the API call. Make sure to put “stamp” to new child record for the parent record row ID so that the parent/child relationship between new set of rows are established.

---

Please find the step by step guide.

1. For the table, you wish to copy a row, add new physical fields where we store the row ID of the original row to copy. For this senario, I will name the column as “OriginalIDCopiedFrom”. At the same time, let me name the ID column for this table, called “RowId”

2. Add action to this table. “For a record of this table” select the name of the table you do copy a row.

3. “Do this” ; Data : Add a new row to another table using values from this row

4. Table to add to ; This is a trick. Select the same table name.

5. Set these columns ;

   Set the column you would like to copy from original row to new rows
   ///// IMPORTANT /////

   “OriginalIDCopiedFrom” : “RowId”

   By doing this, we are able to keep the track record from which particular row the new row are generated (copied)

6. Now move to the next step to create new Workflow rule.

7. I name this workflow as AddChild. Update event is set as ADD_ONLY

8. Fire this workflow conditionally. Add expression ; isnotblank([OriginalIDCopiedFrom])

9. Select action - Webhook. I omit the basic set up for API, but the important poinst are ’

   Verb POST / Content type JSON

   JSON body

   {
   “Action”: “Add”,
   “Properties”: {
   “Locale”: “en-US”,
   “Location”: “47.623098, -122.330184”,
   “Timezone”: “Pacific Standard Time”
   },
   “Rows”: [

   <<Start: Select(ChildTableName[Key_ID], [RowId]=[_THISROW].[OriginalIDCopiedFrom])>>
   { “RowId”: “<<[_thisrow].[RowId]>>”,
   “Field1”:"<<[Field1]>>",
   “Field2”:"<<[Field2]>>",
   “Field3”:"<<[Field3]>>"
   },
   <>
   ]
   }

Make sure the key / id for the child table should be autogenerated by setting up initial value with expreesion of uniqueid()

End

[TyAlevizos]

@tsuji_koichi this is amazing. I was working on a slightly different permutation of this concept and although I’m probably going to go through some revisions, it makes sense to release it as-is now.

Here’s an app:
https://www.appsheet.com/samples/a-method-to-instantiate-instances-of-predefined-object-definitions?...

and here’s the doc for the app:

docs.google.com

Variable Depth Row Creation using the AppSheet API

Variable Depth Row Creation using the AppSheet API: a method to instantiate instances of predefined object definitions App Link 1 Intro 1 Problem Statement 2 Field Naming Conventions 4 object_designer table 4 object_designer_children table 4...

Such a great technique… thanks for posting it!

[Koichi_Tsuji]

Thanks for your comment and post.
I will find time to read through your idea and docs throughly and revert with any comment to make it better and far more useful.

In terms of my techinique to copy the child table rows, It should be easy to twist around a bit to “delete” child row, along with deletion of the related parent table row etc.

Imagination is UNLIMITED!

[Grant_Stead]

Curious why you’re choosing to do this through appsheet vs on your own database side?

[Koichi_Tsuji]

Hi there,

Very simple question. My notion is to wrap up all the stuffs (as much as possible) at the front-end site, in this case, Appsheet, rather than working on the backend.

My database is always sitting on MS SQL. To do the same on the SQL, we need to write syntax, in this case, SQL trigger. I can ask my friend who are familiar with SQL and trigger etc, but I don’t want to bother my friend, haha.

[Grant_Stead]

I have been of the same mind… Lately considering more back end SQL side for data specific transformations.

[Koichi_Tsuji]

Before Appsheet released full set of API, I have been using SQL trigger. Appsheet API can do most of the stuff, apart from one single thing.
I have placed a feature request and other community member posted the same as well. When we run third party API, GET request, then naturally it returns a response. Then we wish to read the response body and push data whichever we need into the Appsheet data source as target. Once this is achieved, then we can do more and more. I m just looking forward to it and hope Phil in Appsheet will find a time to implement this last thing!

[Grant_Stead]

That’s true… Something we’ve been needing as well… We currently make a push to AWS Cognito to add a user to the pool, and catching the response would be great!

[Koichi_Tsuji]

That’s awesome integration! if we can achieve.
Let 's wait for Appsheet dev team to introduce this one, as a matter of time!

[Linda_Marita_La]

If i dont have pro plan, just premium.
What can i use if i want to automatically (without user input or action), copy a row to the same sheet and edit the copy? Triggered by a ISNOTBLANK value in column. I’ve been trying for several days without luck.

[APiCC_Conor]

The app link doesn’t appear to be working, any chance we can see an example of this setup?

[TyAlevizos]

Sorry that version and link is dead. Here is the correct one:

https://www.appsheet.com/samples/a-method-to-instantiate-instances-of-predefined-object-definitions?...

note that I have stripped out my API key (in the workflows) so this example will not actually work/run until that is addressed.

[_Excelrati]

Thank you so much @tsuji_koichi for this great trick!

Since this requires a business plan, and many like me don’t have it, what would be the alternative?
Is GAS function with trigger from Appsheet an option?

[Koichi_Tsuji]

I think there should be workaround with actions only, but could be little complex ones. I have idea with me, but yet tested if it works out.

[Koichi_Tsuji]

see my new post.

Copy parent record together with child records ; Action Only, No API, No workflow Tips & Tricks 💡

After introducing a trick how to copy the related child records along with parent record through the post of Quite a few people have been asking me how to manage it without API, but actions only. Sharing this trick may get the Appsheet sales team angry with me, so I m bit reluctant to do so, as it may reduce the sales opportunities for business subscriptions … , but let me put it aside now, This is what I managed to achieve the same task, “copy parent record together with child …

[jairod]

Has anyone made this work?

The <<start>> statement is missing it´s <<end>> statement and even after adding it it gives me an REF value missing error at the webhook body.