Filtering records using conditions

Narrow the view of records using simple or advanced filters

Emma Montgomery avatar
Written by Emma Montgomery
Updated over a week ago

One of the key functions of views is the ability to apply filters to records within a view based on the criteria you define. When a record is excluded based on a set condition, it's important to note that the record isn't deleted; it simply becomes hidden from the specific view you're using to visualize your table.

This article aims to provide a foundational understanding of how to work with these filtering conditions. Additionally, we recommend referring to the support article on advanced filtering which delves into customizing how your records are filtered by employing sets of conditions.

A condition is a rule you establish, dictating which records are displayed within a given view. Consider adding conditions when:

  • You need to swiftly locate records that meet specific criteria (e.g., records with a due date after X date and before Y date).

  • You want to focus solely on records that satisfy certain conditions (e.g., records assigned to me AND not marked as completed).

  • Certain records in a shared view link need to be hidden from certain individuals. A compelling example of condition usage is archiving the accounts that are no longer active.

Conditions

Conditions allow you to filter out records within the view are you working in.

Think of a condition as a sentence that describes the data you are looking at. For example, show me the records where the Project Owner is Emma.

Conditions are made up of three parts:

  • Field- The field in your Table that you want to use for filtering.

  • Operator - Once the field is selected, you can choose the operator from the dropdown.

    • The filter operators available depend on the field type. For example, a Date field has filter operators such as "is on", "is before," and "is after." Whereas, Text fields have operators such as "contains," "does not contain," "is empty/not empty."

  • Value: This represents the value you will compare your records against.


Creating a condition

For example, we have a Table with several different types of properties with a single select field called Type that labels each property.

We would like to display only the records that are Residential properties. To add a new filter condition to the view, click on "Filter" in the reporting toolbar.

When creating the condition, think about the overall statement as a sentence. In this case, we need a view that shows only the properties that are residential.

The condition breakdown:

  1. Field to be assessed - the "Property Type" field

  2. Operator - We want to make sure the Property Type is "Residential"

  3. Value - Lastly, we enter the specific value we need to meet the condition. "Residential" is the value we need to achieve this view.

The final condition: Where [Property Type] is [Residential].


Removing a condition

To remove a condition, simply click on the X next to the condition.

Conditions & Conjunctions

In real-world workflows, information requests are often more complex than simple instructions like "Show me all residential properties" Instead, these requests frequently involve multiple factors and investigative angles, like combining different conditions such as "show me the residential properties AND confirm they have available units" or dealing with various priorities like "show me residential OR commercial properties."

To effectively sift through records and create meaningful views, it's essential to use a combination of conditions that can accommodate these complexities.

Using Conjunctions

You have the option to logically link conditions into a condition group using conjunctions. The two available conjunctions are "AND" and "OR":

  • Use "AND" when you require all conditions in a group to be satisfied.

  • On the other hand, use "OR" when you want any condition within a group to be met.

For example, above, two conditions are linked within the conjunction group using "OR."

This can be interpreted as "Display records where Emma is the Project Manager or where Emma is on the Project Team."

Using the "AND" Conjunction

We can leverage conjunctions to link multiple conditions and construct more detailed filters. The AND conjunction creates a condition group that requires the records to meet all the conditions specified. For example, show me all the residential properties that have available units.

Two conditions are involved:

  1. Where [Property Type] is [Residential] AND

  2. [Available Units] (a Count field) greater than 1

Using the "OR" Conjunction

Use the OR conjunction when you want any of the conditions in the group to be met.

For example:

  1. Where [Property Type] is [Residential] or

  2. Available Units is greater than 1

Toggling between AND & OR

NOTE: When using the "And" conjunction: every condition within a group must align for a record to fulfill the criteria of that filter. Conversely, with the "Or" conjunction: any of the conditions within a group can be satisfied for a record to meet the filter's requirements.

To emphasize the distinction between using the "And" conjunction and the "Or" conjunction, let's observe the outcome when we switch from one to the other. In the GIF below, we start with a condition group that states: "[Property Type] [is] [Residential] AND [Available Units] is [greater than] [1].

Utilizing the "Or" conjunction implies that any condition within the group can be fulfilled. Consequently, instead of displaying records that meet both "Residential AND the Available Units is greater than 1," we're now presenting records that satisfy either "Residential OR the available units is greater than 1" Selecting the appropriate conjunction can have a significant impact on the outcome.


Filter Groups

Filter groups allow you to construct views that display just the right set of data for a particular workflow, narrowing down the data that is presented to match complex criteria that you specify.

Use filter groups to specify a set of conditions that contain AND or OR conditions that should be matched. Filter groups allow you to combine AND and OR operators within the same filter.

For more information on how to use filtered groups, visit this article.


Operator Options Based on Field Type

Numeric, Date, Values List and Text field types have distinct filter operators to use in combination with the filter criteria.

Filtering with Numeric Fields

Numeric field operators include: equal to, not equal to, greater than, less than, equal to or greater than, equal to or less than, is empty, is not empty.

Entering a value in the criteria field automatically applies the filter to the current view or saved view.

Filtering with Date Fields

Date field operators include: is, is not, is before, is after, is on or before, is on or after, is empty, is not empty.

Select an operator in combination with a filter criteria: Today, Tomorrow, Yesterday, The past week, The past month, The past year, The next week, The next month, The next year, The next number of days, The past number of days, Exact Date / Range.

Additional filter criteria is required when The next number of days, The past number of days, or Exact Date / Range are selected.

Filtering with Values List Fields

Values List field operators include: is, is not, is any of, is none of, is empty, is not empty.

The field criteria field automatically loads the values list. When the " is any of " or " is none of " operators are selected, multiple values can be selected in the same filter.

Filtering with Text-based Fields

Text-based field operators are: is, is not, is empty, is not empty, contains, does not contain.

Text-based fields for the purpose of filtering include Text, Text Area, SmartDoc and Linked Record fields.

Text filters can be applied to Files & Image fields.

Using Multiple Filters

Pare down your data to the perfect saved view using multiple filters at once. Select between AND/OR for each filter level.

Filters are applied instantly and saved automatically. Click CLEAR ALL to remove all filter criteria, or click the trash icon to remove filters one at a time.


Filtering with View Types

Filtering in Grid View

Grid view supports quick filtering with the Column Controls.


Filtering in Card & Kanban View

Filters instantly remove cards from view.


Filtering in Calendar, Timeline & Map Views

Filtering in Calendar, Timeline and Map views reorders cards in the Record Listing panel and removes records from view.


Pro tip: Filter by Current User

Create one report that will allow everyone on your team to see the work assigned to only them (the Current User)!

Follow these steps:

  1. Go to the desired Saved View

  2. Click on Filter in the Toolbar

  3. Select the Assign To Field you want to filter by... 'is exactly' ... Current User

Now, any time a user access this saved view, they will only see fields assigned to them; no need for a separate saved view for everyone πŸ˜ƒ .

Did this answer your question?