Find duplicates in a rollup

[Kelsey-]

Hi there - I schedule shipments to be picked up on specific dates from specific sites at specific times, and I need a way to identify if I've scheduled any shipments to be picked up on the same date/site/time to make sure I'm not double booking a site. I've created a formula in my Shipments table that concatenates the three fields:

IF(

  AND({Load Date}, {Site Abbrev.}, {Load Time}),

  DATETIME_FORMAT({Load Date}, "MM/DD/YY") & "_" &

  {Site Abbrev.} & "_" &

  {Load Time},

  ""

)

And I've created a Rollup in my Sites table using ARRAYJOIN(values, ", "). All that seems to be working just fine. But the formula I've created to identify any duplicates in the Rollup field always returns No Duplicates even if there are duplicates. 

IF(

  LEN(ARRAYJOIN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}, ", ")) >

  LEN(ARRAYJOIN(ARRAYUNIQUE({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", ")),

  "Duplicates Found",

  "No Duplicates"

)

Here's an example of one of the rollup fields (load date_site abbreviation_load time) that contains a duplicate (11/25_AU_8 am) but is still returning No Duplicates in the formula field: 

11/19/24 _AU _10am, 11/25/24 _AU _8am, 11/25/24 _AU _8am, 11/12/24 _AU _11am, 11/25/24 _AU _10am, 11/12/24 _AU _10am, 12/03/24 _AU _10am, 11/14/24 _AU _10am

What am I doing wrong? I feel like I'm fighting for my life over here lol

Thanks,

Kelsey

[Ron_Daniel]

Kelsey,
I would normally test my answer and supply screenshots, but this one is a shot in the dark while I'm on the move. I think if you use ARRAYFLATTEN, then ARRAYUNIQUE will properly identify the duplicates.
Try something along the lines of
IF( LEN(ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", "))) < LEN(ARRAYJOIN(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", ")), "Duplicates Found", "No Duplicates" )

Let me know how it goes!

[Kelsey-]

No luck, still getting No Duplicates. ☹️ Thanks for giving it a shot. Any other ideas? 

[TheTimeSavingCo]

Hmm I experimented a bit and got this to work:

 

IF(
  LEN(ARRAYJOIN(values)) > LEN(ARRAYJOIN(ARRAYUNIQUE(values))),
  "Has duplicates",
  "No duplicates"
)

Link to base

Not sure how your data's set up though.  If the above didn't work could you provide some screenshots of your tables so I can try to replicate it? 

--
Edited, found a bug with the formula and fixed it!

[Kelsey-]

The problem is that I'm not using any linked fields. Here's the formula on the Shipments table that concatenates the date/site/time:

And here's the setup on the Warehouses table where I've got the Rollup from Shipments and the formula field to identify duplicates:

Thank you for your help! Feels like this should be easier. 

[TheTimeSavingCo]

Hm interesting.  Could you provide a screenshot of the field settings for the 'Dev' field?  If you could DM me an invite link to an empty copy of your base that'd be easiest!