Creating Live Dashboards With Airtable and React

Whether a company is a large enterprise or a budding startup, collecting data from users and customers, and reporting on or visualizing that data is crucial to the business.

I recently worked with a telemedicine startup based in Brazil. Its mission is to provide remote care and monitoring by connecting patients to medical professionals and health coaches. The core need was to create an interface for the coaches and health professionals to easily review a patient’s information and most important metrics related to their particular situation: a dashboard.

Enter Typeform and Airtable.

Typeform

Typeform is one of the go-to data collection tools that enables responsive web experiences for users completing a survey. It also comes with several features that make surveys more intelligent, especially when combined:

• Logic Jumps
• Hidden Fields

Surveys can be shared via URLs that can be pre-seeded with values for the hidden fields, which can then be used to implement logic jumps and alter the behavior of the survey for the user with the link.

Airtable Uses

Airtable is a spreadsheet-database hybrid and a collaborative cloud platform. Its focus on point and click functionality means that non-technical users can configure it without coding. Airtable has a multitude of use cases in any business or project.

You can use an Airtable Base for:

• CRM (Client Relationship Management)
• HRIS (Human Resources Information System)
• Project Management
• Content Planning
• Event Planning
• User Feedback

There are many more potential use cases. You can explore Airtable case studies here.

If you are not familiar with Airtable, the conceptual data model breaks down like this:

• Workspace - Composed of Bases
• Base - Composed of Tables
• Table - Composed of Fields (columns) and rows
• View - A perspective on Table data with optional filters and reduced Fields
• Field - A column of a Table with a Field Type; see here for more information on Field Types

Apart from providing a cloud-hosted database with familiar spreadsheet features, here are some of the reasons the platform is so powerful:

For non-technical users, Airtable provides:

• An easy to use front-end interface
• Automations that can be created with point-and-click configuration to send emails, process rows of data, schedule appointments in calendars, and more
• Multiple types of views that allow teams to collaborate on the same Base and tables
• Airtable Apps that can be installed from the marketplace to supercharge a Base

For developers, Airtable provides:

• A well-documented back-end API
• A scripting environment that allows developers to automate actions within a Base
• Automations that can also trigger custom developed scripts that run within the Airtable environment, extending the capabilities of automations

You can learn more on Airtable here.

Getting Started: Typeform to Airtable

Typeform surveys were already configured by the client, and the next step was to plan how that data would land in Airtable and then be turned into a dashboard. There are many questions to consider when creating dashboards on top of any database: How should we structure the data? What data will need to be processed prior to visualization? Should we sync the Base with Google Sheets and use Google Data Studio? Should we export and find another third-party tool?

Fortunately for developers, not only does Airtable provide automations and scripting to handle the data processing steps, but it has also made it possible to build custom applications and interfaces on top of an Airtable Base with Airtable Apps.

Custom Apps in Airtable

Custom Apps in Airtable have been around since the Airtable Blocks SDK was released at the beginning of 2018, and were recently renamed to Apps. The release of Blocks was huge in that it meant that creators now had the ability to develop, as Airtable puts it, “An infinitely recombinable Lego kit.”

More recently with the change to apps, the Airtable Marketplace made it possible to share apps publicly, as well.

Airtable Apps provide businesses with an infinitely recombinable Lego kit they can tailor to their needs.

In order to build a custom app in Airtable, a JavaScript developer must know how to use React, one of the most popular JavaScript libraries for building user interfaces. Airtable provides a component library of functional React components and hooks, which are a huge help for rapidly building a consistent UI and determining how you will manage state within the app and its components.

Check out Airtable’s Getting Started article for more information and Airtable on GitHub for examples of apps.

Airtable Dashboard Requirements

After reviewing the dashboard mockups with the client team, the types of data to be used were clear. We would need a series of dashboard components that would display as text on the dashboard and charts of different metrics that could be tracked over time.

Coaches and medical professionals needed to be able to build a custom dashboard for each patient, so we needed a flexible way to add and remove charts. Other static data relative to each patient would be displayed no matter the patient selected.

In this case, the dashboard sections boiled down to:

