I am trying to build a database that includes inventory information. What I am trying to do is have a way of storing a base (yearly or quarterly) inventory and then every time a job uses material it will be recorded and a search of inventory can produce inventory used and/or current remaining inventory.
Setting up a basic inventory change via forms is not difficult, but my problem is I need to be able to account for changes in material prices.
For example if there are 20lbs of salt stored and it is worth approximately ($15/lb)
and then 50lbs of salt is bought at ($17/lb) then i have 20x15 and 50x17 or $1150lbs of salt.
If 30lbs of salt are then used I would just take an average price per salt value (IE 30 x $16.43) off of the value of the inventory.
How can I create a method of changing a price value associated with the salt so that I could get an average based on the current price in the inventory and the new price added?
Setting up a basic inventory change via forms is not difficult, but my problem is I need to be able to account for changes in material prices.
For example if there are 20lbs of salt stored and it is worth approximately ($15/lb)
and then 50lbs of salt is bought at ($17/lb) then i have 20x15 and 50x17 or $1150lbs of salt.
If 30lbs of salt are then used I would just take an average price per salt value (IE 30 x $16.43) off of the value of the inventory.
How can I create a method of changing a price value associated with the salt so that I could get an average based on the current price in the inventory and the new price added?