I see that others have had this problem, and I understand that I am having it because I have more than one NL(Rows) in my spreadsheet, but my understanding stops rather abruptly at that point. I use NL(Rows) to get Sales Shipment Line document numbers, then I use NL(Rows) to get the lines from each document. Most documents have just one line, but not all. I then use NL(Rows) to get further information from the Item Ledger Entry table. We started NAV in November and many mistakes were made, hence all of the positive and negative adjustments.
If a company has only made one purchase since we started, this works perfectly. Fortunately for us, this is not common.
In all of the reports where a company has made more than one purchase, the second line of the first purchase errors out. This is regardless of whether that first purchase had only one line or not. In the Results example, the first document has only one line. In the Results2 example, the first document has more than one line.
Also, *some* of the documents are duplicated within the report. I cannot figure out the pattern for this. (Doc #s can occur more than once - once for each line within the document, but they should only group one time.)
I would be very grateful for any help.
Reid Barnes
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Reid -
The issue isn't that there are multiple NL(Rows) functions, but that you have multiple NL(Rows) functions on a single row.
Think of it this way…
Let's say we have a "copy" command that does just that… makes a copy of that row. Let's assume we have two copy commands on a row. The first copy command duplicates everything on that row…. including the other copy command. The second copy command make a duplicate of everything on that row… including the first copy command which, in turn, makes a copy containing the second copy command which, makes a copy… etc, etc, etc.
Granted, that's both a simplification and an exaggeration of how the process works, but it helps to describe the underlying issue.
What you really want to do is to avoid the use of more than one NL(Rows) function on a line [or, for that matter, more than one NL(Columns) function in a single column… same issue]. The answer for this is to use what is commonly referred to as a "grouping report". There are a series of five videos in the Jet Reports online knowledgebase (http://kb.jetreports.com/article/AA-00731) that describe how to create this type of report.
I've attached a couple of modified versions of one of the reports you provided.
The first (sample_group) is a traditional grouping report where the Doc # is listed. Then, under that is the Date, From, To, SO#, Cust. PO and Item #. Then under *that* is the info from the ILE table.
If the natural indentation look is what you are wanting to avoid, you can still use the grouping report. The second report (sample_group2) shows how this is done.
I hope that helps. -
Jet Reports Historic Posts Perfect. Thank you!
Please sign in to leave a comment.