0

Sorting item no. - last entry

Jet Reports Historic Posts

March 06, 2015 13:47

None

Follow

Hi
I am trying to make a sorted list of item nos. (from item ledger entry).
First the item with the oldest last entry with Entry type consumption.

505113 17-05-2013
505105 27-02-2015
505110 04-03-2015
505108 06-03-2015

I have tried with this (even though I thought I thin it should give me the items with newest entries first) but it does not work.
=NL("rows";"Item Ledger Entry";"item no.";"item no.";"505100..505120";"entry type";"Consumption";"-posting date";"01-01-2013..";)

Any help would be highly appreciated
Br.
mdp12

• Facebook
• Twitter
• LinkedIn

8 comments

• 0
  
  Jet Reports Historic Posts March 09, 2015 08:11

  Hi,
  
  In the NL-Function you can choose and set a key from the NAV-table.
  If you don't do that, Jet will choose a key "on its own".
  
  I would check the key with "key=", maybe there is one with "item no", than your code should work.
  
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts March 09, 2015 10:38

  Hello jetsetter
  
  Thank you for your reply.
  Unfortunately setting a key does not seem to help:
  Here are my codes:
  =NL("rows";"Item Ledger Entry";"item no.";"item no.";"505100..505150";"entry type";"Consumption";"key=";{"Item No."\"Entry Type"\"Posting Date"\"Customer Group Code"\"Location Code"\"Entry No."};"-posting date";"01-01-2014..")
  =NL("last";"item ledger entry";"posting date";"+posting date";"*";"item no.";C8;"entry type";"consumption")
  
  And the list:
  Item no. Last entry
  505136 03-03-2015
  505105 27-02-2015
  505111 06-03-2015
  505127 07-11-2014
  505147 19-03-2014
  505134 12-08-2014
  505108 06-03-2015
  505109 06-03-2015
  505110 04-03-2015
  505112 06-03-2015
  
  Can you see what I am doing wrong here ?
  
  Br.
  mdp12

• 0
  
  Jet Reports Historic Posts March 09, 2015 11:45

  OK,
  
  please attache your excelfile with the Report for a better understanding.
  
  thx
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts March 09, 2015 12:09

  Report attached.
  
  What I really would like is to get a list with item nos. starting with the no. with
  the oldest last entry (consumption).
  
  I thought the code below would give me a list starting with nos. with the newest entry,
  but it is not working..
  
  Br.
  mdp12

  • Sorting item nos.xlsx
• 0
  
  Jet Reports Historic Posts March 09, 2015 13:43

  in the "NL(Last)" there is no setting of a key, like you did in the "NL(rows)".
  So the "Last" entry is the last entry with the key, Jet takes.
  
  Try to set a key in the NL(Last) -Function.
  Did you check the item-no? are they correct?
  
  regards

• 0
  
  Jet Reports Historic Posts March 09, 2015 15:04

  Hi
  
  I tried to add a key in the NL("last"; formula, and it gives me the exact same list.
  
  I have checked our Navision database and the item nos. are correct and
  the dates listed are the newest posting dates with entry type consumption.
  
  So data is right, but unfortunately item nos. are not sorted.
  
  Br.
  mdp12

• 0
  
  Jet Reports Historic Posts March 09, 2015 18:15

  Hi.
  
  It looks like you want the items sorted by the last consumption date entry. I was able to do this with the attached file. I had to remove a couple of your filters like the type so it would work on my database. If you add back added filters that you had on your copy it should work perfectly.
  
  Let us know if this works.
  
  Best.

  • Sorting item nos With Sort By Date.xlsx
• 0
  
  Jet Reports Historic Posts March 10, 2015 07:41

  Thanks a lot JasonT.
  It works perfectly :)
  
  Br.
  mdp12