Filtering records using conditions
  • 29 Jan 2024
  • 10 Minutes to read
  • Dark
    Light
  • PDF

Filtering records using conditions

  • Dark
    Light
  • PDF

Article Summary

Plan availability

All plan types

Permissions

  • Owners/Creators - Can create, delete, and modify filters across Airtable

  • Editors - Can create, delete, and modify filters in views

  • Commenters - Can create, delete, and modify filters in personal views

  • Interface end users - Can create and modify filters in visualization layouts when it has been enabled by an interface builder

Platform(s)

Web/Browser, Mac app, Windows app, and some filtering functionality available on mobile platforms

Understanding filtering in Airtable

Using filters in Airtable is fundamental to gaining the maximum power of Airtable’s platform. There are several surfaces throughout Airtable where filters can be configured to hone what is seen or interacted with by end users:

  • Interfaces - Using filters in interfaces is powerful because it allows builders to create a way for end-users to interact with highly specific information that may only relate to individual teams, projects, or insights across visualizations on an interface page.

  • Views - One of the core features of views is the ability to filter out records from a view according to the conditions you set. When a record is filtered out based on a condition, it is not deleted—it's just hidden from the particular view you're using to look at your table.

  • Automations - Using conditions in automations allows you to apply logic so that the automation only runs or performs certain actions when particular conditions are met. The “When record matches conditions” trigger, conditional logic in groups of automation actions, and “Find records” action all leverage conditions to increase the precision of automation runs.

  • Forms - Adding visibility rules to forms allows builders to control when and whether form submitters can input information in form fields. One example of how to apply filters in forms is available here.

A condition is a rule you set that allows:

  • Records to be visible in a particular view or interface

  • Records to be found or watched in an automation run or for particular groups of automation actions to be run

  • Fields to be hidden in forms until some other field has been entered or entered in a particular way

Try adding conditions when:

  • You're trying to find a record or records that meet certain conditions quickly (e.g., records that have a due date after X date and before Y date).

  • You only need to focus on records that meet certain conditions (e.g., the records that have been assigned to me AND have not been marked as completed).

  • You need to prevent people from seeing certain records such as in a view share link.

Understanding the components of a condition

Fields, operators, and values

4409138365463singlecondition.png

A condition reads much like a sentence; the example above can be read as: "Show me the records where the DRI is Jordan Peretz."

  1. A field - Any field in your table. (The “DRI” field using the example above)

  2. An operator- After selecting a field, choose an operator from the pre-populated dropdown menu.

    • Which filter operators are available is determined by the field type. For example, text fields will have filter operators like "contains," "does not contain," "is empty," or "is not empty"; number fields will have filter operators like "is greater than" or "is less than."

    • Note that operators "is empty" and "is not empty" do not require you to input a value because the default value is empty.

    • Airtable also offers functionality that lets you filter by specific linked records by using filter operators such as "is exactly", "has any of", "has all of", or "has none of" along with a linked record field. This eliminates the need for complex string-matching filters.

  3. A value- This is the value you compare all of your records against using the field you specified earlier in the conditional statement.

Want an additional example? Click here!

When you are figuring out a new condition you'd like to create, we recommend stating the rule you'd like to set as a sentence first. For example, you can make statements like "Where [Priority] [is any of] [High priority, Medium priority]" or "Where [Due date] [is before] [today]."

Trust us on this one! By spelling out your condition into a statement, it'll help crystallize which fields, values, and operators should be involved.

So our new rule will be something like, "A record must be of the chair type to be visible." Our condition breaks down easily into three parts:

  1. Field to be evaluated - The “Type” field is the field we mentioned in our new rule above.

  2. Operator - we want to ensure the Type is "Chairs."

  3. Value - finally, we enter the value, "Chairs," that our Type field needs to have the value of to meet this rule.

Summing that up, we want to set a condition to see records "Where the [Type] [is] [Chairs]":

When you're done setting the condition's field, operator, and comparison value, notice that the filtering field is now shaded with a light green color. This makes it more obvious that there's been a filter applied in the view you're currently using.

Conjunctions

In any workflow, information requests are rarely as simple as, "Show me the chairs." More often, questions are complex and investigatory, combining different variables (i.e., "show me the chairs AND make sure the chairs are in stock") or different priorities (i.e., "show me the chairs OR tables").

Using a combination of conditions is essential for filtering through your records to create valuable views, interfaces, automation setups, and form workflows. This is where the ability to logically connect your conditions using a conjunction becomes invaluable.

The two conjunctions available are "And" & "Or":

  • You can use "And" when you want all conditions in a group to be met.

  • Otherwise, you can use "Or" when you'd like any condition in a group to be met.

4409556140951stylizedconditiongroup.png

As you can see in the above image, two conditions are connected into a condition group using the "or" conjunction. The condition group is read together as a new rule: "Show me when Jordan Peretz is the DRI OR show me where Jordan Peretz is on the Team."

Example of using the “And” conjunction

You can use conjunctions to chain multiple conditions together to build out more complex filtering.

In this example, will use the "And" conjunction to create a new condition group where records must meet all of our conditions. Let's get a little more complex with our rules: "We want to see all chairs with a unit cost of over $1,000".

We can break this down into two conditions:

  1. Where the "[Type] [is] [Chairs]", and

  2. The "[Unit cost] is [greater] than [$1,000]".

