Microsoft Dynamics NAV News & Tips
Filtering Basics in NAV Classic Client
Today we would like to discuss the basic filtering functionality in the NAV Classic Client. These functions can be found in the toolbar. Your toolbar may look different, depending on your windows theme. However, the buttons work the same way. We will be using the Vista Theme in this document.
click to enlarge
Filters are a way of reducing the number of records that you are looking at. We will be looking at the Field Filter and the Table Filter in this document. The Flow Filter and Sorting will be covered in subsequent documents.
The Field Filter and the Table Filter are related. In fact, using the Field Filter loads lines into the Table Filter, as we will demonstrate.
Field Filter:
This function operates on the “active” data field. For example, in a customer list, the active field is indicated by a dotted outline for the field. In this case the active field is in the Zip Code column, so any field filter will be set on zip code.
click to enlarge
When we click on the Field Filter button, we see:
click to enlarge
The system has opened a filter box on the Zip Code field and has pre-filled the value that was active. If we click OK, we have only those customers with a zip of 37125:
click to enlarge
But what if you want something more inclusive? Maybe you would like to see all of the customers with a zip that starts with 3. To do this we set the filter to 3*. The * is a “wildcard,” so anything that starts with a 3 will be on the list. Consequently, this filter:
will yield this list:
click to enlarge
We can also input a range of values in a filter in the following format: Start..Finish. First we will change the active field to Customer Number column (No.) by clicking on a value in that column.
click to enlarge
Then we click on the Field Filter and input a filter of: 20000..61000
Resulting in a list with only those customers:
click to enlarge
We may also wish to use other filters:
Greater than >
Yields:
click to enlarge
Less than <
Yields:
click to enlarge
Using “and” does not really work on filtering, as an individual record cannot be both “this” AND “that”.
Instead we use:
Or | (that is the pipe, a shifted \ on most keyboards)
Yielding:
click to enlarge
Field filters can also be used on a card form. Just select the field that you wish to filter on (we will use salesperson code):
click to enlarge
Then click on the field filter button.
Click OK to set the filter. The customer card appears unchanged, but if you scroll through the card records or open a list, only those customers with a Salesperson Code of PS will be displayed.
CAUTION: Some filters persist from prior sessions!
If your data is “GONE”, check the lower right section of the NAV window. If you see that a FILTER is set,
clicking the Show All button may clear the filter and reveal your data. If you have a Flow Filter set, you must go into the Flow Filter and manually clear the filter. We will discuss this in another document.
Table Filters:
As we mentioned, the Field Filters and the Table Filter are related. In fact, the Field Filters are just a handy way to populate the Table Filter. We will demonstrate this by setting a couple of Field Filters and then looking at the Table Filter. Once again we will use the Customer list to demonstrate.
First we will click on Salesperson PS:
click to enlarge
Then we will set the Field Filter:
Clicking OK filters the list so that we only see Customers with a Salesperson Code of PS:
click to enlarge
Next, we click on the location Code of YELLOW:
click to enlarge
Then we will set the Field Filter:
Resulting in a List of Customers that have BOTH a Salesperson Code of PS and a Location Code of YELLOW:
click to enlarge
Now, let’s click on the Table Filter button:
Notice that our two Field Filters have simply created a set of entries in the Table Filter. You can do all of your filtering work in the Table Filter if you wish. The Field Filter is just a handy shortcut.
Notice that there are two columns in the Table Filter Sceen.
The first column is where you choose the field that you wish to filter on and the second is where you set the values that you want displayed.
| Select or input field here | select or input values here |
Notice that there is a LOOKUP arrow in the right side of the Field Column.
Clicking this will give you a list of ALL fields in the data record. This is very powerful, as we can filter on data elements that are not even displayed on list that we are viewing.
Once we have selected a field, it is time to put in a filter value.
Notice: If it is appropriate, the system will display a LOOKUP in the “filter value” column to help you pick a valid entry. (Look back at the Field Filter dialog boxes. The filter LOOKUP is there too!)
Using the Table Filter will allow you to stack up very complex filters to direct the system to present you with the exact information that you need to see.
There is also good context sensitive help for these functions in NAV. Just click the icon in the upper right corner, hover over the button that you are interested in and click.
For more information or help with NAV filtering, please contact Mike Packard, Integrated Computer Systems Support, at 425-284-5426 or mikep@ics-support.com.
Filters are a way of reducing the number of records that you are looking at. We will be looking at the Field Filter and the Table Filter in this document. The Flow Filter and Sorting will be covered in subsequent documents.