Good Day,
I am trying to apply a datefilter to an NF functions to the Balance (LCY) flowfield on the customer card.
I get a list of customers NL(ROWS) without specifying the field to bring back so the entire record is available
I am then trying to use an NF function to bring back that customer balance as of a certain date range "01/06/2015..30/06/2015"
It always returns a zero but I admit that i'm not entirely sure exactly how the NF flowfilters work and if I need to do something different with my NL rows
Thanks in advance
Tom
Date
Votes
3 comments
-
Jet Reports Historic Posts NAN FlowField and FlowFilter example.xlsx
There are a couple of things to remember when dealing with FlowFields in NAV. FlowFields are basically calculated fields within the system. NAV will calculate the Balance of an account based on the Ledger Entries. It gives you access to this total from the Master table so you don't have to do the math. From there, NAV allows you to apply FlowFilters to these FlowFields so you can manipulate the calculation somewhat. Each FlowFiled has a CalcFormula or equation that it uses to calculate the FlowField. Please note that not all FlowFields are affected by all FlowFilters for that table. You are running into this with the Balance field on the Customer card. The Balance will always show the balance as of today - not a date in the past. You will need to find a different FlowField to get to the information you are looking for.
I have attached an example for your reference on how to use FlowFields and FlowFilters. You can see that I am using an NL(Rows) function to pull all customers with a NetChange <>0 for the date range of Jan 2016 (sorry about the US date format). Net Change is a FlowField that allows you to apply the DateFilter (FlowFilter) to it. I then used NF() functions to pull in the Balance and Net Change. On each of these I have a column without the Datefilter applied and one with it applied. This allows me to prove that the Balance field isn't affected by the DateFilter. Another thing to point out is that just because I put the DateFilter in the NL(Rows) function, by default, your NF() functions do not use this. You have to manually put the DateFilter into the NF() functions too.
In reality you are not going to use the Net Change field with a date range to find the balance at a point in time. You would want to use "..1/31/16" to find the balance for that customer on 1/31/16. In some versions of NAV they also include a "Balance on Date" field that you can just put in the date you want to find the balance on.
I hope this explains things and sheds some light on the situation. Please let me know if you have any other questions.
Sarah -
Jet Reports Historic Posts Thank you - it worked.
Can you however explain why if a customer has no ledger entries yet that it returns a #VALUE for the entire report?
I was able to get around this by filtering my customer list to only include customers with a ledger entry but if you have an explanation, that would be great.
Thanks again.
Tom -
Jet Reports Historic Posts Glad to hear that worked.
I am not sure why you are getting errors when a customer doesn't have any ledger entries. I have customers without ledger entries and I didn't get errors. What is the error message from Jet when you click Debug?
Please sign in to leave a comment.