Skip to main content

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)

😎 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

Hi @Margaux_Borgey and welcome to the community!


Let’s start here 🙂



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

Hi @Margaux_Borgey and welcome to the community!


Let’s start here 🙂



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


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

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


Hi, forget about it, I realized there was another field below, I just had to click “sum”

Thanks for your help 🙂

Reply


Terms & Conditions