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

GreenFlux
Participant V

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.

24 37 8,138
37 REPLIES 37

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.
2X_3_33b853e1870b04d6ca5d8eb42b33b877ad58e58d.png
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)
2X_7_781401f2054691fc4992d2fd76fab4b84d3fc967.png
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].
2X_4_4aa9a45312c41bb5b92b21534fb6afb39d0983f2.png


#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")))

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.

https://www.appsheet.com/templates/Original-by-GreenFlux?appGuidString=d49d3b8d-8168-4c9f-b1cd-84876...

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.

Bahbus
Participant V

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

GreenFlux
Participant V

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?

tvinci
Participant V

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.

JuneCorpuz
Participant V

Yup! This is the only problem with this set up. In my case, I just turned on the background sync

tvinci
Participant V

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

tvinci
Participant V

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.

Taylor_Lewis
Participant II

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.

Top Labels in this Space