Hi guys,
I have to support a co-worker by creating a report. The report consists of the two tables "Service Header Archive" and "Service Item Line Archive". I'm joining them by "Service Header Archive" -> "No." and "Service Item Line Archive" -> "Document No.". I'm filtering by date and service order category. Also there are multiple fields I need to be given out, e.g. "Version No.". Now the problem is when the report is executed I'm having multiple lines with the same "No.", but different "Version No." (as you can see the tables are archives so it's obvious that there are multiple versions of objects). I think I can solve this issue with a filter so that only one line with all the fields is showing and under this line there are listed the remaining "Version No."`s. But how?
In the attachement there are two pictures (First: Problem, Second: How it should be). I will upload the whole file if needed.
Date
Votes
11 comments
-
Jet Reports Historic Posts Hi guys,
I have to support a co-worker by creating a report. The report consists of the two tables "Service Header Archive" and "Service Item Line Archive". There are multiple fields I need, e.g. "Version No.", "Document No.", "Repair No.",… I'm filtering by date and service order category. Now as you can see the two tables are archives, so I'm getting different "Version No."-entries with the same "Document No.", e.g. First line "N1234 VERSION1", second line "N1234 VERSION2" and so on. How can I filter (I think that it can be solved by filtering, but correct me if not) the report that I get to show only 1 line with all the fields and under this line only the remaining Version No.'s? I attached two pictures for better comprehension, first the actual result, second picture how i want it to be and I will provide the full report if needed. -
Jet Reports Historic Posts Hi guys,
I have to support a co-worker by creating a report. The report consists of the two tables "Service Header Archive" and "Service Item Line Archive". There are multiple fields I need, e.g. "Version No.", "Document No.", "Repair No.",… I'm filtering by date and service order category. Now as you can see the two tables are archives, so I'm getting different "Version No."-entries with the same "Document No.", e.g. First line "N1234 VERSION1", second line "N1234 VERSION2" and so on. How can I filter (I think that it can be solved by filtering, but correct me if not) the report that I get to show only 1 line with all the fields and under this line only the remaining Version No.'s? I attached two pictures for better comprehension, first the actual result, second picture how i want it to be and I will provide the full report if needed.
-
Jet Reports Historic Posts Here is an example that returns only the last version of each line for each document in the Sales Line Archive.
(click to enlarge)
I hope that helps. -
Jet Reports Historic Posts Hey HPDeskJet,
Sry for the 2 double posts, didn't notice x.x
Thanks man, I will try your solution and give feedback -
Jet Reports Historic Posts Hey,
in your example you're using the "Sales Lines Archive" table in both NL functions, so to which of my tables do I have to refer this? "Service Header Archive" or "Service Item Line Archive" ? (As I already said the "Service Item Line Archive" is joined with the "Service Header Archive" by "Document No.") -
Jet Reports Historic Posts Hello -
I used the "Sales Lines Archive" since I have a database that has data in that field (I don't have sample 'Service' data).
If I needed data from both the "Service Header Archive" and "Service Item Line Archive" tables (and they share a common "Document No." field), it would be a simple matter to have an NL(Rows) function (for example: in cell C3) that brought in the header info:=NL("Rows","Service Header Archive)along with whatever NF() functions I needed to extract the data I wanted [e.g., setting cell D3 to: =NF(C3,"Document No")]
Then in row 4, I could create another NL(Rows) function to get data from the "Service Item Line Archive" table, referencing cell D3:=NL("Rows","Service Item Line Archive",,"Document No.",D3,…)along with whatever other filters I need [such as an NL(Last) to get only the last version of the line]. -
Jet Reports Historic Posts Hi again,
your solution regarding the latest version is working fine, big thanks for that! Yet there's another problem i got while running the report: I get multiple rows with the same content (pic below, version no. 4 is the latest). I was going through all my fields but I can't imagine that there's a field which get the multiple same data from NAV
-
Jet Reports Historic Posts Can you post the formula that gives you the multiple lines?
-
Jet Reports Historic Posts Hi,
here are the formulas:
but it's already giving me out the duplicates with only the first nl function ( =NL("rows=2";"Service Header Archive";;"Category Service Order";$C$4;;;"Start date";$C$3;"No.";"S??????")) -
Jet Reports Historic Posts So I figured out that there are some fields like "date of archiving", "repair status code" which have different values but the same version no.
I think that's why I get the duplicates. Is there a way to explicitly setting a filter for version no. so that jet will only give me the rows with the newest version no. regardless the other fields? Or how can I solve this? -
Jet Reports Historic Posts You could try…
A function to return the Document No.
You could then use an NL(Filter) function to generate the line numbers available for that Document No.
Then, you could use an NL(Last) [filtered by that NL(Filter) function] to return just the last version for each line. Using the sort capability within a filter (putting a + or - in front of the filter field name) allows you to determine what is considered "last" in an NL(Last) function [it could be date or some other field].
Since you have multiple records with the same Line No. field and the same version (I haven't seen that before), you would have to set the criteria you want to use to determine which record to be displayed.
Please sign in to leave a comment.