• Home
  • About Me
  • Contact Me
  • Downloads
  • White Papers
  • Web Sites
  • Post List
  • FAQ

Microsoft Dynamics Tricks and Tips!

Olof Simren - Microsoft Dynamics NAV Blog

  • Home
  • About Me
  • Contact Me
  • Downloads
  • White Papers
  • Web Sites
  • Post List
  • FAQ

Custom Filter Formula in Dynamics NAV 2013

Jul 1, 2014 | Posted by Olof Simren | Development, General, Miscellaneous | 5 comments |

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.

Set-Filter-Using-My-Items-Dynamics-NAV

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

Filters-Set-Using-My-Items-Dynamics-NAV

Cool! 🙂

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

My-Items-Filter-CAL-Code-Dynamics-NAV

The above code generates the following message.

My-Items-Filter-Dynamics-NAV

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).

Custom-Filter-Function-Code-1-Dynamics-NAV

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.

Custom-Filter-Function-GetInactiveCustomerFilter-Dynamics-NAV

That’s it, quite simple!

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

Filters-Set-Using-Inactive-Customers-Dynamics-NAV

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

Inactive-Customers-Filter-Dynamics-NAV

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

Custom-Filter-Function-On-Reports-Dynamics-NAV

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). 🙂

Tags: CustomerCustomizationDevelopmentFilterNAV 2013
5 Comments
2
Share

You also might be interested in

Bill-to vs. Sell-to Customer

Apr 17, 2014

This is an old but still relevant topic; on sales[...]

Turn Report Selections into a Selection Dialog

Jun 12, 2014

Here is a small trick that I have used a[...]

View Change Log from Ribbon

Apr 25, 2014

The Change Log in Microsoft Dynamics NAV is used to[...]

5 Comments

Leave your reply.
  • Daniele Rebussi
    · Reply

    July 2, 2014 at 9:24 AM

    Just one remark: these kind of formula seems only to work if entered inside the “Advanced filter” section and not if written inside the quick filter box.
    Is it the normal behavior or just a bug? (I tested only on NAV 2013 build 34298).

    • Olof Simren
      · Reply

      Author
      July 2, 2014 at 11:24 AM

      Hi Daniele,
      Good point, I think they only work on the advanced filters.
      I experience the same, and I am on 7.1.36366.0.

  • M
    · Reply

    September 19, 2014 at 2:12 AM

    I am missing F7 and show-All
    I dont’ know why standard they cannot handle multiple type to filter’s, so annoying for the users of classic +10 years

  • Luc van Vugt
    · Reply

    April 5, 2015 at 7:16 AM

    Hi Olof,

    Great tip. Thanx for sharing.

    b rg
    Luc

  • Anthony A Montalto
    · Reply

    December 20, 2016 at 1:27 AM

    I stumbled upon this article as I was looking for ideas for a little problem I had with NAV. It’s GREAT. You taught me something I did not know before. Thank you for sharing. Will be following your blog.

Leave a Reply

I appreciate all feedback!
Cancel Reply

Subscribe to Blog via Email

My Dynamics NAV Partner

Naviona, LLC

Categories

  • Assembly (3)
  • Development (31)
  • Finance (14)
  • General (26)
  • Inventory (22)
  • Manufacturing (34)
  • Miscellaneous (25)
  • Purchase (9)
  • Sales (11)
  • Warehouse (7)

Top Posts & Pages

  • Kill Sessions in Dynamics NAV
  • Home
  • Subcontracting Part 1: The Basics
  • XML Buffer and CSV Buffer Tables
  • Functionality Improvements in NAV 2017
  • Scrap in Production
  • Processing of Shipments
  • Add Fields to the Item Tracking Lines
  • Downloads
  • Schedule MRP

Tags

.net Add-in Assembly Assembly BOM CAL Capacity Components Consumption Contact Costs Customer Development Dimensions Excel Finance Flushing General Ledger Inventory Item Items Lot Size Low-Level Code MRP NAV 2015 NAV 2016 Output PDF Planning Production Production BOM Production Orders Purchase Orders Receipts Reporting Reports Role Center Routing Sales Sales Order Sales Orders Stockkeeping Unit Subcontracting Task List Warehouse Warehouse Shipment

Recent Posts

  • XML Buffer and CSV Buffer Tables
  • Functionality Improvements in NAV 2017
  • Reversing Production Output and Consumption
  • Return Merchandise Authorization (RMA)
  • Sales Quote without Customer
  • Parallel Routings
  • Add Fields to the Item Tracking Lines
  • Field Level Security using Events in Dynamics NAV 2016
  • Schedule MRP
  • Activate WMS Functionality for Existing Location

RSS Feeds

RSS Feed RSS - Posts

RSS Feed RSS - Comments

Microsoft Dynamics NAV - ERP of choice for 110,000 Companies

Categories

  • Assembly (3)
  • Development (31)
  • Finance (14)
  • General (26)
  • Inventory (22)
  • Manufacturing (34)
  • Miscellaneous (25)
  • Purchase (9)
  • Sales (11)
  • Warehouse (7)

Top Posts & Pages

  • Kill Sessions in Dynamics NAV
  • Home
  • Subcontracting Part 1: The Basics
  • XML Buffer and CSV Buffer Tables
  • Functionality Improvements in NAV 2017
  • Scrap in Production
  • Processing of Shipments
  • Add Fields to the Item Tracking Lines
  • Downloads
  • Schedule MRP

Recent Posts

  • XML Buffer and CSV Buffer Tables
  • Functionality Improvements in NAV 2017
  • Reversing Production Output and Consumption
  • Return Merchandise Authorization (RMA)
  • Sales Quote without Customer
  • Parallel Routings
  • Add Fields to the Item Tracking Lines
  • Field Level Security using Events in Dynamics NAV 2016
  • Schedule MRP
  • Activate WMS Functionality for Existing Location

Recent Comments

  • Arão Benjamin on Discrepancy Between Purchase and Direct Cost Applied
  • Olof Simren on Subcontracting Part 1: The Basics
  • Olof Simren on Discrepancy Between Purchase and Direct Cost Applied
  • kelseyk on Reversing Production Output and Consumption
  • Shafeeque on Discrepancy Between Purchase and Direct Cost Applied
  • Shafeeque on Discrepancy Between Purchase and Direct Cost Applied
  • Gabriela on Subcontracting Part 1: The Basics
  • Andrew Trayfoot on Activate WMS Functionality for Existing Location

© 2018 · Olof Simren

  • Home
  • About Me
  • Contact Me
  • Downloads
  • White Papers
  • Web Sites
  • Post List
  • FAQ
Prev Next