Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 15, 2024 02:31 PM - edited Feb 15, 2024 02:31 PM
Is it possible to identify if there are any duplicates in a lookup array?
I have 3 tables, all linked together:
Every Meeting is linked to a month (for various uses) and to an Entity.
From the Entities table, I have data as such - the linked meetings and a lookup of their linked months.
What I'd like to do is identify if a month appears twice in any row.
I don't want to eliminate duplicates (i.e. ARRAYUNIQUE). Instead I need to see if 2 or more meetings occurred in the same month for any company.
Is there any way to do that?
Feb 16, 2024 12:52 AM
Hi,
in meetings, you have linked field to entities. add lookup to it, with {Entity name}. It will repeat linked field, but in text form, comma-separated.
Now in month table, create 2 rollup fields with just created lookup. One with ARRAYUNIQUE(values), other - ARRAYJOIN(values, ','). If their values are different, month used twice or more. The company name will be repeated in ARRAYJOIN field.
You can create 1 field instead of 2, with
ARRAYJOIN(values, ",")!=ARRAYUNIQUE(values)
"1" will identify duplicates, others will be "0"
Feb 16, 2024 09:16 AM
This is interesting. So if I'm understanding, this will compare the Length of both the ARRAYJOIN and ARRAYUNIQUE array and tell me if there's a difference.
Would I be able to tell which linked records are the duplicates? Not just a True/False if there is some duplicate.
Feb 16, 2024 01:16 PM
FYI, you have to modify the ARRAYUNIQUE rollup to match your separator
ARRAYJOIN(ARRAYUNIQUE(values), ", ")
Even though visually this looks the same without the ARRAYJOIN, there seems to be some distinction where you can't compare it using an = formula... it won't match.
Feb 21, 2024 02:29 AM
Nope, despite it looks like comma and space, it just a visual representation of Unique array.
It works for comma only