Connecting Lookup Tables via Join Row?

Hi again,

I am trying to connect a table with projects, and with tasks, with another third table called “meetings.”

What I’d like to do is in the table called “Meetings,” create a look up to the tasks table, and a lookup to the projects table. Then, if I want to add tasks to the Project via the “Meetings” table, have the tasks associate to the project I select with that lookup. Something like this:

So what I’m hoping to accomplish is that in the respective project and tasks lists, I can associate each to each other if I choose them both in the Meeting Notes Row.

Thanks guys!

Oh and hey, I’d also be interested to find out how this could vary if the Projects and Tasks were actually in the same table, Task being a SubTask of a Project.

Thanks again!

Hi @ABp,

I played around with a Project Management template and also wanted to include Meeting Notes and Resources and Links. The Meeting Timer is also included in this for fun…and it’s easier for someone to remove it than to put it in. So this is likely overkill for most, but something to pick apart.

I wanted to try various schemas for setting things up and landed on having each entity separate and found that I could include other entities with the same pattern if I needed to. So the Projects table is it’s own thing, then the other tables use it as a lookup to tie everything together. So Meetings, Resources, and Tasks, can all be Project specific.

1 Like

Hey @BenLee, thanks for posting that. Very cool doc, it’s impressive.

I wanted to ask though as I am simply trying to figure out whether the join table I was using could be set up, maybe with a formula or automation? so that the associations between Project and Task will get made in those respective tables.

So if you add into a Meeting Agenda both a project and a task, they are associate to each other automatically. If you look in the sample doc, even though I have in the Meeting Agenda both a project, and some tasks, and those tables are already connected by lookup, the Project #1 doesn’t show the Tasks as relating.

Hope that’s clear, and thanks

Hey @BenLee, would you be able to help me with my basic question here? Was it clearer from my response? Also wanted to ping my friends @Federico_Stefanato and @Jean_Pierre_Traets - in case Ben doesn’t have time to get to this.

And Ben, this is related to my question here: Row Details is not always the same if accessed from different areas in Doc. I’m a bit short on time right now, but I will post there further detail soon. What I mean there is that I am seeing different views of a row that are set up in the same Layouts you are demoing how to change. The issue is with how a subtable is displaying: In one case, I can see a subtable, but when I access the row from another location, that same column will not show as a subtable. Will send over a screenshot of what I mean soon!

Thanks guys.

Click on that same menu I showed a screenshot of and edit the layout. There should be a toggle to show a table view of that column.

So Ben thanks for that response, I posted back to you in the original thread. But getting back to this thread, to review I’d like simply to be able to associate with the join table two respective other tables’ items, tables that also have lookups to each other.

I tried out your doc a bit. I’m not sure if it was supposed to have this handling, but to illustrate what I’m getting at: If I add on the “Projects” page a Task, and I add an Idea, assuming those are also joined by lookups, I’d like to see the Idea on the Tasks page, and also include the reference that it got added to a task via the Project page.

Hope that’s clear, thanks!

You’re looking to add another level of complexity with multiple assignments and lookups here. It can be done, but again, it’s added complexity.

I posted my doc to show that everything is a lookup to a Project. So all arrows only point to one core lookup.

If you want to have Ideas include a lookup for Tasks, then you might also want those ideas to have a lookup for Projects. This way you can lookup a project to assign the idea to, then you can filter down the tasks to only those associated with the chosen Project. Otherwise, your tasks will likely grow to a very large number and become difficult to choose from.

For me, I decided that dialing in to the Project was where I wanted to limit the complexity.

OK Ben, thanks for that insight. I will work on this. In this case, I am trying to set up this arrangement as it’s a particular need I have to be able to show if I have a Project which may contain say 15 tasks for completions, that some of those tasks got into the project from a particular “source,” and the way I’d show that source is with the join table I’m speaking of.

Thanks again.

The trick is to know where you want to edit things because Coda only supports “one way” editable relationships. I.e.: in one table you can edit things, in the other you can merely display what was edited on the other side.

The second piece of the puzzle is that if you go into detailed view, you can view a lookup column as a table. Combined with the fact that you can setup a Column with a filter - and all new entries in that table will be created with the filter, will allow you to create Tasks in the Meetings table, which will automatically have references back to the Meeting & the Project.

Downsides:

  • You can only edit columns from certain perspectives
  • All these lookups will become very expensive relatively soon.
1 Like

Hey @GJ_Roelofs thank you very much, that’s what I was looking for!

I’ve played with this now, and sure enough, I am seeing the issue you have discussed around the community of needing to move to Detail view to be able to add into the “Task” area inside the Project:

In this default view of the “Coda” project:

I cannot add tasks

But if I switch to “detail” view and open up the subtable, viola, I can add the tasks:

And back in the main task table, I can see the tasks that came from that meeting, and the ones that were added outside (with no meeting reference):

Also pinging @Federico_Stefanato who has offered to help me as this is now solved.

Thanks again GJ and I will be following your posts even more closely now, really shed light on a huge blocker for me!

Hey @GJ_Roelofs (and @Federico_Stefanato) , sorry to keep at you, but just wanted to point something I assume you’re aware of, but may be useful to the community, too:

So I tried to add a 4th table “above” the meetings:

That “Column 2” is a lookup back to the “Meetings Start,” the Master table of Meetings. I have the “Allow Quick Adding of Items” selected right here.

But when I went in and tried to set up that Subtable again in Layouts in “details,” I’m only offered a dropdown this time:

Or so I thought. I couldn’t figure this out till I turned on “adding of new items” here in “Item Settings”:

And then the subtable option appeared:

You mentioned that you should set these up for Multiple selection, which I did in the “top part” of the format in “Allow Multiple Selections”, but turns out I needed to also open the “Item Settings” also. This didn’t seem obvious to me, and I’m not aware of this being documented anywhere, so again thought it would be useful to post about it. I found this a bit confusing because the terminology “Item” is not commonly used in Coda, rather i’m used to terms such as “Rows,” “Columns,” and at times “Objects.” Subtables are about my favorite feature of Coda so really appreciate you helping me master all these little subtleties!

Thanks again.

2 Likes

Exactly,
Because of the detail view limitation I added in the Button column - you can use it (if you set up the action to open the new row) to create new tasks from other perspectives.

1 Like

Hey, thanks again yes the button was very useful. That was yet another level of complexity for me, but I enjoyed seeing it in action.

Cheers!