ArrayUnique & ArrayFlatten not working

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

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.

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.

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!

Strange. It works for me. See screenshot below.

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!

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

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.

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.

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.

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!

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

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!

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!

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

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

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