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.
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.
- 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)”.
- If possible, avoid including special filtering characters in master data (item numbers, customer and vendor numbers, document numbers etc.)