1 Reply Latest reply on Feb 28, 2015 6:46 PM by philmodjunk

    Duplication of filtered set of records in to a new table as part of a script.



      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.

      Any ideas?

        • 1. Re: Duplication of filtered set of records in to a new table as part of a script.

          One option is to get rid of two of your tables. By adding a field that identifies a given record as either a quote or an order, you can keep the quotes and orders in the same table and also keep the line items in the same table. I am assuming that you want to maintain the original quote order as a separate record as one option for creating an order from a quote is just to change the label from "quote" to order, but sometimes the final order differs from the quote and you may want to keep track of that difference. If you choose to set things up that way, you can use the script found here for duplicating the quote record to create an order record and also be able to duplicate all of the line item records: Duplicating Bill Of Materials (duplicating portal line items) Be sure to use the script found at the end of this thread. The original example at the beginning has a flaw that is corrected in the latter version.

          But if you still want to use separate tables, I'd look at using import records to copy your line items to the order line items table. Import records can copy a given found set of records from one table to another even when both tables are in the same file. Thus, you can use Go TO Related Records or a scripted find to find all the line items for you quote and then use import records to copy them over in a single batch copy.