Well I am back on my soap box preaching about transactions. :-). This time I was inspired by the Invoice starter solution that comes with FileMaker 11. I was pleased to see that it does not use an un-stored calculation to keep track of the quantity of inventory items on hand. Instead it uses a script to update inventory. This is great for scalability, but in all but the very lightest of use cases the method employed here will simply fail.
The crazy part is that it only takes about 8 extra script steps to make this method far more robust. I created a short little movie that describes how I added transactional processing to the script to make it much less likely to fail.
But before the movie, a little more background.
The basic problem is that there are a number of very common scenarios that can stop a script step from working as you think it will. Chief among these is multi-user record locking, but there are many other issues that can stop your scripts cold. To put it simply, if your business logic depends on a FileMaker script editing more than one record in one or more tables, and you don’t use transactional processing, your data is in trouble. It is only a matter of time before your data is all out of wack!
The good news is that this problem can be dealt with by using database transactions. Database transaction ensure that either all your edits occur or none of them do. There are no in between states allowed. FileMaker has been able to do database transactions since FileMaker 7, and I have been giving devcon presos on the subject ever since :>).
In the movie I show the basics of getting your FileMaker Scripts to support database transactions. If you have any questions post them to the comments and I will answer them ASAP. Thanks!
Watch it BIG on an iPad or iPhone
Your video on the inventory script is terrific. I used it to tweak the solution script and it worked like a charm. Question, do you have any info on adding to the inventory via a purchase order, I tried modifying the update inventory script but cannot get it to work. The PO runs without issue regarding product orders, qty and pricing but when I try to update the amount in stock via my “wonderful” script, nothing happens. I know enough about scripting to be dangerous but not good. Keep doing the videos, they really are top notch.
Best
Bob Moran
Hi Bob,
Glad you enjoyed the video. Sorry it’s really tough to know whats going on with your solution with out a long more info. I will be doing more stuff on Inventory very soon!
Thanks
Todd
Terrific.
I read somewhere that this is still vulnerable to creating false qty numbers…if the invoice is deleted it does not readjust the quantity in stock…Any thoughts?
Hello,
Not if you manage the deletion with another transaction. 🙂 Not to be flip, but thats what you have to do. Every change that could impact inventory must get made using a transaction. Otherwise you have the potential for things not to add up.
Todd
NIce and clear video. Especially with the zoom in close ups. 🙂