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 โ€ฆ , 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โ€

25 41 6,165
41 REPLIES 41

@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.

You have once again proven the age old adage - โ€œWhere thereโ€™s a will, thereโ€™s a wayโ€.

MultiTech
Participant V

manโ€ฆ I need to get the In-App Guide maker up and running so YOU can make guides.

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.

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?

MultiTech
Participant V

3X_1_a_1acde3d81835b7f2154f96fee5b38f24edca5eb9.jpeg
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

See it in action

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

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โ€ฆ

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!!!

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.

Ami
Participant V

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?

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.

Iโ€™m afraid Iโ€™m still not fully catching up with your own unique case but sounds like there is always a variable involved in your copy operation to determine where rows are finally associated.

What about firing one action first ? This action will create new form with column value in parent row where we pass the values of request fields from the selected parent? The hide those fields in the form and let the user to select out of Dropdown to decide where the destination is. Then save this form. Then upon saving the form it will fire the rest of actions , copy parent along with childโ€™s. At this point of time we know destination. In your working case it looked like to me the user need to select the destination after copies are made. But my case, selection of destination is part of chain of actions.

In my case, I open up a field in the originating parent record where I can select where to copy things. Then, as part of the action stack, I clear what was selected and reset the flag - to turn off the quick edits.

You could direct someone to a form where they select what to do, then take over the form even save action and enact your action stack there.

As always in AppSheet

Sure. Back to my original thread, i hope this trick will encourage the creativity of appsheet users.

ihayek86
Participant I

Do you have a working example of this which you can share with us
I tried it it is not working now

Hi Tsuji, are you still using this method?

What if, you wanted to copy the Parent and its Children into an already created Grandparent record?

So basically, give the user the ability to create a new Grandparent record and during that creation process copy into that record a Parent record (and its Children)

Iโ€™ve got your actions working fine but Iโ€™m killing myself here trying to run this action from the Grandparent level so that the application can know what KeyId to use in the copy row action.

Do you have any ideas?

Where is the action initiated? On the parent record ? Hit action on parent record then create new grand parent to parent along with copying the child records? Is that what you want to achieve?

Thanks for replying I would like to initiate the action from the top level, my setup is:

Orders - Parent
Products - Child
Materials - Grandchild

I am trying to copy 1 Product record along with all owned Material records from (hopefully) somewhere on the Order record, as the action will (hopefully) then pass the OrderID into the row copy action and in-turn create the copy of the Product into the correct Order.

So the user process would be;

  • navigate to previously created order
  • open copy product form
  • select from enumlist of Product records to copy
  • run the action on form save

I could do it this way to, ie Hit action on Product record, however the user would need a drop down list of previously created Orders to select the Order they are wanting to copy the Product (and children) into, if that makes sense. I cant just copy a Product, I must give the user the ability to choose which Order in which to copy it into

Nothing is impossible with AppSheet, so I m pretty much sure your business requirement can be filled, most importly, with Aciton only. (no workflow, no automation)
However, it requires tons of jobs.

I m always building up AppSheet app in mind first (in my brain) then once we finised to build, then start to work manually.

Based on your requirement, this comes up with me.

First you create sort of intermediate table. You place this action to order table. This action will open the new form using linkeform expression. New form will capture 1) Source Order ID, 2) LIst of ids for source orders products as options for the enum list. Initially nothing is selected on this enumlist, or even all the producted selected by default (Either way upto your preference) . User will select the product throug the form.

Then save. On save action, fire those actions as a set.

  1. Create new order
  2. Using @steve s add new rows out of enumlist tips and tricks, add new rows based on the selected enumlist items (product) and push the foreing key to make a ref connection to this newly created order

Thats the most efficient ways, but still involved tons of hours to build up physical app (not virtual app in my mind)

Great, thanks for that, i have setup a many to many table once already so iโ€™ll have a look at that for a solution

This is the hard part isnt it, we always try to find the shortcut but often we then need to go back and set it up properly.

If i think through this feature from the users perspective itโ€™s really two features

  1. copying an entire order with all related children
  2. copy specific products into an existing order

So i think i will modify your action group to run on 3 levels and then look at building out the second feature separately.

Thanks for engaging with me much appreciated

Assuming we have
1.Parent
2.Child
3.Grand-Child

tables and records are linked toghther through ref column. I demonstarated to fire action to copy set of rows, triggering action from Parent record, but technically, we can fire the same from everwhere, from child or Grand-Child records, while the set ups are different though.

Good luck.

Hi, tsuji_koichi

You wrote:
โ€œ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.โ€

I choose the action: copy this row and edit the copy
I canโ€™t find how to choose [SUB ID] for the ID of the new row

Sakis

Hey there.

This is an extremely good Tip.

But I have a question, what happens if between the [Sub ID] Parent and the [Sub ID] Child we found a difference of time.

In my case I have:
[Parent ID]
ba28575c4ddc5d8e-735f-432f-945d-973af562b27506/17/2021 20:22:32
[Ref to Parent ID]
ba28575c4ddc5d8e-735f-432f-945d-973af562b27506/17/2021 20:22:33

What Can I do In this case ??

Iโ€™ve had this issue also.

ๅˆใ‚ใพใ—ใฆใ€ใƒˆใ‚ฅใ‚จใƒณใจ็”ณใ—ใพใ™ใ€‚ใ“ใฎใ‚„ใ‚Šๆ–นใ‚’ๅ‹•็”ปใง่ชฌๆ˜Žใ—ใฆ้ ‚ใ‘ใพใ›ใ‚“ใงใ—ใ‚‡ใ†ใ‹ใ€‚
ใ™ใฟใพใ›ใ‚“ใŒใ€ใ‚ˆใ‚ใ—ใใŠ้ก˜ใ„ใ—ใพใ™ใ€‚

Thanks very much for this very cool guide.
it work for me fine after read him several times to understand.

now i want to add the action that sync after fire the action and lead back to the parent table.
you wrote:

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.

i try to find how to do it, and didn't find this action.

i will be glad for some help here/

Thanks again.

Thanks very much.

if i put LINKTOPARENTVIEW it takes to the main menu.

i want to lead to the new record (with the duplicated children)  that made in the previous action.

should i need to use LINKTOVIEW or LINKTOFILTERVIEW?

and what is the correct expression?.....

***update 5/2/22***
After 2 days of search and Trial and error i found the answer:
the right expression is:


LINKTOFILTEREDVIEW("view name" ,([REF_COLUMN] = [_THISROW].[REF_COLUMN]))&"&at="&ENCODEURL(NOW()+1)

Thanks for the solution but can you elaborate a bit? What are your ref columns here?

Paras_Sood
Participant V

Thank you for the explanation, works like a charm. How can I open a form view of the created parent record after the sync is finished so that the user can update this record as needed?

Tsuji

 

This is great. many thanks.

But I get a time difference between parent and child [Sub ID] and so the copied children will not be found by the parent.

Am I doing something wrong or is the solution floored?

 

Many thanks,

Malcolm.

Top Labels in this Space