3 Replies Latest reply on Jan 7, 2015 3:50 PM by philmodjunk

    Need help with a making a specific formula/ calculation

    AnnaFerreira

      Title

      Need help with a making a specific formula/ calculation

      Post

      Hi, I am still fairly new with Filemaker Pro and haven't fully got down the calculations part I need help making the following formula.

      I am using the "file maker pro from starter solution" - inventory database and the window that is separate (we will call it a list of data where you can add new transactions/rows of data. I have a list of data we are entering every time we buy or use inventory. 

      Across the top the titles/columns are: Date, Purchaser, Description, In, Out, Cost$

      In and Out are the counts of inventory either we are inputting because we purchased it and out is for when we sell or use them and then the cost of each unit as we buy them.

      On the same screen in an unrelated table as the In, Out, Cost$, I have information of the specific item, and a reorder level and units on hand that is calculated from the inventory in/out table. Below that I have a stock value of the total inventory of the one part we have on file.

      Originally the stock value was made by using the "units on hand" times the unit of cost that is different that the Cost$ I've created per purchase/sell we do. 

      I need a formula that is something like this:

      SUM (ALL COUNT OF IN * COST OF EACH LINE OF COUNT OF INS) MINUS - SUM (ALL COUNT OF OUT * COST OF EACH LINE OF COUNT OF OUTS)

      For example in non computer database terms:

      I buy 3 pieces of pipe at $3.42 each this should show as 3 put IN to our inventory with a total value of $10.26 stock value (which I can do this calculation easy). 

      BUT

      when I add a new transaction line that I then sold 3 pieces of pipe at $3.42 each it should show as 3 taken OUT of inventory and alter my total stock value to ZERO - however the system won't let me make this formula. 

      Please help.

      Thank you in advance,

      Anna

      inventory_snap_shot.jpg

        • 1. Re: Need help with a making a specific formula/ calculation
          philmodjunk

          Add this calculation field:

          In * cost - Out * Cost

          then add a summary field that computes the total of this calculation field to show your total value on Hand.

          • 2. Re: Need help with a making a specific formula/ calculation
            AnnaFerreira

            Tried it, unfortunately not the formula I am looking for. It gave me a total of all my inventory added together rather than total per inventory item and it is still not subtracting the out value for the stock value. 

            Any other ideas? 

            • 3. Re: Need help with a making a specific formula/ calculation
              philmodjunk

              Please check the info as currently posted in this thread as I modified it after posting. If you are reading your email only, you got a different calculation than what is posted here.

              it will subtract the out values if you set it up correctly. The calculation field needs to be defined in the transactions table and computes a positive or negative value for each transaction. Summing them will then combine both positive and negative values to compute a total balance.

              To distinguish between items to compute a value for each type of item can be done in a variety of ways. Some of those methods would use the above calculation unmodified but rely on relationships or portal filters to narrow the total to a specific type of item.

              One example is to use a self join to total the value of the calculation field from the context of a relationship that matches only to transactions of the same type of item.