Remove duplicates in look up field

You have ARRAYUNIQUE():

Thanks

Yes but it only works in Roll-Up not in Look-up. And when you use that you only get the text from a record, not a link to the record like you do in the Look-up.

I meant to use it in a Formula field. What are you trying to achieve?

I personally am trying to get a rollup of unique values, as the duplicates have no value as links.

This is not my particular use case, but let’s use a simpler recipe example…

There’s a Recipes table with linked tables called “Ingredients” and “Type”.

Ingredients has the following values:

• Chicken
• Bacon
• Lettuce
• Onions
• Walnuts
• Cheese
• Bun

Type has the following values:

• Fruit
• Vegetable
• Nut
• Meat
• Dairy
• Bread

Using a “Chicken Club Sandwich” example, let’s say we wanted to use a look-up to provide the various food types for this sandwich recipe, the results would be “Dairy, Bread, Vegetable, Vegetable, Meat, Meat”.

Example:

However, “Dairy, Bread, Vegetable, Meat” would be much more useful, as the redundancies are removed. Example of what’s desired:

And here is my proposed solution (having a simple switch under the a Lookup field that links to another Linked Record field):

Why do you need it to be a lookup field rather than a rollup?

As @Elias_Gomez_Sainz suggested, you can use ARRAYUNIQUE() in a formula fild and apply it to a lookup field, if needs be.

Because I want the linked records to actually be linkable records for quick access, as opposed to just being text.

The rollup can display only unique values, but they’re just text and not the actual linked records. The same goes for using a a Formula on a Lookup field.

Also, speaking of formulas, ARRAYUNIQUE() doesn’t appear to be working correctly on Lookup fields, as non-unique values are also displaying.

I also want this feature. The simple example explains my use case as well.

Another way to look at this feature request:
Why would someone want duplicates in a lookup field?

Another more generic approach which would be cool (but probably would cause problems), is if there was an UNIQUEREFERNCES function that would return references that are unique. I’m guessing the formula engine doesn’t work with references so everything is converted before hand which would make this new function hard to implement.

You know, I have to admit I didn’t realize lookup fields worked that way. Live and learn…

As you’ve identified, this has something to do with the special way Airtable treats lookup fields/linked records. For instance, if you define in the [Ingredients] table a field called {TypeName} that is simply a lookup field that follows the {Type} link and returns the {Name} of the linked record, then a rollup of {TypeName} using ARRAYUNIQUE(values) properly removes the redundant entries. I sort of understand why Airtable doesn’t deduplicate lookups and linked records, but I can’t actually articulate it other than by waving my hands around…

So here’s my proposed solution: simply add an “Only display unique linked records” switch in the Lookup field configuration menu.

This would both solve my problem, plus allow folks to allow duplicates to display if they need to for whatever reason (as the system currently does now).

That’s a great idea for a solution and exactly my thought @David_Krizan

The Ingridients example is great to illustrate the problem when the same ingrident appears multiple times.

I just want to chime in here to say that I have exactly the same problem. I have duplicates in lookup fields but can’t use rollup because I want to have the links to the actual records. Really, I don’t see why one would want to have duplicates in a lookup field ever.

If someone has figured out a workaround for this it would also be much appreciated!

Yes, cant find a fix for this. If anyone has any ideas let us know! Would love to see the platform updated to address this.

Seriously Airtable, just add an “Only display unique linked records” switch when someone selects a Linked Record field from a Lookup field. This would be so useful and seemingly so easy to do because (1) y’all would know when a Linked Record field is being selected from a Lookup field, and (2) this should just be a simple filter.

Simple Mockup of Proposed Solution

I absolutely love that Linked Record fields can be used in Lookup fields, but I have NEVER ONCE wanted duplicate linked records displayed in a Lookup field, as any redundancy negates the utility of such. This problem comes up frequently and prevents me from doing some very useful things with my data.

I’ve been keeping an eye on this for some time. Has anyone found a workaround or come across a product update I’m not aware of? It’s rather frustrating.

I’m looking for the same solution but there doesn’t seem to be a solution. Does Airtable actually go through these forums?

You can make an instant Zap in Zapier that Zaps the text from the Rollup to a Linked field as a workaround for this for now.

I ran into this problem too.
Here is my solution for the recipe example:
Make a hidden field in the Ingredients table that is a formula that duplicates the value of Ingredients -> Type, let’s call it “Type String”, then do a Lookup ARRAYUNIQUE() of Ingredients > Type String and it will work.

This is a “must have” feature. It’s crazy to have the same link replicates several times. It’s just a filter over the top (underlying data can still be there) to make several of my views so much more valuable to users.

Agreed. This is a “must have” for me.

Yes i need this feature, Please add it.

This solution explained solves my use case as well. I am linking tasks to multiple projects and want a lookup field that displays a unique set of links instead of duplicate links

I came here looking for a similar solution. I hope this comment can be regarded as an extra +1 when evaluating the importance of this feature.

Definitely a needed feature that will make tracking data a lot easier and improve workflow, which is what Airtable is designed to do, right?

The reason I need this feature is, in my accounting base for my business, I have a table of my business assets. It is linked to my business expenses which shows the transactions when I purchased the assets. I wanted to add a lookup field to the assets table for the “cost” field of the business expenses, which would, theoretically, tell me the total value of my business assets. The reason it doesn’t, however, is because some transactions include more than one asset, so both assets link to the same transaction, and the lookup returns duplicate dollar amounts, resulting in an inflated total.

If I had the option to “only display unique records”, this would not happen, and the sum of the lookup column would actually reflect the equity in my business assets.

Without this feature, I would have to manually enter the value of each asset, based on the corresponding transaction record. Which, is not worth the time or effort.

With all the other things Airtable can do and makes easy to just add a column to get a new stat, it’s disappointing that such a simple feature with so many applications, already available in other field types, is overlooked.

Hi Airtable product people,

I need this to cluster information together for a complex impact analysis.

the proposed solution seems fair with the little toggle.
the exposition in a formula is also cool if it does not destroy the lookup link like the ARRAYUNIQUE does today.

just for our information, how is the feature election process working?
Have you disregarded that request as it’s floating around for around 2years?

Yes! this is a very needed feature.

• What’s the use of showing the exact same “clickable link” twice in the same field? I mean, we cannot remove or add or interact with linked records other than clicking it, and if all those clicks lead to the same place – then why show the same link mulitple times?
• => Why should there even be a “Don’t show duplicates” checkbox for linked fields?
• => Should this be treated as a feature or a bug?

To me it feels like a Bug in the Linked Records field. It creates side effects – try “grouping” by a Lookup field, and see it grouped by the same linked record many times over with headings like “LinkA LinkB LinkA LinkA” “LinkA LinkA LinkB LinkA” … creating a nasty permutation of groups.

Is there a way to file a bug report?