Link Main Table rows to various different tables data

1

Hi guys. new to coda and trying to create simple table where rows are linked to other tables depending on what you choose. I have searched everywhere but can’t see, to find a solution and I am not really code friendly :rofl:

So basically this is my question based off the image below:

coda_issue
coda_issue701×419 31.7 KB

Based off the selection under MAIN TABLE / Type column I need column3 to interactively display the correct list from the TEST table. So in row 1 of the MAIN TABLE Italian has been selected so in MAIN TABLE / column3 I would need the drop -down list TEST / Italian Menu to appear with all its options at MAIN TABLE / column3 - and the same for the other rows with the different food options.

What would be the process/formula for this, I got close a few times but could never fully solve it.

As a bonus question, would there be a way to also select multiple options in a single row on the MAIN TABLE?
eg. Row 1 under MAIN TABLE / Type could have both ‘Indian and Mexican’ added and then MAIN TABLE / column3 would add both drop-lists from TEST for both these options.

Any help is appreciated, many thanks.

2

You don’t need two tables for that. Make one table with columns Menu Item, Type, Restaurant. Then you can make separate views of this table or group by Restaurant or/and Type.

Or with this table populated with data, you can create a new table like your MAIN TABLE with the list of restaurants and their menu items.

I created an example doc for you:

“Menu items”, “View of Menu items - grouped by restaurant and type” and “View of Menu items - filtered (only restaurant A)” is the same table, but different views.

“Restaurants” is a separate table and menu items are displayed using lookup column “Menu items”.

1 Like
3

Hi Adnrei, thanks for the reply its much appreciated, however I’m not sure its actually solved my issue.
I don’t really need different views, I need something you select in one row to affect the list in the next row and so and so on, giving you different options per column depending on what you chose before.

The example I gave was just a shortened version of a longer set of custom options that I will eventually need. Apologies if this wasn’t initially clear.

Thanks

4

Hey @INVSTUDIO,
hope that I got it right :slight_smile: My structure is a little bit different, but should be the suggested way to build your tables in that case.

The example included:

  • Show available types dynamically in Main table
  • Multi-select for type
  • Select from menu
  • Automatically show menu

Menu table
Coda tables are more like a database than an Excel table. While you don’t have to stick to that always the main difference is the role of a row. In a Coda table one row usually means one dataset. That’s why the Menu table in the example solution shows both the type and the dish in one row, not with column/field. That structure makes it way easier to filter through it. Alternatively you could also have all dishes of one country in one row, e.g. with a bullet list, depends on what you want to do with it later.

Main table - Type column
Since you might only need Types with a menu available, the type column is already dynamic. The source of the select list with “multi” selected:

[Menu table].Name.Unique()

Menu (auto)
When you just want to show the menu, without interaction, you can just filter the Menu table by the selected type(s) of the current row.

[Menu table].Filter(Name.contains(thisRow.Type)).Dishes.BulletedList()

The filter just checks if the name contains one of the selected types, then returns the dishes and formats it with a bulletedlist.

Menu (selectable)
When you want to make the menu on your own but the options based on the type, you can also use the filter to feed a select list. You basically use the same formula as above but without the bulleted list – and choose multi-select again.

[Menu table].Filter(Name.contains(thisRow.Type)).Dishes

Scenario "single select type"
If you don’t want the multi-select in the main table - type you might need to alter the formula to this:

[Menu table].Filter(Name = thisRow.Type.toText()).Dishes.BulletedList()

This is because a single select makes the selected value a special object (outlined in a pill). Before you can filter by it, you need to fromat it to text with the toText() formula.

2 Likes
5

Theoretically it is also possible with your requested structure, but it is not very dynamic and not recommended - the version above should be better. I tried it more out of curiousity :smiley:

1 Like
6

Daniel, thank you so much for taking the time for this fantastic reply, it really helped and it was what I was looking for. I had to start thinking a little differently with coda, so ended up switching a few options around to make it all work including your solutions here.

I have one last question that I was hoping you could help me on?

If I needed to spread the cost of a meal across various rows that have it added, is this possible?:

  • ‘Mexican Burritos’ cost $15, but I need to spread the cost of that one $15 across multiple rows if I choose the ‘Mexican Burrito’ option. Does that make sense? So spread across ten rows, the ‘Mexican Burrito’ would be $1.50 per row.

Thank you sir!

7

Hey @INVSTUDIO, you are very welcome! It is definitely possible as you can count objects and do the math. To make an example again, I’d probably need a hint where you want to put the 15$ and where you want to see the output of 1.50. Fastes way is to put BOTH in the Menu Table (Total cost column, cost per item column), but might not fit your use case. Maybe you can make a screenshot with colours and arrows again so I can get a picture :smiley:

2 Likes
8

Hi Daniel, sure thing, any help is appreciated, this table is finally coming together!

image
image832×711 46 KB

So as you can see above, if I select the ‘Food Item’ in the ‘MAIN TABE 2’ from the ‘PRICING’ table below I would like the cost of the item to be spread across the rows it has been chosen for. So Vindaloo has been chosen 4 times, so that would be $10/4= $2.50 in each of the rows it appears.

Hope this makes sense?

Much appreciated sir.

9

Hey @INVSTUDIO,
to solve it like in your screenshot you have to count the appearance and than divide it by the cost. If you want to apply it to our first example with multiple items in one row, than it gets waaay more complicated. I made you an example for both in the doc.

Solo
Count appearance
You just filter the table for the item and add a count() formula in the end:

[Main table Solo].filter(Menu=thisRow.Menu).Count()

Calculate cost per item
Than you filter the pricing table for the price and do the math

 [Menu table Solo].Filter(Dish=thisRow.Menu.ToText()).[Cost total]/thisRow.Appearance

Of course you can also do it in one formula/colum, but it easier to explain in two :slight_smile:

Additional notice:
You could use the lookup() OR the filter() formula here. The main difference is, that filter allows multiple criterias and lookup just one. I usually always go for the filter() even I just need one, because I don’t know if I will need more later – I better be prepared :smiley:

Multi
This is more complicated as you have to iterate through the values (e.g. with with FormulaMap()). There are multiple ways where and how to do that. I think the easiest one is to make the calculation in the pricing/menu table already and just use the data in the main table. If you’d do it in the main table, you’d probably have to iterate through everything twice which is not an easy task in Coda, as nested “CurrentValues” are not working.

The highlight here is probably the count appearance part:
With FormulaMap, I go trough every row of the main table and use a formula for each of them. In there I want to search the dish in the bullet list. But the find() formula alone returns just 1 when it is found and -1 when it is not found, which doesn’t help me in the total number of appearances. So I use a if() formula around it to check if the find formular returns a number higher than 0 and if yes it notes down a “1”, if no it notes down a “0”. In the end I sum up all the 1 and 0 which gives me the exact number of appearances.

[Main table Multi].[Menu (auto)].formulaMap(
    if(
        Find(thisRow.Dish,CurrentValue)>0
   ,1,0))
 .Sum()

Important notice: This all only works as long as every dish has a unique name! If e.g. there is a Pizza in ‘american’ and ‘italian’ kitchen, you’d have to give them different dish names e.g. ‘American Pizza’ or even work with dish Id.

1 Like