Learning Airtable

Chapter 1. Introducing Airtable

Airtable is a tool for builders of all kinds. Like other tools in the no-code movement, Airtable democratizes the ability to create custom applications and software processes. And while there are many no-code solutions offering to widen the tent of software development, there are many reasons why Airtable is a popular choice in the increasingly crowded field.

In this first chapter, we’ll start by looking at how Airtable fills an important niche by combining elements of a spreadsheet with a database. We’ll then look at its primary components and what makes it special in comparison to other options, and then we’ll have a brief primer on how Airtable thinks about, and structures, data.

What Is Airtable?

Both Airtable newcomers and veterans can understandably struggle with how to describe it. As you create your first base, you’ll see the Grid view (shown in Figure 1-1), which looks deceptively like a spreadsheet. While Airtable does share some properties of a spreadsheet, it is a full-fledged relational database with the same underlying principles of organizing data as the desktop standby Microsoft Access or the high-capacity databases that run much of the web, such as MySQL and Postgres. What may look like another tab in a freeform spreadsheet is actually a structured table of data.

Airtable’s Grid view mirrors the visuals of a spreadsheet’s multipurpose columns and rows of cells. Beware: these are not columns and rows of independent cells. Instead, each column represents a specific field that’s defined by the type of data it holds (e.g., URLs, text, and data computed from other fields in that table). What might seem like an interchangeable row of cells actually constitutes a distinct record or entry in your database.

Figure 1-1. In the Grid view, columns represent fields and rows are records.

Specifically, it’s a relational database. Why is that meaningful? A relational database has rules, such as what types of data are in your tables and how one table relates to another. These rules define a structure that can be easily interpreted, which enables your database (be it Airtable or another) to sort, filter, visualize, track, plot, and do many more things with your data.

Because the relational data model abstracts the user interface from the data itself, every user can have a tailored experience that lends itself to collaboration across teams. Airtable enables this with its specialized Views, which can be personalized for a task or user. Its Interfaces feature takes this a step further, enabling the creation of sturdy web portals that can simplify unnecessary complexity and restrict a user’s access to data. In the Automations section of the platform, team members can design code-free logic to manipulate their data (by creating, reading, updating, or deleting it—what is referred to in software development as CRUD), whether for their own tasks or to enhance the workflow of a teammate. Importantly, Airtable allows you to achieve all of this by writing little, if any, actual code. It’s a leader in the no-code movement.

Airtable and the No-Code Movement

For decades, there’s been a simple dichotomy inside organizations. Some people can write code, build products to access, and understand complex datasets. Others are not building software; this group often comprises most people in an organization. As we’ll see later, spreadsheets have been a helpful middle ground between building applications and being dependent on developers. Still, this dichotomy is quickly becoming a fluid spectrum, thanks to no-code tools like Airtable.

No-code tools allow users to build software tools without using a traditional programming language. This can mean having a WYSIWYG builder to create interfaces, designing logic flows using a paradigm of “triggers” and “actions,” or using a user-friendly way to approach relational data. As a result, no-code tools are closing the gap between software users and developers.

This democratization of software development has huge consequences. Colleagues of the dev team no longer need to sheepishly submit feature requests for internal tools. And instead of everyone who isn’t a full-time software developer being a nondeveloper, people can ladder up. They can build their proficiency using tools like Airtable without necessarily taking the plunge into learning a full-fledged programming language. As we will see in the chapters ahead, Airtable offers many entry points to help teams be more efficient by allowing everyone to build their own tools and collaborate more quickly and easily than ever before.

How We Got to Now: Spreadsheets and Databases Collide into Airtable

In the upcoming chapters, we’ll see many reasons why storing your data in a rules-based relational database pays massive dividends in the potential for collaboration, analysis, and ease of use. But for now, it’s helpful to understand how Airtable’s popularity was accelerated not just by being a well-made collaboration tool but also by addressing a significant gap in the market of software tools for everyday users.

The relational database is tried and true. Corporations run enterprise databases from Microsoft and Oracle, while the web is powered by popular open source relational databases such as MySQL and Postgres. There is constant experimentation and boundary-pushing design for new types of databases that can improve performance and handle the world’s mushrooming volume of data, but the relational database has endured.