• General Information - Patient Name, Email, Phone Number, Contact Preference, Date of Birth, Age
• Objectives - Goals the patient has based on survey results
• Some Stats - BMI, Height, and Weight
• Medicine Use - Listing all prescription drugs already used by a patient
• Family History of Conditions - Helpful in diagnosing certain conditions
• Charts - A section where the Airtable dashboard user could add a chart and configure which metric it would visualize over time

One way to approach all of the sections except for charts would be to hard-code all the columns for objectives, medicine use, and family history into the dashboard. However, that would not allow the client team to add new questions to a Typeform survey nor add a new column to an Airtable table to present that data on the dashboard without having a developer update the custom app.

A more elegant and extensible solution to this challenge was finding a way to tag columns as relevant to a particular dashboard section and retrieve those columns using the metadata that Airtable exposes when using the Table and Field models.

This was achieved using Field Descriptions as a place to tag a column from the Table as relevant to a dashboard section to be displayed to the user. Then, we could ensure only those with the Creator role (the administrators) for the Base had the ability to modify these Field Descriptions to alter what appears on the dashboard. To illustrate this solution, we will focus mostly on the items in General Information and how to present Charts.

Creating a #TAG# System

Given the dashboard sections, it made sense to make reusable tags for some sections and specific tags for certain columns. For items like patient name, email, and phone number, #NAME#, #EMAIL#, and #PHONE# were added to each Field’s description, respectively. That would allow that information to be retrieved via the Table metadata like this:

const name = table ? table.fields.filter(field => field.description?.includes("#NAME#"))

For areas of the dashboard that would need to draw from many tagged columns we would have the following tags for each dashboard section:

• OBJ - Objectives
• FAM - Family History
• MED - Medicine Usage
• CAN - Family History specific to cancer
• CHART - Any column that should be sourced for adding charts; must be a quantity

In addition, it was important to separate the name of a column in a Table from the label it would receive on the dashboard, so anything that received a #TAG# would also have the ability to receive two #LABEL# tags in its Field Description. A Field Description would look like this:

In case the #LABEL# tags are missing, we will display the column name from the Table.

We can parse out the label set in the description with a simple function like this after retrieving the field with the previous code example:

// utils.js

export const setLabel = (field, labelTag = "#LABEL#") => {
   const labelTags = (field.description?.match(new RegExp(labelTag, "g")) || []).length;
   let label;
   if (labelTags === 2) label = field.description?.split(`${labelTag}`)[1];
   if (!label || label?.trim() === '') label = field.name;
   return {...field, label, name: field.name, description: field.description};
}

With this #TAG# system, we achieve three main things:

• Column names (fields) in the Table can be changed as desired.
• Labels for data in the dashboard can be distinct from column names.
• Dashboard sections for Objectives, Medicine Usage, Family History, and Charts can be updated by the client team without touching a line of code.

Persisting State in Airtable

In React, we use state and pass it to components as props in order to re-render that component if its state changes. Normally this is tied to an API call that fuels a dashboard component, but in Airtable we already have all the data and simply need to filter what we are displaying based on which patient we are viewing. In addition, if we use state, it will not persist the data past a refresh in the dashboard itself.

So, how can we persist a value past refresh to keep a dashboard filtered? Fortunately, Airtable provides a hook for this called useGlobalConfig in which it maintains a key-value store for an app installation on a dashboard. We simply need to implement the logic of retrieving values from this key-value store when the app loads to fuel our dashboard components.

What is even more useful about using the useGlobalConfig hook is that when its values are set, the dashboard component and its child components re-render, so you can use the Global Config like you would use a state variable in a typical React implementation.

Introducing Charts

Airtable provides examples of charts with its Simple Chart App, which uses React Charts, a React wrapper on Chart.js (chart-ception).

In the Simple Chart App, we have one chart for the whole app, but in our Dashboard App, we need the ability for the user to add and remove their own charts from their own dashboard. What’s more, in discussion with the client team, it seems that certain metrics would be better viewed on the same chart (like readings for diastolic and systolic blood pressure).

With this we have the following items to tackle:

• Persisting state for each user’s chart (or even better using Global Config)
• Allowing multiple metrics per chart

This is where the power of the Global Config comes in handy, as we can use the key-value store to maintain the selected metrics and anything else about our list of charts. As we configure a chart in the UI, the chart component itself will be re-rendered due to updates to the Global Config. For the charting section of the dashboard, here is a gist with the components for reference, focusing on dashboard charts.js and single chart.js.

The table passed to each chart is what is used for its metadata to find the fields, whereas the records passed have already been filtered by the patient selected at the top-level dashboard component that imports dashboard_charts/index.js.

Note that the fields listed as options in the dropdown for a chart are pulled using the #CHART# tag we mentioned before, with this line in a useEffect hook:

// single_chart/index.js

…
useEffect(() => {
  (async () => {

...

    if (table) {
      const tempFieldOptions = table.fields.filter(field =>    
        field.description?.includes('#CHART#')).map(field => {
          return {
            ...setLabel(field),
            value: field.id
          }
       });
       setFieldSelectOptions([...tempFieldOptions]);
    }
  })();
}, [table, records, fields]);


...

The code above shows how the setLabel function referenced earlier is used with the #TAG# to add anything provided in the #LABEL# tags and display it for the option in the field dropdown.

Our chart component takes advantage of the multi-axis capabilities provided by Chart.js, which is shown with React Charts. We just extended it via the UI with the user’s ability to add a dataset and a chart type (line or bar).

The key to using Global Config, in this case, is to know that each key can only hold a string | boolean | number | null | GlobalConfigArray | GlobalConfigObject (see Global Config Value reference).

We have the following items to maintain per chart:

• chartTitle which is autogenerated and can be renamed by the user
• fields array in which each item has:
  • field as fieldId from Airtable
  • chartOption as one line | bar as the Chart.js docs indicate
  • color as the Airtable color from the colorUtils
  • hex as the hex code relating to the Airtable color

To manage this, I found it most convenient to stringify this data as an object instead of setting Global Config keys and values all the way down. See the example below (globalConfig.json in the gist), which includes Global Config values to filter records by the patient and some related variables used to support a typeahead filtering component (thanks to react-bootstrap-typeahead):

{
 "xCharts": {
   "chart-1605425876029": "{\"fields\":[{\"field\":\"fldxLfpjdmYeDOhXT\",\"chartOption\":\"line\",\"color\":\"blueBright\",\"hex\":\"#2d7ff9\"},{\"field\":\"fldqwG8iFazZD5CLH\",\"chartOption\":\"line\",\"color\":\"blueLight1\",\"hex\":\"#9cc7ff\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 2:37:56 AM\"}",
   "chart-1605425876288": "{\"fields\":[{\"field\":\"fldGJZIdRlq3V3cKu\",\"chartOption\":\"line\",\"color\":\"blue\",\"hex\":\"#1283da\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 2:37:56 AM\"}",
   "chart-1605425876615": "{\"fields\":[{\"field\":\"fld1AnNcfvXm8DiNs\",\"chartOption\":\"line\",\"color\":\"blueLight1\",\"hex\":\"#9cc7ff\"},{\"field\":\"fldryX5N6vUYWbdzy\",\"chartOption\":\"line\",\"color\":\"blueDark1\",\"hex\":\"#2750ae\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 2:37:56 AM\"}",
   "chart-1605425994036": "{\"fields\":[{\"field\":\"fld9ak8Ja6DPweMdJ\",\"chartOption\":\"line\",\"color\":\"blueLight2\",\"hex\":\"#cfdfff\"},{\"field\":\"fldxVgXdZSECMVEj6\",\"chartOption\":\"line\",\"color\":\"blue\",\"hex\":\"#1283da\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 2:39:54 AM\"}",
   "chart-1605430015978": "{\"fields\":[{\"field\":\"fldwdMJkmEGFFSqMy\",\"chartOption\":\"line\",\"color\":\"blue\",\"hex\":\"#1283da\"},{\"field\":\"fldqwG8iFazZD5CLH\",\"chartOption\":\"line\",\"color\":\"blueLight1\",\"hex\":\"#9cc7ff\"}],\"chartTitle\":\"New Chart\"}",
   "chart-1605430916029": "{\"fields\":[{\"field\":\"fldCuf3I2V027YAWL\",\"chartOption\":\"line\",\"color\":\"blueLight1\",\"hex\":\"#9cc7ff\"},{\"field\":\"fldBJjtRkWUTuUf60\",\"chartOption\":\"line\",\"color\":\"blueDark1\",\"hex\":\"#2750ae\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 4:01:56 AM\"}",
   "chart-1605431704374": "{\"fields\":[{\"field\":\"fld7oBtl3iiHNHqoJ\",\"chartOption\":\"line\",\"color\":\"blue\",\"hex\":\"#1283da\"}],\"chartTitle\":\"Gráfico criado em 11/15/2020, 4:15:04 AM\"}"
 },
 "xPatientEmail": "elle@gmail.com",
 "xTypeaheadValue": "Elle Gold (elle@gmail.com)",
 "xSelectedValue": "[{\"label\":\"Elle Gold (elle@gmail.com)\",\"id\":\"elle@gmail.com\",\"name\":\"Elle Gold\",\"email\":\"elle@gmail.com\"}]"
}

