How can I make a lookup field show only unique results?

I think the following thread is essentially requesting this feature: Remove duplicates in look up field

I personally am trying to get a rollup of unique values, as the duplicates have no value as links. So, I’m essentially having to use a jury-rigged workaround similar to what @W_Vann_Hall is suggesting.

This is not my particular use case, but let’s use a simpler recipe example (I also posted this in a reply to the above link)…

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):

18 Likes

This is exactly what I am looking for - so it’s not possible yet @David_Krizan?

2 Likes

You could try using a Rollup field with the ARRAY_UNIQUE(values) Rollup function.

5 Likes

I tried that - it doesn’t work. For some reason, it doesn’t see the duplicates

5 Likes

Nope, not available yet, much to my displeasure. I’ve tried to find some workarounds, but they’re all janky and inelegant. This really, really, REALLY should be an easy and obvious feature to include. Very frustrating…

2 Likes

Bravo!! This worked perfectly!!! But it is ARRAYUNIQUE(values)

4 Likes

Hi David,
I did the Rollup + ArrayUnique{} and got text-only uniques as you pointed out. Then I changed the field type to Link Records and voila – the text now links.

1 Like

I know that that works, but what I really want here is to set a Lookup field that’s looking-up a Linked Record field in another Linked Record to only display unique fields as clickable linked records. Right now this works except the ability to display linked records (every linked record – including duplicates – is displayed). If there was simply a “Only display unique linked records” switch that would display when you’re looking up a linked record field, this would work.

I want to keep it how it currently works except having the ability to remove duplicates (showing duplicates here provides no value to me, but the converse would be incredibly valuable for many use cases).

1 Like

What’s the advantage of using Lookup instead of Rollup?

They’re two different field types with their own pros and cons, but that’s a whole other topic altogether.

The advantage of a Lookup field in this instance is that looking-up up another Linked Record field will display the data in the typical clickable Linked Record bubble vs. the stripped-down/non-clickable string data that will come back in a Rollup field.

Difference

  • The Lookup displays the format I want here (clickable Linked Records), but has redundant values which are useless in this context.
    *The Rollup displays the data I want here (by using the “ARRAYUNIQUE(values)” function), but the data is just text and is not clickable/linked to the actual Linked Records.
  • What I want is elements of both; I want clickable Linked Records via the Lookup as this field currently allows, but I simply want an option to filter out duplicate Linked Records (basically the ability to apply the “ARRAYUNIQUE(values)” function here).

I’ve personally come across over a dozen use cases where I want this; conversely, I haven’t had a single use case where I actually wanted to display duplicate Linked Records.

7 Likes

@David_Krizan has there been any movement on this from Airtable since this discussion was last live?

Not as of yet. This has actually prevented me from using Airtable for a few use cases, so I really hope this common sense feature is added soon.

2 Likes

I’ve tried this and the text does turn to linked records just as Lookups, and only the unique values remain - but in my case adding a new value that would cause another unique value to show does not work. The list of records is now static and not “rolled up” from existing records.

Really wish this was a feature. A simple toggle like it was proposed would be amazing.

1 Like

Agree with the Unique toggle feature, very important. Would be great to push for this, seems like a smiple win for both sides!

3 Likes

Here is a video I made demonstrating the Rollup feature that helps return only the unique results in Lookup: https://youtu.be/MiyQQxSx7iQ
Thanks to @David_Krizan for the solution.

+1 for this feature - also seems like a no brainer to only show a single linked record when doing a lookup (while preserving the interactivity of the link to that record)

2 Likes

I’ve hit this problem too - and also found that if the Roll Up field (ARRAYUNIQUE(values)) is referencing a Multi-Select field in another table, then it unfortunately fails to parse as expected and will return duplicate string data. I’m working on figuring out another parse of the data to clean it up. =/

I reported this to support today. How annoy.

2 Likes

Hey David,
I found a kinda “hacky” way to solve this with two Automations.
Step 1: Create a Linked Record Field
Step 2: Create the Rollup Field with ARRAYUNIQUE(values) as mentioned before
Step 3: Go to Automation Tab and hit new Automation
Setup as follows
Trigger:
“When record is created” →
Properties on the right:
Table: Select the table of the record
Add action:
“Update record” →
Properties on the right:
Table: Same as before
Record ID: Click the plus, select continue at “Record from step1” and select insert for “Airtable record ID”
Fields: Click “+ Choose field”, now choose the Linked Record Field you created at the beginning, now an empty bar with plus appears, click the plus and again select continue at “Record from step1” and select insert for the Rollup Field.
Now in the top left switch the button from “off” to “on”
Step 5: Repeat Step 3 but choose “When record is updated”
Done!
Now every time your record has a new Value in the Rollup Field it will get transported to the Linked Records field where the Records can be clicked and used as you like.
But it’s not instantaneous you have to wait a bit and it depends on your plan, how often you can trigger it… Pricing - Airtable

3 Likes

thank you, @Jeremy_Oglesby ! That’s exactly what I was looking for.

1 Like

+1 @David_Krizan. This would be such a simple solution. Any response from Airtable on it in the past 3.5 years?