This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Feature Request: Lookup field type returns unique values

Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

It would be extremely useful to have the ability to control whether a lookup-type field only returns unique values versus showing the full list of lookup values. For example:

Table 1:
Record A
Record B
Record C

Table 2:
Record X, Category 1, Links to Record A
Record Y, Category 2, Links to Record B
Record Z, Category 2, Links to Record B

If I add a Lookup field to Table 1 showing which categories are in the values linked to these records from Table 2, I get:

Table 1:
Record A, Linked to Table 2: “Record X”, Category Lookup: “Category 1”
Record B, Linked to Table 2: “Record Y, Record Z”, Category Lookup: “Category 2, Category 2”
Record C: Linked to Table 2: “”, Category Lookup: “”

The desired behavior is that this Lookup field would show “Category 2” without the duplicate for Record B in Table 1.

18 - Pluto
18 - Pluto

Did you try using the ARRAYUNIQUE function in your Rollup field?

Also, you can check out this workaround here:

5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for that suggestion @ScottWorld - I’m going to test out that automation approach now.

I mixed up my nomenclature in the original post (just edited it to correct) - I am hoping to use a Lookup field, not a Rollup Field, so that I can retain the linked fields functionality rather than returning a text string alone.

6 - Interface Innovator
6 - Interface Innovator

Sadly ARRAYUNIQUE doesn’t work in a very, very common case:
[Table A] is the table you’re rolling up in
[Table B] you lookup in Table A, and refers to items in [Table C]
[Table C] is the items you want to rollup

In [Table A], if you roll-up [Table C] values in [Table B] (which is the normal case with the rollups being discussed here), then if there are ever MULTIPLE [Table C] items in a SINGLE line in [Table B], then it ends up getting duplicated

Concrete example:
In the [Meals] table, you look up [Dishes], and each dish has multiple [Ingredients]
In the [Meals] table, when you try to ARRAYUNIQUE rollup the [Ingredients] in the [Dishes] for a meal, you encounter this problem.
Suppose one Dish has [Salt] and [Pepper] and another dish JUST has [Salt]; the ARRAYUNIQUE returns “Salt, Pepper, Salt” ← duplicates

Hopefully they’ll figure out a better way of handling this in the future