Multiple recipe / ingredient configuration

Jason_Calaiaro · ‎Jul 14, 2019

Hi All, I’m building out a comprehensive recipe / ingredient / shopping list tool which aims to be the place where I can convert 15 years of working culinary knowledge and interest. I’ll share this when I’m complete. I’ve read about junction tables, implemented one, and have looked at nearly all examples on Universe. Let me first apologize if I’m searching for something that’s been asked 100 times before in with different language. I’m just getting a handle of airtable (and quickly starting to love this product), but don’t yet intimately know the product / language.

Today I have three tables, “Recipes”, “Ingredient lists”, and “Ingredients”. “Ingredient lists” is a junction table which forms the many to many association between recipes and ingredients. Avocado toast and guacamole both share avocado. This part makes sense and works well.

Here’s where I’m getting stuck. Let’s say I have a recipe called “buttermilk dressing” and another recipe called, “little gems with radish, buttermilk dressing, and fresh dill.” Here, there is a nesting component to the latter. How do I deal with this?

What I’m trying to get to is a place where in the ingredients list table, all the ingredients for the recipe will show up, but I’d like to keep certain components separate, like the dressing, as they are useful as standalone items.

Can anyone point me in the right direction of things to try or places to read? Thank you!

AlliAlosa · ‎Jul 14, 2019

Hi there!

Sounds like you’re on the right track! If this were my base, I would include Buttermilk Dressing on both the Recipe table and the Ingredients table. You could then create a column for {Sub-Recipes} and use rollups across tables to get the ingredients needed for any item that might have it’s own set. You could apply the same logic to directions/steps for each item as well.

Take a look at this example base (click “Copy base” to create a copy and check out the formulas).

The field {List} on the [Recipes] table gives a list of ingredients by rolling up the records from the [Ingredient Lists] table (I assume this is how your base is currently set-up in some fashion :slightly_smiling_face: )

Here’s what I’ve added:

On the [Recipes] table, I’ve added a field, {Ingredients}, that links to the [Ingredients] table for any item that doubles as both a {Recipe} and {Ingredient}.
On the [Ingredients] table, I’ve added a field called {Sub-Ingredients} that rolls up the {List} from the [Recipes] table using the new field from above. For the purpose of this example, this gives us the ingredients needed for Buttermilk Dressing.
Next, on the [Ingredient Lists] table, I added another rollup field called {Sub-Lists} that pulls the above {Sub-Ingredients} field into the [Ingredient Lists] table.
Finally, I added two more fields on the [Recipes] table. One called {Sub-Recipes}, which rolls up {Sub-Lists} from the [Ingredient Lists] table; and another called {Master List} which joins the field {List} with {Sub-Recipes}, if there are any.

I realize this may seem a bit complicated. It’s definitely not easy to explain in one go. Happy to answer any questions you might have :slightly_smiling_face:

Good luck!

Jason_Calaiaro · ‎Jul 14, 2019

You are amazing! Thank you for the very quick, and thorough reply! And you made an example of this, over the weekend no less! How would I address a scaling factor with the sub recipe (buttermilk dressing)? If I have the dressing recipe for a yield of 16oz., but I only need 8oz, how would I have that represented in the master recipe for “little gems with buttermilk”? I could imagine having a “yield scaling” column in the recipe table, but I’m not sure how to do the .5 scaling of individual ingredients inside the buttermilk dressing, for example.

Here’s the base: https://airtable.com/shrACAGSyx1BC9oX5.

Update: I think the right place for the scaling factor is in the ingredients list in a new column. This would require the ingredient list to have columns for quantity and then a unit, so that you can perform math operations on them. It’s not yet clear to me how to do that.

J

AlliAlosa · ‎Jul 16, 2019

Hi there Jason,

So sorry for my delayed response. I have been super busy the past few days and don’t expect to have a ton of time until later in the week. However, I do believe you’re on the right track in putting the scaling factor on the ingredients list. What may be difficult is trying to convert between two different units, i.e. Cups -> Tablespoons.

Let me know if you’ve made any progress - happy to help further once my schedule is a bit more freed up! :slightly_smiling_face:

Good luck!

Jason_Calaiaro · ‎Jul 20, 2019

