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?
Remove duplicates in look up field
+1249 people like this
- Justin_Hall
- Kevin_Moreau
- Calvin_Young
- Adam_Dowsett
- Lior_Neudorfer
- Jesse_Cotari
- David_Krizan
- Shahar_Or
- bob_aycock
- Cyrus_Mohammadi
- Oliver_Holtz
- Philipp_Skriban
- David_Pethick
- Casey_Weiss
- Subhas_Dandapan
- Paulina_Lopez
- James_Lawira-Fe
- Yago_Stecher1
- Julie_DeMello
- Amanda_Nguyen
- Guillaume_Riche
- Joseph_DeVenut2
- Jeremie_POUTRIN
- hlcurzio
- Maria_Robertson
- Martin_Dujol1
- Andrew_Piotukh
- Corporate_Engag
- Scott_Cytacki
- Amanda_Foster
- Thomas_de_Beauc
- Kristen_Singer
- Una_Phillips
- Emily_Gasca1
- Tito_Alverio
- Brett_Workman
- Mari_Vihuri
- Kaley_White
- Natalie_Zdan
- Paul_Warren
- Matt_Stevenson
- Samir_Ghosh
- Russell_Bishop1
- Jessica_Millnit
- gekonwi
- greenant
- deathlessmile
- mikebeam
- coolrick
+6Keeping 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.
+11Just 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!
+1Accidentally 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!
+1Both 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.
+7Just 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.
+8Adam_Minich wrote:
Hello community!
Adam from the Airtable customer support team here. I had an Airtable user reach out with a link to this community post. While I can’t speak to a specific timeframe for when this feature might be available I have a workaround that may help.
I used the Recipe, Ingredients, and food type example from earlier in the thread. Also, you will need to have some comfort with our Automations feature in order to use this method:
Hope this helps and know that our team is very thankful to our community!
P.S. It may make more sense to set up the automation trigger to be “When a record is updated” and have it just watch the “Array Unique” rollup field.
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 ❤️
+11Thomas_de_Beauc wrote:
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 ❤️
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!
+8Natalie_Zdan wrote:
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!
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
+8I 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.
+1Hello everyone.
This works: https://community.airtable.com/t5/other-questions/going-mad-trying-to-avoid-duplicates-with-arrayunique/td-p/103604
answer given by W_Vann_Hall
+2Just another person +1ing the need for this feature, years and years later.
I get that there's a work around and I'm sure with enough time and focus I could figure out how to do it, but as someone who is constantly bringing organizations I work with to airtable, it would be great to have a way to do this clearly useful function without having to have a fundamental understanding of coding/programming to make it happen.
+7Sharing the simplest way I found to do this without the need for scripting or automations. Copying my email to Airtable support:
This was resolved to my expectations by taking the following steps:
- Create the rollup field in Table A on the Linked Table B’s field I want to rollup, in this case a multiselect field.
- Take that same rollup field that was just created, and change it to a Lookup field.
- (The lookup field will now display as text, instead of the colorful word bubbles you usually see from multi-select fields)
- Create a formula field in the Table A, and ARRAYUNIQUE the “formerly a rollup now a lookup field” field.
- Ta-da
Is Airtable aware of this strange behavior? If so, can we make it simpler and more intuitive to get unique values out of rollup or lookup fields?
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.