Copy parent record together with child records ; Action Only, No API, No workflow

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 … :sweat_smile:, but let me put it aside now, This is what I managed to achieve the same task, “copy parent record together with child records” with actions only. No API required. No workflow neither involved.

Those are steps.

First, we have usual parent, child table relationship as a starting point.

For both parent and child, add VC with expressions

[Sub ID] For Parent ; [ParentID]&Context(“Device”)&Now()

[Sub ID] For Child ; any(select(Parent[Sub ID],[_thisrow].[Parent]=[ParentID]))

Basically those two different expression should yield the same text string value “per” parent record. Also I need to state this value will vary “sync by sync” and I should always stay unique, and never going to be duplicated with other user nor devices. The purpose of this arrangement is to generate the one time unique IDs across the app users. But using VC, the data never stored, and it will generate the unique value everytime the app syncs, regardless it is explicit sync or not.

Unless the user / app sync, the values should be stored in the device “statically”. Let me call this VC like [Sub ID] for reference.

  • I hear some voice, we can simply use uniqueid() for [Sub ID], but it wont work. The uniqueid() expression in parent will return the different string from child records, which is not a target of what to do here. Need to generate the uniqueid sharable among parent / child.
  • Using both context(“device”) as well as timestamp text value seems to be a good way to generate much stronger uniqueids manually.
  • The expression is considered to avoid the case where the duplicate set of parent/child records are generated across the different users who are on the app at the same time.

Next step.

Create action to copy new row. For the ID of new parent record, make sure to pass [Sub ID] value rather than initial value expression uniqueid() to generate new ID.

If you want to copy other fields, then prepare action accordingly, but for this particular tips, reminding use parent [Sub ID] fields to generate new ID for new row.

Next.

Create action in child table to add new row to own child table, like we did above to copy (add new row to own) in parent table.

For ref type column looking up the parent table, pass the value of [Sub ID] VC in child record. Else same story for action in parent. You can pass uniqueid() expression to ID for new child record or leave it a job to initial expression.

Next.

Back to Parent table.

Set new action to copy rows in child table using “Data excute an action on a set of rows” type action.

Reference table is off course set to child. And referenced rows are something like select(Child[ID],[_thisrow].[ID]=[Parent])

Just select the current working parent rows child.

Then apply the copy row action we created in child table.

Next.

Set Groupd excute a sequence of actions in parent table.

In the sequence of copy parent row action folowedf by copy set of child records/rows

OPTION

You can add another action in parent to the sequence of actions to force sync and back to the parent table, to make sure all the new data you see after firing those action are all up dates.

Before, I came up with other approach to do the same job with action only, but there was unexpected behaiviro. For instance, I sync with the app, and at the same time another user sync. At that point of moment, both users are standing on the exact same data set. Fire action to copy rows in both parent/child. I m one sec fast to fire the action against others, So I create the new rows, but second action made by other user will overwrite the set of row, which is usually happen in appsheet. On my use case, I needed to avoid the users create the duplicated rows anytime they fire action or workflow. API could nicely deal with it. Before, I was recording Sub ID in physical column rather than VC. Then I moved to VC way of trick using UniqueID(), but I encountered a problem uniqueid is “always” unique, which is not appropriate at this case. Eventually came up with context(device) expression included custom unique id expression, which ensure value is not going to be duplicated across any concurrent app users.

But now action only are doing exact same job as I expect, while we avoid the possibility of duplicating “ set of rows.

It is possible I overlook some important point , steps we need to take into account to achieve the common goals, appreciate for your observation to make this tricks far better.

Additional tips.
Keep using this action many times, we notice we have pretty much long long string in the ID for parents which are generated through this action.
It is upto you, how long you will feel confortable, but I went with

right([ID]&context(“Device”)&text(now()),50)

to shorten the UI values, but still “unique” enough as I believe.

Sub ID become value for new parent Id and same values are passed to child upon firing action, meaning ther child and parent relationship will be automatically generated under new parent and child.

Reason for “sync” after action is also recommended to replace the risk to generate the new row and overwrite by single users. If they quickly hit action button twice and three time, it will overwrite the set of rows… To avoid, push force sync action. Once the sync finish, every user see another fresh unique id even for the same row, like a fresh token, which will completely avoid the case of “duplicates”

12 Likes

@tsuji_koichi I am so glad that you are part of this community. Your grit, tenacity, and sticktuitiveness is extremely impressive.

Your solutions and walk-throughs are beautiful.

8 Likes

You have once again proven the age old adage - “Where there’s a will, there’s a way”.

6 Likes

man… I need to get the In-App Guide maker up and running so YOU can make guides. :nerd_face:

