How to Fix the Location Filter in Dynamics NAV
You may notice that the Location Filter might not be effective when you run the report 5807 “Item Age Composition – Qty.” on NAV 2013 R2 to NAV 2016. On NAV 2009 Classic, NAV 2009 R2 and NAV 2017, you may not have that issue because they write the code in different ways. On NAV 2017, they use a function called FilterLinesWithItemToPlan rather than writing the code directly under Item-OnAfterGetRecord trigger. This blog will walk through the steps to correct this issue.
Let’s look at the code of the filters on the report 5807. The code “ItemLedgEntry.SETFILTER(“Location Code”,”Location Filter”)” looks correct. So why it is not working?
Figure 1 – Dynamics NAV Report 5807 Location Filter
First of all, “Location Filter” (field 67) is a FlowFilter on the Item table(27). But the field that we need to calculate, the “Remaining Quantity” (field 13), is a normal field on the Item Ledger table (32). So the flowfilter is not effective for the calculation of a normal field. The interesting thing is that the “Location Filter” is actually working very well for a similar report 5808 “Item Age Composition – Value.” This is because the calculation on report 5808 involves some flowfileds.
Then how can we solve the issue for report 5807?
Len Bobinski, a senior developer, gave me a light on that. Since “Location Filter” is not a normal field on the Item table, we need to use GETFILTER function to retrieve the value of the flowfilter. Changing that code to ItemLedgEntry.SETFILTER(“Location Code”,GETFILTER(“Location Filter”)) will solve the issue. It is a simple but elegant solution.
Figure 2 – Dynamics NAV Location Filter Fix on Report 5807
Hopefully this simple solution will save you some time and effort.
To read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.
You may also want to check out our collection of NAV Development Blogs.