Custom Filter Formula in Dynamics NAV 2013

One of the new features in Microsoft Dynamics NAV 2013 is the ability to enter formulas like ‘%me’ and ‘%myitems’ while setting filters. Dynamics NAV then replaces the formula with values while applying the filter.

Some of the formulas that comes standard are for example; ‘%me’ and ‘%user’ will be converted in to the USERID, ‘%company’ will be converted into the COMPANYNAME, ‘%myitems’ will be converted into a string with the item numbers you listed in the My Items role center part (same goes for ‘%mycustomers’ and ‘%myvendors’).

This is nice but what is great is that you can also easily add your own filter formulas.

Here is how it is working.

First let’s look at the standard functionality; we enter ‘%myitems’ in the filter on the No. field of the item list like below.

Dynamics NAV then creates a filter using the items the user have listed in the my items list.

Cool!

You can also use the formula when writing C/AL code.

The above code generates the following message.

Seems useful.

That was the standard Dynamics NAV 2013 functionality. Now, let’s see how we can create our own custom filter formula. We use an example where we want to be able to filter all inactive customers using an ‘%inactivecustomers’ formula (in this case inactive means that there have been no transactions during one year back).

The code that converts the formula to a text string is in codeunit 41 – TextManagement. In this codeunit we first add the following code in the MakeTextFilter function (the code itself should be more or less self explained if you are used to C/AL code, the GetInactiveCustomerFilter is a new function explained below).

Note that the InactiveCustomersText is a text variable that can be translated (like any other text variables), this enables you to have the formula in different languages.

Next we write the corresponding function (the GetInactiveCustomerFilter) that loops through the customer records and for each one of them checks the ledger entries to see if there are transactions during the last year or not and build the string to filter with.

That’s it, quite simple!

Now let’s try it on a list of customers; I type in the following as the filter.

When tabbing out of the field Dynamics NAV replaces it with the string of the inactive customers.

It will work where ever you can set a filter on the customer number. Also reports.

The limitation to be aware of is the length of the filter string (for filters on reports it is only 250 characters).

Adding custom formulas like this is powerful. You can also use it to set date filters (like ‘%nw’ to do date filter for the next week, etc.) and a formula can also be a part of a filter (like ‘70003|%myitems’).

So, why not do a ‘%top10customers’ formula that creates a filter with the top 10 customers? Or maybe a ‘%reorderitems’ that creates a filter for items where the inventory level is below the reorder point? I think the possibilities are endless and it can provide a way for the users to apply filters that was not previously possible.

And yes, it works with request pages and saved views (those stores the formula values and not the results).