Filter Records

When you're creating a report or doing an advanced search operation, often you want to see only records that fall within specific parameters. For instance, you may want to see records that are assigned to a particular user, or only those records with a certain status. To do so, you set initial filter criteria to tell Quickbase what values the data must match to meet your requirements and appear in the set of records returned.

Note: Setting initial filter criteria differs from configuring dynamic filters. The initial filter criteria determine what records are shown when the report or search is run. The dynamic filters are shown along with the query results.

This topic describes:

• Creating a filter

• Comparing a field to a specific value

• Comparing the value in one field to the value in another

• Filtering on dates relative to today’s date

• Adding and removing filter criteria

• Re-ordering filter criteria

• Reporting on file attachment fields

• Filtering example

If you want to filter on List - User fields, you need to understand how the operators you choose will affect your results. Click here to learn more.

Note: If you use the Report Builder, you can create both simple and complex queries. Learn more.

Creating a filter

When you create a filter, you're telling Quickbase to return only records that have passed some rule or condition you've created. To create a condition, you specify the following:

• The field you want to filter on. For example, if you’re searching for the field “Start Date,” typing in “Date” will get you a filtered list of all field names that include “Date” along with any Date or Date/Time fields on your table

• An operator. The operator tells Quickbase how to filter the records. Example operators are: is equal to, is greater than, is greater than or equal to, and so forth.

• A matching value or a value in another field. Depending on the type of field you're filtering on and what operator you chose, you may have to type in a value or choose one from a list.

For instance, if you want to see all tasks that are in progress, you'd choose the following:

Comparing a field to a specific value

You can create reports that find records where fields match a certain value that you specify. To create a report that finds a specific matching value, you simply enter or select a value in the field that appears when you choose an operator.

Comparing the value in one field to the value in another

You can also write reports that find records where a field value matches the value in another field of the same type.  For instance, in a project management application, you might want to create a report that shows records where the value in the Estimated Hours field exactly matches the value in the Actual Hours field

Field types that can be used as a query's matching value

Note that you can compare only fields of the same type. In the example below, the two fields being compared are both Date fields. When you choose "the date in the field," Quickbase displays only those fields whose type matches the field type of the field being compared.

The following table lists valid matches for all field types:

Field types that can't  be used as a query's matching value

You cannot use the following field types as the matching value in a query:

• Predecessor

• iCal

• vCard

• File attachment

• Duration

• Report link

Filtering on dates relative to today’s date

When filtering on dates, you can use the following operators to compare a date field with a specific date you enter:

• is equal to

• is not equal to

• is before

• is after

• is on or before

• is on or after

You can also use two additional operators to find records relative to today’s dates:

•  is during

•  is not during

These operators let you search for records that contain dates relative to today—that is, you can find records that are due this week, next month, and in the next 3 quarters. Learn more about reporting with relative dates.

Adding and removing filter criteria

If you want to specify criteria for more than one field, Quickbase makes it easy. If, for instance, you want to see all overdue project tasks in a certain project for a certain employee, you'll want to filter on three fields: Assigned to, Project Name, and Days Overdue.

Quickbase makes it easy to add or remove filtering criteria to your report.

Quickbase starts you off with a single field. You can set a single line of criteria, or add more fields if you'd like. Simply hover your mouse over the field dropdown. Plus and minus sign icons appear to the right of the field.

At any point in your list of criteria, you can use the plus sign () icon to add another line of filter criteria. Use the minus sign ()  icon to delete the current line.

Re-ordering filter criteria

If you are working with a lot of data, sometimes re-ordering your filter criteria will speed up reporting. Filter criteria are executed in order, so for best performance, put the filter criteria that eliminate the largest number of records first. Instead of searching for Task Name contains "network", then Task Status is equal to Overdue, reversing the order of those filters might make the report run much faster.

Simply hover over a row in the list of filter criteria, then click anywhere in the blue highlighted area. The row is selected, and up () and down () controls appear on the left side of the row. Use these controls to move the selected row up or down.

Note: The selected row may only be moved up or down in its current indentation level. If you'd like to indent a row to create a filter set, use the new set () icon. Learn more about filter sets.

To de-select the row, click the selection highlight or a row control on the right.

Reporting on file attachment fields

You can choose to filter on records that contain file attachments that contain certain words or phrases. See Search for or within a Document for information on how to build search criteria for file attachments.

Filtering example

Imagine that you have a project management application, and are creating a Table report to display only the tasks assigned to Colleen Garton.

1. Select Filter tasks.

2. From the list of fields in the dropdown that appears, select the field containing the value you want to filter on. In this example, you'd select Assigned To.

3. From the list of operators in the second column, select the operator that qualifies the value you're about to specify. In this case, select is.

4. In the third column, specify the resource whose tasks you want to display.

   Note: For some types of fields, the third column displays as a dropdown. Select an item from the list, or, if you want to enter text or set OR criteria, select <other...> and then enter text in the box that appears.

5. Click Display in the Page bar to show the new report.

Related topics:

• Building queries using the Report Builder

• Includes and Does not include

• Understanding relative date ranges

• Sorting/Grouping

• Columns to display

• About Row Colorization (Highlighting)