logo
StoreMy Account
Forms
Apr 16

How to filter linked record fields in Airtable forms

Customer Support Engineer

Linked record fields are a great way to connect different tables in your Airtable workspace, but they can get a little overwhelming if you're linking to a big table. Although Airtable introduced basic filtering for linked records in forms, it didn't quite meet expectations:

Scott Rose
Unfortunately, the Airtable community quickly realized that this feature was not as great as expected, because it is way too limited to be used in the majority of business needs.
Scott Rose in this Airtable Community post

In this article, I'll showcase several examples of filtering linked record fields in Airtable forms made with Plumsail. This feature is available for free and supports extensive customization.

Final result  

In this article:

Set up an Airtable form with Linked Record field

I've prepared two simple tables in Airtable to store employees and their tasks. Each task must have an assigned employee, so this is a perfect use case for a linked record column.

Task table  

An employee might have some numeric data, text columns, and multiple choice fields. For example, a name, an hourly rate and a set of skills or qualifications:

Employee table  

I also created a Plumsail form connected to the Tasks table and added the Assignees field. Note that you can use both common fields and Airtable fields on the same form. The common fields are not saved to Airtable, so I'll use them to temporarily store the filtering criteria.

Airtable form made with Plumsail  

If this is your first time working with Airtable forms in Plumsail, follow this guide on setting up the Airtable integration.

Dynamic filtering in linked record fields by a numeric column

Let's configure the form to only show employees with an hourly rate below a certain amount:

Filter a linked record field by a number column  

If you're new to JavaScript in Plumsail Forms, check out this detailed guide on accessing form fields from JavaScript. If not, add a rendered() function to your form and save both the linked record field and the filter query field to variables like this:

fd.rendered(() => {
    const rateField = fd.field('Rate'); // get the common rate field
    const linkedRecordField = fd.field('fldce75XAnNUID5qg'); // get the linked record field
});

 

To filter the field, you need to modify its formula function to return custom filtering criteria:

linkedRecordField.formula = () => {
  return `${airtableColumnRate} < ${rate}`;
};

 

airtableColumnRate from the previous code snippet is supposed to hold the name of the Rate column in Airtable. Open the table and click the small arrow in the header of the column:

Copy field URL  

You will get something along these lines: https://airtable.com/app6DUfe265bVON9x/tbLusOTKLYM3q3Tk0/viw5JXdem0IY65ys7/flko5lJx6TkTEzWOJ. Copy the last part and add it to your code:

const airtableColumnRate = 'flko5lJx6TkTEzWOJ';  // save the Airtable column name in a variable

 

Add a special line to refresh the linked record field whenever the filtering criteria are changed, and you're done.

rateField.$on('change', linkedRecordField.refresh); // refresh the linked record field whenever the rate field is updated

 

The full code should look like this:

fd.rendered(() => {
    const rateField = fd.field('Rate'); // get the common rate field
    const linkedRecordField = fd.field('fldce75XAnNUID5qg'); // get the linked record field

    const airtableColumnRate = 'fld6tjQdTxybvKGvZ'; // save the Airtable column name in a variable

    rateField.$on('change', linkedRecordField.refresh); // refresh the linked record field whenever the rate field is updated

    linkedRecordField.formula = () => {
        const rate = rateField.value;

        if (rate !== null) return `${airtableColumnRate} < ${rate}`; // return the filtering criteria for hourly rate
        else return ``; // if the rate field is empty, don't filter the linked record
    };
});

 

Open the JavaScript tab in Plumsail Forms and add it there:

Code in the Designer  

Tip: To show employees with a higher rate instead, change '<' to '>' in the formula.  

Dynamic filtering in linked record fields by a text column

Let's start with something simple, like using the FIND() function to check if the employee's name includes the filter query.

Filter a linked record field by a text column  

We need to use Airtable formulas in our API request to do anything more complicated than comparing two numbers. Feel free to read up on Airtable's extensive documentation on formulas.

fd.rendered(() => {
    const nameField = fd.field('Name'); // get the common name field
    const linkedRecordField = fd.field('fldce75XAnNUID5qg'); // get the linked record field

    const airtableColumnName = 'fldIHKGr7oaTGiZsV'; // save the Airtable column name in a variable

    nameField.$on('change', linkedRecordField.refresh); // refresh the linked record field whenever the rate field is updated

    linkedRecordField.formula = () => {
        const name = nameField.value;

        if (name !== null && name.length > 0) return `FIND("${name}", ${airtableColumnName}) != 0`; // return the filtering criteria for name
        else return ``; // if the name field is empty, don't filter the linked record
    };
});

 

Note that the functions only work if they are written in uppercase.

Now if we enter "t" into the Name field, the Assignee field will only display employees with a lowercase "t" in their name. However, Tom's name doesn't show up because he only has an uppercase "T". Let's adjust the code to make filtering not case-sensitive. We can do this by casting both arguments of FIND() to lowercase with LOWER():

