Need help with a making a specific formula/ calculation
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).
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.
Thank you in advance,