Gotchas with Filters in Microsoft Dynamics NAV

This is a problem that I’ve come across several times in the last few weeks, so thought it was worth sharing on the blog.

Microsoft Dynamics NAV has a flexible filtering system that helps you to find the information that you are looking for. For instance ? is a single character wildcard, * is a multiple character wildcard and .. (two full stops) indicates a range between the value in front of the stops and the value after the stops.

See the NAV help for full details. The point of this post, however, imageis to warn about a trap that is easy to fall into with filters.

Imagine one of your customers is B&Q (and who doesn’t like a little home improvement now and then?) and the customer no. that you give them is also B&Q.

Now, you want to run a report to show the sales that you have made to them. You filter the report on Sell-to Customer No. for B&Q – but, get no results.

You might well look puzzled, cartoon man, so was I at first.

This is because NAV interprets the filter as “show records where the Sell-to Customer No. is both ‘B’ and ‘Q’”. Of course, you don’t have any customers where the customer no. is “B” and it is “Q” – no results.

Two solutions:

  1. Replace the & with a ? i.e. B?Q which means “Sell-to Customer Numbers beginning with B, ending with Q and having a single character (any character) in between)”.
  2. If possible, avoid including special filtering characters in master data (item numbers, customer and vendor numbers, document numbers etc.)

Leave a Reply