Remove duplicates in look up field

[Mathias_Elmose]

I’m a big fan of look-ups but I would really like to be able to remove records the occur more than one time. Is there a way to do that?

[Sean_Murphy1]

ARRAYUNIQUE(values) may not actually return a list of unique values if the field you are rolling up is a multiple select list. In that case, 'values' is an array of arrays, and ARRAYUNIQUE correctly identifies ['foo', 'bar'] as being different from ['foo','baz'] but the rollup field will still contain 'foo' twice.

I was thinking you oughta be able to use something like

ARRAYUNIQUE(ARRAYFLATTEN(values))

but it doesn't seem to be an option in rollup fields. But even when doing it in a formula field with

ARRAYUNIQUE(ARRAYFLATTEN({Roll-up field}))

I'm still not having any luck. It looks like ARRAYFLATTEN isn't doing anything, or maybe I am misunderstanding the contents of the rollup field.

• I would've expected the rollup to contain [['foo','bar'],['foo','baz']]
• and ARRAYFLATTEN to give me ['foo','bar','foo','baz']
• and ARRAYUNIQUE to give me what I want: ['foo','bar','baz']

But whether or not I use ARRAYFLATTEN the result is the same.

[Maria_Robertson]

Keeping the chain going, would love to have this! Currently using the workaround given, but having just a toggle option would make a bunch of things more streamlined.

[Natalie_Zdan]

Just sharing this improved automation workaround I found (through help from Airtable) that doesn't require checking a box--though a lookup field that only looked up unique values would still be best!

[carly]

Accidentally found a quick semi-fix for this if you just need to get it done. Going back to the Chicken Club Sandwich Example on page 1:
- Create a new new linked column (linking directly to "type")
- Copy the data from the "type" lookup column/cell
- Paste into your new directly linked "Type" column/cell
When it pastes into the new column, the dupes are removed!

Not ideal, but it might work for some use cases!

[john_griffin]

Both depressing and heartening to see so many years of people with the same issue.

I've tried the workarounds but the duplicate value I'm having is duplicates of the same user being listed in a field and the currently presented workarounds don't seem to work for that.

[Tito_Alverio]

Just keeping an eye on this as well. I'm unable to get unique names for a template I'm creating. Been searching, but nothing working so far. My case involves two source tables (Tables A & B) from a base (Base 1) multi-syncing to one target table (Table C) in another base (Base 2). In Base 2, I have Table D linking to Table C, and this is where I run into the issue where linked assignees from the sources (A & B) cannot be shown as unique values only, no matter what kind of rollup, lookup, formula combos I try.

[Thomas_de_Beauc]

Hi, community 👋

I came up with a solution using the Scripting automation that

• Does not require extra columns
• Works with records AND attachments
• Allows you to sort them, filter them, or do any other operation on them to the extent of Javascript's capabilities
• Was battle tested on thousands and thousands of records

Attachments

Here is an example summarizing Attachments from all of a Contact's Emails :

• Contact table with fields
  • Attachments from Emails
  • Emails
• Email table with field
  • Attachments

const { contact_id } = input.config()


const ContactTable = base.getTable('tbl8dODvN066m2wIc'/*Contact*/)
const EmailTable = base.getTable('tbl6LDjmzV2Rc8jKq'/*Email*/)

const Contact = await ContactTable.selectRecordAsync(contact_id)

const Emails = Contact?.getCellValue('fld03ip9p2K8XARnL'/*Emails*/) || []
const EmailIds = Emails.map(i => i.id)

const Emails = await selectRecordsAsyncInBatches(EmailTable, {
    recordIds: EmailIds,
    fields: [
        'fldxrp1KuecaoypBc'/*Attachments*/,
    ],
    sorts: []
})

const attachments = []
for (const Email of Emails || []) {
    attachments.push(Email?.getCellValue('fldxrp1KuecaoypBc'/*Attachments*/))
}

const attachmentsUnique = uniqueAttachments(attachments.flat().filter(i => i?.id))


output.set('Attachments from Emails', attachmentsUnique)


function uniqueAttachments(attachments) {
    return attachments.filter(
        (record, index, array) => 
            array.findIndex(
                ({ filename, size }) => (
                    filename === record.filename
                    && size === record.size
            )) === index);
}

I am basing myself off of filename + size to determine if a file is unique. It is not as good as using a hash, but has been working out pretty well for us for the past year or so.

Records

If, instead of looking up Attachments, you want to lookup records, the code is exactly the same with just replacing the function uniqueAttachments with uniqueRecords :

function uniqueRecords(records) {
    return records.filter(
        (record, index, array) => 
            array.findIndex(
                ({ id }) => id === record.id
            ) === index);
}

Enjoy ❤️

[Natalie_Zdan]

Thanks for sharing, @Thomas_de_Beauc!

I'm not familiar with scripting and only have very minimal JS experience so not sure I want to try to take this on until I know it might do what I want. In my case I have 3 tables: Participants, Facts, Insights.

I enter facts in the Facts table and tie them to both Participants and Insights in respective "link to another table" fields. What I want is to have is a lookup field on the Insights table that looks up Participants from the Facts table, but that only brings in unique Participants.

It doesn't sound like Attachments would be a 3rd table in your case. So I guess I'm wondering: would your script still work to pull in unique records that are clickable (just as normal lookup fields pull in clickable records if they're pointing at a "link to record" field)? Thanks for considering!

[Thomas_de_Beauc]

Hi Natalie,

Sorry for the late reply, life has been busy.

In the context of my script, Attachments are directly interchangeable with Linked records, they are treated the same. The only difference is to replace the uniqueAttachments with the uniqueRecords function, besides that you could just write records instead of attachments in every variable name and it would work just the same.

Here it is: 

const { contact_id } = input.config()


const ContactTable = base.getTable('tbl8dODvN066m2wIc'/*Contact*/)
const EmailTable = base.getTable('tbl6LDjmzV2Rc8jKq'/*Email*/)

const Contact = await ContactTable.selectRecordAsync(contact_id)

const Emails = Contact?.getCellValue('fld03ip9p2K8XARnL'/*Emails*/) || []
const EmailIds = Emails.map(i => i.id)

const Emails = await selectRecordsAsyncInBatches(EmailTable, {
    recordIds: EmailIds,
    fields: [
        'fldxrp1KuecaoypBc'/*Attachments*/,
    ],
    sorts: []
})

const records = []
for (const Email of Emails || []) {
    records.push(Email?.getCellValue('fldxrp1KuecaoypBc'/*Linked records*/))
}

const recordsUnique = uniqueRecords(records.flat().filter(i => i?.id))


output.set('Records from Emails', recordsUnique)


function uniqueRecords(records) {
    return records.filter(
        (record, index, array) => 
            array.findIndex(
                ({ id }) => id === record.id
            ) === index);
}

 In this example, you could imagine that every Email is linked to a sale from the table Sale, so here for a given Contact it would go through every Email and summarize all the Sales that this Contact was involved in.

Hope this is clearer

[hannahRFP]

I find it crazy this feature has been requested for years but still isn't available? 

Airtable please make it an option to have lookup fields only displaying unique values! I don't want to use a rollup for the above reasons that I want it to be linked records.