Working with Airtable in FME Form

Introduction

Airtable is a platform that supports databases in a spreadsheet format allowing for collaboration and the automation of tasks.

In this article, we’ll look at reading a sample dataset in Airtable for tracking equipment for facilities management, enrich it with an external dataset, check the data for quality, and write it to another existing table called Maintenance Schedule in the base. We will also write out a subset of the data to CSV and a third table in Airtable simultaneously for the base Landscaping and Gardening.

While this article will not go into updating an existing table, we have many resources on updating tables that would be applicable to a workflow like this; these will be listed below in the Resources section.
 

Terminology

• Views: Tables can be configured with multiple views based on logic set on filters such as groups based on a column or other conditions
• Bases: Contain one or more tables
• Workspaces: Contains one or more bases

Requirements

• An Airtable account – if you don’t have access to an existing account, you can create a free one at https://airtable.com/
• Access to create an Airtable OAuth Integration App for the FME web connection

Step-by-step Instructions

Part 1: Create an Airtable OAuth App

In order to make a web connection in FME to Airtable, we’ll need a Client ID from our own Airtable account. To get a Client ID, we’ll need to create an OAuth App in Airtable, and register it.
 

1. Go to OAuth Integrations in Airtable
In Airtable, login and select your profile icon on the top right corner and select ‘Developer hub’.  In the Developer Hub, go to ‘OAuth Integrations’ on the navigation menu on the left.



2.  Register a New OAuth Integration
Select ‘Register new OAuth integration’ and fill in the name of the app. The OAuth redirect URL should point to: https://airtable.com/create/oauth




3. Take Note of Your Client ID
Save this information somewhere you can retrieve it; you will need it for your web connection in FME.

4. Enable Scopes
For the best user experience, enable all the read and write scopes for Record data and comments, as well as for the Base schema.

At a minimum, you will need to be able to read and write records, and read and write the base schema. Enable the following:

• data.records:read
• data.records:write
• schema.bases:read
• schema.bases:write

Remember to hit Save Changes when you have finished registering your app.
 

Part 2: Create a Web Connection to Airtable from FME

Now that you have your Client ID and optionally– Client Secret, open up FME Workbench and we will configure our web service definition, and then create a web connection to Airtable.

1. Create a New Airtable Web Service in FME
In FME, navigate to Tools > FME Options > Web Connections > Manage Web Services

1. Select the ‘+’ icon at the bottom left, which will show a drop-down.
2. Select ‘Create From’
3. Then select ‘Airtable (Template) (safe.airtable)’.

2. Configure the New Web Service Definition

• Give the new Web Service a meaningful name like ‘Airtable Tutorial’
• Fill in the Client ID you got from registering your OAuth App
• Update the Redirect URI to https://airtable.com/create/oauth to match the one in your app
• Press Apply and Close.

3. Create a Web Connection to Airtable
Back in FME Options > Web Connections dialog, select the ‘+’ icon and select the new web service you just created and named. For this example, that would be ‘Airtable Tutorial’. Clicking OK will open up a Web Service Authentication dialog. Add a base and select ‘Grant Access’.

Now you’re ready to connect to your Airtable base through FME!
 

Part 3: Create a Workflow in FME to Write to an Existing Table in a Base

Now that we have a successful connection to Airtable in FME, an Airtable template, and our dataset, we can start working with tables in our bases.

In this section, we will create a workflow that reads from an Airtable table, merge it with an Excel spreadsheet of maintenance problem reports, and write to another existing table in the base to create a Maintenance schedule for someone to address these problems.

1. Download Data
Download Maintenance_Reports.xlsx from the Files section at the top right of this article.

Optionally you can also download the completed workspace Writing_to_Airtable.fmwt for reference or as a template workspace to explore.

2. Make a Copy of this Base
Go to FME Article Template: Facilities Management Example and make a copy of the base to your own Airtable workspace.

Explore the schema in each table to get an idea of the structure of the data that we will be writing to. While many of these are long or single line text fields, there are some single select fields with set values that are important to note to be able to write to them successfully.

3. Read in a Table in an Airtable Base

Add an Airtable reader by typing Airtable into the canvas. Select the Connection that you just created. Click Parameters and select the Base you want to read from and press OK.

