Are You Using Airtable? Some Things To Know

I've said it before, Airtable is amazing!

Amazing good! Amazing bad!

I wrote a piece several years ago title, Airtable, The Good, The Bad, and the Ugly... and I mean, really Ugly.

Note: Don't shoot me, I'm only the messenger.

First... I'm an Airtable Consultant. I've set up and work with a number of clients that get great benefit from Airtable. I don't HATE Airtable. I quite like it. I use it myself for tracking consulting time and also for tracking music venues and gigs.

If you are using Airtable or wish to, I want to share a few things you should know.

Spreadsheets are NOT Databases

But Airtable is, in fact, a database. You need to know this.

What makes Airtable so appealing is that it looks like, and in many ways functions like, a Spreadsheet.

That's good. It makes it easy to understand. Easy to adopt.

Businesses use Excel or Google Sheets as make-shift databases every day. Hell, even I do. In a pinch, it makes sense.

That's bad! Spreadsheets lead to horrible data design.

The first task I have when I work with a company using Airtable is to fix the horrible (the less than optimal) database schema. This is true 100% of the time.

When a database looks and feels like a spreadsheet, it is treated like a spreadsheet.

Data design, relationship and normalization (and when to break those rules), is a skill that takes time.

Having designed relational databases since 1991, dBase III+, and then into Sybase, SQL Server, etc., it is nearly second nature to me. I truly think in terms of relational design.

Most people building their first Airtable database are unfamiliar with the concepts and practice of data design. They are utilitarian in their usage.

So, you generally end up with numerous columns in a single sheet (table) extending to the right, to track information that should be in a related table.

Fixing this is time-consuming.

If you are using Airtable and your only experience with database design is the Airtable guide to setting up relationships, you've probably got a little bit of mess on your hands.

It will cause challenges with reporting and simplified automations.

Quick tip for better data design:

Write up - with pen or pencil - a few mocked up reports. What does your data need to tell you? That is one of the best ways to see data relationships. What data elements relate to and are subordinate or contained in other data elements.

That is one of the first places I, and many, database developers start.

Reports reveal design.

Views: Incomplete Data is Lost Data

Airtable allows you to create views. Views are different "views" of the same data. You can filter and group your data, hide and reveal different fields, in specific views.

But there are some insidious challenges with views.

First, views have the tendency to replicate and quickly become a quagmire of confusing data. I've come into organizations where a single table has 50 to 100 views with little to no standardization.

If not careful, data and entire records are overlooked. Add to this, no applied naming convention, and the database quickly ends up with incomplete and lost data.

3 Quick tips to avoid lost and/or bad data:

First: create a checksum formula field. This can get complex but with some thought, a formula that checks key elements of your data to ensure completeness and compliance will save you many headaches.

Second: create one view that has all records in reverse modified order. It becomes a rolling log of newly created or modified records. Orphaned records become easy to uncover. Make your checksum field the first field (after the key field) in that view.

Third: create a checksum error view. This view, also in reverse modified order, will only show records where the checksum formula indicates a challenge with the data.

Depending upon your utilization, check these views daily - or twice daily. If your organization is large enough, you may assign this checking to those you deem data administrators.

This will help you uncover repeated challenges and help you better train and direct your users.

Interfaces: Do Not Use Views for Data Entry

Matt, you just spent an entire section telling us how to better use views. Now you are saying don't use them.

Don't use them for data entry.

Airtable's Interface Designer has some significant challenges but short of building third party interfaces to your data, it is the tool you have.

Plus, if properly designed, you do a lot of things to guide users in their data input and their workflow. From links to specific pages for specific records, creating new records, and creating automation and scripting, a well-designed set of interfaces will go a long way to helping your organization avoid the proliferation of views.

Additionally, the interface feels like an application. Your users will not be scrolling left and right on a "spreadsheet" looking for the field they need to enter data into.

Reporting

Okay... not going to lie. Airtable's reporting is miserable. Almost unusable.

You can create rolled up sheets, properly filtered and grouped. it can give you MOST of what you need.

But their add-on dashboard-esque tools are slow and unintuitive.

Many/most of my clients are using Airtable but also using Google Workspace (Google Sheets and Docs).

For those clients, I port the Airtable data into Google's BigQuery - a SQL-based data warehouse.

From that, I can provide dashboards in Google Looker (Data Studio), Tableau, or Power BI. We also generate ad-hoc reports in Google Sheets and Docs - creating automated report distribution - sent via email as needed.

This method is MUCH faster and robust than anything Airtable provides.

Again, you can use grouped and filtered views for many things. Just not effective dashboards.

I do provide a number of Airtable automation scripts that generate daily emails to management with links to specific Airtable Interface records. That is certainly doable natively in Airtable.

Conclusion

I'll reiterate. I love Airtable. You can have a cloud-based relational database up and running in a few hours. With some planning and proper design, you can build a powerful business application, in a fraction of the time it would take with more full-featured application development products.

That system could serve a growing business or departments in larger organizations, for years.

Add to it some of the free and low-cost tools that integrate with Airtable, and you may find it is all you need.

I just try to ensure my client's do this with open eyes.

With Airtable, you win some, you lose some! That's the world of no-code/low-code.

But the truth is, that's the case with any database or software development tool.