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?
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:
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).
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.
Thank you for your suggestions, @Paul_Danyliuk!
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.
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.
It’s probably not a practical solution, but I tried to create a button that can solve my initial problem/question. And succeeded! 
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()))
))
))
)Здравствуйте Андрей!
Вы бы не могли скинуть пример (расшарить) такого решения (таблицы) очень интересно как это реализовано?
Спасибо!
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…
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 
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.
@Paul_Danyliuk one question
when you say “database design” I assume you are referring to DocumentDB (ie, NoSql) and not RDBMs?
Most of the issues I am hitting seem to be due to denormalization (hard habit to break after many, many decades) but even good NoSql design uses references in some cases (for example, large payload which rarely is needed in addition to the primary doc)
Just trying to get a handle on what the design philosophies are from a data side
PS, I do appreciate your videos, but still have the data design questions in general
If you’re referring to my quote on “two tables = two different entities”, I meant that it’s how it’s usually done. Doesn’t matter if that’s SQL or NoSQL; that is the norm: different kinds of data get stored in different tables/collections/etc.
Coda tables are actually closer to relational databases, it seems. Compared to SQL, the only few prominent differences are:
no need to specify relations by primary keys (IDs etc) — you can just select related rows as references in Relation columns (either single-select or multi-select ones). Each row already has an implicit primary key in the form of i-xxxxxxxxxx and each reference is basically a {"type":"ref", "objectId":"grid-yyyyyyyyyy", "identifier":"i-xxxxxxxxxx"} (grid meaning table)
no strict schema — that’s why it kinda resembles NoSQL — but the schema is still there. Yeah, column types are barely enforced and it’s possible to insert any data into any cell (afaik only numeric and date/time columns will attempt converting inserted data into their respective types) but you still should strive to set up column types correctly and place appropriate data into them for your own good. Think of Coda’s schema as similar to SQLite one, with more of type affinities rather than hard constraints.
cells can contain structures (Lists, Objects, Lists of Lists etc) and not just primitives. Particularly the fact that they can contain lists of items or references makes simple many-to-many scenarios simpler: you don’t need junction tables to e.g. link Posts to Tags unless you need to store extra data (e.g. who applied which tag and when) with each such association; you can just multi-select Tags in each Post. That said, Coda only works well with lists and not so much with objects (except objects-with-schema loaded in sync tables) so if you need to store anything nested it’s still better to normalize into a separate table.
and there is no “query”, no joins etc — you link and reference all the data you want to display in your views already as you’re setting up your tables. E.g., wanna display the project’s name next to each task — you make a column for it that dereferences thisRow.Project.Name. This is not exactly denormalization though: you’re not duplicating the data — it’s formulaic and is read from the linked project in realtime.
The second item in my wish list for Coda is to be able to enforce data type.
First one is a better mobile app.
Do you mean “principles of Coda database design”? Principles of actual database design rely heavily on the ability to query data from multiple tables into one.