In the ‘Select Feature Types (Table)’ dialog, edit the dialog so that only ‘All Objects’ is selected.

4. Read in an Excel File of Reported Maintenance Issues
Add an Excel reader by dragging and dropping the Maintenance_Reports.xlsx file to the canvas.

5. Check the Reports for Duplicates
Since we didn’t use an Airtable form to create this spreadsheet, let’s check for duplicate entries. Add a DuplicateFilter to the canvas and connect it to the Reported Issues feature type. Open up the transformer parameters and set the Key Attributes to ‘Equipment ID’ and ‘Issue Description’.




6. Merge Data Based on the Equipment ID
Add a FeatureJoiner and connect the All Objects to the Left port, and the DuplicateFilter Unique output to the Right port. In the FeatureJoiner, set Join On to:

• Left: Item ID
• Right: Equipment ID

This will join information from both tables, enhancing our existing Airtable data with recent reports of issues that people have filed for facilities management.




7. Remove Unnecessary Attributes
Next, from the Joined output port, connect an AttributeRemover. Now that we have our joined dataset, we don’t need two columns with the Equipment ID, so we will remove this attribute.

8. Add an Airtable Writer
Add an Airtable writer. Set the Connection, pick the Base you wish to write to, and set the Table Definition to ‘Import from Dataset’.



In the Import Writer Feature Types dialog, go to the Parameters, and pick your tables to import. Select ‘Landscape and Gardening’ and ‘Maintenance Schedule’ and press OK out of all the dialogs. This should add two writer feature types to your canvas.

Connect the AttributeRemover to the Maintenance Schedule Writer Feature Type.

9. Make a Local Copy Using the CSV Writer
Add a CSV writer, set the CSV File Definition to ‘Automatic’ and give the CSV File a name. Connect the AttributeRemover to the CSV writer feature type.
Your workspace will look a bit like this:

 

Part 4: Write a Subset of the Data to a Different Existing Table

We’re set up to write to a table for managing a Maintenance Schedule, but we also have an empty table setup for Landscape and Gardening. Using the same workflow, we can filter a subset of data to import into this new table as well.

1. Filter Data for Plants Only
Add a Tester to the canvas and connect the Joined output port of the FeatureJoiner to the Tester. In the Tester parameters, set the Test Clause to

Category = Planting



2. Prepare Data to Match the Target Schema

• Add an AttributeSplitter, and set:
• Attribute to Split: ‘Specification’
• Delimiter to Format String: -
• List Name: _specs

3. Clean up Attributes
After the AttributeSplitter connect an AttributeManager. Here we will set the Specification to Value of ‘_specs{1}’ using the drop-down and selecting Attribute Value.

Create a new attribute called ‘Plant Name’ and press the up arrow to move it above _specs{}. Set the Value to ‘_specs{0}’ to split the attribute parts into two different fields or attributes.

Ensure _specs{} is at the bottom of the list, select it and hit the ‘-’ button to remove it.




4. Connect the AttributeManager to the Landscape and Gardening Writer Feature Type
Lastly, we will connect the AttributeManager to the other existing table in Airtable to populate the Landscape and Gardening table.

5. Run the Workspace
At the top left, click the green play button to run the workspace.

The completed workspace will look something like this:



6. View the Output in Airtable
Check back in Airtable, and refresh the base. You should now see rows populating the tables for Maintenance Schedule and Landscape and Gardening.

Troubleshooting

I don’t get a login dialog when I try to create my web connection.
Ensure that your redirect URL in the OAuth App in Airtable, and the URI in your FME web services definition match. Both of them should point to https://airtable.com/create/oauth

Nothing has written out to my table in Airtable.
Check the Translation Log for error messages. If your attribute names don’t match your table column names or schema, we’ll report errors and FME won’t write that data because it couldn’t find the right column to write it to.

It could also be a schema type mismatch. For example, if the Airtable field is set to Single Select and we’re trying to import data that isn’t an option pre-configured in the dropdown, then FME won’t have somewhere to write out that data successfully.
 

Additional Resources

• Tutorial: Updating Databases with FME
• Tutorial: Getting Started with CSV
• Tutorial: Getting Started with Excel

Data Attribution

The data used here originates from data made available by Autodesk and has been modified by Safe Software. It contains information licensed under CC BY-NC-SA 3.0.