ArrayUnique & ArrayFlatten not working

[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!

[WBZ]

Hello Thank you very much for sharing such valuable knowledge, I am trying Demo2,

I am very new to Air table, and asking this question because of luck of knowledge…
In order to Link the the field which I want to detect duplication, such as (name) , (E-mail),

Do I need to select every single orders one by one,?
I am wondering if I can select all, and link them…
Please can anybody help me…?

[Mark_Schad]

Thank you so so much! This pointed me in the right direction!

[Mark_Dixon]

Thanks from me too! I would never have worked this out.

[Jennifer_Bird]

W_Vann_Hall:

ARRAYUNIQUE()

Thank you so much, I have trial-and-errored my way through a similar problem for far too long and didn’t even think to try this. Solved it in a snap. More than a little embarrassing. Happy Friday!

[Shawn_Carrie]

How might this work for a Multiple Select field instead of a Linked Record field?

[Matt_Gardner]

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

[Justin_Barrett]

Welcome to the community, @Matt_Gardner! :grinning_face_with_big_eyes: Unfortunately it’s not possible, at least not without delving into scripting. This is because a multiple-select field doesn’t pass the selected items as an array. They’re stored internally as an array, but that array is combined into a single string when requested by a formula, lookup, or rollup field.

One way to work around this is to convert the multiple-select field into a linked record field pointing to a new table, which will auto-convert the multiple-select items into individual records. You lose the coloring of the multiple-select field, but gain the ability to (among other things) use ARRAYUNIQUE() to eliminate duplicates.

[Venger]

Thank you so much!
That worked for me.