Re: Arrayunique - can't understand why it not working
Hello!
Please help me to get unique values in my table!
I am trying to organize some data:
Have 3 tables: Supplier, Brand, Groups
And want to see unique values in group field on Supplier table.
I trying to get it from linked Brands table, but cant figure how to remove duplicate Groups.
Thank you!
Sounds pretty obvious and i have a thought that that should work that way, but gain full understanding only after making copy of your sample database and looking into Kit properties :slightly_smiling_face:
Appreciate your help, i love this airtables ❤️
I’m seeing similar behavior with the ARRAYUNIQUE function in a number of instances (using rollups on multi-select fields, using formulas on lookup fields that lookup multi-select fields from linked records, and even just applying an ARRAYUNIQUE formula on a text field).
A few examples:
Example data set
Inability to use ARRAYUNIQUE on multiple fields
My expectation here would be that this field would display “1, 2, 3, 4, 5, 6, 7, 8”, but it only displays the values from the first field.
ARRAYUNIQUE just not working on comma separated values
My expectation here would be that this field would display “1, 2, 5”, but it displays “2” twice.
The problem is multifold:
-
Your
{Test1},{Test2}, etc., aren’t arrays: They are text strings, as the field is defined as a single-line text. Accordingly, the formulaARRAYUNIQUE({Test1},{Test2},{Test3})doesn’t act upon an array of arrays, it acts upon an array of one element, that element having the value of{Test1}. That’s whyARRAYUNIQUE(),ARRAYCOMPACT(),ARRAYFLATTEN(), andARRAYJOIN()all return the same result forARRAY[whatever]({Test1},{Test2},{Test3}). -
AFAIK, there is no way to cast, well, any data type in Airtable to an array; your ‘1, 2, 3, 4’ will always be a text string.
-
The
ARRAY*()functions are intended to be used only against a rollup field — which means if you can rejigger your table to include the values in{Test1}, etc., as a value rolled up from a linked record, you can runARRAYUNIQUE(values)against them. Unfortunately, as they will still be text strings, your result will be ‘1, 2, 3, 4, 1, 2, 2, 5, 6, 7, 8,’ as each string is unique. That was the situation addressed in the sample base given earlier. -
The only meaningful exception I know of to Rule #3 is when you run
ARRAYUNIQUE()against a lookup field retrieved from multiple linked fields. (Lookup fields are always returned as arrays — even if an array of one item — which is why you sometimes have to cast them as text strings in order to use them in functions.) However, all the other caveats still hold, so you probably won’t get the results you want.The following is a screenshot from my ‘scratch’ base — the principles illustrated don’t really merit an actual demo base. This table contains two records linked to records from another table; the first column of this base shows the record indexes and values from the other table. You can see the values of the rollup, lookup, and function fields, respectively, on the right.
Airtable’s lack of accessible array functions has long been a weakness. For now, your only workaround is to restructure your base to get each individual value you want to boil out of ARRAYUNIQUE() as an individual linked value…
Thanks, this is a good explanation of how AT handles arrays. My example also was also a quick and crude representation and didn’t really represent what I actually trying to do, which was to summarize only unique values from multiple linked record finds.
Here’s perhaps a better example from my fake sandwich shop example base using linked records, which is trying to summarize only the unique linked records from 3 separate linked records fields. When I try to apply the ARRAYUNIQUE function against all 3, it only applies the function against the first referenced field (ideally the formula could create a new array from the combined values and then filter out the dupes).
Another example from the same base:
