How to get linked records using the Airtable API

This post is part of a series all about working the Airtable API. If you’re not familiar with Airtable, you should definitely check it out and if you’re not sure about getting started with their API check out my previous article How to get started with Airtable’s API.

In this post, I’m going to go over various practical scenarios when dealing with tables with linked records.

• Display records and their associated linked record details
• Display a record that has a specific linked record

Just want the code?

If you would like to follow along you can get the base code, my Express Airtable Starter or get the full working demo and explore the code.

Display records and their associated linked record details

Let’s say we have the following product inventory base.

Notice we have a linked field to the Suppliers table. When we display our product listing page we would like to be able display all the suppliers that are related to each listed product. See screenshot below.

Let’s see how we can go about this.

First let’s create some helper functions that will focus on getting records from Airtable based on the table and view. In a dataController.js file we will write a function getAirtableRecords.

// dataController.js
exports.getAirtableRecords = (table, view) => {
  let records = [];
  return new Promise((resolve, reject) => {
    // Cache results if called already
    if (records.length > 0) {
      resolve(records);
    }

    const processPage = (partialRecords, fetchNextPage) => {
      records = [...records, ...partialRecords];
      fetchNextPage();
    };

    const processRecords = (err) => {
      if (err) {
        reject(err);
        return;
      }

      resolve(records);
    };

    table.select({
      view,
    }).eachPage(processPage, processRecords);
  });
};

We would need to require this file into our productController.js file so we can use the function.

// productController.js
const data = require('./dataController.js');

Let us now define our route to display the products. I’m going to use the home page. In our index.js file we define all our routes. Let’s define a route to the root of the app (home page) and call a function called displayProductsWithSuppliers functions in the product controller.

// index.js
const express = require('express');
const router = express.Router();
const productController = require('../controllers/productController');

router.get('/', productController.displayProductsWithSuppliers);

module.exports = router;

Before we can visit our page, we have to create the displayProductsWithSuppliers function in our productController.js file. Let us do that now.

// productController.js
/**
 * Get products with their suppliers
 */
const getProductsAndSuppliers = async (page) => {
  const products = await data.getAirtableRecords(TABLE, VIEW, LIMIT);

  const count = products.length,
        pages = Math.ceil(count / LIMIT),
        offset = (page * LIMIT) - LIMIT;

  return products.map( product => {
    return {
      id: product.getId(),
      name: product.get('name'),
      qty: product.get('qty_in_stock'),
      pages,
      suppliers: product.get('suppliers'),
    }
  }).slice(offset, LIMIT*page);
}

const getSupplierById = (id) => {
  return new Promise((resolve, reject) => {
    const processRecord = (err, record) => {
      if (err) {
        reject(err);
        return;
      }

      const id = { id:record.id };
      const fields = record.fields;
      record = {...id, ...fields};
      resolve(record);
    };

    base('suppliers').find(id, processRecord);
  });

}

/**
 * Replace supplier ids in product with
 * actual supplier information
 */
const hydrateSuppliers = async (product) => {
  let supplierIds = product.suppliers;
  let supplierDetails = [];
  for( const id of supplierIds) {
    supplierDetails.push(await getSupplierById(id))
  }

  product.suppliers = supplierDetails;
  return product;
}
exports.displayProductsWithSuppliers = async (req, res) => {
  let page = req.params.page || req.query.page || 1;
  getProductsAndSuppliers(page)
    .then( async (products) => {
      const hydratedProducts = [];

      for(const product of products) {
        hydratedProducts.push(await hydrateSuppliers(product));
      }

      res.render('index', { products: hydratedProducts, page, count: products.length });
    });
}

This above functionGet the current page. Irrevelant to this tutorial but I have added pagination on the page.

1. We get all the products (getProductsAndSuppliers() )
2. We then “hydrate” the product suppliers fields with the supplier information from the supplier table using hydrateSuppliers(). So we are replacing the array of IDs in the suppliers property Airtable has given us with the actual supplier details. This returns a new array of products with the supplier details
3. We pass the new products array to our template

You can ignore the page and count, as this is just there for pagination. If you would like to learn about paginating Airtable records, read my tutorial How to paginate records in Airtable when using the API.

Displaying records based on a specific linked record

You may need this use case if you for example want to filter the product listing by suppliers, like below:

Here’s how we would go about that. Let’s make some changes to our index.pug file to include a dropdown fields that will list all the suppliers. We are going to add this above our product listing markup.

//- index.pug
.flex.box
    form.form.form--filter(action=`/` method="POST")
      .field.has-addons
        .control.has-icons-left
          span.select
            select#supplier(name="supplier")
              option(value="") Filter by supplier
                each supplier in suppliers
                  option(value=`${supplier.id}`) #{supplier.name}
          span.icon.is-small.is-left
            i.fas.fa-filter
        .control
          input.input.button.is-primary(type="submit" value="Filter")

    a.button.is-link.ml-24(href="/") Show All

Let create a route for what happens when the user submits the filter form, that is, send a post request to our home route. Note in the form markup above the action is set the home page. Our index.js file now becomes the following.

// index.js
const express = require('express');
const router = express.Router();
const productController = require('../controllers/productController');

router.get('/', productController.displayProductsWithSuppliers);
router.post('/', productController.filterBySupplier);

module.exports = router;

We now have to write our filterBySupplier function in our product controller. This function will use a helper function getUniqueSuppliers. This is necessary because a product can have multiple suppliers and we don’t want to repeat suppliers in our dropdown.

I have abstracted the filtering of the products into helper function filterProducts for ease of reading. This functional takes in all products, looks at the ids and using the handy ES6 javascript array includes method to check for matches.

In the filterBySupplier function we use filterProducts to create a new array of products (also hydrated with our suppliers details as we did previously above). We can then pass this products array to our template to displayed the filtered records. Add the following to your productController.js file.

// productController.js
const getUniqueSuppliers = (products) => {
  let allsuppliers = [];
  for( let i = 0; i < products.length; i++) {
    products[i].suppliers.map( supplier => allsuppliers.push(supplier));
  }

  const suppliers = allsuppliers.filter(function(obj, index, self) {
    return index === self.findIndex(function(t) {
      return t['id'] === obj['id']
    });
  });

  return suppliers;
}

const filterProducts = (products, supplierId) => {

  function filterById(product) {
    let ids = product.suppliers;
    if(ids.includes(supplierId)) {
      return true;
    }
    return false;
  }

  let filteredProducts = products.filter(filterById);

  return filteredProducts;
}

exports.filterBySupplier = async (req, res) => {
  let supplier = req.body.supplier,
      page = req.params.page || req.query.page || 1;

  getProductsAndSuppliers(page)
  .then( async (products) => {
    let filteredProducts = filterProducts(products, supplier),
        allHydratedProducts = [],
        filteredHydratedProducts = [];

    for(const product of filteredProducts) {
      filteredHydratedProducts.push(await hydrateSuppliers(product));
    }

    for(const unfilteredProduct of products) {
      try {
        allHydratedProducts.push(await hydrateSuppliers(unfilteredProduct));
      } catch (error) {
        console.error(error)
      }
    }

    const suppliers = getUniqueSuppliers(allHydratedProducts);

    res.render('index', { products: filteredHydratedProducts, page, count: products.length, suppliers });
  });

}

There you have it folks, getting linked records using Airtable API can be a bit tricky because of the multiple steps but once you think through what you are trying to achieve step by step it becomes pretty straightforward.

If you liked this article, consider subscribing to my mailing list to receive more content like this and don’t forget to share.

First Name Email Address   Subscribe