Going mad trying to avoid duplicates with ARRAYUNIQUE

[Ed_Moore]

Hi all, please help put me out of my misery.

I have a database to organise film shoots.

I have three tables: scenes, dates and kit

Each entry in the scenes table links to an entry in the dates table (the date we’ll shoot that scene), and may or may not link to a number of items in the kit table, indicating whether that scene requires specialist kit (cranes, drones etc).

I am trying to make a field in the dates table that summarises what kit is required for that day.

When I make this a rollup pointed at the scenes table, kit field, it pulls through all the entries correctly but obviously as there can be many scenes scheduled for a particular day, each of which might require the same bits of kit, I get a lot of duplicates.

No matter what combination of ARRAYUNIQUE, ARRAYFLATTEN, ARRAYCOMPACT etc etc I try I can’t get the rollup to show only unique items.

To be clear what I’m after is that if a particular date has four scenes and two require a drone and one requires a crane, I want the rollup field to produce Drone, Crane.

What I’m currently getting is Drone, Drone, Crane.

I tried making all the kit entries with no spaces as read in the forums that made a difference but didn’t help here.

Any suggestions gratefully accepted!

[W_Vann_Hall]

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!

[Ed_Moore]

W_Vann_Hall:

Filmshoot Demo

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!

[W_Vann_Hall]

Ed_Moore:

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.

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.

[Ed_Moore]

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…

[W_Vann_Hall]

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.

[AlexWolfe]

Just want to say thank you for that Sales CRM dashboard you created which inspired to me learn to create multiple dashboard-like views of my own on multiple bases. Really cool work around!

[Shomrei_Emunah]

W_Vann_Hall:

demo base

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.

[W_Vann_Hall]

Ah… that took me a moment to figure out.

The Duplicate Detection Demo is intended as a real-time check on a base. It looks to see if the most recently entered record is a duplicate. That column of red Xs tells you that whatever you just finished entering duplicates an existing record. In your case, it appears you just entered record 999 for Rachel B Grant — a duplicate of record 998, Rachel A Grant, and record 108, Rachel M Grant.[1]

I’m currently not sure if that base is actually necessary. originally I created it as a lighter-weight variant on my De-Duplication Demo base, which was intended as a one-time process to be run against an existing base, after which the code would be deleted and replaced, if needed, with that from the Duplicate Detection Demo. However, a few months after my original post, I discovered the wonderful world of aggregation formulas, as opposed to aggregation functions, which allowed me to create a more sprightly version of the De-Duplication Demo. (For the 1,000-record demo base, the difference in data stored between versions 1.0 and 2.0 was the equivalent of four copies of War and Peace; with an 18,000-record base, you were looking at the equivalent of a moderately long novel being appended to each record.) Accordingly, today one can probably use the De-Duplication code safely even for real-time duplicate detection. (The link earlier in this paragraph is to version 2.0 of the base.)

Unfortunately, I have not yet updated the write-up I made available on Google Drive to reflect this latest revision; I’ll try to get that handled shortly.

[Ser]

Thanks for Filmshoot Demo
It helped me ))
The only problem is that both KitNeeded_v1 and KitNeeded_v2 fields values are not seen at android app.
Do you have any idea ?