Summarize formulas results from another table

[Margaux_Borgey]

Hi,
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 ?

Thanks

[Databaser]

Hi @Margaux_Borgey and welcome to the community!

Let’s start here :slightly_smiling_face:

Margaux_Borgey:

but they won’t let me do a rollup of all bills for each item

What isn’t working with the rollup field? Could you add a screenshot of your 2 bases?

[Margaux_Borgey · Accepted solution]

Hi,
Thanks for replying so quickly
Here’s my database 1)

I created this one first, each item is one I’m likely to buy and use.
Here my database 2)

Basically when I bought two items at the same time, they have the same order number.
As you can tell, I bought “beurre doux” (which is butter in French) twice, but the unit price has changed

Now if you go back to my first database, you see I added a lookup for the quantity ordered, and a rollup fields which sucessfully calculated the total amount of butter units I have in stock. Now, I wanna do the same but I want to add up with a roll the total bills for each items
I get this result :

Thanks in advance

[Margaux_Borgey]

Hi, forget about it, I realized there was another field below, I just had to click “sum”
Thanks for your help :slightly_smiling_face: