4 Replies Latest reply on May 27, 2014 9:58 AM by philmodjunk

    Inventory database on an item basis



      Inventory database on an item basis


           Hi everyone

           Im hoping someone can point me in the right direction. :)

           I'm a beginner and currently building an inventory database for my home business. I have successfully created products, suppliers, customers, purchase orders, purchase order items, customer order and customer order items tables :)

           So I can now create purchase orders and customers orders and I also have a table that show my total inventory per product per category (ordered, in transit, shipped, etc).

           Next step is for me to create a detailed inventory table (?) where 1 product = 1 record. So the first records in that table would be manually created from my initial stocktake then next records would be created every time I create a purchase order. First issue I see is that my purchase order can have several item per record : 1 line in the order can be for 3 items of the same product. So I'd need to then create 3 records in my detailed inventory table.

           Is this doable? I feel like I'm bitting bigger than I can chew. If you know of any example that could stir me in the right direction that would be lovely.



        • 1. Re: Inventory database on an item basis

               Why do you need one record for each individual product sold? What problem does that solve for you?

               Such might be necessary for "large ticket" items such as selling cars--where you'd want to record the VIN or other manufacturer supplied serial number for each and every item sold, but when this is necessary, one normally already has the items listed as separate records in the invoice that documents the sale of that item and then there is no need to split a line item for 3 products into 3 separate records.

          • 2. Re: Inventory database on an item basis

                 Hi Phil,

                 Some of your products in our product range (let's call them products A) need to be managed on an item per item basis as all they are customed to the need of each customers, much like a car with different options etc.

                 The rest of our products don't need to be managed at the line item basis.

                 So you are right, for products A, the items are already listed as separate record in the invoice for their sale and in the purchase order for the supplier.

                 Im still unsure how to go about this.


            • 3. Re: Inventory database on an item basis

                   I keep working on my database and I think I am nearly there :)

                   I would love a gentle push in the right direction though. My table inventory is trying to reflect how many items of each products I have depending on the status (Ordered, In Transit, In stock etc).

                   It is based on the Purchase Order Line Items table. One of the calculation in the inventory table is not returning the result expecting.

                   Basically I want the calculation to sum the total submitted quantity field of the Purchase Order Line Items tables where the record shows a "Submitted" Inventory Status.

                   I have tried the calculation " Evaluate ( Case (Purchase Order Line Items::Inventory Status) = "submitted" ; Sum (Purchase Order Line Items:Total Quantity Submitted)) But it does not solve every record where the first condition is true, it seems to only return the value of the first record found where the condition is true.

                   I can't find the right formula.
                   Screen shot attached.

                   Thanks :)

              • 4. Re: Inventory database on an item basis

                     Here is a thread that you might find useful: Managing Inventory using a Transactions Ledger