7 Replies Latest reply on Jul 9, 2011 4:15 PM by rjlevesque

    Inventory System calculating stock from materials used

    SteveH

      Title

      Inventory System calculating stock from materials used

      Post

      Hello,  I've been researching the subject for some time now and have struggled to find a answer.  I hope that the expert database designers on this forum can help!

      I'm an American running a non-profit water well drilling organization in South Sudan, Africa.  We want to use the iPad to track different aspects of our work instead of using paper and pen.  For example, I want the crew to fill out the drilling log on a iPad running FM.  My main goal is that I can add inventory/new stock in the Inventory table and then a inventory report will show the total stock minus deductions made during drilling (taken from drilling logs) with a summary of the items left in stock.  

      The inventory system will not only accept deductions from drilling logs, but also from well repairs, equipment repairs, and materials sold.  This way we can in a glance see which materials and spare parts are available and when/how others were used. 

      I was thinking I needed to have a new recorded created automatically in my inventory table via relationships between drilling logs, repairs, and sales and the items used subtracted from those items added.  From reading other threads, it seems that people are saying that this is unnessary and defeats the purpose of relational databases.  I've also read that the best way to do this is similar to a bank balance with credits and debits but I would need the debits to be transfered or related over from a another table.

      Any help you can provide would be greatly appreciated!

      Thank you!

      -Stephen

        • 1. Re: Inventory System calculating stock from materials used
          rjlevesque

          What is your experience level in FileMaker?

          Will the iPad solutions be synced up with a mySQL/PHP back-end or a FileMaker server? Or will each client just spit out the data in some pre-chosen format?

          • 2. Re: Inventory System calculating stock from materials used
            SteveH

            The iPad solution would be synced up to a filemaker server although that is much further than I have gotten.  I would like to keep everything concentrated in one software package for reliability and ease of use.

            • 3. Re: Inventory System calculating stock from materials used
              philmodjunk

              but I would need the debits to be transfered or related over from a another table.

              Can you explain what you have in mind here?

              It's entirely possible to populate the inventory log via a portal on a layout based on a related table. In an invoicing system for example, the line items table can be replaced with this inventory log and the Quantity Sold field is replaced with the "credit" field in the inventory log so that each item sold is automatically logged out of inventory.

              In you case, a table of well maintenance jobs could be linked to this table and a portal to the Inventory Log could be used to update the inventory by logging the consumption of various inventory items used to repair the well equipment.

              • 4. Re: Inventory System calculating stock from materials used
                rjlevesque

                This can become a very complicated database very easily. Take your time thinking things out and you can keep it very managable and easy to use and build upon later if you see a need arise.

                May also need to break down your inventory into consumables and non-consumables per their very different replacement rate, etc.

                ---------------------------

                This is where the "relational" part of relational database systems comes into play. You will want to break everything down so that it makes sense, is easily managable, scalable, and in a way that ensures data integrity. However, at the same time you don't want to go overboard. So we don't want 2000 tables but we don't want everything crammed into one table either. Best rule of thumb is does it make good, common, sense in your mind when you look at your schema and think it through.

                I would suggest using the ACID principle but not really sure if this would apply here considering we are using FileMaker...you think Phil?  =)

                • 5. Re: Inventory System calculating stock from materials used
                  SteveH

                  You're right that this becoming complicated very quickly.  

                  When I said, "but I would need the debits to be transfered or related over from a another table."  I was meaning that the inventory would be one table where I could enter in new materials after purchase but the deductions would need to be taken another table such as the drilling log.

                  I like what you are saying about breaking everything down.  That could work to have a separate table for each material type.  All the prebuilt inventory systems I've seen don't really seem like inventory systems as much as product catalogs.  It appears that the quanities in stock need to be manually deducted...  Is this true?

                  Thanks for the help.  I'm still trying to wipe my mind of spreadsheet logic and embrace the database way of looking at things.

                  • 6. Re: Inventory System calculating stock from materials used
                    rjlevesque

                    LOL believe it or not that was a brilliant self discovery, because that's exactly what we tend to do...look at it as a spread sheet at first...then you realize you have to think spread sheet, only 3D. At least thats how I explain it. hehehe

                    • 7. Re: Inventory System calculating stock from materials used
                      rjlevesque

                      Manually deducted...depends. Say we have two tables of inventory, consumables and non-consumables. Perhaps we want to create our work order or quote before we actually remove the items needed for this project from inventory. See where I am headed here?

                      I promise I am not trying to confuse you or make things more complicated, I just know what it is like to get everything built without thinking a few things through first and then you got a mess on your hands or a huge re-build job just to fix it all.