Going mad trying to avoid duplicates with ARRAYUNIQUE

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!

Thank you SO much! It would have taken me many hours more fiddling to even think of creating a “sorting out the data” field in the SCENES table, I was trying endless combinations of formula fields in the shoot dates table with no luck.

Really appreciate your taking the time to make the demo table too.

Have a great day!

You didn’t think I created that demo base just for you, did you? :winking_face: You don’t want to know how many blind alleys I turned into before I finally found the right one. It took me less time to find the answer than I expected – but still a lot longer than I would have liked.

I think there may still be something here that needs fixing I forgot to mention in my initial response. Even if ARRAYUNIQUE() is technically correct in finding [Drone,Crane] and [Drone] to be two unique entries, I would have thought ARRAYFLATTEN() would render a combination of the two as [Drone, Crane, Drone], which could then be de-duplicated with a call to ARRAYUNIQUE(). As I recall, though, ARRAYFLATTEN() seemingly had no effect. I should poke at that again and see if it needs to be written up.

====================

edit:

Oops, nevermind: I already looked into this, and Drone,Crane has already been cast into a string by the time we get access to it.

Yeah I was trying all sorts of nested ARRAYUNIQUE(ARRAYFLATTEN(values)) etc etc to no effect. There’s a lot that’s rather elegant about Airtable but occasionally around the edges a little fraying is apparent - this is perhaps one of those.

Somewhat related - would be lovely if they added a supported “formula” call that would alphabetise the array…

Oh, there’s a whole raft of array manipulations I wish it could do. A few weeks ago, I was asked to contribute something to Airtable Universe, so I threw together a ‘dashboard’ module for the standard Airtable Sales CRM base. (It’s more a demonstration of how to build and display various types of data visualization ‘meters’ under Airtable than a viable base, but it does a couple of sneaky things. I’m still pleased with it.)

One of the meters involves a ‘Won/Lost’ pie chart showing, in real time, current performance metrics. In reality, there are 21 pie charts, depicting win:loss percentage ratios from 0:100% to 100%:0 in 5% increments, stored in a dedicated table; as the win percentage changes, the appropriate graphic is loaded into the cover image field. After thrashing endlessly in search of an all-Airtable solution, I finally threw in the towel and built the image synchronization function as a Zap. If there was a way to address individual array elements, though, it could be much more easily and elegantly built purely in Airtable.

In fact, given a more robust set of array-handling routines, there’d be no reason for me to hang out on this board any longer. Probably 80% of the suggested solutions I post are nothing more than quirky attempts to work around Airtable’s limited support of arrays.

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!

looking at your demo base. How does one find the duplicated record? Everything is lit up with an X in the dup column? Am I missing something? Thanks in advance.

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!

@Shawn_Carrie did you find the answer to this? I’m also grappling with this issue for multiselect fields…

A-ha! Found it!

I’m guessing you have things organized something along the lines of my Filmshoot Demo base, where {Name} is the primary field in the Kit table, and you’re performing a Rollup from Dates to Scenes aggregating on the Scenes->Kit linked record.

If you look closely at your failing ARRAYUNIQUE call, you’ll notice the response isn’t

Drone, Drone, Crane

but actually is

Drone, Drone,Crane

That is, you’ve rolled up an array with two unique values, Drone and Drone,Crane. Presumably this is a side-effect of the process Airtable goes through in transmogrifying internal record IDs, first, to display as the primary field of the linked records and, second, to allow actions taken against an array of linked records to be expressed as actions against an array of corresponding primary fields. As a result, each Scene’s linked record field is treated not as an array of primary fields but as a single-element array containing an ARRAYJOIN()ed list of primary fields.

(And, yes, I realize, as a description, the previous paragraph is only slightly better than if I’d simply pressed keys at random.)

The solution is to add a field to Scene that performs a lookup on the linked record and returns {Names} from Kit. Then, change your Dates rollup to aggregate values from the lookup field instead of the linked record itself, and ARRAYUNIQUE() will work as you intended.

You can see this working in the demo base. In the Dates table, the KitNeeded_v1 field contains your original formula and demonstrates apparently non-unique values. KitNeeded_V2 reflects the modified formula and gives the desired response.

(Incidentally, the Dates entry for 09/20/2017 shows ARRAYUNIQUE() is working correctly, even using your formula.)

An interesting problem – thanks for asking about it!