Here’s a basic sort of question from a self-trained, beginning Jet Report learner.
Why does NOT on a FilterField work differently when used in this simple NL as opposed when used in an NL with "Link="?
Simple NL
D5 =NL("Rows","Item Ledger Entry",,"Posting Date","3/15/10","+Source No.","<>DSHE001")
E5 =NF(D5,"Source No.")
This returns an ordered list in Column E of all "Source No." (multiple times, if multiple entries exist) from the "Item Ledger Entry" table with the "Posting Date" of “3/15/2010” EXCEPT for the ones with "Source No." equal to “DSHE001”.
Since I want a list of unique "Source No.", I linked it to the "Vendor" Table, where each "No." is listed only once.
NL using "Link="
D6 =NL("Rows","Vendor",,"Link=","Item Ledger Entry","Source No.","=No.","Posting Date","3/15/2010","Source No.","<>DSHE001")
E6 =NF(D6,"No.")
This returns "No." from the "Vendor" table that match "Source No." from "Item Ledger Entry" table that have been filtered by "Posting Date" "3/15/2010" and NOT equal to "Source No." "DSHE001" (or so I had hoped).
However, "DSHE001" shows up in this Column E alphanumeric Source No.s! Why doesn't the "Source No." "<>DSHE001" eliminate it this time?
Date
Votes
3 comments
-
Jet Reports Historic Posts To understand what's going on here, it helps to understand how the Link= is being evaluated. Here's what's happening:
1) The filtered record set from the Vendor table is retrieved. In this case you are retrieving all records, because you have applied no actual filters to the Vendor table itself.
2) For each record in the filtered record set from Vendor:
- Apply standard filters to Item Ledger Entry ("Source No." = "<>DSHE001")
- Apply link field filters to Item Ledger Entry from current record ("Source No." = "Vendor No." from current Vendor record)
- Determine if record exists in filtered record set from Item Ledger Entry.
If a record does not exist in the filtered Item Ledger Entry, then the corresponding record is discarded from the resulting record set in the Vendor table. What's happening is that since the link field filter is being applied after the standard filter for Source No., it is being overridden. Does that make sense?
The solution in this case is to simply apply the filter on the Vendor table ("Source No." = "<>DSHE001"), in which case it will never appear in the record set to begin with. Hope this helps! -
Jet Reports Historic Posts Yes, that makes perfect sense. So the question then becomes, how do you apply a filter to the primary table in an NL using "Link="?
I'm guessing that FilterFields before the "Link=" filter the primary table and those after the "Link=" filter the secondary table.
So I moved the filter in front of the "Link=" and changed the field name to "No." (as it is called in the "Vendor" table).
=NL("Rows","Vendor",,"No.","<>DSHE001","Link=","Item Ledger Entry","Source No.","=No.","Posting Date","3/15/2010")
The list from this NL does not include "DSHE001"!
Are filters always evaluated left to right? -
Jet Reports Historic Posts That's correct. All filters that are specified before the "Link=" are applied to the primary table, all filters that are specified after the "Link=" are applied to the link table. Filters are evaluated from left to right, but you should never apply a filter to the same field in the same table more than once, as the behavior in that case is undefined.
Regards,
Please sign in to leave a comment.