Creating formulas with linked fields?

[Leslie_Engel]

Hello AT community!

I have a question regarding creating formulas.

In my base I have 2 tables: a recipe table with each ingredient in its own field, linked to my ingredient table. In the ingredients table I have several single select fields for each allergen that a particular ingredient contains.

So let’s say I want to summarize the allergens in my recipe for lemon meringue pie.
I’m using a frozen pie crust that contains dairy and wheat, allergens which are indicated as “yes” in their respective fields in my ingredient table, plus eggs (another allergen). Back in the recipe table, I would like to summarize all the allergens present in my recipe for lemon meringue pie, so I could easily look at the recipe and see that it contains eggs, dairy and wheat without having to go back to the ingredient table and look at each ingredient individually.

Is it possible to create a formula to summarize all the allergens present in my recipe, within the recipe table? I’m an AT newbie so not entirely sure how to go about doing this.

Thanks!

[ScottWorld · Accepted solution]

Hi @Leslie_Engel,

It sounds like you’ll want to change the structure of your database.

Currently, you’re using a bunch of extra fields in places where you would only want ONE field:

1. In your Recipe table, instead of having multiple fields for your “Ingredients”, you’ll want to just create ONE FIELD called “Ingredients” and make it a “linked record” field that links to your Ingredients table. Add all of your ingredients into this ONE field.

2. In your Ingredients table, instead of having multiple different fields for allergens, you’ll just want ONE FIELD for allergens, and you’ll make it a “multiple-select field” instead of a “single-select field”.

3. Then, back in your Recipe table, you can create a “lookup field” for “allergens”. This field will show you all the allergens for all of the linked ingredients for each recipe.

4. However, you probably won’t be happy with that lookup field, because you will probably have a bunch of duplicate allergens listed there. So that’s where you’ll want to create another field which is a formula field. Your formula field will have this formula:
   ARRAYUNIQUE({Allergens Lookup})
   You will replace the words “Allergens Lookup” with whatever the name of your allergens lookup field is. That formula will show you only the unique allergens for each recipe.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. If not, please let me know what other questions you have! :slightly_smiling_face:

[Leslie_Engel]

This is super helpful, thank you!

There is one hitch: I need to keep the ingredients in my recipe table in separate fields (long story).
So I have a follow up question- Is there an easy way to concatenate my ingredients into one field (keeping them linked back to the ingredients table), and then proceed as you describe through steps 2-4? As a test, I tried typing all my ingredients into one field, and your instructions worked beautifully, just trying to find a less manual way of making this happen (and having to enter ingredients in twice).

[ScottWorld]

That doesn’t sound like a great way to setup your database. But if you do want to set it up that way, I’m not sure how to manage the arrays to get your unique values. Perhaps someone else can chime in and help with this.

But up until you get to the array part:

Inside your recipes table, you would need to make each ingredient field another linked record field that links to your ingredients table. Then, you would need to make a separate allergen lookup field for each separate ingredients field.

Then, you would have to somehow concatenate all the allergen fields together & turn them into an array that the ARRAYUNIQUE function could tackle.

@Justin_Barrett and @Jeremy_Oglesby are 2 of our resident Array experts — they might have some insight into this! :slightly_smiling_face:

[Leslie_Engel]

Yeah, I was afraid of that. For a variety of reasons I have to keep the recipe table that way. My work around thus far has been to download a CSV of my recipes, and do a text join of the ingredient fields, then paste that back into a separate “ingredients” field in my recipe table, which is…not ideal by any stretch!

[ScottWorld]

Would you like to share why you are required to keep your ingredients in separate fields?

However, if someone with more advanced ARRAY experience than me helps you out on the ARRAYUNIQUE situation, it’s possible that you could structure your database this way! :slightly_smiling_face:

[Leslie_Engel]

Sure! I work for a food company, and we have to keep our recipe ingredients separate to facilitate some other functions that I have less control over.

[ScottWorld]

The original solution that I outlined still keeps your recipe ingredients separate. They’re all separate ingredients in the ingredients table. They’re just linked for a particular recipe. And if you need further separation, you could create a join table that sits in between ingredients and recipes.

[Leslie_Engel]

Sorry, I wasn’t clear in my last note! I need to keep the ingredients within the recipe table in separate fields (can’t have them all joined in one field).

[Justin_Barrett]

Just saw the tag from @ScottWorld, and I’m happy to offer whatever help I can. However, there’s been a lot of back and forth up to this point, and I’m not sure that I have a clear picture of how the base looks currently. @Leslie_Engel would you be able to post some screenshots of the current state of things? If you need to mask out sensitive data, feel free to do so.