All the while, spreadsheets have been the average knowledge worker’s tool of choice for organizing data. This is not for no reason. A spreadsheet allows the user to store and manipulate data easily. That might sound elementary, but consider the alternative. Most software decouples those two things: it combines a data storage system and an interface.

It’s a quantum leap forward to have the ability to put data into a spreadsheet and also manipulate it inside of the spreadsheet without having to build a software interface to interact with that data. This has brought huge productivity gains for any nondeveloper by mimicking the functions of database-driven software with the interactive spreadsheet.

Interacting with a database through a web-based application—something we now take for granted—ushered in the so-called Web 2.0 era. Web 2.0 applications were innovative because, for the first time, using a web app essentially mimicked software running on a desktop. It was a bona fide revolution. Salesforce proclaimed “No Software!” as its rallying cry while moving millions of salespeople from CRM systems run on an in-house server to Salesforce in the cloud. Google successfully introduced its suite of office products that run in the browser and has replaced MS Office in many organizations. GitHub created a multibillion-dollar layer of software version control that’s web native. The world shifted to database-powered applications run in the browser.

But a funny thing happened as Web 2.0 began to pull practically every desktop application into a new iteration of itself on the web. The modern office produced invoices in Xero or QuickBooks. A sales team entered its new prospects into Hubspot or Salesforce. Marketing queued up social media posts in HootSuite. But where should we put ideas for conferences to sponsor next year? Where can we make a detailed list of our top competitors and our positioning against them? How can we collaborate on top priorities for retaining talent across departments?

The answer to these questions won’t surprise you. Spreadsheets have been twisted and contorted to look and feel like a database without gaining the efficiencies that databases make possible. For example, spreadsheets don’t allow viewing your data in different configurations based on user or task. They don’t natively enforce rules on which types of data go where, and they don’t innately consider your data to be a collection of things. Instead, they’re flexible to the point of being flimsy. So people weren’t repeatedly choosing the wrong tool per se, but rather they chose the best tool available. It’s a mystery why no one made a credible software-as-a-service relational database until Airtable, but we’ll see what the world was missing.

Airtable’s Primary Components

Let’s get familiar with the components of Airtable as we begin to wrap our minds around the platform. We’ll revisit each component later in the book in more depth. But, for now, having a passing understanding of these will reveal the contours of Airtable capabilities. (As a general note, because Airtable’s interface evolves continually, we will focus more on the platform’s fundamentals and not be overly concerned with the exact shape or placement of buttons and menus.)

Three Sections: Data, Automations, and Interfaces

The table at the beginning of this chapter is an example of a Grid view (Figure 1-1), which initially looks a lot like a spreadsheet. Grid views, and the other six types of views, are part of Airtable’s Data section. For many years, before Automations and the Interface Designer, there were only views in the Data section (though it wasn’t called that—it was just Airtable).

It’s worth getting oriented with Airtable by noticing the three sections atop the page: Data, Automations, and Interfaces, as shown in Figure 1-2. We’ll start by discussing the concepts of bases, records, tables, and more, which are normally configured in the Data section. However, the structure of these elements is one part of what makes Airtable so uniquely powerful to nonprogrammers in Automations and Interfaces.

Figure 1-2. The Data, Automations, and Interfaces sections of Airtable.

Bases, Records, and Tables

In Airtable parlance, databases are called bases. Each entity, or thing, you want to keep track of in an Airtable base is a record, similar to most database systems. So, for example, if you are producing a music festival, you would have a record for each musical artist performing. Likewise, a journalist keeping track of lawsuits would have records for each case, an auto shop might have a record for each customer, and a marketing department might have records to track the blog posts it’s developing.

Airtable bases are comprised of one or more tables, and each table holds a specific type of entity (e.g., blog posts, court cases, and customers). For example, our Airtable base to organize a music festival, shown in Figure 1-3, might have a table of performers, a table of stages, and a table of vendors. Those tables can relate to each other in different ways, depending on the real-world relationships between those types of entities.

Figure 1-3. A base for a music festival, with tables for performers, stages, and vendors.

If you’re already familiar with relational databases, you know that the relationships of these records between different types of entities (represented by tables) give relational databases their flexibility and power. The relationships between records and tables in Airtable mirror these fundamental database concepts.

