1 Reply Latest reply on Aug 8, 2017 11:09 AM by philmodjunk

    Stock/Balance Calculation

    thong127

      Hello All,

       

      Before I will tell my problem I will give you a brief overview of what I am doing;

       

      I have a system that I used to track Quantity Balance/Returned ; Quantity Dispensed ; Lot Number in which material was used.

       

      Now we are using Inventory Card system to Determine which Lot Number that a certain material was Used  ;  How much Amount was Dispensed and How much Amount they Returned for inventory until that material is finish ; We want our system to put it in filemaker. Please find below our card sytem

       

      card.png

      I made a filemaker file with the following tables Materials & Materials Transactions

      My problem is How can I Calculate my Quantity Balance with my present set up when they dispensed any amount. I cant get exactly on what I have on my Card system

       

      I have attached my file that Im working with.

       

      Thanks in advance.

        • 1. Re: Stock/Balance Calculation
          philmodjunk

          A basic method that you can find in many "transactions" type tables:

           

          Define a calculation field that  combines the "in" and "out" values:

          I'll call it cBal and write the calculation like this:

           

          Returned - Dispensed

           

          define a running balance summary field to compute the "total of" cBal. (You can set this to restart the total with each different material.)

           

          An opening balance can be created by entering that amount in the returned field for the first transaction for a given material.

           

          There are also scripted approaches that can, each time you add, modify or delete a transaction, updates the total on hand value via a script that puts this value into a number field for better performance. This can make a difference as the total number of transactions increase over time unless you periodically "condense" them by replacing the older transactions with a new balance forward entry.