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.
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?
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.