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)
=Fruits.filter(in(fruit,inUse)=false).fruit

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

2 Likes

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

3 Likes