6 Replies Latest reply on Nov 15, 2011 8:46 AM by Tim_Ballering

    Inventory control


      How do most folks handle inventory control. i.e. You have six widgets in stock. You sell one, now reduce widgets in stock to five.


      I have done this at the transaction level for a project that had relatively few, but very large, widgets with typically one or two widgets per order and only a handful of transaction per day by scripting the reduction of quantity as each sale was processed.


      The current project I am working on has many types of widgets with many widgets to an order, in a retail type enviroment with multiple people processing orders at the same time.


      I fear adjusting inventory as the transactions were processed using a script would slow down the transaction process under load. I could see doing this in periodic batches but fear selling more widgets than we have on hand at some point. Or would this be best handled by using a relationship?


      Tim Ballering

        • 1. Re: Inventory control



          I would be careful to test for record locks in this high traffic environment, to make sure you don't have two or more people trying to write the stock particular widget at the same time. One the other hand if you are just reducing the stock, by the ordered amount, it should go pretty snappy. You could also run a script periodically (server-side) that would go back and add up all the transactions during a given period and make an adjustment and/or report any variances. How many committed transactions an hour are we talking about?


          Vince Dolan

          • 2. Re: Inventory control



            See whether this link useful to you..   I am also searching for something in this line for my project.






            • 3. Re: Inventory control



              300 and 301 are the errors codes you are searching for. If you trap them, you can loop back and try again, until you are greeted with success, or send or display a warning email or dialog.


              Vince Dolan

              • 4. Re: Inventory control

                user946 wrote:

                300 and 301 are the errors codes you are searching for. If you trap them, you can loop back and try again, until you are greeted with success, or send or display a warning email or dialog.


                Yes. Error 301 (record lock) is the most commonly anticipated issue. However, if you're going to test for error 300 (which is a locked file) it would make sense to check also for 302 (table lock) and 303 (schema lock).


                That said, assuming the data design is such that long-duration locks on the stock control records are not likely (eg the values are in separate tables that are only updated by script and each scripted update immediately commits the change), any delays due to record locking can be expected to be negligible.


                By contrast, if you implement stock calculations in real time using related data, there will be cumulative delays as the number of transactions (and therefore the number of related records to be aggregated) increases. In general, real-time aggregation, whether by summary fields or unstored calculations is great for moderate record counts, but less so from a performance perspective when the volume of data is (or will become) large.


                Tim, from that point of view, I think you're already on the right track for this task - ie given the size of the data set, a transactional approach using scripted updates is a good fit. But you will need to implement it in a way that minimizes the impacts of user conflicts (eg that breaks out to dedicated separate tables for stock-on-hand numbers) and gracefully/efficiently handles error conditions so that discrepancies aren't introduced.





                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia



                • 5. Re: Inventory control



                  The way we do it is basically how Ray and I have describe it, but with one addition (that may or may not be necessary, depending on how tighly you coded your transaction script). We set it up to remove and add inventory with each transaction, but also create an audit trail of related records, that then are used by a nightly server-side script, that basically checks the transaction's work and reports any variances (this rarely happens, but does couple times a year). This hybrid approach, satifies both the inner FileMaker Geek in me and inner Operations Geek in the rest of our folks.


                  Vince Dolan

                  1 of 1 people found this helpful
                  • 6. Re: Inventory control

                    Thanks Vince and Ray. 


                    First for confirming that scripted is the way to go.


                    And more importantly the warning about the record locking gotcha that I would have discovered on my own, but only AFTER it broke


                    I like the audit table idea and will incorporate that too.  If something does break we should find it at the end of the day rather than after it is too late to correct for properly.


                    Now off to making it work.  But I may be back with a follow up quest or two.




                    Tim Ballering