Spreadsheet versus Database

Explore

Spreadsheet versus Database

How to switch from spreadsheets to databases

Hello, Database!

Setting up your database

Nina Kastenauer

Settings

Comparison of a simple formula in Sheets vs Coda

⁠

Step 1) Define your Database Schema

For this process, we’ll be modelling the “Star” Schema (also called Web-Schema).

We will have two base tables (Projects and Tasks) and

both of these will relate to each other (one project will have several tasks associated with it, each task will be assigned to a project), and

each of these will look-up into static parameters (team members, client, status).

Step 2) Prepare your Coda doc

Open up a new Coda doc (hint: just type

Coda.new⁠

into your browser’s address bar).

Create a page and call it “Settings”, or Admin, or Blueberry - no judgment here ;-)

Step 3) Set up tables for your “static” data

Think about which data will remain mostly the same for this process.

In our example, that will probably be

Team Members (Abby, Bertha, Charlie, Debby)

Clients (Company A, Company B) and

Status (Not yet started, in progress, needs approval, done)

Note that it is not that this data cannot or will not change (Abby might move to a new role, we might win a new client Company C).

⁠

Settings⁠

⁠

Step 4) Set up tables for your “dynamic” data

This is where you store your operational data, in our case one table for “Projects” and one for “Tasks”

You can now copy the data from the spreadsheet “Prepare for export - Projects” and paste it into the table “Projects” and proceed accordingly for Tasks.

Next up, revise the column formats as necessary. Feel free to check out how I have set these up here

Settings⁠

⁠

One more step that’s essential, is to make sure that the two tables are synced bi-directionally. That means, we not only want to see which project a task belongs to, but we also want to see which tasks are related to each project. To accomplish this, we’ll insert a column in the Projects table, that we’ll call “Related Tasks”. We’ll set it to be type formula and will set this as follows:

Tasks.filter([Project Name]=thisRow)

(ie “Hi Coda, please go to the Tasks table, and filter out those items, were the project name equals this row’s project name”. Because we’re fine with Coda returning the display column of the Tasks table (ie Task Name), we don’t need to specify which column Coda shall return. If we’d prefer a different column, we’d simply add eg .Task Owner after the closing parenthesis.

We’ll set up conditional coloring to make the data be more intuitively understandable. For this, we’ll assign colors to the individual status.

Step 5) Create Views

Now that the housekeeping has been taken care of, it’s time to get started on the good stuff.

Remember how we said that it’s a downside of spreadsheets that the data is always presented in the same way? Well, not anymore!

We’ll start by setting up one view for Charlie. We’ll reference the base table Projects, choose layout “Detail” and filter it to only show those projects where the project owner is Charlie.

⁠

Charlie's View⁠

Next up, we’ll set up a page for the Team Dashboard. This time, we’ll reference the base table Tasks, choose layout “Cards”, and group it by “Status”.

⁠

Team Dashboard⁠

As we’ll want to make sure work is split evenly across our team members, we’ll bring in a chart view to visualize the current allocation. We’ll reference the base table Tasks, choose layout “Charts” and set up a pie chart by Team members.

Step 6) Sky’s the limit

And that’s a wrap 🎉. We’ve successfully moved our spreadsheet data into Coda. And now, we can unlock the real magic by making use of:

Attachments

A spreadsheet limits you to text or numbers. A database know no such limitations. Attach a file, embed a picture, show a slider, ask for a thumbs-up, etc.

Automations

For example: every time a task status is changed to “Ready for approval”, the corresponding manager will automatically be pinged in Slack, and asked to review the content by clicking on a link.

Button Actions

When the managers opens the link, they will be able to share their approval by pushing a button. That button action will automatically inform the task owner that the task has been approved, and the cascade continues.

Native Integrations

Bring in a Google Drive Folder containing all of a client’s files, to attach these to a project or task.

External Integrations (Packs)

Connect to a separate tool that is being used (for example a third party CRM tool like Copper). Integrate this into the Coda doc to operate out of a one single interface (no more switching between 7 browser tabs!)

Templates

Save it as a template. Next time you’ll have comparable use case, you won’t need to re-build everything from scratch. Rather, you can hit the ground running!

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.