3 Replies Latest reply on Aug 10, 2016 1:04 AM by sunnychu

    Best way to calculate inventory amount


      Just randomly came across my head. I've built a few system in the past that deal with inventory. And usually I calculate the inventory by summarizing the sum of all inventory transaction record. The downside is that the bigger the database is the slower the performance gets, this will eventually happen due to inventory is usually a high transaction database.


      Wondering what is the best practice out there? Do you store the inventory count as a number, or do you calculate everytime you need it? Or is there some other very creative way?

        • 1. Re: Best way to calculate inventory amount

          Two thoughts:


          1. why don't you calculate monthly the inventory and store as a number (as you say)..much faster


          2. i think it is the ONLY correct way. why? because accounting-wise inventory  is usually physically checked monthly and many times there are differences between the system and reality. reasons range from shipping product A while invoicing product B, forgetting to invoice, theft etc. that means every month the inventory will be manually adjusted (that happens in almost every company with products like this). so your way of calculating every time will only work within a month.

          • 2. Re: Best way to calculate inventory amount

            We would have had the same problem.  It is really easy to add up all the transactions, but there is always the fear that it will slow the system eventually.


            We now always process the stock transactions via a script, which stores what the stock was just before the transaction, what the transaction quantity was, and what the stock was immediately after the transaction, and the type of stock transaction (overkill, I know, but it is so clear to the users who are investigating stock discrepancies that it is worth the time saved in arguments).

            It also means that you can see what the stock was retrospectively.  The real-world requirement for stock adjustments is handled just like any other stock transaction, so they are always there to audit.


            You should check out the postings about Filemaker Transactions - Todd Geist has an excellent tutorial on them:

            FileMaker Transactions - Transactional Scripting - Scale FileMaker

            • 3. Re: Best way to calculate inventory amount

              This makes total sense, storing a calculated number base on the real world workflow need (check once a month). Thanks for your input!