Summing Values depending on duplicates

The recipe base I made accomplishes this:

Basically, it has an [Ingredients] table which uses Rollup fields to add up the total amount of ingredients needed for the recipes planned for the current week. For this to work, [Recipe Ingredients] records act as a “middle man” between the [Recipe] and [Ingredients] tables.

Your meal planner looks great and it looks like it solves the duplication problem. Can you quickly explain the logic behind how using a middle man table prevents duplication? Thanks so much.

Sure. Lets take the Chicken Noodle Recipe as an example:

To make Chicken Noddle Soup, you need several ingredients in varying quantities (i.e. 8 oz egg noodles, 2 lb chicken breasts, etc.). Now, I could store this all in a Long Text field, but then I couldn’t make an automatic shopping list because Airtable wouldn’t be able to distinguish the quantity from the ingredient, and between each line of text.

So instead, I made a [Recipe Ingredients] table to work as a “junction table,” which means it pairs one record from one table ([Recipes]) with another record in another table ([Ingredients]) in order to store more information about the union of those two tables.

So, I know that to make the soup I need 2lbs of chicken and 8oz of noodles. This is recorded in the [Recipe Ingredients] table like this:

• {Recipe} || {Quantity} || {Unit} || {Ingredient}
• Chicken Noodle Soup || 2.000 || lb || chicken breast
• Chicken Noodle Soup || 8.000 || oz || egg noodles
• etc.

Basically, every time an ingredient is used in a recipe, it gets its own record in the [Recipe Ingredients] table describing how much is needed. Therefore, each [Recipe Ingredient] record is only tied to one [Recipe] record and only one [Ingredient] record each.

Because my base is also a meal planner, I have another very simple table which asks for a date and a recipe so I can plan meals out ahead of time. Now that I have dates associated with recipes, I can use a formula to filter down to all the relevant [Recipe Ingredients] records which will be needed that week.

The [Ingredients] table, with the help of another formula, is then able to say: Egg noodles are needed this week, add up all the [Recipe Ingredient] records containing egg noodles if I selected that recipe for this week.