Fields

Instead of generic columns in a spreadsheet, each of the columns (fields) you see in the Airtable Grid view has a defined type of data. Each field’s type is a simple decision about what kind of data that field should store for each of the records in a table. For example, will each of your records have a date-formatted field to note its start and end dates? Will your base have an attachment field to store a headshot? Or perhaps a field to store a URL for each of the records in your base?

There are field types that calculate the date a record was created or modified. The Formula field is the bedrock of many of Airtable’s superpowers; it has elements of Excel formulas and scripting. Any field you define holds the same data type for all records in that table. For example, if you have a field for email addresses named “manager email,” as shown in Figure 1-4, that column can store an email address for each of the records in that table. These self-imposed constraints of what data fields contain make Views possible.

Figure 1-4. Each field holds a specific type of data.

So every table in a base has records and fields. Each record in a table holds a value for each field, which is contained in a cell in the Grid view.

Views

Since fields define the type of data they hold, this enforced structure allows Airtable to offer purpose-built ways to view and understand the records in your base, as shown in Figure 1-5. For example, the Calendar view can place records on a monthly calendar grid. The Gallery view allows you to view your records as tiles across the page.

As you might guess, the Calendar view uses dates associated with your records. Airtable further leverages date data by allowing you to put your records in the Timeline view, where you can see when the events in your base begin and end. Project managers will be familiar with the Gantt chart, which, in addition to making a timeline from critical dates in your records, allows you to put dependencies between records. For example, you can visualize that item B needs to happen before A or C.

The Form view is a handy way to create simple, web-based forms that can be shared from Airtable with a public or password-protected URL. As with everything Airtable, as we’ll see in the coming chapters, it’s easy to get started building forms, but you can add near infinite levels of complexity as you get more familiar with the platform.

Figure 1-5. The left-hand sidebar gives access to all of the views in your base.

Automations

Airtable Automations is a tool inside the platform that allows you to set up conditions to automate things like creating a new record in a table, updating a record, sending an email, and other actions that rely on connecting with external software platforms. Each automation is defined by the trigger that instigates the automation and the resulting actions. For example, we can specify in an automation that if a record is updated in a particular way, a Slack notification with some select details about that updated record will be sent to a specified channel in our Slack workspace.

Since automations are quick and easy to learn without any coding, they can empower less technical team members to start automating processes inside your bases and processes that connect to other services. Automations currently has native integrations with the Google Workspace suite, GitHub, Facebook pages, Outlook, Microsoft Teams, and more. Depending on the service you’re integrating with, you can create a calendar event once a record has been assigned to a collaborator in your Airtable base, add issues to GitHub when the status of a record changes, and engage with innumerable other scenarios.

If there is a third-party app service you’d like to interact with your data inside of Airtable but it isn’t natively supported, you can connect to external APIs through Airtable scripting. We will cover Automations in Chapter 11. For readers who aren’t software developers, Appendix A offers an overview of what their developers’ counterparts can do with Airtable’s web API and the Airtable software development kit (SDK) for building Airtable Extensions.

Interfaces

While the different types of Airtable views offer common ways to work with data, such as Kanban boards and timelines, Interfaces (shown in Figure 1-6) is a quantum leap forward for the platform. You can create dashboards of your data, choose which fields to show, filter which records show up, and choose which fields to allow the user to edit. Essentially, you can quickly build web interfaces for your Airtable data without writing a line of code—creating your own database-driven web app for whatever your needs are. Keeping with our running theme of Airtable’s power and accessibility, in Chapter 12 we will look at how anyone can get started building interfaces.

Figure 1-6. Build web applications in Airtable using Interfaces.

Airtable Pricing

Like all software-as-a-service products, Airtable is subscription-based and offers progressively more features and functionality in each pricing plan. In the summer of 2023, Airtable overhauled its pricing into these four tiers:

• Free

• Team: $24 per user per month (or $240 annually)

• Business: $54 per user per month (or $540 annually)

• Enterprise Scale: custom pricing

The revised Free plan has far fewer records and automation runs, and it doesn’t include Extensions or Sync. For the purpose of this book, we assume you’ll at least be on the Team plan. (There is a free trial for this pricing plan.)

