How to calculate a quantity of bottles for an event as an open bottle is sold?

[Gregoire_de_Mon]

Hi guys,

I’m new on Airtable. I organise mixology events and would like to know how many bottles I need for each event and the cost of each event.

I sell two type of events (pack) for groups :

• BA.ba = you can taste one recipe (Basil Smash)
• Expert = you can taste two recipes (Smash Daïquiri)

Each recipe is made of bottles (raw material). Each opened bottle is sold - this is the tricky thing.

Customers order one pack and told me how many people they are.

How can I know each event cost without manualy calculate the cost of each opened bottle ? As a pack is alwalys the same recipes and recipes are always made of the same raw material, I don’t want to fill linked table each time.

How would you do ? Thank you for your help :slightly_smiling_face:

[Mohamed_Swella1]

Hi @Gregoire_de_Montety,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

You will need to do all this work just one time and everything will fall into place after that.

You need to step a tables for the recipes, ingredients of recipes, prices of ingredients, etc.

The part where you say that an opened bottle is sold, this can be done in so many ways depending on how it is used. Meaning you will use 9+ bottles in an event so you will charge them 10? Cause then you can use the Ceiling formula and it will give you the highest integer. Or is it more complicated that that?

BR,
Mo

[Gregoire_de_Mon]

Hi Mohamed, thanks for your quick answer !

Here is my draft : https://airtable.com/shroO8vmINRhBnCvB

I get somewhere yesterday with a roundup formula - wich is quite similar to your formula I think (same nb of value).

I overcome by manualy link the order with all the ingredients in the Order_line table.

As for each pack, it’s always the same recipe, I don’t want to link every ingredient to the order as if it they were always different. In other words, it’s not (yet) about automate my work from a unique order to unique items, it’s about gather info so when someone order a pack, it always means recipe X and recipe Y wich mean ingrédient A,B,C,D,etc. and to link the number of attendees to the quantity /cocktails and the quantity /btl.

Do you have any trail ?

[Mohamed_Swella1]

That looks really impressive ! I think you got it working already

[Gregoire_de_Mon]

But no matter which pack the customer chose, I have to select manually all the ingredients - I can even pick ingredient which are not in the pack or pick some twice. Do you think it’s possible to link pack, ad hoc recipes and ad hoc ingrédients together so when someone make an order I just need to link is order line with the pack in an order line table ?

[Mohamed_Swella1]

I think you cannot do that because of the bottle thing. You need to check every time how many bottles you need.

[Gregoire_de_Mon]

Indeed, this is what I was afraid of :confused: Do you think I can fix it with Zapier ?