Filters allow you to customize a view so that only database records that meet certain criteria appear in that view. In other words, each filter is a piece of logic related to a specific field’s value (greater than, equal to, not blank, etc.) that ensures only qualifying records make it into the view. Here are some facts about filters:
- Each filter is based on a specific field, and the filter’s options are based on that field’s type (number, date, etc.)
- You may use 0, 1 or many filter conditions in a view
- You can connect 2 or more filter conditions with AND | OR logic
- In a view of 3 or more filter conditions, you can build conditional groups
- By default, all of your filter conditions are in a single group connected by a single AND | OR connector
- You can then sub-group filter conditions and use a separate AND | OR connector for the subgroup
- It is possible to achieve any logical subset of records using filters
- In other words, filters let you "slice-and-dice" your records to the most specific degree you can come up with
Building a Filter
In the "Filters" section of the "App Overview" page, click on "Add New Filter For Table" and select the appropriate table. When the Filter Builder opens, you will see a list of fields from the selected table on the left-hand side of the page.
In the middle of the page, there will be one filter group available for you to drag and drop fields into. Adding conditional groups can be accomplished by clicking the green "+Add Conditional Group" button. After creating your filter, you can click the green "Preview Filter" button to see the results it would produce. Below is an example filter:
Do not forget to name your filter! Click on "Filter Name" in the upper left-hand corner to assign your new filter a name. After your filter is working correctly, click the "Save" button in the upper right-hand corner of the screen. You can now use this filter in a view!
Once you have added more than one filter to your view, you have created a filter group. A filter group is simply a collection of individual filter conditions along with a setting of AND | OR. The AND | OR setting determines whether all of the filter conditions in the group need to be met (AND), or any of the filter conditions in the group need to be met (OR) for a record to appear in the view. You can toggle between AND | OR using the drop-down menu at the left side of each filter or filter group.
The AND/OR setting in a filter group has important consequences for your views and will yield very different results depending on the selection. For example, if you have a filter group that contains a filter for "Company Name [is identical to] [Apple]" and another filter for "Invoice Status [is identical to] [paid]", the AND setting will only match records for the company Apple that have a invoice status of paid. The OR setting, however, will pull all records for the company Apple (paid or unpaid) along with all records that have an invoice status of paid (Apple or otherwise).
By adding more filters to a filter group, you can very precisely control which records are shown in the view.
To accomplish more sophisticated filtering you’ll need to utilize 2 levels of filter groups. For example, you might want to find all records that have "Company Name [is identical to] [Apple]" OR "Company Name [is identical to] [IBM]", and of those records, only include those that have "Invoice Status [is identical to] [paid]". In this example, a single filter group won't work. We need to create one filter group that matches Apple OR IBM, and then join that filter group with "AND paid".Conditional groups allow you to do this.
*NOTE* To create conditional groups, you must have at least three filters for a view.
Filtering based on the TrackVia User field
TrackVia User field types operate similarly to choice field types, with the additional functionality of filtering dynamically based on the "currently logged in user". This filter condition allows you to build a single view that will work for hundreds or thousands of users. Each user automatically sees only records assigned to them.
If your application is set up with relationships, you can use what we call "grandparent filters" to limit micro results based on a macro filter.
The easiest example of this is with the table hierarchy: Regions > States > Cities > Mayors. Add a filter to the Mayor-View where you only want to see the Mayors from the Mid-West Region. This prevents the need to filter on every state or city in the Region you want to see Mayors for.
Below is an example of the filter builder screen, the "+ Show More Tables" button will reveal parent and grandparent table's fields.