Creating formulas with linked fields?

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!

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! :slight_smile:

1 Like

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).

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! :slight_smile:

1 Like

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!

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! :slight_smile:

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.

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.

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).

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.

1 Like

Thanks so much, @Justin_Barrett! :slight_smile:

@Justin_Barrett, I think my one unresolved question is this — and the answer to this could help me guide @Leslie_Engel further:

If I have 2 lookup fields in the same table that are each returning their own arrays, is there a way to combine those 2 lookup fields together into an array that we can then apply the ARRAYUNIQUE function on?

Both of those lookup fields might have some overlapping values between the 2 fields, so we are essentially trying to list ONLY the unique items, once you combine the values of both of those lookup fields together.

  • So if Lookup Field A has: “Gluten, Soy, Peanuts”
  • And if Lookup Field B has: “Gluten, Peanuts, Corn”
  • We would want the final “combined unique” list to be: “Gluten, Soy, Peanuts, Corn”

That’s where the ARRAYJOIN() function comes in handy. Join the two arrays, then find the unique options in the single array that’s returned from the join.

ARRAYUNIQUE(ARRAYJOIN({Lookup Field A}, {Lookup Field B}))

That’s exactly what I tried as my formula yesterday, but it doesn’t work. When I use that formula, it only returns the values of {Lookup Field A}. It seems like it should work, so I don’t know why it doesn’t!

@ScottWorld I’m an idiot. ARRAYJOIN() does something completely different. Not sure where my head was at, and it’s not even after 10pm! :laughing: I was thinking of ARRAYFLATTEN(), but that doesn’t work either. (I just remembered this: I had a side chat with Airtable support not long ago, and was told that ARRAYFLATTEN() pretty much only works when doing a lookup of a lookup. The example shown in the formula field reference is misleading.)

With those options out of the running, there’s not much left.

  1. Some code could be written in the Scripting block to find the unique options and stick them into another field.
  2. Zapier or Integromat may be able to do the same on an automatic schedule, removing the need to manually run a script.

Neither is ideal, but I don’t know where else to go…except to the #show-and-tell:product-suggestions category to put in another vote for more robust array functions.

Thanks so much, @Justin_Barrett. This is so disheartening! :frowning: I think they need to pay more attention to their ARRAY formulas. I’m going to post in the Product Suggestions category.

Hi @ScottWorld & @Justin_Barrett

Thanks to both of you for taking this on! This may be a bit late, as I see there was a lot of back and forth yesterday, but attached is a screen shot of how my recipe base is set up.

As you can see, I have 4 lookup fields for the ingredients in my recipes. This is how I need to organize my base for other business functions. I had to create the additional “ingredients” field in order to be able to get to the arrayunique function, but now I’m in the situation of having to input this info twice, which isn’t great. Based on what I’m reading here, it sounds like this is the only solution at this point. I’m fairly new to Airtable so I’m still figuring out the best ways to set things up. Appreciate all your help here!

AT screenshot 2|700x205

Are you referring to the four SKU fields? Those are link fields, not lookups. Your {Ingredients} field is also a link field.

Unfortunately linking to all relevant records in that {Ingredients} field is the only way that ARRAYUNIQUE() is going to work to get a non-dupicated list of allergens.

My next thought is how to automate those links so you don’t have to make them all manually. This could be done via a custom script in the Scripting block, or automation via Zapier or Integromat. With the Scripting block option, you would have to run the script manually every so often to copy new links over. Using Zapier or Integromat, the process would be hands-off, but wouldn’t be instant. It would run on a schedule depending partly on your account type on the platform you choose.

Yes, Integromat has the distinct function for removing duplicates from Arrays, so that might be an excellent option.

Oops, yes, I meant linked fields (that was written pre-caffeine this morning!).

I like your thought on automating links so they don’t need to be done manually. That would be the ideal scenario. I’m running up against my own lack of expertise on that front though-- I have zero experience writing scripts and I have never used Zapier or Integromat.