My apologies if this is a duplicate post. I couldn't find any information on my issue.
I have a report where I need to return all the sales invoice lines for items by a given vendor in a given date range. I need to see all invoice lines for items from vendor no. 'x' in the Invoice posting date range of 11/1/2008..11/30/2008.
I tried using nested NL("Filter"…) within my primary NL, as demonstrated in the help topics. I was unable to return the needed subset. I'm sorry I don't have the formula I created because it didn't work, I had a deadline to meet, and I deleted it to get the report done.
As you may know, the item's vendor No. is held in the Item table and the Invoice Posting Date is in the Sales Invoice Header. The information I need to return is in the Sales Invoice Line table.
Any help would be greatly appreciated.
Thanks,
Todd
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi there. Please see the attached spreadsheet as an example. In our system, the posting date is available on the line level, which simplifies the report substantially.
Take a look and let me know if its what you had in mind. -
Jet Reports Historic Posts For those of us who don't have a modified database, you can use the following formula instead.
=nl("Rows","Sales Invoice Line",,"Document No.",nl("Filter","Sales Invoice Header","No.","Posting Date",$E$5),"No.",nl("Filter","Item","No.","Vendor No.",$E$4)) -
Jet Reports Historic Posts Thanks for the responses. I will give them a try tonight after I get caught up for taking a day off…
Todd -
Jet Reports Historic Posts Thanks for the assist on this one. The NL code worked; it took 60 minutes, but it worked. After further digging I noticed our partner put the Vendor No. in our
Sales Invoice Line table. Because I could eliminate one filter this was much faster.
Thanks for the help,
Todd -
Jet Reports Historic Posts You may be able to increase the speed of your reports by analysing the keys on the tables. Go into a table in design mode, and look at the keys. Try to make your filters match one of those keys, in terms of the order and sequence of the criteria. Sometimes Jet doesn't choose the best key, and making subtle changes to your NL functions can (at times) have a dramatic effect on your reports.
In addition, some times adding keys to Navision to match a report also have the same effect - but be warned too many keys on a databases table will have a very negative impact on your Navision performance.
Good luck with Jet!
Please sign in to leave a comment.