4 Replies Latest reply on Jul 14, 2014 10:49 PM by RobertChan

    How to automatically create related records in a third table

    RobertChan

      Title

      How to automatically create related records in a third table

      Post

           I am new in Filemaker.  I have an order header related to order details by the order number.  I also have a packing details table related to the order header via the order number.   I have another Kit master related to the Kit details via the parent item code. I have the order details showing as a portal in an order entry layout.  User will enter the kit item code during order entry.  How can I get Filemaker to expand the kit to the component level based on the kit details and fill out the packing table with the expanded components?

           e.g.

           Parent 1 - Component 1 x1 , Component 2 x 1, Component 3 x 2.

           Parent 2 - Component 4X2, Component 5x 1, Component 6 x 1.

           When user order 10 of Parent 1 and 5 of Parent 2, I would like the packing details table to have the following 6 records.

           Component 1 with 10 units,

           Compoennt 2 with 10 units

           Component 3 with 20 units

           Component 4 with 10 units

           Component 5 with 5 units and

           Component 6 with 5 units.

           Thanks..

            

        • 1. Re: How to automatically create related records in a third table
          philmodjunk

               I'm not sure if your data model matches what I am about to post or not:

               Invoices----<LineItems (InvoiceData in starter solutions)>------Products-----<ComponentList>------Products|Component

               Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
               Products::__pkProductID = LineItems::_fkInvoiceID
               Products::__pkProductID = ComponentList::_fkKitID
               Products|Component::__pkProductID = ComponentList::_fkComponentID

               Important detail: Products and Products|Component are two table occurrences with the same data source table.

               Now "packing list" can be the LineItems table here or a different table also linked by InvoiceID just like LineItems. (You might use line items to document the sales transaction and Packing slip to document the packing requirements.)

               But the basic process is the same. In the portal to LineItems located on your Invoices ID, a script trigger on the portal row or a field in the portal row performs a script that:

               a) checks for the existence of related records in ComponentList for the selected product. If [ Not IsEmpty ( ComponentList::_fkKitID ) ]. If there are related records there, this is a kit and the next part of the process takes place. If not, this ends the script:

               b) A variable is set to the List of ComponentIDs from the records related to this product: Set Variable [$CompList ; value: List ( ComponentList::_fkComponentID ) ].

               c) The current __pkInvoiceID is set to a variable.

               d) Another variable is set to the list of Quantities specified for the kit in Component list.

               e) A fourth variable is set to the quantity specified in LineItems for this kit.

               f) the script changes to a layout based on Packing List and creates one new record for each value in $CompList, multiplying the specified kit quantity from the list of componentList quantities by the lineitem quantity so specify a total quantity for that component.

               g) after exiting the loop, return to the original layout so that a user can add another item. (Or you can set up a looping script that loops through all the line item records to do the above after the invoice has been completed.

               Now the result isn't exactly what you specified. If two kits specify the same component, you have two entries in your packing list table, one from each kit. If you use a list view layout with sub summary layout parts and some summary fields to subtotal the quantities, you can produce exactly the merged list that you specify.

               Alternatively, you could modify the above approach to first find and search all existing packing list records for a record with the same componentID so as to merge in the next kit's quantities into one record, creating a new record if one is not found, but that's about the same amount of work and will slow down the execution of your script as it will have to do a lot more with each listed line item.

          • 2. Re: How to automatically create related records in a third table
            RobertChan

                 Thank you PhilModjunk. That make sense.  Not sure I can use List function that way.   I will try that.  My data model is exactly like what you mentioned.  Thank you very much. I also would prefer to have multiple component records from different kit so when it prints. So no need to summarize.  Really appreciate your help.

            • 3. Re: How to automatically create related records in a third table
              RobertChan

                   Hi Phil.  How do I refere to the individual value in the list $CompList?  

              • 4. Re: How to automatically create related records in a third table
                RobertChan

                     Hi Phil.  Please don't worry. I figured it out.