0

sorting with NL ("Filter"). Can it be done.

Jet Reports Historic Posts

February 19, 2010 21:49

None

Follow

This is an NL I am using to give me a list of a customers items, and their prices.

=NL("Rows","Sales Price",,"Sales Type","Customer","Sales Code",$D$6,"Item No.",NL("Filter","Item","No.","+Description","*","Item Category Code","@@"&$D20,"No.","<>@@"))

This works, but it brings back items from the Item table, and it sorts it automatically my item no. I want it to sort alphabetically by description. I have put in a "+Description", "*" to sort by desctiption, but this doesn't work.

Does anyone know how to do this?

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts February 21, 2010 13:53

  Sorting on description this way gives you a list of sorted item no. to use in a filter for Sales Price.
  The sorting does not effect the way the rows from Sales Price are brought back.
  You need to include the description and the sorting in the filters you are using on Sales Price.
  Something like
  =NL("Rows","Sales Price",,"Sales Type","Customer","Sales Code",$D$6,"Item No.",NL("Filter","Item","No.","Item Category Code","@@"&$D20,"No.","<>@@"),"=+NL(""First"",""Item"",""Description"",""No."",NF(,""Item No.""))","*")
  I did not tried it in Excel, so there might be some glitches, but be aware of the double quotes used in the last NL: they are needed for keeping the string a correct piece of text
  What I am trying to do is adding the description from the item table as a criteria for the Sales Price table. The description should be coming from the item no. that is in the sales price line I am getting, so I need the NF() without the first parameter.
  
  HTH
  
  rmw

• 0
  
  Jet Reports Historic Posts February 22, 2010 17:23

  I tried what you mentioned, but it still seems to come back sorted by item no.
  
  I have attached my jet report for you to look at (if you have time).
  
  You can ignore the Options tab. On the other tab, I have highlighted the cells i think come into play for this situation.
  
  Let me know what you think.

  • Contract Order Form for Jet.xls
• 0
  
  Jet Reports Historic Posts February 23, 2010 20:19

  The solution proposed by rmw is very close. The only problem is that the "+" must go before the "=" to indicate that a sort is being requested:
  
  =NL("Rows","Sales Price",,"Sales Type","Customer","Sales Code",$D$6,"Item No.",NL("Filter","Item","No.","Item Category Code","@@"&$D20,"No.","<>@@"),"+=NL(""First"",""Item"",""Description"",""No."",NF(,""Item No.""))","*") Try that out. I tested it using the Item Ledger Entry table and it worked perfectly. This is a clever solution, good job rmw!

• 0
  
  Jet Reports Historic Posts February 23, 2010 22:56

  You are correct, thank you very much, i appreciate it !!!!

• 0
  
  Jet Reports Historic Posts February 24, 2010 19:17

  The solution proposed by rmw is very close.

  Like I said:

  I did not tried it in Excel, so there might be some glitches

  ;)
  
  

  This is a clever solution, good job rmw!

  Thank you very much :D