How to display rows from 2 different tables in one table?

1

I have two tables: Projects and Tasks. Each of these two table have a lookup column with values from table Tags.

I’d like to make a table that will display all the rows from Projects and Tasks tables so I can group them by Tags column and by Type (Project or Task).

Is that possible?

2

You cannot combine different tables in Coda because it’s fundamentally wrong. Even if columns are the same, two separate tables are two entirely different concepts, according to the principles of database design.

What you can do:

  1. Look up all projects and all tasks for a tag into a Tags table. Then build a Details view on top of this: tags as navigation groups on the left, tasks and projects subtables on the right (in the layout itself).

  2. If projects and tasks are essentially the same thing in your system (i.e. a Project is just a task that has subtasks), then make it a unified table of both projects and tasks and build a grouped view on top of that.

2 Likes
3

Thank you for your suggestions, @Paul_Danyliuk!

  1. I understand how to use your first solution, but it’s not ideal, because I have different types of tags (Normal Tag, Area Tag etc), and it’s impossible to group tags by type first in the Details view. Yes, I understand that I can make separate views using filter for different tag types. But the second problem with the Details view is that it’s too narrow to display more then one-two columns from the lookup tables.

  2. My tasks and projects are more or less the same thing, but not really. I also have a third level of organization above projects (initiatives) and settled with 3 different tables for initiatives, projects and tasks. I didn’t find a solution that uses one table and is intuitive to use. Maybe you can point me in the right direction for a project management template with many levels of organization that uses one table.

4

It’s probably not a practical solution, but I tried to create a button that can solve my initial problem/question. And succeeded! :tada: As I said, it’s probably a stupid way to do things in Coda, but I did it as a learning exercise. Posting here the code in case someone will find it interesting.

This button will populate [Tags table - Projects and Tasks - Split] table with of all projects and tasks that have tags, pulling them from Projects and Tasks tables.

[Tags table - Projects and Tasks - Split] table has columns Tag / Project or Task / Type.

The button puts every combination of Task or Project with unique Tag on separate rows (for example, if Task 1 has two Tags, in the final table there will be two rows with Task 1 for each Tag it has).

Button formula is using intermediate table [Tags table - Projects and Tasks] to copy all the Tasks and Projects with Tags first, then splits it to the final table.

Button formula:

RunActions(

[Tags table - Projects and Tasks - Split].DeleteRows(),

RunActions(

Projects.Filter(Tags.IsNotBlank()).FormulaMap(
AddRow(
[Tags table - Projects and Tasks],

[Tags table - Projects and Tasks].Tag,CurrentValue.Tags,

[Tags table - Projects and Tasks].[Project or Task], CurrentValue,

[Tags table - Projects and Tasks].Type, "Project")),

Tasks.Filter(Tags.IsNotBlank()).FormulaMap(
AddRow([Tags table - Projects and Tasks],
[Tags table - Projects and Tasks].Tag, CurrentValue.Tags,
[Tags table - Projects and Tasks].[Project or Task], CurrentValue,
[Tags table - Projects and Tasks].Type, "Task"))
),

Sequence(1, [Tags table - Projects and Tasks].[Number of Tags].Max()).FormulaMap(
RunActions(

[Tags table - Projects and Tasks].FormulaMap(RunActions(
AddRow([Tags table - Projects and Tasks - Split],

[Tags table - Projects and Tasks - Split].Tag,CurrentValue.Tag.Nth(1),

[Tags table - Projects and Tasks - Split].[Project or Task], CurrentValue.[Project or Task].Nth(1),

[Tags table - Projects and Tasks - Split].Type, CurrentValue.Type),

ModifyRows(CurrentValue, Tag, Tag.Slice(2, CurrentValue.Tag.Count())),

DeleteRows([Tags table - Projects and Tasks].Filter(Tag.isBlank()))

))
))
)
1 Like
5

@Andrei_Kharlanov

Здравствуйте Андрей!

Вы бы не могли скинуть пример (расшарить) такого решения (таблицы) очень интересно как это реализовано?

Спасибо!

6

I believe it would be a good idea to have a general set of “principles of database design” list somewhere, so those of us who are not experts in databases can start with a better table(s) arrangement as well as having more reasonable expectations from the system we’re trying to build. I started to feel many (if not most) of our problems arise from starting with a wrong type of database / table setup and trying to solve these fundemental problems by workarounds…

1 Like
7

The closest to that is perhaps these old series by yours truly.

Subscribe to that youtube channel btw; it’s getting seriously relaunched sometime soon by the end of this year already :wink:

8

These series are great. I highly recommend them.
I’ve rebuilt most of my docs following the principles and I’m very pleased with the results.

1 Like