Linking to multiple tables in one record

[Bon_Champion]

I’m working on a schema for a cocktail recipe app. I have ingredients split into three tables based on taxonomy: Broad Spirit Types (eg Gin), Narrow Spirit Types (eg London Dry Gin), and Bottles (eg Beefeater). In the wild, recipes can call for any type of those three ingredient types, and I’d like for my schema to accommodate that.

Right now, my workaround is for each recipe line item there are three separate columns (Broad, Narrow, Bottle). But that means I don’t have a single “ingredients” record that collects all the ingredients for each recipe (for example, a recipe that calls for gin, Campari, and sweet vermouth would have its ingredients in the Broad, Bottle, and Narrow ingredient columns, respectively).

So I’m looking for a way to have one ingredient column where I can choose from any of the three tables, OR a field that can essentially gather up the items from my 3 workaround columns and group them into one ingredient column.

And it’s important that the final ingredient column maintain the links to the tables. I imagine I could achieve a rudimentary version of this with formulas, but as far as I can tell, that’d convert them to text-only and destroy the links to the tables. Not ideal.

Any ideas?

Read only link: https://airtable.com/invite/l?inviteId=invozrMSzx6oWUoBG&inviteToken=30346466b8c4a1ce0adc5b62923f989...

[Elias_Gomez_Sai]

You need an auxiliary table, so you have:

• Ingredients
• Cocktails
• Recipes (or Ingredients used in Cocktails)

Check this: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relation...

[Bon_Champion]

I already have a similar set up, which I based on that many-to-many support article. My join table is called “Per-Recipe Line Items.” You can see it in the link I included.

The complexity I’m trying to solve for is in that I have 3 ingredient types. So that join table needs to accept ingredients from three different tables, and I’m hoping to consolidate those ingredients into one column at some point, so that each cocktail can have a list of its ingredients.

[Elias_Gomez_Sai]

Oh excuse me, I didn’t read well. Why do you don’t have all the ingredients in the same table? You could have a Type field to then do filters, counts, or whatever you want.

[pcf_fro]

Bon_champion,
As Elias said… Use ALL ingredients in one table. Diferentiate them with a only a field “type”… Do NOT use various tables for this… Type is an “attribute” not a Identity… In DB lingo…

[Arnau_Rovira]

Hi @Elias_Gomez_Sainz,

I found this conversation about multiple tables. I have a similar problem, so to follow-up on the issue raised by @Bon_Champion, how can I generate multiple joins? Following the cocktails analogy, if I have all the ingredients in 1 column (ingredients 1, 2, 3, 4, 5, 6), but I only want to see the ingredients that apply to the coctkails I’m interested, how can I do so?

As an example:
So if I select cocktail A I will like to see ingredients 1, 2, 3
but if I select cocktail B I will like to see ingredients 1, 4 and 6

[Elias_Gomez_Sai]

Airtable - Support

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...