Recipe cost calculation

[Djiro_Ido]

Hello!

I‘m currently struggle with one problem. I want to make a system that allows a user to see recipes and calculate their costs. In a conventional database it’s pretty straightforward: you need a Recipes table used to present all know recipes and let user choose one than Ingredients table with actual costs of the ingredients and IngredientsInRecipes table telling us how much of each ingredient is in each recipe. Than we do SELECT from IngredientsInRecipes JOIN with Ingredients and have all costs for a given recipe at our disposal.

It should be easy but I can’t do it in Airtable at the moment. Linked tables work in a peculiar way here and it seems there is no way to get a separate table including amounts of ingredients from one table and prices from another. Can you give me some advice?

[Kamille_Parks]

You would accomplish this with the setup you described with the addition of Rollup or Lookup fields. You’ll need a setup like this:

• [Recipes] table

  • {IngredientsInRecipe}: a link to another record type field pointing to the [IngredientsInRecipes] table
  • {Cost}: a rollup type field pointing to the [IngredientsInRecipes] table at the {Cost} field with this aggregation formula: SUM(values), formatted as a currency

• [Ingredients] table

  • {IngredientsInRecipe}: a link to another record type field pointing to the [IngredientsInRecipes] table
  • {Cost Per Unit}: a number field where you type how much each ingredient costs per ounce/pound/whatever

• [IngredientsInRecipes] table

  • {Recipe}: a link to another record type field pointing to the [Recipes] table
  • {Ingredient}: a link to another record type field pointing to the [Ingredients] table
  • {Cost Per Unit}: a lookup field pointing to the [Ingredients] table at the {Cost Per Unit} field
  • {Quantity}: a number field storing how much of that ingredient is used in this recipe
  • {Unit}: a single select field of how the quantity should be measured (ounce, quart, liter, etc.)
  • {Cost}: a formula field with this formula {Quantity} * { Cost Per Unit}, formatted as a currency

Note: you may need a single price per ingredient using one chosen unit (say ounces) and have one field in the [IngredientsInRecipes] table with a complicated formula which divides the quantity of the unit to ounces.

[christoskanot]

Can you give an example?

Maybe a templete?

I did it as you write it but it didn't work!!!!(for me)