I have two tables item and bin contents, I can link these two tables but what I really what to do is report back an item no if the quantity (from bin content) < Reorder Point (from item table). I'm not sure how to do this, below is my two filter tables;
CellC3 =NL("Filter","Item","No.","Available for Sale","true","Replenishment System","Prod. Order")
Cell C4 =NL("Filter","Bin Content","item no.","Default","true")
I'm on jetreports version 9.2
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Melissa,
The way I have always done this is to run an NL within an NL. I know of two ways to do this, which you use depends on how big the tables are compared to each other.
I would assume "bin contents" is a bigger table than "item", so you would use the "link=" syntax:
=NL("Rows","Item","No.","Available for Sale","true","Replenishment System","Prod. Order","link=","Bin Content","item no.","=No.","Default","true")
If the "item" table is actually bigger than "bin contents", use the "filter" syntax:
=NL("Rows","Item","No.","Available for Sale","true","Replenishment System","Prod. Order",NL("filter","Bin Content","item no.","=No.","Default","true")
3 things to note:
*Both of the initial NLs are set for rows, although you could also use first or last etcetera. The way used "filter" in your examples is not, I think, how it is meant to be used: that "filter" is in my second function. I don't know about Jet 9.2 (I'm on v7), but the Jet Function Wizard only includes "filter" in its choices, not also "link=".
*In the nested NLs (which look at "Bin Content"), the "=No." syntax looks up bin contents that have the same number for that record in the "item" table.
*Again I'm not sure about Jet 9.2, but in v7 the Jet function Wizard cannot assist you to write the nested NL functions, only the principal ones. So you'll have to write it out in full inside one FilterX box in the Jet Function Wizard or in Excel in the cell itself or the formula bar.
Good luck! -
Jet Reports Historic Posts Melissa
Just noticed I got the syntax wrong for the "NL(Filter…" way of doing this which is different to the "link=" way (I use "link=" much more often).
It should read something like this instead:
=NL("Rows","Item","No.","Available for Sale","true","Replenishment System","Prod. Order","No.",NL("filter","Bin Content","No.","Default","true")
Justin
Please sign in to leave a comment.