Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Summarize formulas results from another table

Topic Labels: Formulas
Solved
Jump to Solution
1570 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Margaux_Borgey
5 - Automation Enthusiast
5 - Automation Enthusiast

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)
image

Could you help me or give me other ideas ?

Thanks

1 Solution

Accepted Solutions
Margaux_Borgey
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
Thanks for replying so quickly
Here’s my database 1)
image
I created this one first, each item is one I’m likely to buy and use.
Here my database 2)
image
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 :
image

Thanks in advance

See Solution in Thread

3 Replies 3

Hi @Margaux_Borgey and welcome to the community!

Let’s start here :slightly_smiling_face:

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

Margaux_Borgey
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
Thanks for replying so quickly
Here’s my database 1)
image
I created this one first, each item is one I’m likely to buy and use.
Here my database 2)
image
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 :
image

Thanks in advance

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