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