Help

Re: Arrayunique - can't understand why it not working

3721 3
cancel
Showing results for 
Search instead for 
Did you mean: 
11130
4 - Data Explorer
4 - Data Explorer

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.

img-4e23-359x798-25-02-03_32_07.png

7 Replies 7

As I only have limited visibility into your base, I’m not certain this is the answer, but I suspect your answer can be found in this earlier post. Essentially, [Group1, Group2] is different from [Group2, Group1] — so ARRAYUNIQUE() would return [Group1, Group2, Group2, Group1]. As I recall, there are workarounds available…

(Apologies for so telegraphic a reply, but I’m typing from a friend’s PC, using a horrible keyboard…)

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:

img-0633-503x905-25-02-09%3A13%3A36

Appreciate your help, i love this airtables ❤️

Glad to be of assistance!

David_Krizan
7 - App Architect
7 - App Architect

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
image.png

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

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.

image

image.png

The problem is multifold:

  1. Your {Test1}, {Test2}, etc., aren’t arrays: They are text strings, as the field is defined as a single-line text. Accordingly, the formula ARRAYUNIQUE({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 why ARRAYUNIQUE(), ARRAYCOMPACT(), ARRAYFLATTEN(), and ARRAYJOIN() all return the same result for ARRAY[whatever]({Test1},{Test2},{Test3}).

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

  3. 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 run ARRAYUNIQUE(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.

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

array_unique_again.png

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

image.png

image

Another example from the same base:
image.png

Your problem is the same: You’re taking a function designed to apply to an array of rolled up values and feeding it a string (and another string and another string).

For instance, {Breakfast Special Items} is a two-element array:

[ [Chicken Biscuit] [Bacon Egg Cheese Biscuit] ]

{Lunch Special Items} is a three-element array:

[ [Chicken Biscuit] [Chicken Club Sandwich] [Grilled Cheese] ]

Unfortunately, when you reference an array plus any other field, including another array in a formula, Airtable converts the array to a text string. So ARRAYUNIQUE({Breakfast Special Items}) works; if {Breakfast Special Items} was equal to the following array

[ [Chicken Biscuit] [Bacon Egg Cheese Biscuit] [Chicken Biscuit] ]

then ARRAYUNIQUE({Breakfast Special Items}) would return ‘Chicken Biscuit, Bacon Egg Cheese Biscuit’. (While ARRAYUNIQUE() returns an array, Airtable displays that array as a comma-separated string.)¹

This is because ARRAYUNIQUE({Breakfast Special Items}) is a formula in the syntax of

ARRAYUNIQUE([array type])

But when you try to stack arrays, as in

ARRAYUNIQUE({Breakfast Special Items},{Lunch Special Items},{Dinner Special Items})

you’re essentially defining a formula with this syntax:

ARRAYUNIQUE([string type],[string type],[string type])

which to Airtable is the same as

ARRAYUNIQUE([array type],[I don't care],[I don't care])

Airtable sees a single-element array where the element consists of a string assembled from the multiple-element array in {Breakfast Special Items}, plus two other variables it ignores. Since the only element in a single-element array is by definition unique, it returns it. (In your example you can see the response is assembled from the individual array elements in {Breakfast Special Items} because they are returned in LIFO order, the inverse of the way they are listed in the lookup field itself.)

If you can find some way to include breakfast, lunch, and dinner special items in a single rollup/lookup field, then ARRAYUNIQUE() will work as you want/expect. Otherwise, it merely works the way it was designed to work — which may or may not be the way one needs it to work. :winking_face:


  1. However, just because Airtable displays it as a comma-separated string doesn’t mean it actually is one; for instance, LEN(ARRAYUNIQUE({Breakfast Special Items})) returns #ERROR!. To address the results of an ARRAY*() function, you first need to cast it as a string; for instance, LEN(ARRAYUNIQUE({Breakfast Special Items})&'') returns 41.