0

Column Sorting in NL Table

Jet Reports Historic Posts

July 16, 2014 15:04

None

Follow

I am looking to sort on a column in a NL Table.
Column 1 has the Document No & Column 2 has a G/L NO, but currently jet sorts by the G/l No, I would like to sort by Column 1 Document No

Does this help=
=NL("Table","Purchase Line",$E$13:$V$13,"Headers=",$E$12:$V$12,"TableName=","Purchase Line","UseLocalFormulas=",TRUE,"Filters=",$C$5:$D$6,"Link=Purchase Line",$E$11,"IncludeDuplicates=","True")

This is from Nav 2009 R2

Thanks

• Facebook
• Twitter
• LinkedIn

9 comments

• 
  Jet Reports Historic Posts July 23, 2014 12:38 Official comment

  Hi Clipif,
  
  All function arguments before the Link= to Purchase Header are related to Purchase Line. So, if you want to filter / sort on Purchase Line, move the fields a bit. This is what I did in my Jet (latest version) and it works - it sorts the lines from z to a:
  
  =NL("Table";"Purchase Line";$E$13:$W$13;"Headers=";$E$12:$W$12;"TableName=";"Purchase Line";"UseLocalFormulas=";TRUE;"Filters=";$C$5:$D$6;"Document Type";"Order";"-Document No.";"*";"Link=Purchase Line";$E$11;"IncludeDuplicates=";"True")

• 0
  
  Jet Reports Historic Posts July 16, 2014 15:17

  Hi,
  
  Just add the fields you want to sort on at the end of the table formula with a "+" for ascending and a "-" for descending sorting. Also add a wildcard so data will not be filtered.
  =NL("Table","Purchase Line",$E$13:$V$13,"Headers=",$E$12:$V$12,"TableName=","Purchase Line","UseLocalFormulas=",TRUE,"Filters=",$C$5:$D$6,"Link=Purchase Line",$E$11,"IncludeDuplicates=","True";"+fieldname1";"*";"-fieldname2";"*")
  
  Hope this helps?
  
  Regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 17, 2014 08:46

  Hi, I have amended to read:-
  
  =NL("Table","Purchase Line",$E$13:$U$13,"Headers=",$E$12:$U$12,"TableName=","Purchase Line","UseLocalFormulas=",TRUE,"Filters=",$C$5:$D$6,"Link=Purchase Line",$E$11,"IncludeDuplicates=","True","Document Type","Order","+No.","*")
  
  But the sorting ignores this, it doesn't appear to be sorted at all?
  
  The report is based on Purchase Line but linked to Purchase Header, but when I look at the fields to sort on as above, it only gives me fields from the purchase header, but I would want to use Document No. from the Purchase Line but it is not available?

• 0
  
  Jet Reports Historic Posts July 18, 2014 13:35

  Hi Clipif,
  
  Can you please post (an example of) your report. This is easer to check.
  
  Thanks,
  Gerard

• 0
  
  Jet Reports Historic Posts July 22, 2014 14:54

  This is the first time I have uploaded, so please advise if ok?
  
  Thanks

  • Purchase Orders Site 2 up.xlsx
• 0
  
  Jet Reports Historic Posts July 23, 2014 06:51

  Hi Clipif,
  
  Did some testing yesterday and I am afraid I have to admit I was wrong :-(. Sorry for that!!!
  Thought this worked, but no luck so far.
  
  I will try to find some time today to do some further testing…
  
  Regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 23, 2014 12:54

  Hi Hans,
  
  Thanks for helping us out!
  
  Regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 23, 2014 13:21

  Hi Hans,
  
  That's great, worked like a dream & I learnt something new, also thanks to gjvk70 for your assistance, good to see people helping, I might post another question now!

• 0
  
  Florian Kibler March 31, 2020 13:39

  Hello,

  could you please provide the solution / trick how the problem got solved finally as it seems I'm looking for a similar solution. Thanks!