I have a working report. My NL looks like this…
=NL("Filter","Sales Line","Document No.","Artist ID",Options!$G$6,"Item Category Code",Options!$G$12,"Reserved Quantity",Options!$E$11,"No.",Options!$E$13,"Quantity Shipped",Options!$E$14,"Qty. Shipped Not Invoiced",Options!$E$15)
I would like to eliminate any results that begins with "COMM:" in the description.
We thought we would use … "Description","<>COMM:*")
However, it seems not to want to allow us to use <> and * in the same filter. Is this common? Is there another way to say that we want to ignore any results that begin with COMM:?
A little background:
COMM: is the prefix we use for Commissioned artwork. However, there is an undetermined amount of characters that could come after the prefix. Usually the client name, sales order number etc. for quick reference. In this particular report, we want to omit the commissions. However, the commissioned sales look identical to any other sale except for the “Description” field that starts with COMM: which is how we identify them on sight.
I hope I made sense. Our version of NAV is very customized.
Thanks.
Troy
Date
Votes
1 comment
-
Jet Reports Historic Posts Hello Troy,
I had the same problem when I tried to exclude in a report all item numbers starting with 10. Here is the solution from Jetreports Sherman Glass. This is his text I only added my version of your formula the way I think your formula could work:
QUOTE:
This is a problem with Navision and not Jet Reports which can be confirmed easily by trying the same filter in Navision.
However, we do have a work around using what we call "Array Calculations". If you change your formula to:
=NL("Rows","Item","No.","No.",NL("Difference",NL("Filter","Item","No."),NL("Filter","Item","No.","No.","10*")))
END OF QUOTE
Now Troy, I believe your formula should be adjusted to the one below:
=NL("Filter","Sales Line","Document No.","Artist ID",Options!$G$6,"Item Category Code",Options!$G$12,"Reserved Quantity",Options!$E$11,"No.",Options!$E$13,"Quantity Shipped",Options!$E$14,"Qty. Shipped Not Invoiced",Options!$E$15,"Description",NL("Difference",NL("Filter","Sales Line","Description"),NL("Filter","Sales Line,"Description","Description","COMM:*")))
QUOTE
You can look in your Jet Reports Help for further information about using "Difference","Filter" and other array calculations "Union" and "Intersect". Please note that anytime you use NL("Filter") that you wont get the correct results until you "run" the report by selecting Jet >> Report.
For best results when using array calculations, you should use the latest version of Jet Reports.
END OF QUOTE
Happy Jetting!
Arnoud
Please sign in to leave a comment.