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.
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โ
Add action to this table. โFor a record of this tableโ select the name of the table you do copy a row.
โDo thisโ ; Data : Add a new row to another table using values from this row
Table to add to ; This is a trick. Select the same table name.
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)
Now move to the next step to create new Workflow rule.
I name this workflow as AddChild. Update event is set as ADD_ONLY
Fire this workflow conditionally. Add expression ; isnotblank([OriginalIDCopiedFrom])
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
@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:
Such a great techniqueโฆ thanks for posting it!
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!
Curious why youโre choosing to do this through appsheet vs on your own database side?
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.
I have been of the same mindโฆ Lately considering more back end SQL side for data specific transformations.
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!
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!
Thatโs awesome integration! if we can achieve.
Let 's wait for Appsheet dev team to introduce this one, as a matter of time!
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.
The app link doesnโt appear to be working, any chance we can see an example of this setup?
Sorry that version and link is dead. Here is the correct one:
note that I have stripped out my API key (in the workflows) so this example will not actually work/run until that is addressed.
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?
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.
see my new post.
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.