Recently our company just upgraded to Navision 5.0 and also Jet Reports version 7.0.6 build 1759
One of the reports I run uses the formula below to return the last (most recent) posting date for an item. Before the upgrade, the report would return the correct date between the two locations. Now the report shows only the last date for the highest alphabetical location (in this case the last date for the PRI location). See the formula below
=NL(-1,"Item Ledger Entry","Posting Date","Entry Type","Positive Adjmt.","Location Code","JGP|PRI","Item No.",$E10,"Document No.",$D$4)
I have been seeing this on other reports as well, either when the location is not added as a filter or when their are multiple locations in the filter as shown above.
Does anyone have any ideas?
Thanks in advance,
Scott
Date
Votes
5 comments
-
Jet Reports Historic Posts Try adding an additional filter for the Posting Date at the end of your formula. Such as:
=NL(-1,"Item Ledger Entry","Posting Date","Entry Type","Positive Adjmt.","Location Code","JGP|PRI","Item No.",$E10,"Document No.",$D$4,"+Posting Date","*")
This will force Jet to sort ascending order by the Posting Date column (thats the '+' sign), effectively giving you the first record.
Alternatively you could use positive 1 and a descending sort (may be quicker than last and ascending).
There may be better ways to get this information but I can't think of any right now.
cheers -
Jet Reports Historic Posts Thank you
It seems to be working fine now. I appreciate your help
-S -
Jet Reports Historic Posts No worries. Happy to help.
-
Jet Reports Historic Posts Funny how it worked before though!
Did you switch from native to SQL too? -
Jet Reports Historic Posts Good morning all,
The Item entry statistics form also gives you the latest date of all entry types. You may find this form by opening the item card -> item menu button -> statistics -> entry statistics.
Checking the C/AL code for this form shows that NAV 5.0 uses this key:
ItemLedgEntry2.SETCURRENTKEY(
"Item No.","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date");
I wonder what happens to the result / performance of your report if you change your formula to:
=NL(-1,"Item Ledger Entry","Posting Date","+Item No.",$E10,"+Entry Type","+Positive Adjmt.","+Variant Code","*","+Drop Shipment","*","+Location Code","JGP|PRI","+Posting Date","*","Document No.",$D$4)
The reason I'm asking is that we have another topic on this forum about selecting keys: http://community.jetreports.com/viewtopic.php?f=7&t=224&st=0&sk=t&sd=a
And as you are using version 7.0.6 I'm curious what happens. Many thanks if you want to try this out and tell us about the results.
Please sign in to leave a comment.