Airtable has traditionally shown restraint in changing feature availability and pricing. They’ve tended to do it usually when a new component is introduced (e.g., Automations), and they assign the availability to the different pricing tiers. Still, things can change. The pricing and plans are kept current at https://airtable.com/pricing.

Airtable’s Competitive Advantage

The no-code database, automations, and interfaces of Airtable give us the ability to leverage our data in highly productive ways. But aren’t there other no-code products that allow you to build a database, connect to other platforms, and even construct your custom interfaces? Yes, but there are reasons why Airtable has become so popular and why it’s so widely used.

The Airtable Community

There are many considerations for adopting a piece of mission-critical software that may house some of your organization’s most sensitive data. It’s essential to assess the functionality of the product today and try to ascertain its longevity. Like open source software projects, web platforms can be judged by the activity and dedication of their communities. In the same way that a new JavaScript framework may die on the vine from a lack of contributors, a productivity software-as-a-service (SaaS) platform needs ardent users to advocate for the changes required for users working and building in Airtable every day.

The number of users that make up the Airtable community is not only large. It’s fanatical. Given the platform’s flexibility, there are endless edge cases and unique questions that arise, and most answers can be found in Airtable’s voluminous community forum and on the many YouTube channels dedicated to Airtable. (Note to reader: Because of these edge cases, it’s virtually impossible to account for them all inside this book. However, we will attempt to address the most common snags and potentially confusing orthodoxy of Airtable.)

Airtable’s Backers

Founded in 2012, Airtable has consistently grown. In its most recent round of funding, the press release announcing the company’s valuation at more than $11 billion says that “over 80% of the Fortune 100 use Airtable.” This high rate of corporate adoption by an independent software platform is no small feat. As the saying goes, “no one gets fired for buying IBM,” which is as true today for Google and Microsoft as ever. Airtable has beaten the odds by gaining loyal users inside large, discriminating organizations with incredibly high security and data standards.

While venture capital funding alone is never a clear indicator of success, Airtable has raised funding from some of the most successful venture capital funds. These include Benchmark and Founder Collective and institutional funders like T. Rowe Price and JPMorgan in its fundraise of December 2021.

Design and Product Excellence

If Facebook’s infamous motto was “move fast and break things,” Airtable might be the opposite. Every button, every menu, and every option exudes sophisticated restraint. Users are drawn in by its use of color and the thoughtful interplay of elements in its interface. Airtable has been known for carefully adding features that serve an acute purpose and do so sparingly.

Airtable in Context

Airtable isn’t a database that’s suitable for an application with millions of users. Instead, the platform is a good match for projects with some complexity. This is specifically the case when you need collaboration among stakeholders at different levels but don’t need to access massive amounts of web-scale data.

While an Airtable base has more limited storage than a production database, it is much easier to use and much harder to break. Airtable makes it easier than a large-scale database to undo mistakes, and it eliminates the pitfalls of a traditional database by abstracting away the crucial components. For example, in Airtable you can’t see the actual unique identifier for each record, which is formatted something like rec1234123412341234, but you can enter anything you like into the cell for that record’s primary field (more on this in Chapter 2).

Airtable has a similar trick for the names of bases (prefixed with appxxxxxxxxx), views (viwxxxxxxxx), tables (tblxxxxxxxxx), fields (fldxxxxxxxxx), and more. If you’re interested in the actual identifier of your base or a given record, it’s straightforward to find that data. These identifiers can be seen in the URL bar of the browser. Airtable structures a URL by listing the base ID, the current table, the view being accessed, and then, when a record is opened, that record’s ID. Here’s the format:

airtable.com/appxxxxxxxxx/tblxxxxxxxxx/viwxxxxxxxx/recxxxxxxxx

This convention of abstracting the editable names of Airtable components in your base from the actual identifiers allows for a broader spectrum of users to interact with the platform and have the advantages of the relational database model without the strict requirements that are needed for a burly production database that might store hundreds of millions of rows. (Airtable’s Team plan allows for storing up to 50,000 records per base, and its Business offering allows up to 125,000 records.)

What This Book Will Cover

Let’s preview what we’ll cover about Airtable in the rest of this book.

Chapter 2: Working with Records and Fields

