How to get all the unique values from a select list of select from table

Let say you have the following tables:

  • Table “Fruits”, that contains a list of fruits: Apple, Banana, Orange, Kiwi
  • Table “Fruit salads”, that contains a list of different fruit salads:
    Name: Apple salad, Fruits: Apple
    Name: AppleAna, Fruits: Apple, Banana
    Name: NotOrange, Fruits: Apple, Banana, Kiwi

The Fruits column in the “Fruit salads” table is a table lookup. The question is now how do I get the unique list of all the fruits used in any salad? I’ve tried using ListCombine(), but it doesn’t seem like it combines list from Select list or lookup tables.

I expected [Fruit salads].Fruits.ListCombine().Unique() to give the unique list of fruits used, in this case it means all fruits except Orange. Instead I will get the following result: [Apple], [Apple, Banana], [Apple, Banana, Kiwi].

Any ideas how to get the unique list of fruits?

The problem is that the items within a cell are not considered individually. As far as I know (and I already asked a similar question over here ) there is so far no work-around for this within coda.

I definitely get that, and I also think that is correct. I would imagine that the values in such a cell is considered to be one list containing multiple values, and that is why I tried ListCombine.

@Tomas_Jansson you’re looking to get the list [Apple, Banana, Kiwi], since these are the fruits used in salads listed? I would recommend doing this the inverse way.

Instead of selecting which fruits are in a salad, select which salads a fruit belongs to. You can use the same multiple lookup column.

Then, to get the list of ingredients which are used, simply use =Fruit.filter([Used in?].IsNotBlank()). In the “Recipes” table, use =Fruit.lookup([Used in?], thisRow) to see the ingredients of each recipe.

I’ve attached a screenshot of my two tables—let me know if you have any questions!

1 Like

Makes sense. Thank you!

I love your solution @chris_homburger , since often you just need to think from another angle to get it working :slight_smile:

There is a nasty way to work with lists/table lookups out of a cell.

In LIP2, I needed something similiar to calculate, if you have the right items in your inventory to interact with an object or not. The only way I could find was to flatten these “formatted lists” using “totext()” in a formula map – just to split it up again to a real and clean list.

For your example instead of:
=[Fruit salads].Fruits.ListCombine().Unique()
you could make
=[Fruit salads].Fruits.ListCombine().FormulaMap(ToText(CurrentValue)).split(", ").Unique()
to get the results.
Note that in the split formula you have to use ", " instead of “,” (so with a space), to get it right.

To get the leftovers, you can filter the fruits down (I named the first formula “inUse”, so it represents our in use list)

Maybe this flatteing could also be a workaround for your importing case @Philipp_Alexander_Asbrand-Eickhoff?


@Michael_Varney - made a recent checkin that makes this super simple. Just use [Fruit salads].Fruits.ListCombine().Unique() and it will work as @Tomas_Jansson initially expected it to.

Yay! Thanks for working together on making things simple.