Show lookup as table issue

I am trying to figure out how to have one table lookup the contents of another and display it as a table in detail view.

Basically, we have volunteers that sit on panels. I want to be able to see who sat on the panel as a list within the detail view of a panel row. I’ve set it up so that each volunteer row has a multi-select list of panels, which are lookups to a second table. Is this the wrong way to do it - should I have set up the panels as the main table with lookups for the volunteers? Is there an error with doing things the way I’m doing? Some sort of circular referencing issue?

It doesn’t matter whether you have
a) a multi-select list of Panels for each Volunteer, or
b) a multi-select list of Volunteers for each Panel.
You go with what feels more logical to you.

What matters is that one is an input (i.e. you select it yourself), and for another you want to write a lookup formula that would update automatically when you re-assign volunteers.

E.g., if you go with option a), then the Volunteers column in your Panels table should be a formula:
Volunteers.Filter(Panels.Contains(thisRow))

It will pull all volunteers that have this row’s panel in their selection. And then you can make it a table in display view.

UPD: seems like your doc was briefly in edit mode, and I fixed that right in your doc (I thought I was making changes in embedPlay mode)

2 Likes

Thank you! Glad to have that clarity around what can reference what. So when our users are adding someone to a panel I always need them to remember to do it from the participant table and not the panel table, is that right? Is there a logic to why it can’t happen from either place?

I think an issue I was having with the above is that you have to delete a formula totally in order to let it populate the column with the right data. So if you just edit the formula it sticks with the outputs of the earlier, wrong formula.

Thank you

Yes, on the data structure level it’s always that you enter the linking (e.g. link rooms to volunteers) in one specific place. Then you can build different UI atop of that, e.g. make a separate dedicated form to add assignments like here.

The logic to this is that you don’t want to have multiple sources of truth in your doc, or multiple ways you can enter the same data. That’s why you adopt some convention where you want the inputs to be, and in all other places you’re using formulas to calculate whatever you need from those links. Like that reverse mapping.

Hm interesting. It seems as if I can add new rows from either view seamlessly… so if I create a volunteer in the volunteer lookup table of the panel view, a new volunteer will correctly be added to the volunteer table. And if I add a volunteer to the Volunteer Table then it correctly appears in the volunteer lookup table of the panel view. BUT if I try to link an existing volunteer to a panel from the lookup table on the panel view, then it will correctly “look up” the volunteer I’m searching for, but instead of linking to that volunteer it creates a new row in the Volunteer Table with the same name and none of the details. Shouldn’t it work such that adding a volunteer in the panel view should correctly look up the correct row? Is there a logic here I’m missing?

Hello Tim, the problem here is that you are directly referencing the Volunteer Table, so if you press an empty space it automatically adds a new row to the Volunteer Table. What you can do instead is press the + button that appears below the table and it gives you all the options you need.

1 Like

What I like to do is make a Table that combines both Panel and Volunteers Table this way I can add either from the Detail View of the Volunteers or from the Detail View of the Panels. You can do this just by clicking in the empty table inside the view.


It also helps if you want to know when a person was added or things like that.
Of course if you are a free user the first approach is better because it doesn’t eat your row limit like this one, so keep this in mind.

Here’s a sample showing this:

5 Likes

OK, this is actually new. Just a few months ago it was not possible to modify another table through a lookup-pulled data set.

Didn’t know this, thanks everyone.

2 Likes

Thank you Saul! I echo Paul’s comment - I tried something like this a few months and I could have sworn it simply didn’t work both ways.

Saul, I’m intrigued by your third table idea. This kind of blows my understanding of how table referencing works in Coda. So you have Rows A, B & C of Table A referenced in Row A of Table B, and you also have Row A of Table B referenced in rows A, B & C of Table A. That makes sense so far.

Then you add a third table, Table C, that references all the info from Table A & Table B? How is that different from just adding from A to show in B and vice versa?

I believe it’s more of a “coder-with-SQL-background” approach where you make many-to-many relations with the help of join tables because a cell cannot contain a list of values but only a single one.

Not necessary in Coda, but as @Saul_Garcia demonstrated, it enables this nice UX of adding a relation by adding rows to a filtered (sub)table. A noteworthy trick with less confusion for the user (no need to remember to press the + button and not add a row).

1 Like

Thanks for the explanation, that helps. Interesting wrapping my head around this stuff as a complete non-coder, trying to understand schema design principles for the first time.

That’s exactly right @Paul_Danyliuk, I like making things easy to use.
It also helps you to do other things. Say you want to know how much time a person has been in a Panel. Without the third table it would be pretty difficult. But with the help of the third table, you can just add the “created on” column and make a simple subtraction :slightly_smiling_face:

2 Likes

I’ve just been trying to replicate Saul’s version above, hoping that by recreating the 3 table solution I would understand it… but I still don’t! I just don’t understand what is going on with all these formulas - where I should add new rows, etc.

If I set it up just like this, am i right in saying that I should only ever add volunteer/panel affiliations by the Panel/Volunteer table - and to do so I should add each panel/volunteer affiliation as an individual row and never via multi-select? And that way the “Volunteers”/“Panels” tables work for display purposes only and for the source of truth for the volunteer and panel names?

Thanks!

Trying to figure out my own ELI5 for this:

  • Tables are the “home” for their display column data.
  • Lookup fields bring data from other tables into a second table and orient the data around a different variable. This adds another dimension to a piece of data than just how it relates to the display column in the original table.
  • A third table can bring together two types of data that relate to each other and orient this data around the relationship itself. Thus there can be a dimension of data around the relationship itself.

So e.g. there are People, Panels and People/Panels Relationship (P/PR) tables. All info relevant to People and Panels belong in their respective tables, but the P/PR table is a “bridge” where the connection is made between those other two tables. This allows metadata to exist for each relationship, rather than there just establishing that there is a relationship. Also, for some reason, it lets you add data to those other tables more easily than if you didn’t use a third table.

Am I getting this?

Yes basically

I wouldn’t say you should never use multiselect.
If it is something relatively simple, and you are no planning on getting info from those relations is better to keep it simple and use a multiselect.
I’m not very good at explaining, so I’ll give another example on when to use a table :sweat_smile: .
Imagine that instead of volunteers you have a employees and instead of Panels you have something like Status (Hired, Fired, Quitted).
If you wanted to know in a given date if a person was employed, with the help of the third table (and a date column) you could make a few calculations to determine that.

Not necessarily. It can help you to optimize other calculations. If you find yourself creating a Panels filter that matches a certain Volunteer + other conditions over and over, you can directly reference the Volunteer.Panels and from there filter to add your other conditions. In this post it’s deeply explained how to optimize your formulas.

That’s it!, you got it!

3 Likes