Airtable Database Systems: The Good, The Bad, and the Ugly (really ugly)

I've begun consulting organizations on using Airtable for tracking company information.

Airtable provides an amazing platform for moving away from Spreadsheets (Excel or Google sheets) as a way of tracking company workflow and data. It is a rudimentary relational database system that looks and feels like a spreadsheet. As a stop-gap, proof-of-concept, or even the actual system used by small to mid-sized organizations, it works well. A fairly robust system can be implemented in a few short weeks.

While it does provide a way to implement better data tracking without the full-scale development of customized software, it has a number of problematic limitations to work around.

This entry is meant to provide some of the benefits and challenges with Airtable. I'll also outline some of the advanced solutions that can be built using the Airtable API.

The Good

Feels like a spreadsheet

Okay... I'll be candid. This is good only because so many small businesses manage aspects of their business using Spreadsheets - so this will be listed under, "The Bad" as well.

But, with very little trouble, a basic tracking system can be setup. It beats a spreadsheet in that it has distinct data types, allows for attachments, and provides rudimentary formatting for those data types.

Allows for data relationships

This is the primary selling point for Airtable. Relational data is critical for ensuring a degree of data integrity and allowing you to properly report on business operations.

Airtable's function for relationships is defining a field as "Linking" to another table. Once completed, the only way to enter data into the field is by selecting or creating a record in the linked table.

While simple, the truth is, without proper knowledge of relational data, I've run into some "interesting" designs.

Provides row/record logging

Airtable provides a basic logging at a row level. Depending upon your plan, you can view a few weeks of logging or up to a year. Being able to see what user changed what field in a row is helpful - and necessary when you understand that Airtable does NOT provide a way to use forms to edit data or control workflow. (see below).

Allows saved custom data views

Critical to allowing some level of workflow control is Airtable's ability to create custom views. These views can be user specific, locked down, or open to all users. You can create filters, groupings, sorting, and hide, show, and re-order fields as necessary based on the function.

You can also create special views that can be shared via link.

Has an API for more robust automation

Airtable is an integration partner with Zapier and with integromat. But provide decent built-in tools for automating aspects of airtable - creating data as need, sending custom emails, moving data between tables and bases (Airtable databases) or even moving data to other systems like Google Sheets, Salesforce, etc.

Look below for "challenges" (putting it lightly) with the Airtable API.

The Bad

Feels like a spreadsheet

This means that bad table/data design abounds. Companies using spreadsheets to control operations and store data do not typically understand good data design. For this reason, I've run into nearly disastrous data schemas (the table design and relationships). Often, I spend more time fixing data than we do on the data design.

Limited spreadsheet style functions

While many of your basic spreadsheet functions exist, I am more surprised by the functions it does not have. Specifically, effective text parsing and numeric formatting functions. This can prove problematic when presenting data to both users and to clients.

Poorly implemented security/logins

Managing users is almost incoherent. There is no ability to create a user - defining their login name, email address, password, and access. Instead, you invite people to the database. I want the ability to setup users in a single interface, without having to go through a setup process for each users.

Also, there are no security groups. This is a painful deficit.

The Ugly

No API Logging or User-level Security

Okay - this one almost makes me quit Airtable daily. It freaks me out. I can't get a clear idea if the Airtable team even understand the problem they have here.

Every user has their own API key. The access provided is the same as the user. I have no ability, as an admin, to disable a user's API or audit when/if/what that API key is being used for.

Besides the obvious, someone could be sending company data to an external source type of issue, simply workflow control is also an issue.

What if a user sets up some Zapier automation but we change critical elements of the data. Perhaps their automation continues working but is creating bad data. Furthermore, what if the organization begins relying on that automation - though they have no documented idea why it is happening or with whose account.

The user who set it up leaves the company, their account is disabled/removed, and voila - the automation goes away.

If anyone from Airtable reads this... PLEASE FIX THIS! I have clients who cannot legally user your service without the ability to disable and audit any API access.

No forms for workflow or data entry

This is a surprising one to me. They do have rudimentary forms for data entry. But they have no ability to create a system of forms that check data, open existing records, and control the workflow of data entry.

So rather than a few simple forms controlling how clients enter data, I create a series of locked views that show bad data. I connect these to Zapier to provide reporting and notifications to allow clients to fix that data - or at least be aware of it.

Conclusion

Their API uses Javascript. I've been asking their support and development teams to allow us to connect javascript to field and row events. This alone would be a POWERFUL tool to ensure data is entered correctly and provide users a better experience with the product.

And even with the deficits, Airtable provides an amazing tool for tracking data and operations. It just requires some forethought and training to ensure you get out of it what you need.

GIGO (Garbage In, Garbage Out) is a critical warning here.

If you have additional questions about Airtable or its automation, feel free to contact me.

Posted in Consulting, Services and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *