2 Replies Latest reply on Apr 22, 2015 8:07 AM by DavidMaffucci

    Ultra-simple Inventory Example Database

    DavidMaffucci

      Title

      Ultra-simple Inventory Example Database

      Post

       

      Hello FileMaker Wizards!

      I wonder if you guys can help me with a problem I've been avoiding for a very long time. I have a complex legacy database system and I need to add some measure of inventory to it. I've created and attached the most simple database which can be used to illustrate the issue and hopefully one or more of you guys can help me come up with a solution. Here is what I've got...

      Imagine two simple tables, Products and Sales.

      Products has just three fields, Product Number, Product Description and Stock Quantity

      Sales has only four fields, an Invoice number. It also has three other fields which are repeating fields with 10 repeats - Quantity, Product Number and Product Description. 

      OK, so when you type a product number into sales, it looks up the product description from the Products table. Easy. What I can't seem to do is find a way to reduce the Stock Quantity by the Quantity sold in Sales. The other fun is that since there are 10 repeats, it needs to be able to change the numbers not just for the first line but all repeat lines. 

      Initially I thought of using a script trigger which would execute OnObjectExit. Then I thought it would be better to run a script when done with the whole invoice. I'd kind of like to be able to do either...(I know I'm not making this easy) at least I'd like to learn how to do it using either method and then actually implement the one I think will work best. 

      I realize that the structure of this sample (and attached) database is rather old-school. Today there would be portal fields and lots of relationships and all kinds of cool modern touches. The reason for this dinosaur-style database is that I'm only trying to learn to do this on a baby database so I can integrate the fix here into my actual, twenty year old, 40,000 record database which really can't be re-made. 

      Any help or advice on how do to what I need to do would be greatly appreciated!

      Thank you!

      Here is a link to my Sample File: http://files.visionarycomputer.net/fmp/InventoryTest.fmp12.zip

        • 1. Re: Ultra-simple Inventory Example Database
          philmodjunk

          It's not just the repeating fields are "old school" they are a poor design choice for your database and have been since FileMaker 3.0

          .so when you type a product number into sales, it looks up the product description from the Products table. Easy.

          Don't see how that works given the use of a repeating field for the product number. A repeating field matches all of the product numbers in every repetition of the field in the same record to records in Products and this generally keeps a relationship based method from working as a way to access the date in a related table such as products.

          You really need that related table. It can be set up to serve not only as the basis for your portal in sales but as an inventory transactions table where you log every change to every product's inventory levels--not just sales but new product received or manufactured and product lost due to being discontinued, destroyed, stolen....

          Here's a more detailed description of that method: Managing Inventory using a Transactions Ledger

          • 2. Re: Ultra-simple Inventory Example Database
            DavidMaffucci

            Thanks for the reply and I will check out...promise. I do want to see if there is a way to make it work with what I've got for now and last night I actually got a little closer. I can make it work for the first repetition but I can't make it work for any others...Sadly I left the database on my home Mac, I'll upload the revision tonight in case it helps to see that...