In fact, I had to overcome this very same functionality problem while building the In-App Guide maker, but I did mine a different way.

For one thing, I wasn’t concerned about multiple users - so there’s a whole lot of complexity that I didn’t account for with this.


But to give you an idea of how my mind wrapped around this one:


  • I wanted the ability to copy a parent (and it’s child records) and insert it into a hierarchy; not just copy the record but insert it properly, with all associated ref connections, into a hierarchy that already exists.
    • So I needed a way to designate where the copy should go.

To accomplish this I added an additional column into my table, allowing me to select where the copy should be made.
Then, using a series of actions and slices, I automated the process of copying:

  • first, the parent - which was flagged and held in a temporary slice for easy identification;
  • then the children, auto associating them with the newly created parent record.

I was gonna post a video showing some of this, but my use case is highly specific and very far-afield of the standard parent-child schema. :nerd_face:

Hello buddy,

The key point is “where the copy should go” I believe. Could you elabrate this point bit deeper?
My quick thought was the copy row action button is hidden unless there is a value in this field, otherwise the user could not copy it.

Also what dot hierarchy means in this context? The grouping of rows ? Or the sequntial number etc?

1 Like


First, a brief overview of how this setup is different from your traditional parent-child setup

Basically you’ve got a common parent record, then but then there’s the ability for a sub-parent record as well.

Next, a little about how I flag the sub-parent record

And now some craziness :crazy_face:

See it in action

1 Like

Thanks matt. I m now on morning coffee break, and will be back after viewing this interesting stories. Reverting.

1 Like

These 2 last videos dont seem to work, just me?

All video are working with me, but contents are super complex i need to take time to digest…

2 Likes

Yes, this is not a Sushi type of light food, bit oily too me, take a bit of time to digest in my stomach! … really complex use case you are on.
What I understand is it looks like there are three tables grand parent, parent and child, but actualy it is not. Just two tables.
Parent(child) should be referering to own table and make it looks like a grand child. I assumed you are setting up showif constrain. If it is “page” then show the set of grand children (which acutally sitting on own same table).

My trick is based on simple parent/child relationship structure. Even we have grant children, just apply the same tricks, add Sub ID to grand child table/records, which enable to copy both child/grand child upon copying parent row.
However, your use case is out of this traditional tree structure.

I thought if it is necessary to copy the child rows, but you have need to do so. Copying a single child record then push some mechanism to refer to the existing set of child records rather and copying news rows, but again, I understand you need to copy the set of childs on your case.

All in all, no help from my side!!! :cry:

1 Like

It should be out of the point for you. I just thought that we arrange it completely upside down? I reckon currently child records are copiied first, followed by new parent, and manual action to associate them?

Then what about creating parent first and select the set of childs? Then fire action to copy set of child records. ? NOt sure if we can arrange and bring difference with you, just a thought.

Hi

Are you copying the parent record along with the child record(s) to a third table or copying the parent into the child table?

Hi Ami is this address to me or Matt?

You and him as well.

Also, What i dont seem to understand is this:
Sometimes there is a difference with the amount of fields(columns) between parent and children, how does that solved when the two are combined

You can copy from a table to anywhere basically. You can add new row to the same table copy action is originated or any other table.

The data schema , fields names are not issue but you need a fields where it receives the value coming from add row action. If you don’t have inbox ? How do you receive?

1 Like

Didnt get the last part regarding the inbox

data fields to receive value

Here are the examples of a parent and child tables i have. Options 1 and 2 are to combine each in the other table. Option 3 is to combine them in another table.

I understand those are all “physical” tables. Not sure the necessity of parent/child or child/parent combine table …

you can create those two table virtually within appsheet I believe.

Please note, this is a non-standard way to build databases - I only used this schema because my use case required it.

In my case, I’ve got a parent-child-(sub-child) system (and yes, this is different from a (Grand-parent)-parent-child system) - where even the sub-child records are related to the parent.

The real variance with my setup is the fact that child records can have associated child records, so I had to have a way to create that association during the automated process.

So technically what’s happening - in my case - is:

  • I’m copying all relevant child records into the same parent table - just with a different parent association.
  • I’m also associating some of the records created with others that were created. (So create one, then associate all others with that one.)

Negative.

  • I create the parent first, with a flag on it so that it’s separated out in a slice: Live_Page

  • Then all the child records are created, automatically pulling in that page reference that’s being held in the Live_Page slice.

  • After all the records have been copied, I clear the Live_Page flag - resetting the system.


I didn’t think about doing it backwards… If I did it that way, I’d have to generate the ID of the parent while creating the child records. Not impossible, but this is where my mind went first.
:nerd_face: