How to build an app using Airtable and Retool

Let's build a customer support dashboard from scratch using Airtable and Retool. The app will allow you to search through all your customers and then see all the contacts, tasks, meetings and invoices related to the customer in one, clean view.

Retool doesn't come with a native Airtable integration. And while you can use Retool's REST API connector with Airtable, because Airtable's API comes with rate limits and pagination, it's a bit clumsy to work with.

Luckily, Retool comes out of the box with first-class support for SQL. So in this tutorial we'll use a new tool I helped build called Sequin to turn Airtable into a Postgres database. This will allow you to connect Airtable to Retool as a database so you can build the apps you need in minutes.

Airtable Setup

For this tutorial we'll use Airtable's customer success management template. This base is a great example of how Airtable can manage all your customer data and relationships. But it also showcases the limits of using Airtable when you need finer workflows or permissions. This is where Retool comes in.

First, add the Airtable customer success management template to your Airtable workspace:

1. Log into your Airtable workspace and then open the customer success management template in a new tab.

2. Click the Use Template button to add the customer success management template to your workspace.

Sequin Setup

Now, let's turn the customer success management base in Airtable into a Postgres database that works with Retool using Sequin.

1. Go to https://app.sequin.io/signup and create an account.

2. Connect your Airtable base by going through the tutorial or clicking the Add Base button.

1. You'll be prompted to enter your Airtable API key. After that, select the Customer Success Managment base and all its tables. Then click Create.

1. Sequin will immediately provision you a Postgres database and begin syncing all the data in your Airtable base to it. You'll be provided with credentials for you new database. Keep these handy as you'll use them to connect your Sequin database to Retool.

Retool Resource Setup

Now, just add your Sequin database to Retool like any other Postgres database:

1. In a new tab, log into your Retool dashboard. In the top menu bar click Resources and then the blue Create New button.

1. Select Postgres from the list of resource types.

