Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 01, 2020 03:22 PM
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!
Solved! Go to Solution.
Jun 02, 2020 08:06 PM
Thanks so much, @Justin_Barrett! :slightly_smiling_face:
Jun 02, 2020 10:08 PM
@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.
Jun 03, 2020 11:01 AM
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}))
Jun 03, 2020 11:38 AM
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!
Jun 03, 2020 04:13 PM
@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.
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.
Jun 03, 2020 04:37 PM
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.
Jun 04, 2020 07:32 AM
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!
Jun 04, 2020 10:09 AM
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.
Jun 04, 2020 10:40 AM
Yes, Integromat has the distinct function for removing duplicates from Arrays, so that might be an excellent option.
Jun 04, 2020 10:59 AM
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.