by Barbara Plagens for KCP Dynamics
Filtering is such a great tool in Microsoft Dynamics NAV 2016 that it deserves its own blog. When users understand how to use filtering options, they immediately have a means to retrieve information without the need for special reports or cues.
Let’s start with a simple filtering option that is readily available. From a list page in NAV, users can select a field in a record and filter the list for the value in that field. What exactly does that mean? First, you open a list page, in my case the customer list. Next select a record and a field in the record. Then right click and select Filter to This Value from the drop down menu.
The result is the list is filtered to the value selected as shown below.
Additionally, NAV displays the filter in the Quick Filter as shown above in the red outlined box. You can release this filter by clicking the Filter icon at the right end of the Quick Filter; otherwise, when you select another page from the menu, the filter is released automatically by NAV.
The next filtering option is setting a filter directly using the Quick Filter. To do this, you need to select the field for which you want to set the filter and you need to enter the value you want to filter. One advantage of using the Quick Filter over the Filter to This Value selection is that you can enter criteria for the filter instead of the absolute value. We will look at this on the Item list page.
Let’s assume you are interested in the Unit Cost field. If you right click and select Filter to This Value, you would have a filtered list for those items with the exact value selected. By using the Quick Filter, you can specify a range or other criteria for the filter. For example, enter ‘>500’ in the ‘Type to filter (F3)’ field and select Unit Cost as the filter field.
The resulting list is shown below; all of the items listed have a Unit Cost greater than 500.
The Quick Filter is great, but it has limitations. For example, when you use the drop down button to select the Quick Filter field, only those fields displayed on the page are available to select. And, not all of the table fields are available to select when you Choose Columns – so you may not be able to set a filter on the field you would like. The other limit is you are only able to filter on a single field.
This is where the Advanced Filter comes to the rescue!
There are three methods you may use to display the Advanced Filter option:
- press the keystroke combination Shift+F3
- click on the name of the page on the left, then click Advanced filter from the drop down menu
- click on the down arrow to the right of the quick filter
BUT WAIT!!! What do you do if you are on a page and you don’t see the filtering options … what if your page looks like the one shown below.
The tip here is to add the Filter Pane by customizing the page. To do that, select the Application Menu button (the blue down arrow), then select Customize and click to select Filter Pane … and, miraculously, your screen is updated to include the Filter Pane.
After executing any option to display the Advanced Filter, the screen refreshes as shown below.
To set the criteria for the Advanced Filter, you must select the field and the value. In this case, we are limiting the entries to account 2310, for entries posted from the 1st through the 31st of January 2017, where the System Created Entry value is No. Since we navigated to the ledger entries from the general ledger account, the first part of the filter for the G/L Account No. is already set for us.
To add the Posting Date filter, click the Add Filter icon to add a second line to the filter. Then, in the field name, verify Posting Date is selected – if Posting Date is not selected, click the drop down arrow and make the selection as shown below.
In the value field where Enter a value displays, enter the date range for the period you are researching – the first through 31st of January 2017; entered as 010117..013117 when using US formatting for dates.
Note that after the filter is added, the list page refreshes to include only those entries meeting the two filters specified.
The field for the second filter is selected from the Visible Columns – the columns selected to display on the page. The last filter makes use of the All Columns option.
Use the following steps to add the last filter and select the System-Created Entry field:
- Click the Add Filter icon, a new line is added to the Advanced Filter.
- Click the drop down in the field name, the drop down menu for All Columns and Visible Columns displays.
- Click All under All Columns, a complete listing of available fields for the table displays.
- Scroll through the list using the down arrow icon at the bottom of the list to locate the System-Created Entry; you will notice that the fields are in alphabetical order.
- Click to select the System-Created Entry and return it to the field name.
Once the System-Created Entry field is added, the value needs to be specified. To do this, click the drop down in the value field where Select a value displays and click to select the No checkbox as shown below:
After setting this filter, the general ledger entries are filtered to match the criteria and, in my database, display as shown below – the page is personalized.
For additional help on setting filter criteria, use this link https://msdn.microsoft.com/en-us/library/hh879066(v=nav.90).aspx to an article with detailed information.
If this is a filter you plan to use repeatedly, you can add the filter to your menu. To do this, first select the Save View As option on the Advanced Filter menu
In the Save View As page, enter a Name for the filter and select the Activity Group (Navigation Pane Menu Button) to add the filter to the menu.
NAV will display a confirmation message as shown below.
After you restart NAV, the new menu item is added for you to use as needed.
Our hope is that this information on filtering makes you a better user of NAV.