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]

Thanks so much, @Justin_Barrett! :slightly_smiling_face:

[ScottWorld]

@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”

[Justin_Barrett]

ScottWorld:

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?

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

[ScottWorld]

Justin_Barrett:

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!

[Justin_Barrett]

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

[ScottWorld]

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.

[Leslie_Engel]

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

[Justin_Barrett]

Leslie_Engel:

As you can see, I have 4 lookup fields for the ingredients in my recipes.

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.

[ScottWorld]

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

[Leslie_Engel]

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.