Help

Handling varying ingredient quantities in recipe database (without junction...?)

8618 25
cancel
Showing results for 
Search instead for 
Did you mean: 
JJxyz
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m working on a recipe production database. I will have a table for all available ingredients, and another table with recipes that uses ingredients. When writing a recipe, i have a linked field where the author can pick ingredients in the recipe table.

Problem is of course specifiying the amounts of ingredients in the recipe. With this method, I can just select what ingredients a recipe contains, not what amount that exact recipe contains of each ingredients.

I was looking for some tips on this in the airtable “recipe organiser”-template.
Here there is a recipe QTY field in the ingredients table – however, in the samples each ingredient is only linked to one recipe. Seems a bit superficial? In my base, same ingredients will be linked to many recipes. Probably the case in most recipe databases. The Recipe QTY field, of course varies between different recipes so the ingreedient cant be re-used ebtween recipes with different amount of say potatoes or cucumber.

Is there any way of doing this, to specify amounts of a specific ingredient from a linked table, without a junction table? or without having separate “ingredient” entries in the ingredient table with same name but different Recipe QTY like in the template? Trying to figure out the most straigthforward way for the author to specify ingredients and amounts at once, without the database getting too cluttered.

If a junction table is a must - is there a form in Airtable that could fill out many tables at once (i.e. add stuff to recipe and recipe-ingredients junctions table), in one view?

The exact amounts will be needed in order to write out the recipe accurately directly from airtable and to calculate prices, on which data is found in the ingredients table. Was thinking of linking different QTY entries between each other WITHIN the same table, unsure how valid a solution this would be.

Would like to hear any solutions or discussion on this!

25 Replies 25

The short answer is no, you’ll need a junction table if you want to store quantities in any useful matter.

Here’s my personal recipe base, which includes a junction table: https://airtable.com/shrfE9hHqlLbOM4CV/tblZGGje5LFftOppz/viwNXc0tlElIWPxlm?blocks=hide

I’m using a combination of Integromat and CognitoForms to input ingredients for recipes since I can add more than one ingredient at a time this way.

My input form is here: https://www.cognitoforms.com/None1841/RecipeIngredients

My base also includes an unessential but nice to have formula that converts decimals to fractions for “prettier” quantity displays(i.e. “0.125” = “⅛”)

Hi, that seems like a neat way of solving this exact same issue.

So you have cognitoforms fill in ingredients and quantities, and make integromat take this data to the junction table, and somehow relate hte ingredients to correct recipes and ingredients found in your airtable?

Yes. My form has a field at the top where I type in the recipe name. If the recipe exists, Integromat adds it to the recipe. If not, Integromat creates a new recipe.

My Airtable includes a formula field in the ´[Recipes]´ table which calculates a link to the form with the recipe name pre-filled in so i dont have to worry about typos.

If i wanted I could have added the other fields related to recipes (step instructions, cook time, source, etc) to the Cognito form. I didn’t, but its not hard to do if you want to add recipes in one fell swoop.

Here’s what my Integromat scenario looks like: Screen Shot 2019-03-15 at 7.21.40 AM.jpg

Hey, thanks for sharing, seems like a smart solution! Did solve this with a recipe-ingredients junction table, and filling in amounts is doable but a bit tedious within airtable but a form solution is probably leaner for a recipe author.

Hi JJxyz, David here from Sydney Australia. Might it be possible to share your recipe system? I can’t seem to get my head around junction tables and I’m having the exact issue you initially describe.

Kind regards, David

Hi Kamille, your solution looks so clever! When I copy the base and try to add an item - it doesnt add it via the cognito form… what do I have to do to get them connected?

Kind regards David (from Sydney Australia)

The cognito form is linked to my base. You would have to make your own form and connect it to your copy of my base using Integromat as shown above.

Hi Kamille, firstly thanks for your reply! I created a Cognito Form account and replicated your form (wow what an amazing tool). I connected the form to airtable using my zapier account. I submitted a form but it didnt send the ingredient information… in Zapier when I tried to connect it … it said something like ‘can’t send to a computed/formula field’ (is there something obvious I can fix)… should I try using integromat instead? This is amazing I never thought I would get this far… its almost working

You can’t update computed fields through Integromat or Zapier. When mapping fields to update using either service, leave computed fields alone and just worry about the editable fields.

Again thanks for the prompt response… when I don’t try to connect one of the ingredients field it doesn’t send any ingredients to airtable. Could I perhaps bother you to send me a screenshot of your integromat field ‘mapping’?
Also, are you available to be retained for paid work?
Regards, David

The full explanation of the Integromat scenario:

  1. Cognito Forms Module: Get new Cognito Form submissions using a webhook.

  2. Iterator Module: Loop through each {Recipe Steps} item:
    image

  3. Tools/Compose a String Module: Format each {Recipe Steps} item:
    image

4: Tools/Aggregation Module: Combine the reformatted recipe steps, adding an empty paragraph between each one for spacing:
image

  1. Airtable/Create a Record Module: Map each Cognito Form field to the relevant Airtable field in the [Recpies] table. Do not map the {Ingredients} or {Recipe Ingredients} fields. Map the {Recipe Steps} field using the output from the Tools/Aggregation Module (Step 4):
    image

  2. Iterator Module: Loop through each {Recipe Ingredients} item:
    image

  3. Airtable/Create a Record Module: Create a new record in the [Recipe Ingredients] table for each item Iterator Module (Step 6). Map the {Recipe} Airtable field to the {ID} from the Airtable/Create a Record Module in Step 5. Map the other fields to the outputs from the Iterator Module (Step 6).
    image

  4. Resume Module: For error handling, resolve the Recipe Ingredient output as follows:
    image