Thanks for the continued support. Here’s where I’m at now. See this base: https://airtable.com/shrACAGSyx1BC9oX5.

For each ingredient, I have a column for “allergens” and “derived from animal product” which is very useful in planning a menu. This is straight forward for an ingredient like milk. I’m running into a challenge when the sub-recipe’s I have here. The ultimate objective is to be able to have the recipe, which rolls up allergens and derived from fields for the entire recipe, to be able to include the allergens and derived from data for the sub-recipes as well. I haven’t figured out a way to do that that doesn’t involve creating a circular reference. In the base I linked, for example, the “little gem lettuce with buttermilk dressing” recipe should include lactose and derived from cow attributes of the buttermilk dressing. It does not, because I can’t figure out how to make sub recipes carry forward this info. Any help/guidance is appreciated.

AlliAlosa · ‎Jul 21, 2019

I made a copy of your base and added a few fields to each table. I was able to get all the allergens listed on the [Recipes] table under the field {Total Allergens}.

To avoid the circular reference, I joined the results from {Sub recipe allergens} and {Allergens} on the [Ingredients] table, and carried all of that over in new rollup fields from the [Ingredients] -> [Recipe Ingredient Lists] -> [Recipes]. I then repeated the same process for the {Derived from Animals} fields, and joined them together in the field {Total Allergens}.

I hope this is helpful!

Jason_Calaiaro · ‎Jul 21, 2019

Thank you so much! This is very helpful!

I have one last tweak that I’m trying to resolve. Here’s the base after incorporating edits: https://airtable.com/shrACAGSyx1BC9oX5.

I cannot get the summary rollup for “All Allergens”, “All Animals”, and “All categories” to remove duplicates through a function like ARRAYUNIQUE.

See example in base the little gems recipe. Expected behavior in “All allergens” is that Lactose only appears once. Incidentally, this was done correctly for “All derived from animals” as Cow and Goat only appear once. This seems as though that there is an entry in one of the records that is “Lactose, Tree Nuts” for the sub recipe, and then “Lactose” listed as an allergen on another ingredient, so when these are rolled up, they are not combined. I had thought that “ARRAYUNIQUE” function would do the work here. Is there another way to accomplish this? Conditional rollup with string search function?

AlliAlosa · ‎Jul 21, 2019

Ah, unfortunately after a lot of testing I’m not sure if this is going to be easy or even possible. My hunch is that because we’re joining two arrays together in the early stages of the process (the field {All Allergens} on the[Ingredients] table, the values inside are treated as a string and not as separate values in an array.

For example… on the [Ingredients] table, the value of {All Allergens} for <Sub recipe: Buttermilk dressing> is Tree nuts, Lactose, where as the value in the same field for <fresh dill> is Lactose.

When these two values are ultimately rolled up in the recipe <Little gems, buttermilk dressing, fresh dill>, we would hope to only get Tree nuts, Lactose as a result. But, we’re actually comparing Lactose to Tree nuts, Lactose which Airtable is seeing as unique values.

There may be a simple workaround that I’ve yet to find. I’ll keep digging and let you know if I come up with anything.

Also… this post doesn’t quite relate to this scenario, but has helped me a ton in wrapping my head around ARRAYUNIQUE().

Jason_Calaiaro · ‎Jul 21, 2019

I found that pointer, thanks for that!

I’ve updated the base per that strategy, but can’t seem to get it to work. New base here: https://airtable.com/shrVz2uSnINUwFWpc.

I’ve added another table, “Allergens” so that ingredients can be linked here. I’m then doing a name lookup going forward and trying to combine them this way. Still no luck. If you trace “Allergens” from the “Recipe” table, you see this as an example.

Jason_Calaiaro · ‎Jul 21, 2019

I’ve arrived at a good enough solution for now. Since I really just need to be able to filter for the presence of an allergen, I can just use the filter function manually and search insight the “All allergens” column.

culinarian's companion main - Airtable

Explore the "culinarian's companion main" base on Airtable.

It’s frustrating why joining two arrays doesn’t produce the desired result. If you’re curious to take a look, F(x) All Allergens is a union of Allergens and Sub-recipe allergens, both of which are arrays (to my knowledge). Is the formula for combining them incorrect in some way? No matter which combination of arrayunique() I use, I cannot get this to comply.