Sorting alpha column - need blank values last  [SOLVED]

Questions and Answers asked by users to users, about how others use Jet Reports.

For additional "how to" information, please visit our online Knowledge Base https://jetsupport.jetreports.com/hc/en ... ofessional
Forum rules
The Jet Reports Community forums are not a guaranteed support medium. Official support for Jet Reports is available only through the support desk at http://support.jetreports.com. Staff responses to posts on the forum are not guaranteed.
YJury
Posts: 20
Joined: Wed Sep 09, 2015 9:56 am

Sorting alpha column - need blank values last

Postby YJury » Tue Apr 25, 2017 10:20 am

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?

YJury
Posts: 20
Joined: Wed Sep 09, 2015 9:56 am

Re: Sorting alpha column - need blank values last

Postby YJury » Tue Apr 25, 2017 11:01 am

So I found another post that I thought would solve my problem, but it's not working for me.
http://community.jetreports.com/viewtop ... 1e3d#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. :?

Heather
Posts: 122
Joined: Tue Jul 09, 2013 2:04 pm

Re: Sorting alpha column - need blank values last  [SOLVED]

Postby Heather » Tue Apr 25, 2017 1:50 pm

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.

YJury
Posts: 20
Joined: Wed Sep 09, 2015 9:56 am

Re: Sorting alpha column - need blank values last

Postby YJury » Tue Apr 25, 2017 2:19 pm

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

Heather
Posts: 122
Joined: Tue Jul 09, 2013 2:04 pm

Re: Sorting alpha column - need blank values last

Postby Heather » Tue Apr 25, 2017 2:33 pm

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


Return to “How do I?”

Who is online

Users browsing this forum: No registered users and 8 guests