Here is my formula: =NL("Rows","Sales Invoice Line",,"+Sell-to Customer No.",D11:D12,"Posting Date",$C$3,"Quantity","<>0")
This works unless D11 is blank and some times it is. The formula in D11 is =NL("Rows","Customer","No.","Parent Agency No.",$C$4,"No.","*L*|*FR|*H*|*R*&<>*FRC")
How can I correct this so that I don't get an Empty Filter Not Allowed error?
Thanks!
Date
Votes
8 comments
-
Jet Reports Historic Posts You need to add a "@@" to the formula to allow for empty values and special characters. So, that would look like: =NL("Rows","Sales Invoice Line",,"+Sell-to Customer No.","@@"&D11:D12,"Posting Date",$C$3,"Quantity","<>0")
-
Jet Reports Historic Posts Hello GinaWard,
When using filters that can sometimes have empty values or be blank, you would need to add the suffix "@@" to let your database know that it is 'ok' that the result is empty, otherwise NAV will display an error.So formula in D11 might look something similar to this:
D11 is =NL("Rows","Customer","No.","Parent Agency No.","@@"&$C$4,"No.","*L*|*FR|*H*|*R*&<>*FRC")
For additional information on this topic, please take a look at the below Jet Reports Knowledge Base article:
"Blank Filters"
http://kb.jetreports.com/article/AA-00521
Cheers, -
Jet Reports Historic Posts Thanks for the responses. So sorry, I should have mentioned that I had already tried "@@". On both formulas and it does not work. Any other suggestions?
Thanks -
Jet Reports Historic Posts Just a quick question, why is your sell-to customer no a range rather than just a single cell?
If you select just D11, does it work?
Including the full report in design mode so we can see cell references could also helpful. -
Jet Reports Historic Posts This formula =NL("Rows","Customer","No.","Parent Agency No.",$C$4,"No.","*L*|*FR|*H*|*R*&<>*FRC") creates a list of customers assigned to a specific parent customer (Parent Agency).
Then, this formula =NL("Rows","Sales Invoice Line",,"+Sell-to Customer No.",$D$11:$D$12,"Posting Date",$C$3,"Quantity","<>0") creates a list of all the sales lines for those customers.
Everything works great if the first formula returns a value. But there will be cases that the parent customer does not have any customers assigned to it that meet the criteria. And when this happens, we get the #Value error and Empty Filter Not Allowed.
And to answer your question, the formula will work if it just "@@"&$D$11, but not if it's "@@"&$D$11:$D$12
I've attached the report. -
Jet Reports Historic Posts Thanks for including the report.
The problem you are having doesn't have to do with using the empty filter but that error message certainly could give that impression but the @@ can do other things.
The @@ also signifies that there are could be special characters in the reference. That can cause problems when you use an range of cells like you have because it is now seeing the data incorrectly.
Instead of seeing the value is D11, it is interpreting it as the value in D11 with the value in D12 attached to it.
With one value it sees customer number "1234" but when you have that range with that @@, it then sees "1234ADMIN" and there are no customers with the customer number "1234ADMIN"
Ideally, there is a lot about this report I would change and working with a report analyst could certainly help but in general, you want to try to filter your data so you don't possibly return blank data.
As I look at this, it seems like a report that would be better suited for a "Grouping Report" because from what I can tell, you want all the sales and credits lines for each retail group in the parent agency. -
Jet Reports Historic Posts Hi I am actually trying to avoid pulling blank records. I have tried "<>@@", but I get an error. Any help would be appreciated!
-
Jet Reports Historic Posts Dinah - what you are describing is actually a different problem, try this: " < > ' ' " (without the spaces I've added here to make it clear what keys I'm using)
Please sign in to leave a comment.