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
I love your solution @chris_homburger , since often you just need to think from another angle to get it working 
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.
4 Likes
I want this to be a column formula so that each fruit is a separate row in a new table. How can I do that?
Thanks!
1 Like
