Sep 17, 2017 05:57 AM
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!
Jan 15, 2019 11:43 AM
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…?
Mar 21, 2019 04:56 PM
Thank you so so much! This pointed me in the right direction!
Oct 18, 2019 08:15 AM
Thanks from me too! I would never have worked this out.
Jun 18, 2021 09:18 AM
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!
Aug 17, 2022 11:31 AM
How might this work for a Multiple Select field instead of a Linked Record field?
Sep 21, 2022 11:01 AM
@Shawn_Carrie did you find the answer to this? I’m also grappling with this issue for multiselect fields…
Sep 26, 2022 07:35 PM
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.
Mar 12, 2024 04:05 AM
Thank you so much!
That worked for me.