Hello,
my question may seem too basic, but I am not able to find a way to obtain the rows of a table based in an OR condition.
I'd like to obtain all the rows from a table that meet one (and only one) of these conditions:
1.- Date1 is in DateRange OR
2.- Date2 is in DateRange OR
3.- Date3 is in DateRange OR
4.- Status is OPEN
If I build this:
=NL("Rows";"Sales Header";;"Document Type";"Return Order";"Date1";DateRange)
=NL("Rows";"Sales Header";;"Document Type";"Return Order";"Date2";DateRange)
=NL("Rows";"Sales Header";;"Document Type";"Return Order";"Date3";DateRange)
=NL("Rows";"Sales Header";;"Document Type";"Return Order";"Status";'Open')
then I get a lot of repeated lines. How can I build a single NL function that takes the OR condition and does not bring the lines more than once?
Many thanks in advance,
Josetxo
Date
Votes
2 comments
-
Jet Reports Historic Posts Every set of filters in a NL is an AND filter.
But there is a way around.
What version of Jet do you use?
For v7 you need NL("Filter"), for Jet 2009 you better use NL("Link").
The Help provides nice explanations.
What you need to do is collect Sales Header ID's and use that array in your final NL
Something like this (not checked in excel, just typing here…)
C5=NL("Filter";"Sales Header";"Nr.";"Document Type";"Return Order";"Status";'Open';"Date1";DateRange)
D5=NL("Filter";"Sales Header";"Nr.";"Document Type";"Return Order";"Status";'Open';"Date2";DateRange)
E5=NL("Filter";"Sales Header";"Nr.";"Document Type";"Return Order";"Status";'Open';"Date3";DateRange)
F5=NL("Rows";"Sales Header";;"Nr.";NP("Union";C5;NP("Union";E5;D5)))
Be aware of a strange limitation of C/Front: the array you get back from NL("Filter") can NOT exceed a 779 character limit….
If it does, Jet won't report a failure, but the report won't work.
rmw -
Jet Reports Historic Posts Thanks a lot rmw, it works now.
By the way, it is Jet 7.
Best regards,
Josetxo
Please sign in to leave a comment.