Hello,
I'm trying to figure out how to filter for blank entries. In this example I'm searching out all my item numbers. I created NF functions to gather further information (IE: routing number, production BOM, procurement card etc). I only want to see the item if it has a "blank" values for anyone of the NF functions. If it has value all the way across I dont want to see it. What would be the most effecient way? I was thinking to filter on the NL function but at the same time was thinking a conditonal hide may be easier. Any help is appreciated.
Thanks,
Chris
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Chris,
So the real issue here is that you want a group of filters that are combined with a logical OR. You want to see records where Field1='' OR Field2='' OR Field3=''. The way Jet Reports filtering works is that all your filters are combined with a logical AND. So if you filter in Jet Reports, what you get is Field1='' AND Field2='' AND Field='' which is not what you want. So there are a couple ways to get what you want. As you pointed out, conditional hide is one of them. Another would be to use the NL(Filter) and NP(Union) functions something like this:
C3: =NL("Filter","Item","No.","Routing No.","@@")
C4: =NL("Filter","Item","No.","Production BOM","@@")
C5: =NL("Filter","Item","No.","Procurement Card","@@")
C7: =NL("Rows",NP("Union",C3,C4,C5))
This should list the unique collection of item numbers where any of these 3 fields is blank. You should experiment in your report and try both the conditional hide route and the Union route and see which one is easier and faster for you. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
That was exactly what I was looking for. It worked like a charm.
Thank you for your help it was much appreciated!!
-Chris
Please sign in to leave a comment.