An informative video (and associated blog post with downloads) by Daniel Shanahan of New Leaf Data on four different ways to accomplish the same task: Updating inventory records in a FileMaker database. Shanahan explains four ways:
• 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.
As Daniel notes, there are more than four methods out in the real world.
I favor one based on Todd Geists work with transactional rollbacks, which delays using the Commit Records step in the script until all items have been processed and no errors have been detected. Using the Revert Record /Request script step will undo all of the changes on all of the related records affected, insuring the purchase order (or invoice) is not processed until all items can be adjusted. This is important when a related inventory record is in use in a multi-user system and cannot be modified. Such an error would result in incorrect inventory counts.
Be sure to follow the link, watch the video, and download the four example files.