Good afternoon.
I am having an issue dealing with mutiple filters from multiple tables.
Client wants a basic report from the Sales Price Table.
But they would like to be able to filter the report by 'item category code' and 'product group code' from the item table.
And also 'salesperson code' and 'customer no.' from the customer table.
So I have made a couple NL filters to start….
Cell E5=NL("Filter","item","no.","Item Category Code",$C$7,"Product Group Code",$C$9)
Cell E7=NL("filter","customer","no.","Salesperson Code",$C$5,"No.",$C$11)
Here is my starting NL for the Sales Price table…
=NL("rows","sales price",,"item no.",$E$5)
This works fine for filtering to the item table, but how can I add the filter to the customer table ?
I am guessing by doing this…
=NL("rows","sales price",,"item no.",$E$5,"Sales Code",$E$7)
Would this be the right way ?
Date
Votes
5 comments
-
Jet Reports Historic Posts That looks like it should work. Do they use the Sales Price table pretty extensively? The solution that you provided will work for all of the items and customers that are in your NL("Filter") functions but it will ONLY return items that have a specific sales price set up. This won't necessarily show all of the items in the database. If they only want to see items that they have set up prices for on the Item Card under Sales -> Prices then your solution should work fine.
-
Jet Reports Historic Posts This doesn't seem to work though…
The second I add the =NL("rows","sales price",,"item no.",$E$5,"Sales Code",$E$7)
Then a bunch of #VALUE error messages show up.
When I go to diagnose it, it says 'empty filter not allowed'
I am not sure why…. -
Jet Reports Historic Posts This should only show up if the function in cell E7, which is:
NL("filter","customer","no.","Salesperson Code",$C$5,"No.",$C$11)
isn't returning any customers. Is it possible that the filters in C5 and C11 aren't returning any customers? You can change the NL("Filter") to an NL("Count") and it should tell you how many are being returned. Is it zero? -
Jet Reports Historic Posts Both of my NL filter are bringing back records…..I have attached my report…(i have taken out the sales code filter)
-
Jet Reports Historic Posts To further this…
When i put in a customer filter, the report works.
When i put in a Item Category Code filter the report comes back with the 'empty filter not allowed' error message…
Please sign in to leave a comment.