Dynamically Create X Number of Child Records from Parent EnumList with Only 2 Actions!

Normally a parent record is created first (e.g. Orders), then child records are added one at a time (e.g. Line Items)-- using two different forms, and saving after each entry.

Using the method below, multiple child records can automatically be created based on EnumList values in the parent record, using only 2 actions sent from the device. A single Order form can be saved, automatically creating as many Line Items as needed.

  • I’ve set up a Sample App to demo the technique.

  • Note that you’ll have to copy the sample to see it in action, since the public sample won’t save changes.

The key to dynamically creating as many records as needed is pairing the EnumList with a set of Master Line Items. Then a workflow action uses execute an action on a set of rows to copy all the Master Line Items to the new Order, but only IF they were selected in the Order’s EnumList.

Limitations

  1. The user must update each Line Item after creation to adjust the quantity. I’m currently autofilling [Quantity]=1, but any default could be programmed.
  2. New Products will need new Master Line Items created to keep the Order EnumList populated with all options. With a little more work this could be automated but I wanted to keep it simple for the demo.

Other Notes
This can be done with fewer actions and no workflow, but then every child record counts as a new change to be synced.

In my testing, the 2 changes took longer to sync than a regular row edit, but it was still considerably faster than creating each row from the device.

12 Likes

This is awesome. Thank you @GreenFlux

#Sync
As I see, this is working only when Delayed sync is off. So after each ADD / EDIT, there will be a Sync.
image
This is necessary, because you’re working with a Workflow.
In fact Delayed sync is what I would prefer. Because User go crazy when there is a sync after every ADD / EDIT. But there is a workaround:
Enable Delayed sync & Automatic updates, and put a forced sync only where it is needed.
So in your case:
Change the Action “OrderTrigger CreateLineItems” to a grouped Action with:
“OrderTrigger CreateLineItems” + “Sync”.
The App Link for “Sync” would be: LINKTOROW([Key], "Order_Detail")&"&at="&(NOW()+1)
image
With this there will be a Sync only after Adding / Editing an Order.


In my sample this Sync is not needed.

But the advantage of your App is, that you can add as many Children as you like. You don’t have to care about “how many Actions do I have to create?” --> Very good solution!


#Quantity

You can add Action “Plus” and “Minus” to the Inline View. Even the “Delete” Action. You can set the column Line Item[Key] to Show?=FALSE. Then attach those Actions to column [Key].
image


#Master Line Items

To get this done simply change the Order[Products] EnumList to Base type “Ref” and the Referenced table name to “Product”.


With this you will be able to add new Products via the EnumList.

Then change the Action “OrdersCreateLineItems” not to Reference on Table “Line Items” but on Table “Product”. And change the Action “CopyMasterLineItem” not to be for Table “Line Items” but for Table “Product”.
With this, you don’t need the “Master-Products” in you Table “Line Items”.


The Expression for [Order] would be:
ANY(FILTER("Order",CONTAINS([Trigger],"CreateLineItems")))

3 Likes

You can see these additions in this Sample App.

3 Likes

Nice one @Fabian

1 Like

Consider: This function would not work if you are offline. Because the Workflow (Sync) needs Internet Connection.

1 Like

Great additions, @Fabian! Especially the workaround for Delayed Sync.

I didn’t like having it force a sync every time, but it seemed necessary for this technique. Your idea for using the “&at=NOW()…” is a perfect solution. Thanks!

1 Like

I am just noticing now that with Delayed Sync and Auto Sync, everything syncs in the background after only a few seconds - including additions to tables. However, I will definitely be stealing some of this to pretty up some of the roundabout stuff I was doing.

Quick question, @Fabian…

Is this the part that forces the sync?

EDIT: Nevermind, answered my own question. The answer is yes. And for anyone else that is looking here, it seems you can use it at the end of any deep link.

Hi @Bahbus Yes, this is the part that forces a snyc.
Please don’t ask me why :joy:
I learned it once here in the Community.

1 Like

Haha, dang, you beat my edit.

The Force Sync is actually at the very very bottom of the Deep Link Docs.

1 Like

You are right, but it says
CONCATENATE("MyApp-123#at=", ENCODEURL(NOW()))
without the +1 at the end.
In my Apps it’s only working when I add the +1 at the end. :thinking:

1 Like

It also does not show that you can use it in combination with the regular LINK functions.

Adding @Steve :blush:

Hi. Is there some reason why I’m only able to get the sample app to create line items when only one product is selected? When I select more than one Product the functionality seems to break down and no child rows are created at save/sync.

Hi @timsimpson. This sample app just quit working recently without any changes to the app. I have a support ticket in and am waiting to hear back.

I’ll post an update here when the sample is working again. In the meantime, the same technique still works when triggered via Action button by the user, instead of by workflow.

1 Like

This sample app is working again.

It seems something with the AppSheet platform has changed with how ENUMLISTs are evaluated.

When I first posted this sample, I was able to Perform an action on a set of rows with this filter:
FILTER("Line Item",AND( [Order]="MASTER", IN([Product],[_THISROW].[Products])))

But the filter stopped returning any matching rows. Now I have to SPLIT the EnumList to get the same result as before:
FILTER("Line Item",AND( [Order]="MASTER", IN([Product],SPLIT([_THISROW].[Products],","))) )

@Aleksi any thoughts on why the previous expression stopped working?

1 Like

@GreenFlux I noticed the same today with a client app. @Adam any thoughts about this?

1 Like

That’s clearly a bug.

Good to hear it was not just me! I recreated the actions and workflows and managed to get around the need for the sync so long as the user gets a confirmation warning them that the child records might take a few seconds to appear. I’m currently trying to see if there’s a similar way to copy a parent record and it’s child records using a similar set of workflows. Not having much luck yet but I’ll keep you posted, Thanks!