Duplication of filtered set of records in to a new table as part of a script.
Ok so I have a scripting problem that I have been mulling over for several days that I cannot figure out.
I am designing a sales quotation and order tracking database that is used to compile a dynamic list of products to be quoted out for sales. Once a sale has been made, I want a button to execute a script that duplicates the contents of the quotation in to the order tables.
Both the Quotation and Order modules consist of 2 tables. The first table in each houses the logistics of the customer and total sale, the second table houses the individual items included in the quotation/order.
They are named "Quotations" and "QuotationDetail" and "Orders" and "OrderDetail".
Each new record in "Quotations" and "Orders" is auto assigned a serial ID that is used as a uniquer identifier in the secondary table for each individual item that is added to a quotation or order. When the user adds an item to the quote or order, it creates a record entry in to the "QuotationDetail" or "OrderDetail" table and amends the QuotationID or OrderID that was autoassigned in the "Orders" or "Quotations" record creation to create a passive relationship to the data. The related items from the "QuotationDetail" and "OrderDetail" tables are displayed as a portal in the "Quotations" and "Orders" layout respectively so that all relevant information is displayed in one place for the user interface experience.
I created a script that sets variables for all of the information in the "Quotations" Layout, changes to the "Orders" Layout, creates a new entry, and duplicates all the values to the related fields. I am having difficulty with a secondary script that filters all the related records in the "QuotationDetail" to the relevant QuotationID and duplicates the entries to the "OrderDetail" table, while assigning the newly created OrderID to each record.
There can be anywhere from 1 to 50 entries in the quotation detail for each quotation, so I need a solution that is dynamic in how it filters and duplicates the data in the secondary table.