LinkedIn
Twitter
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โ