1. Enter the name for your resource (i.e. "Airtable - Customer Success") and then enter the Host, Port, Database name, Database username, and Password for your Sequin database. You can copy and paste these from Sequin (make sure there aren't any leading spaces). Then click the blue Create resource button.

1. Retool will confirm that your resource was created. Click Create an app to return to the app page.

Retool App Setup

With Airtable successfully connected to Retool using Sequin, we are ready to build an app that shows you all your customer data in one view. Or, as some call it, a single view of customer.

1. On the Retool app page, click the blue Create new button and select the Generate app from data option.

1. Because your Airtable data is now in a Postgres database, Retool can jump-start your application with a table and search bar. This is a good foundation for your single view of customer application. So in the modal that appears, select the Sequin Postgres database you created (i.e. Airtable - Customer Success), the accounts table, and the acount column as the initial data for the app. Then click the blue Next button.

1. Now, give your application a name. You can go with something like "Customer Central" and click the blue Create app button.

1. Retool will now generate an app with a table and a search bar. Because we selected the accounts table and the account column, the app will initially show the entire accounts table and allow you to search by account. Give it a try. If you enter "long" in the search bar and hit enter, the table automatically shows "Long Beach College." Isn't that nifty!

1. Now, let's begin to edit this app. Click the Edit button in the top right corner.

1. First, we can remove the helpful hints that Retool loads into the template app. We don't need them. Select and delete the two text boxes at the top of the page as well as the extra button at the bottom. This will leave you with a clean table and search bar:

Create the customer accounts table

With the basic scaffolding of your Retool application in place, you can begin to tailor the app to our needs using SQL.

First, look at the query section in bottom panel. You can see that because Sequin has transformed your Airtable base into a proper Postgres database, you have access to all of Retool's SQL capabilities. For instance, you can see the entire schema of your base as well as the column types. And as you write SQL queries, you get the benefits of autocomplete:

Now, go ahead and edit the query that populates the account table so that you only pull in the data you need for each customer account:

select id, account, primary_csm, partnership_growth, customer_stage, num_of_tasks, next_meeting
from accounts
where ({{ !searchBar.value }} or account ilike {{ '%' + searchBar.value + '%' }});

This query is selecting just the columns you need from the accounts table. It's then filtering the results at the WHERE clause based on the value in the search bar. If nothing is entered in the search bar, the entire list of accounts is returned.

When you click to Save and Run button, the results of the query are shown as a preview. Everything looks good. So as a last step, rename this query to list_accounts by clicking and then editing the name of the query in the left bar.

Finally, let's make this table look good. With accounts table selected in the canvas, go into the inspector in the right panel to re-organize the columns and give them clear titles. Hide the id column by clicking the little "eye" icon since its more of a reference field. Lastly, adjust the table sort and pagination to your liking.

With the table looking good, give it a title. Drag and drop a text component above the table.

Then, in the inspector, use markdown to give the text component a value of # 📂 Accounts. This will make the text an H1 header.

Add the remaining customer data

Voila, your first table is complete. The accounts table is now pulling data live from Airtable and is easily searchable. With SQL and Retool at your fingertips, that was pretty quick. Now, lets add the remaining customer data.

We're going to repeat the same steps we used to create the account table, but in a slightly more efficient flow:

1. Layout the rest of the application by dragging and dropping components.
2. Add data to each of the components.
3. Make the components look good.

1. Layout the remaining tables and titles

In order to show all the relevant information for any given account, your app should show the Contacts, Tasks, Meetings, and Invoices for the account selected. Each of these will be their own table. So we can layout the entire application by dragging and dropping tables and text components into the canvas. In a couple clicks you'll end up with an app that looks something like this:

Note: as you drag tables onto the canvas, Retool will auto populate them with the list_accounts query. Don't worry about it.

2. Add data to the tables

Filling each table up with data is a two step process:

1. Write a query to retrieve the data for the selected account.
2. Configure the table to pull in the data from the query.

Let's start with the contacts table.

First, open the bottom panel and click New to add a new query:

To retrieve the contacts, we can write a simple SQL query:

SELECT contacts.name, contacts.role, contacts.email, contacts.linkedin
FROM contacts
WHERE contacts.company[1] = {{table.selectedRow.data.id}};

Here, the SELECT statement is pulling in the specific columns for the table. While the WHERE clause is filtering the returned results based on the account selected in the accounts table (i.e. table).

You might be wondering why you are using contacts.company[1] in the WHERE clause. In short, the company column is originally a linked record field in Airtable. Linked records can potentially hold more than one value. So in your Sequin Postgres database, company is stored as a Postgres array in case there are multiple values. The [1] is how you select the first value in the Postgres Array. For a complete explanation check out the Sequin cheat sheet or this dev.to tutorial.

When you select an account in the accounts table (I recommend selecting Soho Real Estate account for testing) and then click the Save and Run button, you'll see this query returns just the contacts for the selected account. Exactly what you are looking for.

Name the query list_contacts so it's easy to identify. Now, click on the empty table under the Contacts heading in the canvas. The inspector on the right will now show the settings for the table. Enter {{ list_contacts.data }} into the data field. This will fill the table with the data returned from the list_contacts query.

Just repeat this pattern to populate the remaining tables. Here are the PostgreSQL queries you can use for each table. They all follow the same pattern:

Tasks (list_tasks)

SELECT task_manager.task_name, task_manager.status, task_manager.assignee, task_manager.due_date
FROM task_manager
WHERE task_manager.account[1] = {{table.selectedRow.data.id}};

Meetings ('list_meetings`)

sql
SELECT meetings.date, meetings.name, meetings.meeting_complete
FROM meetings
WHERE meetings.account[1] = {{table.selectedRow.data.id}};


Invoices ('list_invoices`)

SELECT invoices.invoice_number, invoices.invoice_frequency, invoices.invoicing_status, invoices.invoice_amount, invoices.invoice_period_beginning_date, invoices.invoice_period_end_date, invoices.invoice_type
FROM invoices
WHERE invoices.account[1] = {{table.selectedRow.data.id}};

After creating and connecting all your queries, your app should now feel almost complete. You can search for an account like "Soho Real Estate", select it, and all the tables will show the details for the account.

3. Make the tables look pretty

To make the app easy to use, let's make each table a little more functional.

As a first step, to make these additional tables easy to read, turn on Compact mode in the inspector. This will allow each table to show more information.

Next, add readable column names and column types to each table. Simply select the table in the canvas, then select the column in the inspector and give it a name and type. For example, change the column title for invoice_amount to Amount and make it's column type USD (dollars).

Retool has many options to make these tables sparkle, but with just these minor changes your app should be easy to use.

You now have a single view of customer app with all your key customer data in one, clean window.

Writing to Airtable

Let's say you want to allow your customer success agents to create new tasks for accounts.

To add this functionality, you'll first set up the components and then connect the data.

In this case, you'll use a simple modal to collect the details of the task.

At the moment, Sequin provides a one-way sync from Airtable to Postgres. To create a new task on Airtable, you'll perform a PUT to the Airtable API. Then, because Sequin keeps your data in sync in real-time, the data will flow from Airtable, through your Sequin Postgres database, to your Retool app within seconds.

Add a modal

From the components list, find the modal component, and then drag and drop its button onto the canvas just above the Task table.

You can make the button green with the text "New Task".

Now, let's build a form in the modal to collect the details of a new task.

When you click on the "New Task" button the modal will appear. The modal doesn't need to be huge, so you can adjust the height of the modal to 350px in the inspector.

Now, drag and drop several components into the modal:

• A text component that says Add a new task
• A text input component with the label Task
• A date and time selector component with the label Due Date
• A drop down component with the label Status. Set the values and display values to be the following array: ["Not Started", "In Progress", "Complete", "On Hold"].
• Two buttons at the bottom. One that reads Save and the other Cancel.

By the end, your modal should look something like this:

Connect the modal

First, make sure the user can close the modal:

1. Open the bottom panel and click to create a new query
2. For the resource, select Run JS code (javascript)
3. Enter modal1.close() for the query and save the query as close_modal.

Trigger the close_modal query when the user closes the modal or clicks the cancel button.

Next, let's trigger the creation of a new task when the user clicks save.

To do so, we first need to add the Airtable API as a resource.

Click the Retool icon in the upper left corner, and select Resources.

On the Resource page, click the blue Create New button and then select REST API from the options.

Configure your the Airtable API:

• Name the API Airtable API - Customer Success
• To get the Base URL, go to https://airtable.com/api and select the Customer Success Management base. In the middle of the page you'll see your base ID (the same one you used for Sequin) in green. Just append that base ID to this URL: https://api.airtable.com/v0/{YOUR_BASE_ID}
• Then, retrieve your API key from the your Airtable account page and add these two headers:
  • Authorization: Bearer {YOUR_API_KEY}
  • Content-Type: application/json

Then click the blue Create resource button.

Navigate back to your app, open the bottom panel and click to create a new query as follows:

• For the resource, select the Airtable API resource that you just created.
• For action type, select POST and enter Task%20Manager (the %20 is the URL-friendly version of a space)
• For body, select Raw and enter the following JSON:

'{
  "records": [
    {
      "fields": {
        "Status": "{{select1.value}}",
        "Due Date": "{{datetimepicker1.formattedString}}",
        "Account": [
          "{{table.selectedRow.data.id}}"
        ],
        "Task name": "{{textinput2.value}}"
      }
    }
  ]
}'

The body of this API query is creating a new record in the Task Manager table. The details of the task are being pulled from the values entered in the modal.

Click Save and name the query ceate_new_task.

When the user clicks save in the modal, we want to create a new task, close the modal, and then refresh the data on the page.

So as a last step, let's create one more query to string these functions together.

Click to create a new query and select Run JS Code (javascript) as the resource. Then enter this code:

create_new_task.trigger();
modal1.close();
setTimeout(() => list_tasks.trigger(), 5000);

1. The first line triggers a new task.
2. The second line closes the modal.
3. The third line waits 5 seconds and then pulls fresh data from Sequin.

Click the save button and name this query run_modal:

Now, let's connect this new query to the modal. We want to trigger the run_modal query when the user clicks the save button. So open the modal, and select the Save button. In the inspector, select to Run a query on click. Select run_modal from the drop down.

Last step! Test it out. Open the modal and create a task. Click the save button. The modal will close and after a short delay your new task appears ✨

Now, with the power of SQL, you can build apps on your Airtable data in no time.