Filtering your data in Microsoft Dynamics NAV

Microsoft Dynamics NAV 2015 provides several options to the user for filtering data on list screens. This post will show different ways to filter your data using Microsoft Dynamics NAV 2015.

Quick Filter:

Quick Filter is provided on lists screen and can be used to filter values in the columns that are visible in the current view. You can only filter on one column when using quick filters. Note that if you show/hide the column in your list views, the available filtering columns in the Quick Filter changes too.

Microsoft Dynamics NAV filtering data im1

Advanced Filters:

Advance filters lets you define more than one filter and is not limited to the fields shown in the view. You can filter on any field present in the table. To show/hide advance filters use the arrow icon next to the Quick Filters.

Microsoft Dynamics NAV filtering data im2

This will show the list of filters already applied and also allows the users to provide more filters. You can add more filters by clicking the “Add Filter” button at the bottom of the filter list and remove a filer by clicking the “X” before the particular filter option.

Microsoft Dynamics NAV filtering data im3

To access the fields that are not shown in the current view, hover on “All” this will open the list of all columns present in the table.

Microsoft Dynamics NAV filtering data im4

Filter to Value:

Apart from above, another quick way to filter values in lists is to right click on any cell in the list and choosing the “Filter To This value” option. This will apply the filter on that column with the value in the current cell. For example to list all vendors with the payment term 30 days you can right click payment term value of the vendor 30000 and choose “Filter To This Value”.

Microsoft Dynamics NAV filtering data im5

Expressions in Filters:

Microsoft Dynamics NAV allows using expressions in filters. The following are a list of expressions that we can use in filters, with an example of each.

Expression Description Examples
.. Range 100..200, V001..V010, 01/05/15..03/05/15
| And QLD|NSW
Less Than <150 (exclude values 150)
Great Than >1000 (exclude value 1000)
<= Less Than or Equal To <=150 (include value 150)
>= Great Than or Equal To >=1000(Include value 1000)
<>  Not Equal to <>QLD, <>’’

You can also combine the expressions to make a more complex expressions for example 01/01/15..01/31/15|03/01/15..03/15/15

Using Wildcards in Filters:

Wildcards refer to the use of “?” and “*” in filters. You can use the “?” to substitute one character, while “*” can be used when you are not sure of the length of the text string. Let’s explore this through an a few example.

  1. To find records with the word Tech anywhere in the name, your filter will be *Tech*
  2. To find records with the word Tech in the start of the name, your filter will be Tech*
  3. Similarly to find records with the word Tech in the end of the name, your filter will be *Tech
  4. If you are not sure if the word is Tech or Tach, the above filters can be modified to *T?ch*, T?ch* and *Tech.

Another handy feature is the use of “@” character in filter. This character when used in filters, tells NAV that the filter applied should case insensitive.

For example to make the above filter case insensitive the filter can be modified to @*T?ch*

This post was written by Imran Zulfiqar, Microsoft Dynamics NAV Developer/Consultant at EBS.

If you have outdated business software that’s limited in its reporting and analytical capabilities – then you should to talk to EBS about a powerful business management software solution – Microsoft Dynamics NAV. To find out more about Microsoft Dynamics NAV or other Microsoft business management software solutions, please call Paul Woods at EBS for an obligation free discussion on 1300 303 973, email info@ebsys.com.au or visit ebsys.com.au

Microsoft Dynamics NAV is global enterprise resource planning (ERP) software that helps more than 100,000 companies worldwide manage their accounting and finances, supply chain, and operations. Start with what you need now, and easily adapt as your business needs change. In the Microsoft cloud or on your servers—the choice is yours.

Evolution Business Systems (EBS) is a business management software specialist that equips its clients with software designed to achieve their business goals.

Melbourne based EBS is a Microsoft Gold ERP Partner specialising in Microsoft Dynamics NAV and other Enterprise Resource Planning solutions that can be tailored to the needs of small to medium sized businesses. We can automate your core business processes and integrate your specialist applications with the right financial management solution. Our suite of Microsoft Cloud products suit businesses who have outgrown their accounting system giving them a full featured ERP system: Microsoft Dynamics NAV, Microsoft Office 365 and Microsoft Azure packaged at an affordable monthly subscription price. We work alongside our clients to understand their business and vision, and offer solutions to support these both now and into the future.