Guide to filtering records using conditions

One of the core features of views is the ability to filter out records from a view according to 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. 

This article will go over the basics of working with conditions. We also recommend reading the support article on advanced filtering to fully customize how your records are filtered by utilizing condition groups. 

Overview

A condition is a rule you set that records must follow to be visible in that particular view. 

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 in a view share link.

One great example of how to use conditions is to archive old projects that are no longer relevant.  For this guide, we will be using the "Product catalog" template; please feel free to create a copy of the base if you'd like to follow along!

Conditions

You can create a condition to filter out records within the view you are currently viewing.

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

A condition consists of three parts:

1. A field - Any field in your table.
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."
3. A value - This is the value you compare all of your records against—using the field you just specified.

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

Creating a condition

Let's start with a quick example. Here we have a table with many types of furniture, with a field called Type that tracks the type of each piece of furniture:

We want to set a condition only to show the records that represent chairs in this table.

To add a new filtering condition in a view, start by clicking the "Filter" button, then clicking the "Add condition" button:

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]":

Here is how quickly we set up that new condition in real-time:

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.

Removing a condition

You can remove a condition from a view by clicking on the trash can icon next to that condition:

Rearranging conditions

You can easily rearrange conditions by clicking on the pull handle for that condition, then dragging and dropping it where desired:

Conditions and 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.

This is where the ability to logically connect your conditions using a conjunction becomes invaluable.

Using conjunctions

You can logically connect conditions together, into a condition group, using conjunctions. 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 "Or" when you'd like any condition in a group to be met.

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."

Using the "and" conjunction

Going back to our Furniture table example, we can use conjunctions to chain multiple conditions together to build out more complex filtering.

We 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]".

We already have one of those conditions in place so let's add a second condition the same way we did the first. 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:

Using the "or" conjunction

If you want any of your conditions to be met in a condition group, you can use the "Or" conjunction. We'll create another view where we want to see 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":

Toggling between "and" & "or"

To highlight the difference between using the "And" conjunction, and using the "Or" conjunction, let's see what happens when we toggle the conjunction from one to the other. In the below GIF, we start with the condition group of: “[Type] [is] [Chairs] AND the [Unit cost] is [>] than [$1,000]”. :

Using the "Or" conjunction means that any of the conditions in the group can be met. So now, instead of showing records that are "Chairs AND the unit cost is greater than $1,000", we are now showing records that are "Chairs OR the unit cost is greater than $1,000". Picking the correct conjunction for the job can make a big difference.

FAQs

Is the ability to filter views available on mobile devices?

If you are on a mobile device, your filtering experience will look slightly different; check out this article for more details.