This topic describes how to filter the data you want to connect using a connected table. After you create your connected table, you can set filters on the connected table to view an even more refined subset of your data. Only the connection owner can create or edit a filter on a connected table.
Filtering the data to connect
You can set a filter to limit the data that you connect from an external service, such as QuickBooks Online or Salesforce.com. Setting a filter helps restrict connected records to the ones you really care about; for example, invoices from this year, rather than all invoices.
The filter criteria that you set determines the values that the data must match in order to appear in the set of records returned; for example, only records with an Active status.
You can filter on any of the fields in your source data - not just the fields that you selected to connect. This helps you refine your connected data, without bringing unnecessary fields into your connected table.
You can set or change a filter on a connected table any time.
To create or edit a filter on a connected table
-
Open your connected table and select Settings.
-
Click the Connection, for example, QuickBooks connection.
-
On the Details tab, click the edit link next to Filter.
-
Define the filter and then click Done.
-
Click Yes to refresh your data. Your changes won’t take effect until the data is refreshed. You can refresh your data any time by clicking Refresh data.
Filtering to view a subset of connected data
Defining a filter
A filter tells Quickbase to connect only those records that match the criteria you set. To set filter criteria, specify the following:
-
The field you want to filter on. If you want to see all tasks with a particular start date, you'd choose Start as your filtering field.
-
An operator. Operators vary according to the field type. The operator tells Quickbase how to filter the records. Example operators are: equals, 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:
Field |
Operator |
Matching value |
Status |
is |
In progress |
You can set a single line of criteria, or add more fields if you'd like. Hover your mouse over the field dropdown. Plus and minus sign icons appear to the right of the field.
Click the plus sign () icon to add another line of filter criteria. Use the minus sign () icon to delete the current line.
Comparing a field to a specific value
To create a filter that finds a specific matching value, enter or select a value in the field that appears when you choose one of the following operators:
Field type |
Operators |
Text |
* Notes contains, does not contain, starts with, and does not start with operators are case-insensitive. All other text operators are case-sensitive. When you connect to Exchange or Gmail, some operators only support complete matches, not partial matches. For example, the filter "Subject contains Project" would return messages with "Project" but no variations such as "Projects" or "Projections." For Exchange, these operators filter on complete words:
For Gmail, these operators filter on complete words:
|
Numeric |
|
Date |
Enter a specific date or choose a relative value (yesterday, today, tomorrow, one week from today, the first day of the previous month, and so forth).
Choose a relative date range (previous, current, next) for day, month, quarter, or year. |
Date/Time |
Enter a specific date and time or choose a relative value (midnight yesterday, midnight today, midnight tomorrow, midnight one week from today, midnight on the first day of the previous month, and so forth). Midnight refers to the beginning of the day.
Choose a relative date range (previous, current, next) for day, month, quarter, or year. |
Time of Day Duration |
|
Checkbox |
|
Notes:
- Connected table filters for Date and Date/Time fields are based on the timezone setting for the app where the connected table resides.
- If you change the timezone setting for a connected table’s app, the new timezone may not be immediately used for existing Date and Date/Time filters during the next manual refresh. One way to ensure that the new timezone will be used is to sign out of Quickbase and sign back in before manually refreshing the connected table.