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. 

[ScottWorld]

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]

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]

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]

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]

Strange. It works for me. See screenshot below.

[NoreG]

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]

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]

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]

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.