When adding a second condition, clicking the "Add Condition" button will add both a conjunction and a new condition to be filled in. The "And" conjunction is selected by default. Now we can add our second condition, and by using "And" as our conjunction, we ensure that all records must meet both conditions to be visible in the view.

Example of using the “Or” conjunction

If you want any of your conditions to be met in a condition group, you can use the "Or" conjunction. In this example, we'll create a view where we want to see records either:

  1. Where the "[Type] [is] [Chairs]", or

  2. The "[Materials] [has any of] [Reclaimed wood]".

By clicking on the conjunction type, you can choose between "And" & "Or.”

Using conditions in Airtable

Conditions in interfaces

Several components of interfaces might include the option to configure filters. Briefly, these include:

  • Setting conditional filters for interface visualization layouts

  • Allowing users who have access to an interface visualization the ability to configure their own filters

  • Setting filters for dashboard layouts to gain specific insights into underlying data

  • Utilizing and configuring filter elements (Blank, Record Review, and Record summary layouts only) on interface pages

Conditions in views

Depending on the view type you set up, filtering conditions may play a slightly different role in the way that the view ends up appearing. But, in general, filtering just alters the type of records that are shown in the data layer presented in the view.

Conditions in automations

Adding filters to automations allows you to prevent unexpected runs and only perform certain actions or groups of actions when conditions are met. This is important because narrowly-scoped automations are generally more trustworthy and will only update records when necessary.

Conditions in forms

There are several reasons why you might choose to utilize conditions in the form builder. In general, you’d want to utilize conditions any time when:

  • You want to hide fields until other fields are entered

  • You only want certain form submitters to see certain form fields

  • You want to create efficient form flows to save submitters time and ensure they only fill out absolutely necessary fields

Rearranging conditions

With the conditions menu open (from any surface that supports filters), find the six-dot icon to the far right of the condition that you want to move. When hovering over that icon, you should see your cursor/mouse change to a hand. Click and drag the filtering condition to the appropriate ordering location of your choice. Keep in mind that conjunctions (And/Or) may be altered depending on how many conditions you have set or the position of the condition that you are moving.

Removing conditions

With the conditions menu open (from any surface that supports filters), find and click the trash icon next to the condition (or, in some cases, conditional group) that you want to delete.

Condition groups and advanced filtering

Nesting condition groups

Now let's expand on this concept of conditional filtering. You can use conjunctions to combine and connect condition groups nested within other condition groups.

Combining conditional groups allows you to start building more and more complex filters. For example, we can create a rule to “Show chairs, AND those chairs must be in stock OR show furniture made of reclaimed wood”:

4409564339479twoconditionalgroupssecondlevel.jpg

We strongly recommend playing with conditional groups as you read along. Advanced filtering is a feature with a delightfully mix-and-match feel that is best learned by doing.

Creating new condition groups

There are two ways to create a new condition group.

  • The first way is from the first level condition group, by clicking on the Add condition group button:

    4409572680215addnewconditiongroup.gif

    This will add an additional condition group nested within the first level condition group we’ve been using up to this point:

    4409564400407secondlevelnestedcondtiongroups.jpg

  • The second way to create a new condition group is from within a second level nested condition group:

    4409564425239addgroupdropdown.png

    This will create a nested condition group on the third level. You can nest condition groups for a maximum depth of three levels:

    image.png

Some important things to note:

  • You cannot nest a group any deeper than the third level and the option to add a group will be grayed out

  • Though three levels are the maximum depth level possible, you can still achieve complex and precise filtering by combining conjunctions with multiple levels of condition groups:

    4409572792343multiplenestedconditiongroups.jpg

  • Each level of a condition group can have multiple conditions, but only a single conjunction. Meaning that each condition group can use either “And” or “Or,” but not both.

FAQs

Are there any special considerations to be aware of when changing filters in views?

Yes. When you change view filters we recommend turning off any automations that may be associated with the view that you are altering. This is because live changes to filters can often cause unintentional automation runs while you are modifying them.

Where are advanced/nested filters currently integrated within the product?

You can currently access advanced filtering within the view filtering menu, record coloring, conditional form fields, and interfaces. This functionality is not available in the Airtable Automations feature.

What if I try adding a condition to a nested condition group and make a mistake? Is there a way to revert to the original?

You can easily rearrange, add, or remove conditions from any of your condition groups. If you are in the process of building a new view and are playing around with filters, we recommend creating a duplicate view. Within your new duplicate view, you can change filtering without affecting any ongoing workflows or other collaborators.

What are the best practices for migrating my current filter configurations to advanced filter options?

Advanced filters will not change any of the filtering conditions you previously had set up in your views. However, you will be able to add new condition groups—the world is now your oyster!
Using our product example above, imagine a customer comes in asking for either a:

  1. A living room piece, OR alternatively,

  2. Something made of wood OR above a price point of $3,500 per unit AND is in stock now.

Advanced filters can help you quickly get to the data you need. Here is a visual for the scenario we just described:

4409573050007threelevelsofnestingnospotlight.jpg

Will I be able to copy view configurations that have advanced filters?

Yes, you can copy a configuration from one view to another within the same table. Either follow the procedure described in this article or click on the “Copy from another view” option at the bottom of the filter menu:

4409564814231copyfromanotherviewconditiongroups.png

This functionality does not exist across other areas of the product, just views.


Was this article helpful?