I think the concept is good but I'm not 100% on it.
If you add a sort to your date of the first, hopefully that will resolve the issue.=NL("first","item ledger entry","posting date","Entry Type","Sale","Variant Code","*","Item No.",$E40,"+posting date",".."&$D$8)Essentially, sort the Posting dates in ascending order so it that way the first record should always be the oldest.
http://kb.jetreports.com/article/AA-00779/0/
"However, the last record in the table is subject to change based on how Navision is sorting the records overall. So the last record in the table is not always based on the entry order, but could be based on another field organizing the records in a different way."
Same idea applies to first. If the record is sorted by something else, perhaps that is causing the issue.
Date
Votes
2 comments
-
Jet Reports Historic Posts I am trying to extract the first sale date from the Item Ledger Entry table for all open items. My report is basically a list of all items, followed by a NL function to pull the first sale date. See formula below:
=NL("Rows","item","no.","no.",$D$5,"blocked","False","Sales ($)",">0") D5 is an item filter.
=NL("first","item ledger entry","posting date","Entry Type","Sale","Variant Code","*","Item No.",$E40,"posting date",".."&$D$8)
My issue is that on some items, we have sales where the variant code is both blank and used. On these items, the results seem to ignore the sales with Variant Code, and report the first sale without a Variant Code.
Here's my sample data:
Item: 123, Variant: Blue, Posting Date 06/20/2012
Item: 123, Variant: Red, Posting Date 06/21/2012
Item: 123, Variant: , Posting Date 06/22/2012
NL("First") returns the 3rd result!
Does anybody have a workaround? -
Jet Reports Historic Posts Sorting by posting date did the trick! Thanks. That seems rather obvious now :)
Please sign in to leave a comment.