Advanced Filtering
The ability to filter data is an important part of Counterpoint’s functionality. Filtering data allows you target or limit what information you see. You can filter when running reports, using lookups, or using table view to make edits to multiple records.
Accessing Filters
| Area of Counterpoint | Method for Opening Filters | Available Filters Based on |
|---|---|---|
| Reports | Click tab(s) of the report window | Database table(s) being reported on |
| Lookups | Click Filter button or choose Options → Filter | Data available in the Lookup |
| Table View | Click the Funnel icon in the Toolbar | Data available in the Table |
Example Filter Tab for Inventory Report:
Example Filter Window for Customer Lookup:
Example Filter Window for Items window in Table View:
Filter Components and Definitions
Each filter line is composed of three or four parts:
- Filter Field – The field that will be used to limit the scope of the data returned
NOTE: Filters are customizable by ALL the fields within the table.
- Operator/Modifier – The way that the filter ‘works’ or how the filter evaluates the data
- Filter Data – Data the user enters to complete the filter
NOTE: If using one of the ‘is blank or empty’, ‘is empty’, or ‘is not empty’ filters, there will not be any filter data fields for the filter line because the operator does not require any additional data.
- Conjunction – Either an ‘AND’ or an ‘OR’ used to link a filter line to the next filter line
These components are not always visible from the Simplified Filter view. The Simplified Filter view is the default for filters, and it allows users to fill in filters without customizing or changing them. For example, the ‘AND’ conjunction does not show for filter lines in the Simplified filter, as it is implied that the filters are used together.
Available Operators/Modifiers
| Operator/Modifier | Purpose/Use Case |
|---|---|
| At least | Enter a minimum data point in the Filter Data field. Any data that is greater than or equal to the data point will display. Usually used with numeric fields. |
| At most | Enter a maximum data point in the Filter Data field. Any data that is less than or equal to the data point will display. Usually used with numeric fields. |
| Begins with | Enter a series of characters that you want the field to begin with. Any data that begins with those characters will display. |
| Between* | Enter a range of data (beginning and end of range). Any data that falls within that range will display. |
| Contains | Enter a series of characters that the field must contain. Any data that contains that series will display. |
| Doesn’t contain | Enter a series of characters that the field cannot contain. Any data that does not contain that series will display. |
| Ends with | Enter a series of characters that you want the field to end with. Any data that ends with those characters will display. |
| Is (exactly)* | Enter or look up a data point for the filter. |
| Is blank or empty | Display data where the field is blank or empty. |
| Is empty | Display data where the field is empty. |
| Is in* | Enter or look up 1-3 options for that field. Any data matching any of the options will display. |
| Is not (exactly)* | Display data that is not exactly the specified value. |
| Is not empty | Display data where the field is not empty. |
| Less than | Enter a maximum data point in the Filter Data field. Any data less than the data point will display. Usually used with numeric fields. |
| More than | Enter a minimum data point in the Filter Data field. Any data greater than the data point will display. Usually used with numeric fields. |
*Most commonly used operators.
Customizing Filters
If the criteria you want to filter by does not appear by default in your filter window or tab, you can customize the filter to add additional fields, remove filter lines, or change the way the filter behaves (e.g. is NOT in a category, or is between two dates). Customizing filters works the same way for report filter tabs, lookup filters, and table view filters.
To customize filters:
- Right-click in the filter window or tab, and select Customize from the drop-down
OR
Click Options and choose Customize from the drop-down
NOTE: The ability to customize filters is a security setting maintained in System Security Codes. If you cannot customize filters, contact your system administrator.
Click the words you would like to change
Changing Existing Filter Lines- Select the field from the list available
- Select the operator by which to filter
- Enter or select the values at the <?>
NOTE: You can also change the data in the filter after Simplifying the view (see step 5 At the end of the filter line, choose AND if that and the next statement must also be true, or choose OR if that can be true OR the next statement can be true
5. Right-click and select Simplify to change back to the simple filterAdding a New Filter Line
If you need additional filter lines, follow these steps:
1. Click the light gray <Click here to add condition>
- Select the field from the list available
2. Select the field from the list available (in red)
3. Select the operator by which to filter 
4. Enter or select the values at the <?>
NOTE: You can also change the data in the filter after Simplifying the view (see step 7)
5. (Optional) Repeat steps 1-4 to add other filter lines
6. (Optional) If you added other conditions, make sure to adjust the conjunctions at the end of the new lines. At the end of the filter line, choose AND if that and the next statement must also be true, or choose OR if that can be true OR the next statement can be true
7. Right-click and select SIMPLIFY to change back to the simple filter
Nesting Filters
There might be situations where you need to use a complex combination of filters, where certain filters need to have the ‘and’ conjunction between them and others need the ‘or’ conjunction. Counterpoint allows filters to be indented to change how the conjunction affects the filter lines below. Think of these indentations as parentheses in a math problem. They allow you to group a series of filters together so that you don’t need to add excessive amounts of lines to a filter.
For example, you might need to see 6 categories of items from a specific primary vendor.
You might think that a filter like the one below would get you the results you are looking for, but the results would show the items from the specific vendor for the first line of three categories, and ALL the items in the second three categories:
If you indent the two lines for item category you would see results for items in those 6 categories from that specific primary vendor:
To indent a filter line:
- Right-click the line
- Choose Indent from the drop-down menu
To outdent a filter line:
1. Right click the filter line
2. Choose Outdent from the drop-down menu
Examples of Filter Options
Filtering for 3 Categories of Items from a specific Primary Vendor
Filtering for 6 Categories of Items:
Filtering for Ticket Date:
Filtering for a single Location:
Filtering for multiple Locations:
