Cloning Parent to Other Parent Table together with cloning Child to Other Child Table

[stephenrich]

Dear Appsheeters,

I'm making two modules: "Sales Quotation" and "Sales Order".

The Sales Quotation module, consists of three tables: [Sales Quotation], [Sales Quotation Details], and [Sales Quotation Payment Terms]

While the Sales Order module, reflecting similar tables, consists of three tables: [Sales Order], [Sales Order Details], and [Sales Order Payment Terms].

I want to create a button (Convert SQ to SO) in the Sales Quotation, which will do several actions:

1. Create a new record in the [Sales Order] table using data from [Sales Quotation] table. The New Record will have a unique Sales Order No (SO No) generated using our own code in Initial Values.
2. Create child records in the [Sales Order Details] table using data from [Sales Quotation Details] table.
3. Create child records in the [Sales Order Payment Terms] table using data from [Sales Quotation Payment Terms] table.

I managed to do action no. 1, since it’s a direct copy between parent tables [Sales Quotation] to [Sales Order]. But I have problems that are confusing me to do a loop for copying data from [Sales Quotation Details] to [Sales Order Details], since doing so will need me to put the newly generated [Sales Order No.] from the [Sales Order] parent table.

The problems also occurred with the Payment Terms.

I need your advice.

Thank you.

[Aurelien · Accepted solution]

Hi @stephenrich 

Here is an option: Parent_Child_GC_noBot

Another option: Parent_Child1_Child2_noBot

Please note these options don't use any bot

[stephenrich]

Thanks for your advice. Will check it and try it 😀

[stephenrich]

I have check your advice. But the sample always using UNIQUE ID with no generated ID. While my Sales Order should use my generated number based on my desired format, and use that Sales Order Number also to create the Child. How can I do that? Can you check my initial Question? thank you.

[Aurelien]

you have to define the value you want for your next record, using this:

[stephenrich]

I know, but, my next record is not in the same Table. Can you re-check my first message? I have put an image to make it simpler.

[stephenrich]

No. I think you missunderstand my point.
I have two parent tables (Sales Quotation and Sales Order).
Each has two child tables (Sales Quotation has Sales Quotation Details, and Sales Quotation Payment Terms), while (Sales Order has Sales Order Details and Sales Order Payment Terms).

My Question is, how can i copy Sales Quotation to Sales Order including all of those child?

I cannot grab my Sales Order No. (Auto Generated using my own code below)

How can I grab the newly created Sales Order Number, and use it to create the new Child?

[stephenrich]

Dear Aurelien, do i really need to create a Buffer Column? to Temporary Keep the newly generated Number or ID?

I need your advice. Thanks a lot.

[stephenrich]

Thank you. Finally I understand your point and made it. 😁

[Steve]

Consider backlinking each order row--parent and children--to its respective quote row. Having that will make it easier to implement the loops by tracking which quote rows have already been copied. It'll also allow an order user to easily reference the original quote should you want to offer that.

[AleksiAlkio]

If you are using at least Core subscription, you could use AppSheet API to do this. Check the sample app "Copy Parent and 2x Childs" from https://appsheet.com/portfolio/77079.
In the sample app it copies them to the same table, but it will work with other tables as well. 

[Aurelien]

You are welcome 🙂