0

Sorting alpha column - need blank values last

Jet Reports Historic Posts

April 25, 2017 17:20

None

Follow

Hello,
In an NL Rows function I'm sorting ascending on an alpha column. Jet sorts with the blanks first, then continues with the values sorted ascending. I need it to sort with the blanks last (like the way Excel defaults). How can I accomplish this?

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts April 25, 2017 20:50 Official comment

  what if you did two versions of the ROWS command - the first one filtered to return ONLY blanks:
  
  =NL("Rows","Production Order",,"Status",$C$2, "+=NF(,""Bin Code"")"," ' ' ", "+Due Date","*","+Bottling Date","*","+Description","*","link=","Prod. Order Line","Prod. Order No.","=No.","Remaining Quantity",">0")
  NOTE: I have included spaces in the red text above to show the characters used
  
  then immediately below that command, use another ROWS command that does NOT include the blanks (again, spaces added to red text to show characters
  =NL("Rows","Production Order",,"Status",$C$2, "+=NF(,""Bin Code"")"," < > ' ' ", "+Due Date","*","+Bottling Date","*","+Description","*","link=","Prod. Order Line","Prod. Order No.","=No.","Remaining Quantity",">0")
  
  I'm not using the Production Order table in my database, but this trick has worked with some other areas that I needed special sorting for.

• 0
  
  Jet Reports Historic Posts April 25, 2017 18:01

  So I found another post that I thought would solve my problem, but it's not working for me.
  http://community.jetreports.com/viewtopic.php?f=7&t=2134&p=6202&hilit=sort+code+field&sid=8a2f85e509ae0c550cdfac22deee1e3d#p6202
  
  The field I'm sorting on is actually a code field in NAV. My original formula was (differences in Red):
  
  =NL("Rows","Production Order",,"Status",$C$2, "+Bin Code","*", "+Due Date","*","+Bottling Date","*","+Description","*","link=","Prod. Order Line","Prod. Order No.","=No.","Remaining Quantity",">0")
  
  and I changed it to:
  =NL("Rows","Production Order",,"Status",$C$2, "+=NF(,""Bin Code"")","*", "+Due Date","*","+Bottling Date","*","+Description","*","link=","Prod. Order Line","Prod. Order No.","=No.","Remaining Quantity",">0")
  
  I'm still not having any luck; blanks are still at the top. :?

• 0
  
  Jet Reports Historic Posts April 25, 2017 21:19

  Thank you, Heather. I had not thought of that, but it works! :D

• 0
  
  Jet Reports Historic Posts April 25, 2017 21:33

  glad I could help :)
  It's one of those puzzles that make Jet a "fun yet frustrating" tool some days…