5 Replies Latest reply on Sep 16, 2014 1:24 PM by philmodjunk

    Table Relationship Order



      Table Relationship Order


      My database is currently laid out as pictured below.  I have an order table that is related to a Order Detail table, Product table and Customer table.  My Inventory table is related to my Product table as shown.

      I would like to have a portal in my Production layout that shows inventory for each Order.

      Is this possible  with current configuration??


        • 1. Re: Table Relationship Order

          Products should be linked to Order Detail rather than orders:


          What is the difference between your records in "Products" and those in "inventory"?

          What does one record in "Production" represent? How is it different from an "order detail" record?

          You may be interested in an approach that takes the Order Details ( LineItems ) and sets up that table as an inventory transactions table were each order, production run, shipment sent, shipment received, shrinkage, etc. is logged by adding a record into that transactions table in order to both compute quantities on hand and to keep a record of how the inventory levels change over time:

          Managing Inventory using a Transactions Ledger

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Table Relationship Order

            I'd like to tackle these questions one at a time, if you don't mind?

            Products table contains what I call Finished Products, Inventory contains Finished Products, Raw Materials and packaging.

            • 3. Re: Table Relationship Order

              Which leads to another question: Why do you need records for Finished Products to be in two different tables? That may be necessary or it may be redundant depending on what data for Finished Products that you put in each table.

              • 4. Re: Table Relationship Order

                I can certainly see the reason for your question.  I have no reason to separate the two.

                I have made the change you suggested above as shown below.

                The production table contains labor hour data and quantities produced and so on....

                An order may contain multiple line items... and a line item will have many shifts of production data.

                Currently I am carrying OrderID and OrderDetail ID into Prodcution.

                I will be using your "Ledger" idea for my inventory transactions, but need my inventory data to display in my Production layout.

                • 5. Re: Table Relationship Order

                  So for one record in order detail, you may have many records in production? No problem there. But you do not need an Order ID field in Production so long as all records in production match to a record in Order Detail. You can get the Order ID form the parent record in Order Detail should you need that value.

                  but need my inventory data to display in my Production layout.

                  You can link an occurrence of the transactions table to Production for that purpose. Either summary fields defined in the transactions table or aggregate functions such as Sum( ) defined in the Production table can show quantity on hand. And there are ways to set up scripts that update a number field in the related products record each time a transaction record is added/changed/removed. This can be necessary in order to speed up performance.