3 Replies Latest reply on Dec 10, 2012 2:59 PM by philmodjunk

    best way to create an Inventory Database?



      best way to create an Inventory Database?


           Hi everyone.  i am pretty new here and have a question i hope one of you can assist with.

           I currently have database that has a digital PO and a data database with product information.  i now want to add inventory control to this.  so if i have product A, product B and product C and the all have sizes S-XL, 50 of each size,; when i create POs with these products listed, the Product Database will keep track of the inventory as i enter it in.

           i couldnt find anything this simple on the forum.  if any of you could help that would be great.



        • 1. Re: best way to create an Inventory Database?

               i couldnt find anything this simple on the forum.

               And why would it be that simple and still work? You'll need to add additional tables and relationships to use what you have to manage inventory.

               Does PO stand for "purchase order"? or something else?

               Does your inventory represent items you sell, items you buy, both, items that you make and then ship...???

               What version of FileMaker do you have?

          • 2. Re: best way to create an Inventory Database?

                 sorry, didnt mean to offend with the "simple" comment. i understand it isnt simple or i would be able to just do it.  :)  i also understand there are other tables and relationships needed to be created. 


                 PO means purchase order.  i am selling it so i have inventory then when i recevie an order, i enter it into the PO with the sizing ordered.  say Product A 1 small, 2 medium, 3 large, 1 XL.  what i would like to see ideally, is the product database subtract those units from the inventory as the PO gets entered.


                 not sure the best way to make that relationship.


            • 3. Re: best way to create an Inventory Database?

                   No offense taken or intended on my part, just trying to caution you that you may have a bit of design work ahead of you before you get what you need working for you.

                   Seems like you should already have 3 tables, not two:


                   PO::__pkPOID = POLineItems::_fkPOID
                   Products::__pkProductID = POLineItems::_fkProductID

                   See this thread if my notation is unfamiliar: Common Forum Relationship and Field Notations Explained

                   I realize that the names may differ, but do you have that basic set up?

                   Generally speaking, you have multiple types of events that can change your inventory in a business that buys and sells its inventory (as apposed to manufacturing or a lending library...)

                   Some events: Selling, shrinkage, product discontinued.... reduce your inventory.

                   Other events: Shipments received, product returned, inventory corrections.... can increase your inventory.

                   One approach is to set up the POLineItems table to function as an "Inventory Ledger" where each such event can be logged in this table and then relationships and/or summary fields are used to compute current inventory levels. Such "ledger" based approach can be useful as it will tell you not only what is your current inventory for each product but also how your inventory levels are changing over time--which can be very useful for adjusting the "re-order levels" that determine when you need to acquire more product.

                   If that sounds like what you want, see this thread: http://forums.filemaker.com/posts/445739f103?start=1&stop=10#211358