Is it me, or has the way NL("Filter") works been changed?
Previous to Jet2009, you did not need to use the 'ignore blanks' sign in a NL("Rows") statement that referred to a cell with the NL("Filter") function.
E.g. this would work
F3 = NL("Filter";"Customer";"No.";"No.";">100000")
F4 = NL("Rows";"Sales Invoice Header";;"Sell-to Customer No.";$F$3)
Regardless if there was any customer with a number higher than 100000.
Jet2009 reports an error: 'empty filter not allowed'.
Changing F4 to NL("Rows";"Sales Invoice Header";;"Sell-to Customer No.";"@@"&$F$3)
will report the error 'invalid filter in F4'
That is known from pervious versions of Jet: no need to include the 'ignore blanks' sign with the filter function.
Is NL("Filter") broken?
What is happening here?
If both options are considered invalid, there is no way to use NL("Filter") anymore.
rmw
PS Just tried build 9.2.10041.0, but that doesn't make any difference
Date
Votes
7 comments
-
Jet Reports Historic Posts Official comment rmw,
I should expound on what is going on in the report you sent. Since there are actually blank values in the table for "Lot No.", the NL(Filter) creates a filter array with a blank value and therefore you get the #VALUE! with the 'Empty filter not allowed' because you can't filter for blanks. In this case you could get around the issue by using a calculated field to put an @@ in front of the blank value like this:
=NL("Filter","Item Ledger Entry","=""@@""&NF(,""Lot No."")")
In the case of Document No., your filters are excluding all values, so the NL(Filter) creates an empty filter array. An array with no values is treated differently than an array with a blank value. When the array has no values filtering by it also returns no values. This behavior is consistent with the behavior in Jet Reports v7. Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts Hi rmw,
This sounds like it could be an issue. Could you post this to the Jet Reports support site http://support.jetreports.com rather than the community site? That way, you can be guaranteed that this will make it to the Jet development team to be fixed.
Regards,
Hughes -
Jet Reports Historic Posts Done.
rmw -
Jet Reports Historic Posts Is it me, … ?
It is official: the answer to this question is Yes. ;)
Nothing is changed, the problems cause was SBKAC*
rmw
[size=50]* Somewhere Between Keyboard And Chair[/size] -
Jet Reports Historic Posts It seems it wasn't me after all….
The attached example shows why.
When using a NL("Filter") that results in nothing, but is used on a field that has some information in it, all works fine (the Document No.-filter)
When using a NL("Filter") on a field that has no information at all, the empty filter error is returned (the Lot no.-filter)
This example was made using the Cronus database.
rmw -
Jet Reports Historic Posts Hi rmw,
I checked your report and indeed it does give that error. However, I also checked the same report in Jet Reports v7 and it also gives a similar error when the report is run. So we can look into this, but the behavior has not changed from v7 to 2009.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Thanks for the effort of investigating this.
I think I get what you are trying to say, but I don't see right away why this is happening:…Since there are actually blank values in the table for "Lot No."…
…An array with no values is treated differently than an array with a blank value…
This is kind of confusing. There is a difference between an empty string and no value to Navision?
Well, I have to take that for a fact, I guess.
You suggestion of using "@@" in a NL("Filter") will go into my toolbox 8-)
Thx
rmw
Please sign in to leave a comment.