I am using a Union to bring through two fields for width and thickness from 2 tables, the name of the field is different, but the data is the same, the union and rows brings the correct info through
NL("Rows=7",NP("Union",NL("FILTER","Report X Detail",{"Width","Thickness"},"Create Date",$B$7),NL("FILTER","pack",{"Target Width","Target Thickness"},"Create Date",$B$7)))
But, I cannot use an NF(,Width) or NF(,Target Width) for both sets of receords, so at the moment I am using an excel SEARCH for the table ID instead to point the NF at the correct name.
Is it possible to use field number, or SQL CAST to name the fields the same so it is irrelevant which table the record came from?
Date
Votes
3 comments
-
Jet Reports Historic Posts I'm amazed this works at all! When I try to return multiple values in a filter, using the syntax you use, it informs that an array isn't allowed where it's expecting a field. Am I missing something?
-
Jet Reports Historic Posts There must be something different in our systems, your vlookup does not work in my system
I resolved the issue by this
=NL("Rows=7",NP("Union",NL("FILTER","Report X Detail","=NF(,""Width"")&""|""&NF(,""Thickness"")","Create Date",$B$7,"=RIGHT(NF(,""Length""),1)","1"),NL("FILTER","pack","=NF(,""Target Width"")&""|""&NF(,""Target Thickness"")","Location Code","LINERESAW","Link=","Pack Ledger Entry","Pack ID","=ID","Entry Date",$B$7,"Type","Consumed")))
This pulls the two dimensions into astring with the | symbol, I then split this using
=NL(2,NP("Split",I11,"|"))
And can now sum the two value tables together referencing the width and thickness -
Jet Reports Historic Posts Here is a screenshot of the first formula working
Please sign in to leave a comment.