Note: All data contained above, and the data included in the animations below, are not real patient data.

Here’s a look at the final result:

What About the Typeahead?

In order to filter by patient, we needed a way to select a patient and then filter the records based on this patient. In this section, we review how this was achieved.

For the typeahead, react-bootstrap-typeahead was an easy choice, as the only steps left were preparing the options for the typeahead, mixing it with an Airtable input for styling and loading bootstrap, and some other styles for our menu. Dropping components from your favorite component libraries into an Airtable app is not as straightforward as in typical React web development; however, there are only a few extra steps to get everything to look the way you would expect.

Here is the final result:

To render the Airtable input and keep all our styles consistent, react-bootstrap-typeahead comes with a renderInput prop. See more on how to modify the rendering of the component here.

For the bootstrap styles and to override our menu items, the following two utils were used from Airtable:

• loadCSSFromString
• loadCSSFromURLAsync

See frontend.js in the gist for an excerpt of the typeahead implementation.

This line was used to load bootstrap globally:

// frontend/index.js

loadCSSFromURLAsync('https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css');

You will notice some added logic for things like handling style changes on hover or restyling links (<a></a>) to get the familiar bootstrap look and feel. This also includes the handling of setting the Global Config values for the typeahead and filtering of records so that if a user leaves their dashboard, refreshes their page, or would like to share this dashboard with others, the app persists the selected patient in the Dashboard App. This also allows the users to install multiple copies of this same app side by side in the same Airtable Dashboard with different patients selected or with different charts.

Keep in mind that a dashboard in Airtable is also available to all users of the Base, so these custom app installations on a dashboard will be filtered to the same patients and charts no matter which users are looking at the dashboard at the same time.

Let’s recap what we’ve covered for far:

1. Airtable allows both non-technical users and technical users to collaborate in Airtable.
2. Typeform comes with an Airtable integration that allows non-technical users to map Typeform results to Airtable.
3. Airtable Apps provide a powerful way to supercharge its Airtable Base, whether selecting from the marketplace or building a custom app.
4. Developers can extend Airtable rapidly in nearly any way imaginable with these apps. Our example above took only three weeks to design and implement (with enormous help from existing libraries, of course).
5. A #TAG# system can be used to modify the dashboard without requiring code changes by developers. There are better and worse use cases for this. Be sure to limit permissions to the Creator role if using this strategy.
6. Using Global Config allows developers to persist data within an app installation. Mix this into your state management strategy to seed data for your components.
7. Don’t expect to drag and drop components from other libraries and projects directly into your Airtable App. Styles can be loaded using the loadCSSFromString and loadCSSFromURLAsync utils provided by Airtable.

Future-proofing

Use a more sophisticated middleware

With Typeform and Airtable, it’s easy and cost-effective to configure the mapping of questions to columns.

However, there is one big drawback: If you have a survey of more than 100 questions mapped to Airtable and you need to modify a mapping, you must delete the entire mapping and start again. This is clearly not ideal, but for a free integration, we can deal with this.

Other options would be having a Zapier (or similar) integration manage the data between Typeform and Airtable.Then you could modify the mapping of any question to any column without starting from scratch. This would have its own cost considerations to factor in as well.

Hopefully, some of the lessons learned and communicated here will help others who are looking to build solutions with Airtable.

Finally, you can check out the gist with the files discussed in this article.