3 Replies Latest reply on Aug 14, 2015 7:47 AM by philmodjunk

    Which is better Stored or Unstored Calculation for Converted Inventory?

    starstuff

      Title

      Which is better Stored or Unstored Calculation for Converted Inventory?

      Post

      Hi All!

       

      im trying to make a simple inventory system based on this thread https://community.filemaker.com/thread/79583, it doesn't use calculation in the fields instead it use scripts.

       

      i need to add a convert inventory option (eg from box to pcs, doz to pcs) to be able to add/remove inventory of different Unit of Measure.

       

      i solved this by adding a button that would convert the value to the converted unit and replace the Unit to the converted unit.

       

      in the Table, i added a field to calculate the value to the available units.

       

      i would like to ask if its better to have it Store or have it UnStore calculation, and will get the right inventory levels?

       

      Thank you!

        • 1. Re: Which is better Stored or Unstored Calculation for Converted Inventory?
          philmodjunk

          I don't see this as a case where you have to do one or the other.

          I generally recommend this method for managing inventory: Managing Inventory using a Transactions Ledger

          Which sets up a transaction ledger. Any inventory change is logged in this ledger table just as you would log deposits and withdrawls to/from a bank account. The advantage to this method is that you then keep a history of all inventory changes over time and this can help a business person better manage their inventory levels and determine ideal re-order points. The disadvantage is that calculating a current level on hand can become quite slow as the number of transaction records grow.

          But there are two methods--both can be used to improve speed:

          a) Periodically archive the transaction records in another table and "condense" the transactions into a "current balance" entry to keep the number of records that have to be totaled for a given product reasonably small.

          b) Use scripting to continually update a simple number field with the current total in your products table--which would be the method you appear to describe here. This makes viewing totals very fast as none need to calculate.

          A method of maintaining inventory strictly by scripted changes to such a number field work, but if used alone, has two key draw backs to consider:

          a) there's no "history" of how those changes occurred--except by reviewing Invoices and purchase orders to recreate how those totals were produced. This makes monitoring how inventory levels changed over time difficult.

          b) the developer has to meticulously capture every inventory event and ensure that the correct script steps correctly update the right record with the new total. This isn't always easy and if you make a mistake and a user manages to change inventory without the right script being performed, detecting and correcting the error can be difficult.

          • 2. Re: Which is better Stored or Unstored Calculation for Converted Inventory?
            starstuff

            Thank you PhilModJunk for the explanation!

             

            Im going back to your Transactions Ledger, I was able to produce the drawbacks that you describe.

             

            I like to suggest that you add these great advice on your Transactions Ledgeryes

            Please add these to the Transactions Ledger.

            a) Periodically archive the transaction records in another table and "condense" the transactions into a "current balance" entry to keep the number of records that have to be totaled for a given product reasonably small.

            b) Use scripting to continually update a simple number field with the current total in your products table--which would be the method you appear to describe here. This makes viewing totals very fast as none need to calculate.

             

            also, could you explain these further (a & b) with examples like the Transaction Ledger? :)smiley

             

            Thank You!

            • 3. Re: Which is better Stored or Unstored Calculation for Converted Inventory?
              philmodjunk

              a) Say you have these entries in the transaction Ledger:

              Product       in        out        Balance
              A                 3                          3
              A                 3                          6
              A                             2              4

              A "condense" script can use import records to copy these transactions into an archive file and then replace the above records with a single entry:

              A              4                           4

              A non-running summary field can easily compute this balance and your script can capture that value in a variable, delete the transaction records shown and then create a new records with the value of the variable used to set the "in" field to this "balance forward" value.

              b) get's a bit complicated in how you set up the needed perform script calls so that every possible event performs the needed scritp 100% of the time, but the script itself is quite simple.

              From the context of the newly created transaction log record, the following single script step would be performed:

              Set Field [Products::QtyOnHand ; LedgerTable::sBalance ]