In this chapter, we will delve into the core of Airtable: records and fields. We will explore how tables in an Airtable database are made up of records and how each record represents a specific entity. We will also learn about the different field types, which represent each record’s properties or attributes.

We will start by exploring the primary field and then dive into various field types that Airtable offers. We will cover input fields like text fields, URL fields, and Single/Multiple select fields as well as calculated fields like Autonumber fields, Date fields, and User fields. Later in the book, we will explore in more detail the most complex field types. Along the way, we will provide tips and tricks for using certain field types effectively.

Chapter 3: Linked Records and the Lookup Field

Chapter 3 explores how Airtable establishes relationships between different items in our database, creating a relational structure. Using an example involving orders and wholesalers, we see how linked records improve efficiency and understanding of our data.

Linked records create a reliable single source of truth by centralizing and updating data consistently. Understanding one-to-one, one-to-many, and many-to-many relationships allows for effective linking of records. We also discuss two field types that offer insights related to linked records. The Lookup field cross-references data between linked tables, enriching records. The Count field efficiently tallies the number of linked records between tables.

Chapter 4: View Essentials

This chapter explores the fundamental tools that make Airtable a powerful collaboration platform: Views. Think of Airtable Views as the different ways you and your colleagues can see and interact with your data. We’ll begin by tackling the Grid view and will look at how you can filter, sort, and hide fields in your Views to focus on what’s important to you. We’ll walk you through examples and show you how to use conjunctions and nesting condition groups to create complex and precise filters.

Once you have your data filtered and sorted to your liking, we’ll delve into grouping records within different fields. Grouping is a great way to understand patterns and similarities among your records. You’ll discover how easy it is to collapse, expand, and organize groups to gain deeper insights into your data. We’ll also explore features like changing row heights and using the summary bar. These options allow you to present and analyze your data more flexibly and efficiently.

Chapter 5: Importing Data

Chapter 5 explores the practical aspects of importing data into Airtable. We’ll discuss how to import CSV files and spreadsheets. You’ll learn about importing data from Google Sheets as well as syncing data from other sources using Airtable Sync. We’ll also delve into the Form view, which allows you to gather structured data from outside parties. Lastly, we’ll touch on security measures for forms and the use of conditional form fields. This chapter will equip you with the knowledge and tools you need to incorporate into your Airtable base data from various sources.

Chapter 6: View Types

Building upon our understanding of the Grid view and the options across views, we delve into other views that offer even more versatility and insights.

First, we dive into the Gallery and Kanban views, which are ideal for visually organizing projects. Next, we explore the Calendar view, which allows us to visualize our data over time. Then, we move on to the Timeline view—a powerful tool for planning and tracking projects. Next, we introduce the Gantt view—a specialized view for scheduling and tracking activities. Finally, we discuss the List view—an ideal choice for managing hierarchical data and complex projects by providing a comprehensive overview of project structures and relationships.

Chapter 7: Creating the Fall Tour Tracker Base

In this chapter, we create a Fall Tour Tracker, which we’ll use through the rest of the book to demonstrate new features and concepts in a practical form. We show you how to import and link records from CSV files and synced tables to build tables for cities, venues, and lodging. You’ll learn how to establish meaningful relationships between these tables, from one-to-one to one-to-many connections. We cover the creation of a Shows table from scratch, linking it to the other tables for efficient data management. Additionally, we introduce the concept of a Regions table and demonstrate how to use linked records and a Rollup field to calculate the number of cities in each region. This chapter sets the foundation for concepts explained in subsequent chapters as we stick with the Fall Tour Tracker throughout the rest of the book.

Chapter 8: Formulas

In this chapter, we explore the power and versatility of formulas in Airtable. Formulas allow users to manipulate, calculate, and analyze data in their tables without requiring any programming knowledge. We will uncover the different types of data that formulas can manipulate, including text, dates and times, and numerical values.

You will learn how to use Airtable’s formula editor, which provides a user-friendly interface for building complex formulas. We will delve into the various formula functions and operators that enable you to perform calculations, manipulate text, and create conditional logic. Through real-world examples and step-by-step explanations, you will discover how to format data, calculate revenue estimates, and build a formula to determine whether to buy insurance for specific events.

Chapter 9: The Rollup Field