The terminology/process may vary for Zapier.

I’m a hobbyist. All help I provide is free of charge.

Email_David
4 - Data Explorer
4 - Data Explorer

OMG - that’s amazing. Thanks again.

Email_David
4 - Data Explorer
4 - Data Explorer

Hi Kamille,

Firstly thanks again for all your help.

Now i have built the integromat structure (trying to copy and build everything the same) - see screenshot below.

In the example of Egg On toast in the screenshot, I can get it to add the recipe and other information, but not the ingredients?

One issue is ‘Step 3’ ‘Tools Compost a string’ I can’t find the Instruction field to add it next to Item Number.

The other issue is I am not sure how to setup the ‘Resume’ step (if I might be able to bother you to send me a screenshot of those settings?

Alternately, maybe I could give you login details for you to look at??

Kind regards,

David

There is no screenshot.

Is Integromat correctly creating the [Recipe Ingredients] records, but not associating them with the newly created [Recipe] record? Or is it not creating [Recipe Ingredients] records at all? Remember, the {Ingredients} field you see in the [Recipes] table is a Rollup field and cannot be directly edited. Ignore that field, and focus on the {Recipe Ingredients} field; if this one works, the {Ingredients} will work.

Follow instructions for Step 4. When using the Tools/Aggregation Module you have the option of adding the output from any of the previous modules. Select the output from the Tools/Compose a String Module. If you’re following my example exactly, add two empty lines (click the A at the top of the popup, “newline” is one of the options.

See the edits to my original step walk-through.

Email_David
4 - Data Explorer
4 - Data Explorer

Here is the Log output I get on 8 (I’ve been changing this trying to get it working). Significantly in your step 8 you map Recipe to ID (blue) … when i try this is says I cant (see below)

  • Operation 1

INPUT

  • Bundle 1Collection

    • Base

appvqaCMTD5P2hL5c

* Table

Recipe Ingredients

* Record<small>Collection</small>

* Smart links

false

HANDLED ERROR

  • type

RuntimeError

  • message

Field “Recipe ID” cannot accept a value because the field is computed

OUTPUT

  • Bundle 1Collection

    • ID

rec1naDIgnlFE8KpT

* Unit

Unit

* Recipe<small>Array</small>

* Quantity

1

* Meal Week<small>Array</small>

* Qualifier<small>Array</small>

* Recipe ID<small>Array</small>

* Identifier

empty

* Ingredient<small>Array</small>

* QuantitySub

empty

* Created time

21 January 2020 9:42 AM

* Display Name

empty

* Unit this Week

empty

* Meal Multiplier

empty

* Quantity this Week

empty

  • Data size: 275.0 B

Operation 2

INPUT

  • Bundle 1Collection

    • Base

appvqaCMTD5P2hL5c

* Table

Recipe Ingredients

* Record<small>Collection</small>

* Smart links

false

HANDLED ERROR

  • type

RuntimeError

  • message

Field “Recipe ID” cannot accept a value because the field is computed

OUTPUT

  • Bundle 1Collection

    • ID

rec1naDIgnlFE8KpT

* Unit

Slice

* Recipe<small>Array</small>

* Quantity

1

* Meal Week<small>Array</small>

* Qualifier<small>Array</small>

* Recipe ID<small>Array</small>

* Identifier

empty

* Ingredient<small>Array</small>

* QuantitySub

empty

* Created time

21 January 2020 9:42 AM

* Display Name

empty

* Unit this Week

empty

* Meal Multiplier

empty

* Quantity this Week

empty

Exactly. None of my steps involve touching that field. The {Recipe} field is NOT the same as {Recipe ID}. Wherever it is you’ve typed a value into {Recipe ID}, remove it. In all my steps you’re dealing with the {Recipe} field. This includes Step 7 which maps the Airtable record ID retrieved from Step 5, you’re still mapping the {Recipe} field.

Email_David
4 - Data Explorer
4 - Data Explorer

Hi Kamille (please dont get frustrated with me - i think i have tried every combination to try and get it to send ingredients to Airtable without success)… screen shots in this link (it wont let me post images or links?) Any way I can send you a link or images directly. dg@ipe.com.au

What field should I be mapping to Recipe in step 8? I have tried everything and left it blank - no luck. Step 7 Iterator sees for example ‘peas’ yet peas in step 8 is obviously not a valid ID

Email_David
4 - Data Explorer
4 - Data Explorer

Hi Kamille,… well success!!!
I didn’t have ‘Smart Links’ ticked on the second last step. That was it - that’s all the whole time. can you believe it? I saw it when you did that great interview with BuiltOnAir. It has been such a steep learning curve on Integromat (but powerful - I’m not even sure how you could handle multiple step through ingredient creation on zapier? (I’m sure there is a way but…) Thank you so so so much for all your assistance!

Hey one thing… in terms of entering ingredients… shouldn’t it be a dropdown drawing info from say the ingredients table? Can cognito dynamically populate from the ingredients table in decent time? Or would you populate a second cognito form (table) as you enter info in the main cognito form… select from existing item or add new ingredient as you type. Similarly with Recipe names… (I know you are doing some clevers with this in terms of duplication)

Thanks so much again, David