5 Replies Latest reply on Aug 25, 2011 3:58 PM by philmodjunk

    Inventory, Lineitems, and Purchase Orders



      Inventory, Lineitems, and Purchase Orders


      I have a database that's set up to track inventory and ordering. It's separated into some tables as follows:

      • Products 
      • Purchase Orders 
      • Lineitems.

      So I can create a purchase order, and in this table there is a lineitem portal where I can put several lineitems on the purchase order to place orders for more than one product from a company all at once. What I need (and have tried, but can't seem to get it right) is for the status of the purchase order to affect the inventory. So if the status is "Sent" then the inventory shows that the qty ordered is "on order". Then when the status is changed to "Shipment Received" what was ordered is then subtracted from "on order" and then added to  "on hand". 

      The Problem - Everything I've tried doesn't add up right. It either tries to add the quantities of all the lineitems on the purchase order up and then add that number to the inventory of the first item listed only, or it only takes into account the first item listed. It won't go through the individual lineitems separately. 

      Does anyone have any suggestions on how to create a calculation, summary, script, function, whatever that will accurately track the shipping and receiving of products? Or maybe a different way to set up the tables and relate them?

        • 1. Re: Inventory, Lineitems, and Purchase Orders

          In which table are you tracking inventory?

          In Products, LineItems or a third table?

          • 2. Re: Inventory, Lineitems, and Purchase Orders

            I'm tracking the inventory in the Products table. It has a "quantity on hand" field and a "quantity on order" field. Once the order gets in, of course, the "quantity on order" will go down and the "quantity on hand" will go up ideally. 

            • 3. Re: Inventory, Lineitems, and Purchase Orders

              Either Products or LineItems can be used to track inventory. Both in fact can be used in the same solution.

              When using the line items table, you can see your inventory levels change transaction by transaction. From the Products table, you can just see the totals for Quantity on Hand and Quantity on Order.

              How do you currently document which ordered items have been recieved?

              Presumably partial shipments may be sent with less than the full number of ordered items included in the shipment. How do you handle such partial shipments?

              Once we work out how you do that, we can take a look at a way to construct a relationship that matches records in LineItems to products, but returns the two different totals.

              • 4. Re: Inventory, Lineitems, and Purchase Orders

                The received shipments are currently documented by changing the shipment status on the Purchase Order to "Shipment Received". However, I had not given thought to partial shipments yet. I will most likely be adding a field to the LineItems table for qty received to keep track of how much has been received and adding a status to the Purchase Orders that says "Partially Received". This seems to me the best way of handling it, but if you have a better way, I'm more than open to it.

                • 5. Re: Inventory, Lineitems, and Purchase Orders

                  With a "Received" field in each line item, you can set up a calcualtion field in line items as: QtyOrdered - Received to produce the quantity on order for that line item. This gives you two fields, "Received" and this calculation, cOnOrder for your two amounts that you need for your inventory counts in Products.

                  If you have this relationship between LineItems and Products:

                  Products::ProductID = LineItems::ProductID

                  Then Sum ( LineItems::Recieved ) will compute the total received for the current product record.

                  Sum ( LineItems::cOnOrder ) will compute the total on order.

                  Seems like you'll need one other field for documenting the quantities removed from inventory (Sold, shrinkage, discontinued, dontated, etc.)

                  YOu can create records in the Line items table where you document these inventory changes as well.

                  Note: there are several thread here that discus an "inventory Ledger" you may want to examine if you want to see a way to compute these totals from within the line items table instead of products so that you can track how your inventory levels change over time in order to better adjust re-order levels.