The Rollup field allows you to extract valuable information from linked records. It has two main functions: it looks up data values from a linked record field in the same table, and it can answer questions about these values using various Rollup functions. This chapter explores the process of “rolling up” data, from choosing the linked field to setting filter conditions and selecting the appropriate Rollup function. With real-world examples and step-by-step explanations, you will discover how to calculate totals, find maximum or minimum values, and gain insights from linked record relationships.

Chapter 10: Extensions

In this chapter, we delve into the world of Airtable extensions and explore an array of tools that can enhance your Airtable experience. Extensions provide a simple way to perform specialized tasks within Airtable. Whether it’s cleaning up data, triggering text messages, importing data from platforms like GitHub, or finding stock photos, there is an extension available to meet your needs.

We then explore several popular Airtable extensions and provide examples of how they can be used effectively. The Chart extension allows you to visualize your data, the Web Clipper extension enables you to import data from web pages, and the Page Designer extension allows you to create custom PDF layouts. By the end of this chapter, you will have a clear understanding of how extensions can enhance your Airtable workflow and be equipped with the knowledge to make the most of the available extensions for your specific needs.

Chapter 11: Airtable Automations

In this chapter on Airtable Automations, we explore the power and versatility of using simple no-code tools to streamline your workflow. With Airtable Automations, you can reduce the repetitive busywork that comes with managing data while minimizing the potential for human error.

We delve into different triggers and actions available in Airtable Automations, including options for integrating with popular third-party services and executing complex conditional logic. By automating repetitive tasks, leveraging conditional flow control, and syncing data across multiple platforms, you’ll gain insights into how powerful and efficient your workflow can become with Airtable Automations.

Chapter 12: Interface Designer

We explore the power of Airtable Interface Designer to create tailored web experiences for different users and streamline workflows within organizations. We begin by examining the different premade interface layouts. Then we look at the individual elements that unlock the ability for custom interfaces to provide a flexible and intuitive frontend to access and manipulate data effectively.

We delve into the practical applications of Airtable Interfaces through the creation of three examples: tracking a concert tour; creating a reporting dashboard for ticket sales and revenues; and constructing an interface for managing deposits from venues. These examples reveal how interfaces can be customized to meet specific business needs, control data access, and provide valuable reporting insights. With point-and-click no-code functionality, anyone can build powerful interfaces without code, putting highly accessible application development in everyone’s hands.

Chapter 13: Platforms That Extend Airtable

In the no-code realm of Airtable, where simplicity meets functionality, a host of external platforms have arisen to extend its power even further. This chapter explores two categories of applications that seamlessly integrate with Airtable: connectors and app builders. Connectors such as Zapier and Make open up a world of automation workflows, bridging the gap between Airtable and other software. Meanwhile, app builders like Softr and Stacker allow users to create customizable and user-friendly web applications that are built on top of their Airtable data and are especially well suited to building apps for external users.

Appendix A: The Web API and Blocks SDK for Nondevelopers

If you aren’t a developer, this chapter is a gentle introduction of Airtable’s powers beyond the realm of no-code.

We start by explaining the essence of REST APIs and how the Airtable Web API follows its conventions to communicate with other platforms seamlessly. Delving further into Airtable’s API, we discuss its flexibility in creating custom connections and advanced features that go beyond prebuilt integrations. Expanding on the enhancements that are possible through the API, we delve into developing custom extensions using the Airtable SDK. Airtable provides a toolkit that developers use for creating custom Extensions. Featuring descriptions of how integration and custom development work within Airtable, this chapter lays the foundation for nontechnical readers to converse fluently with developers.

Appendix B: Formula Functions and Operators

This book concludes with a comprehensive appendix that serves as a reference guide for all the formula functions and operators available in Airtable. This appendix provides a complete list of these functions and operators, along with clear explanations and examples for each one.

Summary

The world doesn’t suffer from a lack of web applications; we are drowning in them. Airtable stands apart from the rest by combining a robust relational database with a multitude of tools for both the developer and the less technical user. Airtable can transform how you organize, view, and ultimately understand your data to make the most critical decisions. We will dive headfirst into all you can do with Airtable until we approach its very distant limits. In the next chapters, we will understand the role of records in Airtable and the many field types that can better structure the data inside each of your bases.