Aggregating rows from multiple databases

1

Hello there!

Before asking for help with my problem, let me give some context:

CONTEXT
I manage a team of 20+ people where each person works in specific projects independently.

I’ve been exploring Coda as replacement of the project management tool that we are currently using to organise (store files, docs, notes) and keep track (status, deadlines, resources) of our projects.

I was able to implement - and even improve - pretty much everything that we need in such a tool in order to make this migration, but there’s one thing that I still couldn’t figure out - and the main reason why I’m considering changing to a different tool:

I want to give my team more visibility of what everyone is working on.

PROBLEM
The way I’m organising my database is:

  • PROJECTS - Here is where we’ll create all of our projects, since they all have the same properties/columns.

    • GOALS - Inside each Project there’s a Goals database where people can add the Goals for each Project.

      • TASKS - Inside each Goal there’s a Tasks database where people can add the Tasks for each Goal.

I want to create an independent database that only shows the Tasks that are “in progress” - independent of the Goal and Project it’s related to.

I added an image below to make it easier to understand.

Tasks aggregator rational
Tasks aggregator rational1920×1060 109 KB

I saw some similar questions here in the forum, but they didn’t work for my situation.

QUESTION
Does anyone know if what I’m trying to do is possible in Coda?

My team creates new projects every other week, so this solution would have to be as automated as possible. I tried the “Merge table pack” but it requires me to keep linking new tables to it, which wouldn’t be ideal.

Thanks so much in advance!

2

Hi Heitor,

Welcome to the Coda community!

Generally, fewer tables are better (As always there are exceptions) but Coda is not a database where you want or need to go to 3rd normal form.

I am at the extreme end of reducing tables. I would suggest you look at a single table with all the tasks. There is a column on the table to link to projects, another to goals and maybe one for the responsible person.

Then it is just a case of views with filters:

  • a page for each project filtered with all entries for that project.
  • If a person works on multiple projects, you can then create a page and a view, with all of the tasks from all projects and all goals that he needs to work on.
  • Whatever other view you need. Need an issue list? Add a checkbox “Issue?”, and create a view filter on that, and you immediately have full visibility on where people need help.

Regards
Piet

1 Like
3

Yeah the biggest power of Coda is having multiple views of the same underlying database, filtered to only show certain entries, and only showing certain columns. Most of my tables in project management docs like this have dozens of columns, but they’re never seen all together in any one view.

Another tip: if you have a column for the person responsible for the project, you can have a page with a table filtered just to Person=User() which shows only the current user’s projects

1 Like
4

Thank you so much for your reply @Piet_Strydom and @Nick_HE!

Unfortunately it seems that Coda might not be the solution that I’m looking for.

5

I’m curious as to why? Is it related to securely controlling access to other records? Working with either 1 or multiple docs?

The structure you drew would be uncommon in any relational database system I think. It makes sense that you would want a view of ongoing tasks, but if I were designing this in a traditional MySQL database for example, it would still be the same architecture:

  • Projects Table
  • Goals Table
    • Relation field to Projects Table, to say which project each goal belongs to
  • Tasks Table
    • Relation field to Goals Table, to say which goal each task belongs to

Everything after that is just queries (in MySQL land) or filters (in Coda land), to create the end user experience that feels like it’s structured the way you drew it (to be clear, nobody ever actually looks at the giant Tasks table containing all Tasks in the universe - you hide that unfiltered view).

I’ve got no skin in the game here, I’m just a fan, but I still think Coda might serve you :slight_smile:

2 Likes
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.