Hi Guys,
I have done a quick search but wasn't really sure how to word the problem.
What I am trying to do, is run an NL function to get all rows from a table where 3 fields are filtered on.
I also want to link to another table to add another filter. The problem is, the table I link to has multiple rows that are linked to the row from the primary table.
I want to exclude anything where there is a row with a field that is equal to a value.
I now it's not very well explained, so let me show you the code:=NL("Rows","job-hdr",,"job-dept",$C$2,"job-date",$C$3,"address-code",$C$4,"Link=","job-line","job-id","=job-id","cargo-desc","RICE","Link=","doc-adds","job-id","=job-id","address-code","<>BOND09")
As you can see, I am getting rows from the "job-hdr" table, where the "job-dept", "job-date" and "address-code" are filtered on.
I then link to the "job-line" table using the "job-id" as the link, which allows me to get only rows where the "cargo-desc" equals "RICE".
The second link is to the "doc-adds" table, again using "job-id" as the link.
You can see I want to exclude anything where the "address-code" in the "doc-adds" table does not equal "BOND09".
The issue is, in the "doc-adds" table, there are normally about 4 rows for that job-id, and only one will ever be BOND09.
So how can I exclude rows from the primary table (Job-hdr), if there are ANY rows in "doc-adds" which have an "address-code" of "BOND09"?
Hopefully that makes sense and i'm sure this will be simple for most.
Thanks for any assistance offered.
Eds
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi Eds,
So I guess the question is, is there anything else you can filter on in the doc-adds table so that you just get the entries with BOND09? Any other link fields you can use or anything like that? If so, then it's easy. If not, then I think you would need to do an NP(Difference) to get the right affect like this:C5: =NL("Filter","job-hdr",,"job-dept",$C$2,"job-date",$C$3,"address-code",$C$4,"Link=","job-line","job-id","=job-id","cargo-desc","RICE","Link=","doc-adds","job-id","=job-id","address-code","BOND09") C6: =NL("Filter","job-hdr",,"job-dept",$C$2,"job-date",$C$3,"address-code",$C$4,"Link=","job-line","job-id","=job-id","cargo-desc","RICE") C7: =NL("Rows",NP("Difference",C6,C5))
This would take all the rows matching your others filters and subtract from that set the rows that have an address-code of BOND09. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
There is a key for that table called "line-no", and I think the line where "address-code" equals BOND09 is always line-no 2.
I tried adding that to the original filter as below, but it didn't seem to affect the results.=NL("Rows","job-hdr",,"job-dept",$C$2,"job-date",$C$3,"address-code",$C$4,"Link=","job-line","job-id","=job-id","cargo-desc","RICE","Link=","doc-adds","job-id","=job-id","line-no","2","address-code","<>BOND09")
Am I perhaps using the Link= incorrectly?
Cheers
Eds -
Jet Reports Historic Posts Hi Eds,
Well assuming that it's still returning records, I assume that there are rows in the doc-adds table with line-no of 2 and an address-code that is not BOND09. Given that this is the case, you might want to try my other solution using NP(Difference).
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for you quick replies.
When entering the NL(Filter), it just returns #VALUE!
As I understand it, this means it is failing to retrieve anything from the database?
I tried creating it from scratch, and shortened it for testing to just:=NL("Filter","job-hdr",,"job-dept",$C$2)However, this still returns the same.
This means that the NP(Difference) also returns #VALUE!
My user would like this done today, so our temporary solution will be to create BOND10 as an address code, which I can filter on to get only the data I need.
If you have any advice on wether I am using the NL filter incorrectly, I would appreciate it.
Cheers, Eds
P.S. As it turns out, the "line-no" in the "doc-adds" table is not unique, and all of the important rows all have a line-no of 1, very odd database design IMO. -
Jet Reports Historic Posts Eds,
#VALUE actually doesn't mean that no data was returned. It means that you have an error in your formula and you can use Jet -> Debug to diagnose the problem. In this case I failed to specify a field for the NL(Filter) to return. So the question is, what is the primary key field for the Job-hdr table? We need to specify the primary key field in both of those NL(Filter) functions and then this should work. Does that help?
Regards,
Hughes
P.S. Specifying a BON10 as the address code in the database so you can just filter on a specific value that you need will probably be much faster in Jet than using the NP(Difference) solution, so if that is a possibility I would go with that. -
Jet Reports Historic Posts Hi Hughes,
Thanks again for all your quick replies.
Yes creating a new address code does seem to be working well.
It will be handy to have a look at the NP(Difference) function as well, in case this is useful in future situations.
I hadn't realised that there was a debug option, I will definitely have a look at this, as I'm sure it will come in handy.
Once again, thanks for all your help!
Eds
Please sign in to leave a comment.