if (name !== null && name.length > 0) return `FIND(LOWER("${name}"), LOWER(${airtableColumnName})) != 0`;

 

Much better:

Filter a linked record field by a text column regardless of case  

Dynamic filtering in linked record fields by a multiple choice column

Now let's step up the game and compare the employee's list of skills against the value of a common multiple choice field.

Filter a linked record field by a multiple choice column  

We can achieve this by putting all filtering conditions into an array and then using join(',') to assemble them into a long string:

return `AND(${array.join(',')})`

The full code for this example should look something like this:

fd.rendered(() => {
    const skillsField = fd.field('Skills'); // get the common skills field
    const linkedRecordField = fd.field('fldce75XAnNUID5qg'); // save the Airtable column name in a variable

    const airtableColumnSkills = 'fldMNgQVs1e7DWngr'; // save the Airtable column name in a variable

    skillsField.$on('change', linkedRecordField.refresh); // refresh the linked record field whenever the rate field is updated

    linkedRecordField.formula = () => {
      const skills = skillsField.value;
      if (skills.length > 0) {
        const conditions = skills.map(skill => {
            return `FIND(LOWER("${skill}"), LOWER(${airtableColumnSkills})) != 0`;
        }); // create separate conditions for each skill chosen in the common field

        if (conditions.length === 0) return '';
      }
      
      return `AND(${conditions.join(',')}, ${airtableColumnSkills} != '')`; // return the filtering criteria for skills
    };
});

 

Tip:

You can replace AND() with OR() in the formula to get all employees who have at least one of the specified skills. If you do that, use an additional AND() to add the empty check:

else return `AND(OR(${conditions.join(',')}), ${airtableColumnSkills} != '')`;

 

Dynamic filtering in linked record fields by several columns at the same time

Finally, let's assemble an ultimate example that filters the field based on everything at the same time.

Final result  

We need to put all conditions into an array and assemble them into a formula with the AND() function:

fd.rendered(() => {
    const nameField = fd.field('Name'); // get all fields from the form
    const rateField = fd.field('Rate');
    const skillsField = fd.field('Skills');
    const linkedRecordField = fd.field('fldce75XAnNUID5qg');

    const airtableColumnSkills = 'fldMNgQVs1e7DWngr'; // save all Airtable column names
    const airtableColumnName = 'fldIHKGr7oaTGiZsV';
    const airtableColumnRate = 'fld6tjQdTxybvKGvZ';

    skillsField.$on('change', linkedRecordField.refresh); // refresh the linked record field whenever any of the fields is updated
    nameField.$on('change', linkedRecordField.refresh);
    rateField.$on('change', linkedRecordField.refresh);

    linkedRecordField.formula = () => {
        const skills = skillsField.value;
        const name = nameField.value;
        const rate = rateField.value;

        const conditions = [];

        if (name !== null && name.length > 0) conditions.push(`FIND(LOWER("${name}"), LOWER(${airtableColumnName})) != 0`);

        if (rate !== null) conditions.push(`${airtableColumnRate} < ${rate}`);

        if (skills.length > 0) {
          const skillConditions = skills.map(skill => {
              return `FIND(LOWER("${skill}"), LOWER(${airtableColumnSkills})) != 0`;
          });

          if (skillConditions.length > 0) {
              conditions.push(skillConditions.join(','));
              conditions.push(`${airtableColumnSkills} != ''`);
          }
        }

        if (conditions.length === 0) return '';
        else return `AND(${conditions.join(',')})`;
    };
});

 

Note how I put all skill requirements into a separate array, only to assemble them into a string and put into the main array as an element. The resulting formula looks like a list of criteria inside of an AND():

AND(FIND(LOWER("Name"), LOWER(fldkkGHQFPRqKeFUN)) != 0, fldo5lJx1TkTEzWOJ < 30, FIND(LOWER("Excel"), LOWER(fld15pF0R9Xoqb9Fw)) != 0, FIND(LOWER("SharePoint"), LOWER(fld15pF0R9Xoqb9Fw)) != 0, fld15pF0R9Xoqb9Fw != '')

 

Get started with Airtable forms in Plumsail

The examples provided in this article should cover most of your bases. If your use case requires more complex string manipulation, you can always get some advice from our Community. Don't shy away from writing an email to support@plumsail.com either, we'll be happy to help you with any problem.

You can create a free account any day and test all features with no restrictions for as long as you'd like. Get started with Plumsail today and follow this guide to get started with Airtable forms in Plumsail.

Happy automation!

Forms

Ilia is passionate about writing and problem solving. He's driven to create the optimal solution for each user of Plumsail, so that our products can bring the most value to customers.

Discuss the post
Documents
Forms
HelpDesk
Actions
Org Chart
Charts
Customer Story

Related posts