Hughes,
Sorry for notgetting back earlier. My NL(last) function looks like this: =NL("Last";"Item Ledger Entry";"Entry No.";"Entry Type";"Purchase";"Item No.";"XYZ"). So no date filter and I ran the report. It seems that the NL(Last) does not work in this function. Regardless if I use First or Last it always come up with the first record based on the selction criteria Item No. and Entry Type. It doesn't work with filters? Any help is appreciated.
Thanks,
Andor
Date
Votes
6 comments
-
Jet Reports Historic Posts Official comment Hughes,
Some more tinkering did the job! Following works: =NL("Last";"Item Ledger Entry";"Entry No.";"Entry Type";"Purchase";"Item No.";$E8;"+Entry No.";"*"). In other words, first sort the entries and thean pick the lasts.
Regards,
Andor -
Jet Reports Historic Posts I have created a very simple NL("Last"; from the item ledger entry table in Navison but it seems that the last function is only returning the last transaction from 2011 and does not take into account the 2012 transactions. Can anyone help to solve this?
Thanks, Andor -
Jet Reports Historic Posts Hi Andor,
The NL(Last) has absolutely no concept of which year a particular transaction is in. The only way NL(Last) would be taking transaction dates into account is if you're filtering by the Posting Date field in the NL(Last) function. What does your full NL(Last) function look like? Also, did you actually run the report by select Jet -> Report?
Regards,
Hughes -
Jet Reports Historic Posts Also a small performance improvement could be to pick to first with reverse sorting.
=NL("First";"Item Ledger Entry";"Entry No.";"Entry Type";"Purchase";"Item No.";$E8;"-Entry No.";"*")
This way if the number of results is more than SQL datasetpage (dont remember exact name) the server does not have to fetch everything. -
Jet Reports Historic Posts Hi Sebastiaan,
Actually you are wrong about that. Andor's version would perform better than yours. When there are descending (negative) sorts, Jet can't have NAV do the sorting for it. With descending sorts, Jet has to pull back all the records that match the filters and sort them manually. However, if you have an ascending (positive) sort and a NAV key matches it (which in his case it does), then Jet can have NAV do the sorting and just ask NAV for the last record in the set. For performance you should always favor ascending sorts if there is a matching key because that will be faster.
Regards,
Hughes -
Jet Reports Historic Posts Ok, then I was misinformed about that… Good to know…
Please sign in to leave a comment.