Help

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

5854 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dapp_M
4 - Data Explorer
4 - Data Explorer

The result I am looking for is similar to the ARRAYUNIQUE function for a Rollup field. However, the results are a link to another table, and it seems that using a rollup can only show text results as opposed to clickable records. How can I make lookup field show only unique results, or make the unique results of a rollup field clickable?

27 Replies 27

I don’t understand. The results of a Lookup field are not clickable neither.

Anyway, could explain a little further? I don’t know what are you trying to do :thinking:

When I first read your post, I was pretty sure I knew what you meant — but once I tried to dummy up a base demonstrating it, I confused myself. Rather than spread that confusion around, I won’t link to that base; instead, let me know if this comes anywhere near your intent.

It appears to me you want to perform a lookup from [TableA] to [TableB] where the field you wish to look up is a linked records field connecting to [TableC]. In doing so, you want to be able to click on a returned value and have the corresponding record in [TableC] open. Furthermore, you want the returned values to be de-duplicated, containing only unique entries.

Am I anywhere close? If so, I have good news and bad news.

The bad news is that what you wish to do cannot be done, as far as I know, within the scope of standard, core Airtable functions. As @Elias_Gomez_Sainz points out, once a value is returned through lookup, it is no longer a link to a record; instead, it simply contains the value of the primary field of the formerly linked record.

The good news is, given that value, it is a trivial task to recreate the original link. This can be performed manually with a handful of keystrokes; alternatively, it can most likely be automated using Zapier or IFTTT.

Here is the process for performing this manually: I assume you have defined the lookup field and have been rewarded with a response containing the values of the primary fields of the linked records, including possible duplicates. At this point, you have two options:

  1. If your database is complete — that is, if you do not anticipate adding any more records to the table at hand, you can right-click on the lookup field, select ‘Customize field type,’ and change the field type from ‘Lookup’ to ‘Link to another record,’ indicating the field should link to [TableC]. After a moment, Airtable will have converted the field to a link records field and the static values to live links to records in [TableC]. In the process, redundant links are combined, effectively leaving you with only unique items.

  2. However, if yours is a living database with records added regularly, the approach described in the previous entry is unsuitable. If you need regular, incremental, and ongoing conversion of looked-up values to links, you can accomplish this by copying from the lookup column and pasting into a linked records column. This preserves the integrity of the data in the original lookup field and allows subsequent conversions to be made without having to ‘back out’ previous changes.

    To support ongoing conversions, first create a new field in [TableB]; configure it as a linked records field pointed at [TableC]. Click the cell for row 1 of the lookout field you wish to convert. Scroll to the last row of the table and, while holding down the Shift key, click in the cell for the lookout field for the bottom-most row. In the lower left of the screen, a message reading ‘### cells selected’ should appear. Press Ctrl-C; in the lower left, an alert reading ‘### cells copied’ will appear.

    Finally, return to row 1 of the table. Select the frame in row 1 for the linked records field you just now created. Press Ctrl-V to paste the looked-up values into the linked records field. Airtable automatically converts the values to links and eliminates duplicate entries.When you next add records to the table, you need only to copy/paste the lookout fields from the newly entered records to the linked records field.

I suspect one could easily configure Zapier to perform this same copy/paste operation — albeit one record at a time. (I do not know how strictly Zapier enforces matching types, though; conceivably it might not permit one to paste the results of a lookup field into a linked records field.) If you are interested in pursuing this option, in the [Documentation] table of my Wardrobe Manager base from Airtable Universe you will find a document entitled Wardrobe Manager Zapier Guide. It provides step-by-step instructions on configuring a Zapier Zap that will copy the contents of one cell of a newly added record into another cell of that same record. Although in the guide the data flows from a text-formatted formula field to a long text field, rather than from a lookout field to a linked records field, the process and the bulk of the programming should be identical.

Again, feel free to ignore any of this that doesn’t apply to your situation. If none of it is applicable, let me know a little more about your intent, and I’ll try again.

I believe this feature request would help, no?

The above feature (quoted from Jeremy_Oglesby’s response to a feature request) would provide us with a more general solution.

David_Krizan
7 - App Architect
7 - App Architect

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:
image.png

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

image.png

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

image.png

Mindy_Silva
4 - Data Explorer
4 - Data Explorer

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

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

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

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…

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

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.

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

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

David_Krizan
7 - App Architect
7 - App Architect

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
image

  • 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.

Kellan_Baker
5 - Automation Enthusiast
5 - Automation Enthusiast

@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.

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.

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

Adam_Johnson
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Steven_Benders
5 - Automation Enthusiast
5 - Automation Enthusiast

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