0

Sorting trouble

Yvonne Jury

November 30, 2017 23:30

None

Follow

Hello,

I have the following NL Rows statement that will not sort the way I want it to.  I need to sort on Batch Code, but it's not working.

=NL("Rows","Posted Work Order Line","Vessel code","+Batch code","*",work order date",$C$4,"New batch owner",$D$12,"Filters=",$B$5:$C$7,"=NL(""Last"",""16020671"",""After Volume"",""+work order date"","""&$C$4&""",""+Posting Date"",""*"",""+Posting Time"",""*"",""Vessel code"",nf(,""vessel code""))",">0")

Below is a subset of the results I'm getting, which clearly isn't sorted on Batch Code.

Any help is appreciated! Thanks.

Vessel Code		Batch Code
MKG15		15CSCAL3003AGV
C108		15MXFRC9001AGV
B207		15OMFRC9001AGV
M613		16VILOD3004AGV
H902		15ZILOD3002AGV
SD1922		15ZILOD3002AGV
G3215		17CHPRS027AGV
D1206		17PNCLK055AGV
E1218		17CHCLK012AGV
A304		16CHLOD3017AGV

 

 

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis December 01, 2017 16:39

  Hello Yvonne -

  First question:  What function call are you using to populate the Batch Code column?

  Next... I notice that you have multiple sorting fields in your NL(Last) function.  Keep in mind, only the last sort will be used (in this case: Posting Time).

  Finally, shall we try a test?  I created a rudimentary report based on the Customer and Cust. Ledger Entry tables (my sample database does not have a Posted Work Order Line table).

  My results look like this:

  

  (I'm having the report do a descending sort by the Balance field in the Customer table).

  Here are the functions I used...

  Cell D6:

  =NL("Rows","Customer","No.","-Balance","*","=NL(""Last"",""Cust. Ledger Entry"",""Amount"",""Sell-to customer no."",NF(,""No.""),""Amount"","">0"",""+Posting Date"","""&$D$3&""")",">0")

   

  Cell E6:

  =NL("First","Customer","Balance","No.",D6)

   

  Cell F6:

  =NL("Last","Cust. Ledger Entry","Posting Date","Sell-to customer no.",D6,"+Posting Date",$D$3)

   

  Cell G6:

  =NL("Last","Cust. Ledger Entry","Amount","Sell-to customer no.",D6,"+Posting Date",$D$3,"Amount",">0")

   

  Cell H6:

  =NL("Last","Cust. Ledger Entry","Entry No.","Sell-to customer no.",D6,"+Posting Date",$D$3)

   

  Keep in mind, I'm not trying to build an efficient report (and this report definitely isn't).  

  What I'm trying to do here is show customers who have a non-zero transaction on a specific date and sort that list by the customer balance.

  If you build this report (and set the date to something that will sufficiently limit the amount of data returned), is the info sorted by the Balance?