How to join multiple rollups into one big array with only unique items?

[Kim_Trager1]

I'm using my airtable to attached keywords images. 

I have one big keywords table with multiple categories of different keywords. Then i have multiple linked columns to the different categories. However some of the keywords overlap into different categories.

So I thought I could rollup all my linked columns and do something like this:

ARRAYUNIQUE(ARRAYJOIN(ARRAYJOIN({RollUp1}) & IF({RollUp2}, ',' & ARRAYJOIN({RollUp2})) & IF({RollUp3}, ',' & ARRAYJOIN({RollUp3}))))

However I still have duplicates of keywords in my final array. Does anyone know how I can join my rollups and then filter out all the duplicates?

[TheTimeSavingCo]

As a last resort, you could try copying the value from that formula field that has duplicates in the final array and pasting it into a linked field to another table?  That'll give you a list of unique items at least

Hopefully someone else knows how to solve your actual quandary so that you don't have to do this weird thing though!

[ScottWorld]

@Kim_Trager1 

I'm not sure if this thread will help or not, but maybe it will:

https://community.airtable.com/t/removing-duplicates-from-a-string/35677/9

[Kim_Trager1]

That would be a last resort, however not ideal

[Kim_Trager1]

Hi @ScottWorld I've tried pretty much every version of ARRAYUNIQUE(ARRAYFLATTEN(ARRAYJOIN(Values)))

I've also tried to change my rollups to lookups but still the same. 

I have the feeling the problem, could be how I try to join the two column together into one array. What would be the best way of doing this?

[ScottWorld]

@Kim_Trager1 

I’m not really sure of the solution to your problem within Airtable itself, but some of the Airtable consultants who are hanging out at tableforums.com might have some answers for you. You might try posting there. 

One thing that I do know, however, is how to use Make to work with complicated arrays from Airtable. Make has a whole bunch of array tools to work with.

I give a brief demonstration/introduction to working with Airtable arrays using Make in this video: 

https://m.youtube.com/watch?list=PLqssva4liHRwHhQIpTXekG8WObEoyC2F1&v=sKPyG0HZCYY

[Kim_Trager1]

Thanks @ScottWorld - I'll try tableforums and see if there is anyone there. Ideally I'll find an inside Airtable solution, if not I'll move onto make.

[kuovonne · Accepted solution]

There is no practical way to do this with formula fields. When you use ARRAYJOIN() or the & operator, the end result is a text string, and not an array. Thus ARRAYUNIQUE() no longer sees the individual items that were in the original array; it only sees the single text string. ARRAYUNIQUE() also only acts upon the first parameter, so including different arrays as different parameters doesn’t work either.

[kuovonne]

You could have an automation that runs whenever any of the linked record fields are updated, and then have a scripting automation that determines the unique keywords and puts them in an editable text field.

[Kim_Trager1]

Thanks for @kuovonne  for confirming this. I'll go with @ScottWorld suggestion and do something with Make