ArrayUnique & ArrayFlatten not working

NoreG · ‎Nov 04, 2024

Hi everyone,

I am trying to get unique values from a lookup field. I have tried the solution suggested in: Solved: Exclude Duplicate/Repeating Values via Lookup or R... - Airtable Community . by @kuovonne. Which stated:

Create a rollup field that rolls up the multi-select field. Set the formula to values. Save the rollup field.
Convert the rollup field to a lookup field. Notice that the lookup will be formatted as plain text instead of the usual pill format.
Create a new formula field that refers to the lookup field (that used to be a rollup) and use the formula ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({lookup field})), ", ")

But this does not seem to work for me, as converting the rollup back to a lookup, changes the formatting back to pi lls.

In the screenshot provided you can see the formula I am using, as well as both the rollup and lookup field. I have tried different combinations of using ArrayFlatten and ArrayUnique across the rollup and formula but nothing seems to work...

Any help would be greatly appreciated.

ScottWorld · ‎Nov 04, 2024

@NoreG

Oh, you're right! Duh! 🤦

You are correct that my example is completely different than your example, because all of my values are single-value arrays.

As soon as I make my values multiple-value arrays, then I get the same incorrect results as you.

And yes, this is a very longstanding (and very annoying) bug in Airtable.

But here is the solution:

Your lookup field needs to be created in the LINKED TABLE, not the main table that you're currently working in (i.e. NOT the table where you want to see the results).

So take these steps to solve it:

1. Go to your LINKED TABLE. In my example, that would be the "Features" table. In your example, that would be the "fakes" table.

2. Find the LINKED RECORD FIELD in that table. in my example, that would be the "Link to Linked People" field. In your example, that would be the "Dummies" field.

3. Right-click on that field header and add a lookup field. Choose the primary field as your lookup field. What this results in is that you are going to see the EXACT SAME VALUES in BOTH your linked record field AND your lookup field.

4. Now finally, you can go back to your main table (i.e. the table where you want to see the results). You still need the linked record field that you've already got setup, but you just need to add one rollup field based on that linked record field. And the rollup field will point to your brand new lookup field instead, with the formula ARRAYUNIQUE(values)

And that's how you workaround this very annoying & very strange bug in Airtable! 😅

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

See Solution in Thread

ScottWorld · ‎Nov 04, 2024

This is a tricky one for multi-select fields.

Try these 3 steps, and see if it works:

1. Start with your linked record field.

2. Create a lookup field (based on that linked record field) that looks up your multi-select field. (Note that this will only work if your lookup field DOESN'T add colored bubbles to your values. If the lookup field DOES add colored bubbles to your values, then you'll need to entirely scrap this method altogether and use this hack instead.)

3. Create a formula field with this formula: ARRAYUNIQUE({Your Lookup Field Here})

See if that works!

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

NoreG · ‎Nov 04, 2024

Hi @ScottWorld

Thanks for your reply. Unfortunately that didn't work, the formula field returns the same data (with the duplicates) as in the screenshot provided initially.

Alexey_Gusev · ‎Nov 04, 2024

Hi,
based on your screenshot, it's not clear why your rollup field still exists and which field you are referring in your formula. For curiosity, I just tried solution you mentioned and it worked.

On my example screenshot, myfield - usual lookup

I just created rollup field myfield2, converted it to lookup (values didn't change format - still plain text) and used simple ARRAYUNIQUE formula against it - worked as written in solution.

I think, in your case, there is something wrong with step 2.

NoreG · ‎Nov 04, 2024

Hi,

I included both the rollup field and lookup field to illustrate two things:

1. I tried using both as input for the formula, but no success.

2. I am struggling to convert the rollup field to a lookup field.

I think 2 is indeed my issue, it might be a really straight-forward question but how do you convert the rollup to a lookup?

When I go to edit field in the rollup, and select the type lookup, Airtable forces me to reconfigure (select source...) the field, in essence just creating an original lookup (with the typical pi lls formatting).

Thank you so much for your reply!

ScottWorld · ‎Nov 04, 2024

Strange. It works for me. See screenshot below.

NoreG · ‎Nov 04, 2024

Hi Scott,

I’m having trouble converting a rollup field to a lookup field to remove the pill-style formatting. I think this formatting issue is also why the ARRAYUNIQUE() function isn’t working as expected. My lookup fields aren’t formatted as plain text, whereas I see your Multi-select field (from Features) is in plain text format.

Could you advise on how to properly convert rollups to lookups? As I explained in my reply to @Alexey_Gusev, I can't seem to figure that out. It is probably very straightforward but I am struggling sadly...

Thank you for your patience!

ScottWorld · ‎Nov 04, 2024

I’m not sure what you mean. You can just create a new lookup field, or you can right-click on your linked record field and choose “Add Lookup Fields”.

NoreG · ‎Nov 04, 2024

When I create a lookup field in the ways you suggest, the pill-style formatting is still present. This is, in my opinion, what is blocking the ARRAYUNIQUE() from working correctly. @Alexey_Gusev suggested creating a rollup first and then converting it to lookup to get the plain text formatting, instead of the pill-style. For some reason I am struggling to do this conversion.

On another note, I have recreated the data structure with fake entries, to show you better what my issue is:

As you can see, the calculation in the last screenshot does not return unique values.

ScottWorld · ‎Nov 04, 2024

Those are not multi-select fields. Those are linked record fields.

All of our advice so far has been for multi-select fields.

To get the unique values for those linked record fields, you just need one rollup field and use the ARRAYUNIQUE(values) formula to point at the linked record field in the linked table.