I have 2 databases that are used in the context of restaurant management for inventory tracking, valuation :
Database 1) A list of products that I am likely to buy (ingredients such as butter, milk)
Database 2) A list of purchases
Since the unit price of these items changes often, I want to track the valuation of my stock.
So on data base 2) I have :
Column A) Order number (i.e : #12584)
B) A linked field that allows me to select a single item from database 1) (i.e : butter or milk)
C) A number field in which I indicate a number of items bought (2 or 3)
D) A currency field in which I indicate the unit price (€2,50 or €3)
E) A formula/currency field which calculates the total expenses for the item (i.e €2,50*3 items = €7,50)
In my first database, I would like to add, for each field (each item), a valuation of my stocks thanks to my purchases tracker, and another column giving a cost per unit average :
i.e on 01/01 I bought one unit of butter at a unit price of 2€, on 02/01, I bought two units of butter at a unit price of €1,75
In that case, I would like a column to show 1) ((€1,752+€21)=5,5€ and the other to show (€1,752+€21)/3=€1,83). How do I do that ?
I already added a linkedfield in data base one, and I managed to sum all quantities ordered, but they won’t let me do a rollup of all bills for each item, which would give the total valuation and would allow me to get an average cost per unit (5,5€/3)
Could you help me or give me other ideas ?