As I mentioned in a previous post (see below), I began a conversation on FileMaker's community forum regarding the various methods for updating quantity fields in an inventory database (e.g. on hand, allocated, available, on order, etc.). it was a great conversation and several developers explained their methods for updating inventory. This download contains four methods as I understand them.
None of the methods involves calculation fields or summaries. I know some developers use these for inventory. However, I've chosen not to include them as they can adversely affect large data sets. Since they don't use calculation or summary fields, these methods are highly scaleable. The four methods are:
• 01_Find and Update.fmp12 is straight forward enough. Find the item and the Item location records and update them in another window.
• 02_Portal Method 1.fmp12. This is the method I was using and is in the previous post "Inventory Update Demo". It involves creating a relationship and a portal on every layout and updating without leaving the current record.
• 03_Portal Method 2.fmp12 is a different portal method, which, in my opnion, is more elegant than the first. Rather than a portal on every layout that requires an update (e.g. Purchas Order, Receiving, Sales Order, etc.), portals are placed on the Transaction layout.
• 04_Qty on Transaction_v2.fmp12 is different from the first three methods in that the quantity fields are NOT in the Item and Item Location tables. Rather, they are in the Transaction table.
• 05_Qty Table.fmp12 is yet a different approach. This approach stores the quantity fields in their own table rather than in the Item table or the Transaction table. By including them in their own table, the only record locking that can occur is if another script is trying to update the record. A loop in the script checks for record locking and since the script quickly moves through the process, even if a record lock occurs, it can be retested immediately, essentially forming a queue, wainting for the record to become writable.
Many thanks to all those who contributed to the discussion. In particular, thanks to Todd Walker (02), Wim Decorte (03), and HOnza (04) whose methods are the foundation for this demo.FMP 13 Files Updated: 2015-Mar-27 9h14 EST