I am looking to create a jet report that will give me the last date that an item had any type of transaction. I am using Excel 2003. I tried =nl("last","Item Ledger Entry","Posting Date","item No.",C4) but it comes back Invalid What "Last".
Date
Votes
8 comments
-
Jet Reports Historic Posts Which version of Jet Reports are you using, mwack?
What happens if you enter "-1" instead of "Last". That used to be the way to retrieve the last record in older versions of Jet Reports.
O, and don't forget to sort your data on date to make sure the data are in the right order:=nl("-1","Item Ledger Entry","Posting Date","item No.",C4,"+Posting date","*")
Please let us know if this did the trick for you. -
Jet Reports Historic Posts I tried the -1 and it worked. Thank you very much!
-
Jet Reports Historic Posts What version of Jet Reports are you using? I believe NL(Last) was added in Jet Reports 2009 so if you are using a version before that, you would have to use -1. Of course I would highly recommend you upgrade to the latest version of Jet Reports if possible.
Regards,
Hughes -
Jet Reports Historic Posts I have a similar situation that I am having issues with sorting.
I am using Jet 2010 and NAV 5.0
I am using the Job Ledger Entry table and trying to pull the last date of activity for a particular job. When I originally wrote my formula, I used:=NL("Last","Job Ledger Entry","Posting Date","company=",$T4390,"Job No.",$B4389")
This returned a result that matched the last entry in my NAV job ledger entry table but the table in NAV is sorted by task code and not by posting date. After reading this post, I modified my formula to:=NL("Last","Job Ledger Entry","Posting Date","company=",$T4390,"Job No.",$B4389,"+Posting Date","*")
It changed the date that it is returning, but I can still see that this is not the most recent posting date available on the table. If I do a Sort in NAV, I am able to see the most recent date with no problem. Unfortunately, I have a list of 4000+ "Jobs" that i need to find the last activity for, so any help would be greatly appreciated!
Thanks! -
Jet Reports Historic Posts Hi!
Are you actually the running the report (Jet -> Report or Jet -> Refresh)? I tried your formula and it works great but you have to actually run the report for the sort to work and give you the last result. In design mode, it's probably just using the primary key which is a speed optimization to show results quickly in design mode. If you run the report, you should see the last value with the sorts applied.
Regards,
Hughes -
Jet Reports Historic Posts Hi!
Are you actually the running the report (Jet -> Report or Jet -> Refresh)? I tried your formula and it works great but you have to actually run the report for the sort to work and give you the last result. In design mode, it's probably just using the primary key which is a speed optimization to show results quickly in design mode. If you run the report, you should see the last value with the sorts applied.
Regards,
Hughes
Aha!! Genius! :) -
Jet Reports Historic Posts Is there a way to achieve the last record without running the report? In previous versions of Jet, a report in design mode would show the last record in the senerio described above. I have many reports that are used only in design mode just for this reason.
-
Jet Reports Historic Posts Nope, I'm afraid not. This was probably a design mode optimization made in Jet 2009, although I would have to go back and look to be sure. Jet Reports has always recommended and will continue to recommend that you do not trust the values on reports in design mode. Sorting and some types of filters are not applied in design mode, so many formulas may not have the correct results. The values returned in design mode are just placeholders that allow you to design reports more easily.
Regards,
Hughes
Please sign in to leave a comment.