Base/detail: Creating dashboards with formulas

We’re simplifying large datasets in part three of our schema series.

Explore this schema as an interactive doc here.

In our exploration of doc structure, you’ve built One Big Table. You’ve transformed that table into a constellation of connected tables. And at this point, you’re probably giving more thought to data efficiency.

When it comes to designing your doc, it’s helpful to think of efficiency from your perspective. As we’ve said before, more data is better until it isn’t. With every doc you create, think about what structure gives you the minimal amount of data you need to make a decision and to understand at a glance.

Today’s schema, base/detail, aggregates data points to help you deliver concise summaries and charts that speed up your workflow. And saves you the mental energy of trying to absorb an entire spreadsheets worth of data analysis.

Base/detail schema

Like star schema, base/detail can evolve from your One Big Table (OBT). When data in your OBT starts to become unmanageable, you might want a macro-level view of trends — this is when you can leverage base/detail schema.

Choose this schema when:

• You have a large dataset that you need to summarize by attributes.
• You want an understanding of progress across different dimensions.
• You need aggregate data, like tasks per project.

Learn more about base/detail schema in this episode of Designing Docs:

Uncover data trends with formulas.

Turning your OBT into base/detail should feel familiar. You’ll convert a single column of your OBT into a smaller table, very much like star schema. But then we’ll fill out our detail table using formulas.

Let’s imagine that you want to see how many tasks per project are complete.

1. Convert your Project column into a table, making it a lookup column.
2. Add a column to your new Project table.
3. Add a formula using thisRow to calculate the total tasks completed.

Note: You will almost always use Filter() to summarize your data. For example:

• .Filter() will show all related rows.
• .Filter().[currency column].sum() will show outstanding payments.

Using this same formula structure, you can add more to the filter to further define what you want to aggregate. For example, find tasks with Status=Done by adding a new column to the Complete projects table above and expanding our original formula to:

[All Tasks].Filter(Project=thisRow AND Status=”Done”).Count()

Keep in mind, this is just the beginning of formula options available to you. You can calculate averages, sums, and more. As long as you’re using thisRow and a lookup column, Coda connects the tables together.

Go beyond the numbers.

If you want to go even further with this schema, you can aggregate other information types as well. Let’s say you want to add a Resources table to our previous example to help you track materials required for each project.

To do this, create a Resources table, and then create a Project column in that table. Change the Project column to a lookup, and choose your Projects table as the reference table. Now created and assigned resources will appear in your Projects table, just like your tasks.

A bit of inspiration.

Take a look at this schema in the wild:

Spotify's Squad Goals Doc

Uber's App Redesign Project Doc

Travel Review System

Project Task and Resource Management

And some extra credit.

If you want a comprehensive overview of an entire project, you can use a table’s detail view to show both tasks and resources as subtables inside each project. The trick is learning when a formula returns a full row’s data and when it returns the data from only one column in that row. Let’s take the formula we used above as an example:

[All Tasks].Filter(Project=thisRow).Count()

• [All Tasks] tells Coda to look in the All Tasks table.
• Filter(Project=thisRow) tells Coda to filter the All Tasks table to only return rows where the Project is equal to the Project in that row.
• Count() counts how many rows match the filter above and results in a number.

Let’s leave the Count() part off of the end of the formula:

[All Tasks].Filter(Project=thisRow)

The returned results are the actual rows from the All Tasks table, which Coda can read as their own table. If you change the Projects table layout to detail view and then customize the layout, you’ll have an option to show this particular column as its own table — then you can easily see a breakdown of related resource statuses, etc.

Thanks to the authors of this post: Ben Lee, Maria Marquis, and Hannah Rochau.