ArrayUnique & ArrayFlatten not working

[NoreG]

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: 

1. Create a rollup field that rolls up the multi-select field. Set the formula to values. Save the rollup field.
2. Convert the rollup field to a lookup field. Notice that the lookup will be formatted as plain text instead of the usual pill format.
3. 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. 

[NoreG]

Hi Scott 

I know those are linked fields. What you are suggesting is the first thing I tried, before even consulting the community 🙂 . But when my duplicates persisted (see screenshot below), I tried looking for solutions and found similar issues in the thread that I referenced in my original post, hence why you may thought that I was having issues with a multiselect field. I indeed did not clarify that I was dealing with linked fields.

Anyways, in the screenshot below I re-tried what you suggested, but as you can see, duplicates persist.

I have also tried combinations with ArrayFlatten, but no luck.

I hope I understood you correctly and this is what you were suggesting. Thank you for bearing with me and your responses so far.

[ScottWorld]

Right, I thought you were using multi-select fields because that's what you said in your original post. Maybe you were just quoting that from another thread.

However, your setup with that rollup field looks perfectly fine to me, and that is exactly how I have it setup on my end which is working properly.

So I have no idea why yours is not working properly while mine is working properly.

You may want to open up a support ticket with support@airtable.com to have them take a look at this, and write back here with what they tell you!

[ScottWorld]

You can see my 3 screenshots below. (The lookup field is just there to show the duplicates, but it's not required.)

[ScottWorld]

You can see from my 3 screenshots above that it's working just fine on my end, so I'm not sure why it's not working for you. I would definitely send an email to support@airtable.com for them to look into this for you! Please post back with whatever they say!

[NoreG]

I think your example is working because the array is [['Garnet Sizemore'],['Garnet Sizemore'],['Garnet Sizemore']] ----> so ARRAYUNIQUE returns [['Garnet Sizemore']].

In my case the array is [['dummy 1', 'dummy 2'], ['dummy 1', 'dummy 2', 'dummy 3']] ----> so ARRAYUNIQUE recognizes it as two different "elements".... 

This is why I originally also tried ARRAYUNIQUE(ARRAYFLATTEN(...)), to remove the nesting, but that also did not work...

I am sorry for not clarifying earlier that I was dealing with linked fields, it was indeed not clear from my original post. 

I will contact the Airtable support and update this thread when they get back to me. Thank you for your patience and guidance!

[ScottWorld · Accepted solution]

@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

[NoreG]

Oh wow! That is indeed quite the workaround, but it does work! 😄

Thank you so much for your replies & bearing with me!!!

[ScottWorld]

You’re welcome! And Airtable should really fix this bug, so you may want to report it to support@airtable.com.