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
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.
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.
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)
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].
#Master Line Items
To get this done simply change the Order[Products] EnumList to Base type โRefโ and the Referenced table name to โProductโ.
ANY(FILTER("Order",CONTAINS([Trigger],"CreateLineItems")))
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!
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
I learned it once here in the Community.
Haha, dang, you beat my edit.
The Force Sync is actually at the very very bottom of the Deep Link Docs.
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.
It also does not show that you can use it in combination with the regular LINK functions.
Many thanks @GreenFlux the solution works well without the need to mimic โloopsโ. Does it also update the order-items if the order is amended and fewer products are chosen? I.e. so the Line item table would need to have Products removed, which were are no longer part of the order.
I have tried some examples using the sample app and from I can tell it doesnโt appear to remove โoldโ unwanted products.
Thoughts?
Thank you @Fabian_Weller . Is the expression for [Order] column, correct?
Hi @harikiran2412 Back then it was working ๐ I didn't test it now, but you can see it in the sample app.
You can see these additions in this Sample App.
Nice one @Fabian
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.
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!
@GreenFlux @Fabian thanks for this work!
Just one question, @Fabian : what is triggering the โOrderTrigger CreateLineItems + Syncโ action?
It is not the workflow โOrderAddLineItemsโ. Thanks again!
Hi @Lucas Itโs the Form View โOrder_Formโ.
Consider: This function would not work if you are offline. Because the Workflow (Sync) needs Internet Connection.
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.
Adding @Steve
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?
Thatโs clearly a bug.
@GreenFlux I noticed the same today with a client app. @Adam any thoughts about this?
Hi @GreenFlux,
Imitation is the sincerest form of flattery and I am trying to imitate your app. My question is how did you configure the workflow rule? Under โDo Thisโ the Data Change Action Name says โOrdersCreateLineItemsโ but when I try to do something similar I only have the option for โDelete.โ I added a trigger column to one of my tables just like yours. Thanks.
Hi @tvinci,
The OrdersCreateLineItems action is one I created. There are a few custom actions you have to add before setting up the workflow.
Look under Behavior>Actions and copy the actions I used in my sample before you configure the workflow.
Yup! This is the only problem with this set up. In my case, I just turned on the background sync
Hi @GreenFlux,
Thanks for your response. Which is the action that populates the Customer column in the Order table with the correct ID? For some reason it stopped populating in my table and Iโm trying to fix it. From the front end this means that when a user selects items from the EnumList they are not saved to the โcustomerโ they correspond to. Thanks again
The Customer is directly filled in by the user. There is no action involved in setting the Customer.
I just checked the sample app and it is working normally. Perhaps youโve changed something with the Order[Customer]
Ref-column, or the Customer table.
Try comparing your current settings with the sample to see what changed.
This is a Great! solution. Thank you.
Is it also possible to create the order and automatically copy ALL products as child records?
I mean without having to select the products within the enumlist?
You can use the SELECT ALL button at the bottom of the EnumList dropdown. This will work without needing to edit the app any further.
Or you could add a New Action to:
Perform an action on a set of rows
, and select an action that will:
โAdd a row to another table using values from this row
(from Products to LineItems)
Set the filter to select all the products you want to add. If you want all products, then enter Product[Key]
.
When the order is changed after the initial create line items will be created doubleโฆ Is it possible to make sure the child records are only created on the initial create?
You could add a condition to the โForm saveโ action so that it only runs when:
COUNT([Related Orders]) = 0
FYI- This app is just a demo of the technique for copying EnumList values to individual child records.
Itโs not really setup to be the starting point for an inventory app. There are better samples for that here:
https://www.appsheet.com/SampleApps?q=inventory&hPP=100&idx=help&p=0&is_v=1
thanks, iโm using this functionality for my soccer squad. creating the training registraton for players
Hello GreenFlux,
Would you happen to know why certain records populate in the Order table but not in the Line Items table?
Thank you.
Tiffany
Hi @tvinci. I just checked the sample app and it seems to be working normally.
If youโre working with a copy that you have made changes to, it might be better to start a new post describing the issue.
Thanks for the sample! Iโve modified it to address the creation of repeating calendar events within our app. Now we use one calendar event (customer) and create multiple calendar dates (products). Thanks again.