Hi Folks,
I have seen all the solutions on the site here but, none seem to be able to solve the problem that I am having filtering NAVISION data.
I am trying to use a NL("Filter") function to get a set of data to use as a filter, specifically to exclude records instead of include.
Let me take you through my simplified example for this explanation.
For my purpose let say i have a table "Standard Cost Detail" with fields "Location Calc. Filter","Location Code","Item No.", and "Cost"
I would like to return all the "Location Calc. Filter" entries that have a "Location Code" of not blank or "<>''".
I would like to now take those values and filter another table but, I want all the locations EXCEPT the ones that were returned in the filter. Below is syntax for filter.NL("Filter","Standard Cost Detail","Location Calc. Filter","Location Code","<>''")
So let's say the filter returns the following data. "00|00-QC|05|10" (these are my location codes) this reads (00 OR 00-QC OR 05 OR 10)
In Order for Jet/navision to exclude these they cannot read the way they are. The filter must be constructed in the following way "<>00&<>00-QC&<>05&<>10" this reads (NOT 00 AND NOT 00-QC AND NOT 05 AND NOT 10)
which is what is needed. So does any one out there know the solution to get this to happen using Jetreports.
I saw someone answer with ="<>"&$K14 assuming the filter above is in cell $K14 but this only adds the <> to the beginning of the filter and does not change logical OR to logical AND
Maybe I am doing something wrong, not sure. Thanks for help in advance.
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi,
There are a couple ways to do what you're trying to do. Obviously my first choice would be to change your NL(Filter) formula to include what you WANT, not what you don't want. Assuming you can't do that (in some cases it's really hard to do), there are couple options. One would be to use NP(Join) to create the filter you're trying to create here. Basically it would look like this:="<>"&NP("Join",K14,"&<>")
This formula will join all the results of the NL(Filter) function in K14 and put "&<>" between all of them with an extra "<>" on the front. I think this will create the filter you're looking for. Now, in some cases this will work, but if the filter gets too long then it won't work. In this case, you would need to use NP(Difference) to get the difference between the query which returns ALL the results and your NL(Filter) which returns the results you don't want to see. I don't know what table you're actually trying to return data from, but assuming it is called "Table" with a primary key field of "No." here would be your formula:=NL("Rows",NP("Difference",NL("Filter","Table","No."),NL("Filter","Table","No.","No.",K14)))
This should take the set of all numbers in the table and subtract the set of numbers which match the filter value in K14, which I believe is what you are looking for. Does one of these solutions work for you?
Regards,
Hughes -
Jet Reports Historic Posts Great! The second solution worked for me.
Awesome Thanks for the Help!
Please sign in to leave a comment.