Flipping a table on it's head

Hello helpful community!

So I have a table. Let’s say it’s a table full of recipes.

Each recipe row has a multi-select field called ingredients.

So maybe there’s a recipe called Scrambled Eggs and in the multi-select field called ingredients there are three entries:


…I’d like to create a new table called ingredients where each row in the table is an ingredient and for each ingredient I want to be able to see every recipe that uses that ingredient.

So, for example, for the row called milk I’d see at least three recipes:

scrambled eggs
chocolate milk
milk shake

How do I do this?

I suspect my original setup is flawed and I should have started with one table called Recipes and another table called Ingredients and then figured out how to pull in all the ingredients I want for each recipe. But is there another way with the multi-select setup I already have?


I think you want this.

That’s what I was afraid of. I’m afraid of going down that Rabbit Hole and not coming out for days.

What about if I change my table structure so I have two tables:


How do I pull multiple ingredients into one field of the Recipe table? Can you source a multi-select from another table?

Actually, I just figured out the answer to my question directly above. It’s quite easy.

One table called Recipes.
A second table called Ingredients.

Create 3 entries into the Ingredients table called


Then create a column in the Recipe table called Ingredients. Make that “field type” a “Link to”. Link to the Ingredients table. And make sure to allow for linking to multiple records.

Now you can create a recipe called “Scrambled Eggs”. Tab over to the Ingredients column on this Recipes table and enter all your ingredients. If they’re found in the ingredients table it will pull them in. If they’re not found they’ll create a new entry in the Ingredients table.

This will require about 15 minutes of refactoring on my part but it’s clearly the right way to do things.