1 Reply Latest reply on Jun 17, 2014 6:30 AM by philmodjunk

    Populating an order with a list of pre-defined parts?

    pinchvalve

      Title

      Populating an order with a list of pre-defined parts?

      Post

           I have a database that users build an order by selecting parts from a related table of available parts.  The users have mentioned that 3 or 4 markets have a set group of parts that are included in every order.  They would to have a button where they could add these groups with a single click.  Before I start working on this, I thought I would ask if anyone had built something similar or had a best practice on doing this?  

           My initial thought is to add a new "group code" field to the Parts table.  That would allow me to identify individual parts as belonging to a certain group.  Then build a script that searches for that code, grabs all of the records, then pastes them into the current order.  Sounds simple, but I have to figure out the steps.  

        • 1. Re: Populating an order with a list of pre-defined parts?
          philmodjunk

               We do that hundreds of times a day here. We have a customer PO for customers bringing in used beverage containers and since 90% of what they bring in will be 1 of 4 line items on the PO, we "Pre-Load" each PO with those 4 common line items to save time and serve people faster.

               But we don't use the copy/paste script steps to do that as there are better options.

               Here's one approach that can be adapted to what you want:

               I am assuming these tables, match fields and relationships:

               Invoices---<LineItems>-----Products

               Invoices::__pkInvoiceID = Products::_fkInvoiceID
               Products::__pkProductID = LineItems::_fkProductID

               Freeze Window
               Set Variable [$InvoiceID ; Invoices::__pkInvoiceID ]
               Go to Layout ["Products" (Products) ]
               Enter Find mode []
               Set Field [Products::GroupName ; Get ( ScriptParameter ) ]
               Perform Find []
               Go to record/request/page [first]
               Loop
                  Set Variable [$ProductID ; Products::__pkProductID ]
                  Go to Layout [ "LineItems" (LineItems) ]
                  New Record/Request
                  Set Field [LineItems::_fkInvoiceID ; $InvoiceID ]
                  Set Field [LineItems::_fkProductID ; $ProductID ]
                  Go to Layout ["Products" (Products) ]
                  Go to Record/Request/page [next ; exit after last ]
               End Loop
               Go to Layout [ Original Layout ]

               Note: the main drawback to this type of script is that the Go to layout steps can trip a lot of different script triggers which might then perform a trigger controlled script in the middle of executing this script. It is possible, using relationships and the List function or using ExecuteSQL, to cut down on the layout changes by getting a list of ProductID's in a variable and looping through the variables instead of looping through the records on the products layout.

               Note 2: After performing the find, an alternative approach would be to use Import Records to copy the data from Products to LineItems followed by using Replace Field Contents to add the needed Invoice ID.