Hi I'm hoping someone here could help me please.
I have an options Sheet which will return a list of customers for a Specific Salesperson from the customer table…however the second field that i want returned is showing up a blank.
=NL("Lookup",NP("Union",NL("Filter","Customer","Bill-To Customer No.","Bill-To Customer No.","<>''","Salesperson Code",$C$2),NL("Filter","Customer","No.","Bill-To Customer No.","''","Salesperson Code",$C$2)),{"No.","Search Name"})
$C$2 is the Value of another option which lists the Salespersons and their names
Array1 is a list of Customers without a Bill-To Number
Array2 is a Unique list of Customer Bill-To Numbers (not including Blanks)
Problem 1: When the options box comes up and I click the drill-down, there are the two columns I wanted {"No.","Search Name"} but the Search name is blank.
there is definitely information in the "Search Name" fields.
Problem 2: When the options box comes up and I click the Drill-Down button it takes about 10 seconds for the list to populate…All the other reports ive done rarely take more than 1 second.
Thank you in advance
-Bromy
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Bromy2004,
How about this:
=NL("Lookup","Customer",{"No.","Search Name"},"No.",NP("Union",NL("Filter","Customer","Bill-To Customer No.","Bill-To Customer No.","<>''","Salesperson Code",$C$2),NL("Filter","Customer","No.","Bill-To Customer No.","''","Salesperson Code",$C$2)))
Does that work?
You replaced the Customer table by the NP(Union), which gives an array of values rather than a link to a table. I restored the table name and moved your Union to the filter part of the NL function.
Please let us know if this works for you! -
Jet Reports Historic Posts Thanks Hans,
Works Perfectly.
-Bromy
Please sign in to leave a comment.