Even after having the requirements to deal with huge chunks of data, organizations still use Spreadsheets for analysis, instead of Databases. They don’t opt for using databases because these often require SQL scripting skills. And this is where Airtable comes into the picture! It combines the Functionality of a Database and the Style of a Spreadsheet in such a manner that users are constantly aware of what is going on.
Airtable is a Cloud-Based Project Management Tool targeting small and medium-sized enterprises, as well as departments inside bigger corporations. This platform is primarily concerned with data organization and collaboration. Airtable claims to be able to “organize anything, with anybody, from anywhere.” Data that is related to one another can be linked together.
Moreover, Airtable uses features of Relational Databases. Airtable Linked Records are the way of establishing relationships between tables. These tables related through Airtable Linked Records tend to have a “Reciprocal Relationship” among them.
In this article, you will have an understanding of the process of setting up Airtable Linked Records. You will also gain a holistic understanding of Airtable Linked Records. Read along to find out how you can Link Records in Airtable seamlessly.
What is Airtable?
Airtable is a progressive Spreadsheet Application with Database Capabilities. It is easy to use and allows anybody to rapidly construct a database. It is one of the numerous no-code tools that are gaining popularity. Airtable makes it easy to interact with your data while also enabling Programmatic Data Management.
The User Interface of Airtable is Straightforward, Colorful, and User-friendly. It allows anyone to create a database in minutes. You may use it to save, organize, and collaborate on information about anything, including employee directories, product inventory, and even student records. You don’t even need to know SQL, in fact not any kind of scripting. Few common use cases for Airtable are Content Calendar, Project Management, Event Planning, Product Catalog, Job Hunting, etc.
Key Features of Airtable
- Customized Views: Airtable provides Customized views. You can use them according to the way you want to view the data. Some examples of views are the Grid view, Calendar view, Gantt view, etc.
- Relational Database Functionality: The Bases in Airtable follow the functionality of the Relational Database. Data is stored in the form of Records (rows) and Fields (columns) in a Table. And the Tables are related through Linked Airtable Records.
- Application integration: Integrations help you automatically move information back and forth between Airtable and other apps. Airtable has built-in support for many popular Applications and a Robust API. Some of the Application Integrations offered by the vendor are Dropbox, LinkedIn, WordPress, Slack, Gmail, GitHub, Twitter, etc.
- Snapshots: Airtable automatically takes snapshots of your Bases regularly, and you may also manually take a snapshot. Restoring a base from a snapshot will produce a new base that will not interfere with your existing base.
- Calendar Management: You can integrate your Calendar app with Airtable. If you use Airtable to plan and schedule, you can import information from Airtable to an external Calendar app that you’d like to keep in sync. You can even move your events from your Calendar into Airtable.
- Collaborative Workspace: The collaboration capabilities enable different divisions to effortlessly collaborate and manage teamwork.
- Automatic Notifications: When anything significant occurs in Airtable, you will be informed via notifications. Notifications are presently triggered when someone shares a base with you, adds you to a workspace, @mentions you in a record, or assigns you to a record using a collaborator field.
- Templates: For the template gallery, Airtable has assembled a variety of pre-made Template Bases. They cover a wide range of basic use cases and are ideal starting points for creating your own custom Bases.
Prerequisites
- Basic Knowledge of Relational Databases.
- Basic Knowledge of Airtable.
- An Airtable account.
Understanding the 6 Basic Components of Airtable
There are 6 basic components of an Airtable Database:
1) Bases
Databases in Airtable are known as Bases. A single database stores all the information of a particular project. Bases can be created either by leveraging existing templates provided by Airtable or from scratch using Spreadsheets or an existing Base. Examples of some Bases are “Employee Directory”, “Telephone Directory” etc.
From the image below, you can see different Bases, such as Product Planning, Product Launch, User Studies, Sales CRM, Contact Calendar, and Project Tracker.
Image Source
2) Tables
Each Base can have one or more Tables similar to one Excel workbook having multiple worksheets. Each Table holds a particular type of information within the Base. For example, within the Sales Base, we have a Discounts Table.
From the image below, you can see that there are three Tables in this Base, which are Content Production, Social Schedule, and Freelancer Timesheets.
Image Source
3) Fields
In Airtable, Columns are called Fields. Each column holds a single data type. Airtable currently has 16 basic data types for its records. These are:
- Single-Line Texts
- Long Text Articles
- File Attachments
- Check Boxes
- Single Text from Drop-Down List
- Multiple Selects from Drop-Down List
- Date and Time
- Phone Numbers
- Email IDs
- URLs
- Numbers
- Currency
- Percentage
- Auto-Number
- Formulae
- Barcodes
From the image given below, you can see the different Fields in the Table, which are Headline, Section, Sub-head, Status, Header image, Author, and Draft date.
Image Source
4) Records
Every single entry of information in an Airtable Table is called a Record i.e, rows are called Records. For example, in a Student Table, each Record holds the information of different students.
From the image given below, you can observe that every single entry in the Table represents a Record.
Image Source
5) Views
An Airtable View allows you to “view” the data stored in a Table in different forms. The default view of a Table in Airtable is Grid format in which the Records are rows and Fields are columns. Few examples of different types of views are Gallery View, Calendar View, Kanban View, etc.
From the image given below, on the left side, you can see some of the View names and the Table is currently visible in Calendar View.
Image Source
6) Workspaces
A Workspace is a group of Bases (Airtable Databases) shared between a group of people who contribute to the project, called Workspace Collaborators.
From the image given below, you can observe that all the Bases come under a Workspace called Marketing.
Image Source
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 150+ different sources like Airtable to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ integrations to SaaS platforms like Airtable, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
You can try Hevo for free by signing up for a 14-day free trial.
Understanding Relational Databases
A Database represents a collection of information, organized to make it easy to access, manage, and update. It is an organized collection of information or data that is generally kept electronically in a computer system.
A Relational Database is a form of database that stores and makes data points linked to one another, available. Relational Databases are based on the Relational Model, which is a simple and straightforward manner of expressing data in tables. Tables are also called Relations. Hence, a Relational Database represents an organization of data in the form of tables where tables have relationships among them.
In Relational Databases, Tables are related based on common attributes between them. For example: In a Sales Database, the Customer Table and Product Table are related to each other through the Product Field. The below image illustrates the model of a Relational Database.
Image Source
What are Airtable Linked Records?
Linking Records in Airtable is the way of implementing Relational Databases. Records between tables are linked to capture dynamic relationships between them. Linked Record Field is a special field type that needs to be set for fields that are to be Linked with other Tables.
For example, If we have a Table of Musical Artists and a table of Albums in a Music Records Base, we can use Linked Record Fields to link the tables of Artists and Albums such that each Artist is linked to his/her Albums and each Album is linked to its associated Artist.
Airtable Linked Record Fields ensures that you only have to enter information once and make changes in one place—for example, if an album name is changed, then you only need to change this information in one table, and it will automatically update the information across other Linked Tables.
Airtable Linked Record Fields contain Blue Link Tokens. These tokens denote the link from one record to another. When you click on the link, it expands the record to which it is related, allowing you to bring up essential information from another table with a single click.
Image Source: support.airtable.com
When to use Airtable Linked Records?
Some simple guidelines for determining when and where to use Airtable Linked Records are as follows:
- Information is relevant to or maybe referenced across multiple tables. For example, a client name appears in both the “Projects” and “Client” tables, or an upcoming launch date appears in both the “Tactics” and “Campaigns” tables.
- You may want to view additional context that does not necessarily belong in the table. For example, Associated costs for various services, which may be useful to view at times but do not necessarily belong in the “Projects” table.
Ways to Set up Airtable Linked Records
Now that you have learned about the basic components of Airtable Databases, you can dive into the ways to set up Airtable Linked Records.
There are multiple ways to Link Tables in Airtable:
A) Airtable Linked Records: Converting an Existing Field into a New Linked Table
The steps followed to convert an existing field into a new Linked Table are:
Step 1: Click on the field name which you want to link. Then from the drop-down menu, select the “Customize Field Type” option.
Image Source – Self
Step 2: Change the field type to “Link to another record”.
Image Source – Self
Step 3: Then select the “Create a new table” option.
Image Source – Self
Step 4: Then click on Save.
Image Source – Self
Step 5: A new table is created with the same name as the Field name of the Airtable Linked Record. View the new table. Here, the primary key represents the list of names for all your Linked Records. And the primary key of the previous table has become the non-primary key field of the new table.
Image Source – Self
Step 6: You can also add fields to the new table by clicking on the + symbol.
Image Source – Self
Image Source: support.airtable.com
If you make any changes to one table then the changes would also be reflected in the Linked Table. This is called a “Reciprocal Relationship” between the Linked Tables.
Image Source: support.airtable.com
To have further information about Creating a New Linked Table from an Existing Table, visit here.
B) Airtable Linked Records: Linking Two Existing Tables
The steps followed to link two existing tables are:
Step 1: Click on the + symbol to add a new field that you want to link.
Image Source – Self
Step 2: Add the name of the field which you want to link to another table.
Image Source – Self
Step 3: Then expand the drop-down by clicking on “Single line text” and then select “Link to another record”.
Image Source – Self
Step 4: Now, click on the name of the table, from the list of tables, you want to create a link to.
Image Source – Self
Step 5: Then click on Save.
Image Source – Self
Step 6: Once you have your linked record field created, click on the + symbol, and add the records in the current table from the list of Linked Records of the Linked Table.
Image Source
Even without creating a new field, an existing field could also be linked to another table. When Airtable converts a text field to a Linked Record Field, it checks to see if there are any commas in the cell to determine if there are any multiple values. Make sure that multiple field values are separated by commas before beginning any conversions. If any name values contain commas, make sure to enclose them in double quotation marks.
Image Source: support.airtable.com
Step 7: Then go to the Field Customization Menu and select the Customize field type option.
Step 8: Click the “Link to another record” option, and select the table to which you want to link to. Then select save.
Image Source: support.airtable.com
Now go to the Linked Table, you’ll see that by adding the Linked Record Field to the first table, you also added a reciprocally Linked Record Field to the second table. i.e, the associations are mirrored in the other table.
Conclusion
In this article, you have learned about the ways to Link Tables in Airtable. This article also provided in-depth knowledge about Airtable, its key features, its components, and implementation of the Relational Database in Airtable through Airtable Linked Records.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources such as Airtable and destinations with a few clicks. Hevo with its strong integration with 150+ sources & BI tools such as Airtable allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools. Check out the details about Hevo’s pricing here.
Give Hevo a try by signing up for a 14-day free trial today!
Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.
