How Stuff Works

Data Relationships

May 31, 2021

Relationships are Complicated

and How Much I spent on Starbucks Last Month

Relationships are complicated. We all know that. But they also hold everything in our lives together! In a database like Airtable, they're just as important. Data relationships allow you to capture and view information much more effectively than by using a typical spreadsheet like Excel.

The other day, I decided to go over my monthly expenses. And because I'm a nerd, I actually did it twice, once in Excel and once in Airtable. In this day and age, it has become common (thankfully) for credit card companies to allow you to download your transaction data. Downloaded as a .csv and opened in Excel, it looks like this:

Ok, I didn't actually go to Starbucks. I'm more of a smoothie guy. But I wanted to know how much I spent on prepared food items like smoothies, how much I spent on groceries, gas, etc. In Excel, this can be achieved by using a SUMIF formula. Basically, you ask Excel to sum items that meet certain criteria. In this example, my credit card company has nicely categorized my expenses already. So I asked Excel to sum the transactions where the category is "Groceries," and for each of the other categories. "Luxury" food spending turned out ok... green light for smoothies in July!

In Airtable, I'd make the same table:

Then I'd change the field type of "Category" to "Link to another Record", and choose "Create a new table", then hit "Save".

The new table looks like this:

As you can see, it knows which category each transaction is in, because they're linked. Once we've done this, we can easily summarize the totals for ALL of the categories, rather than doing them one-at-a-time in Excel. This is similar to an Excel SUMIF formula, and in Airtable it's called a Rollup. To make it, create a new field, make it a "Rollup" type, and then follow the prompts to specify that you're rolling up the transaction amounts from the expenses table, and that you want to sum the results. You could also choose to average, or show the most expensive or least expensive transaction (MAX and MIN), or use many other formulas.

Here's the result:

And that's it! Data relationships have saved me a million COUNTIF, SUMIF, INDEX(MATCH)), AND VLOOKUPs. Once you have the hang of it, it's more intuitive to sum values this way, and much easier to sort and filter the data. Happy linking!