Ultra-simple Inventory Example Database
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!
Here is a link to my Sample File: http://files.visionarycomputer.net/fmp/InventoryTest.fmp12.zip