Hi all,
I have an issue with JET Report in Navision.
I want Purchase order No. in Rows. that is achieved by =NL("Rows","Purchase Header") IN C4 and =NF($C4,"No.") in C5.
Now in C6 i want all comments attached to this purchase order.
If i am trying that will columns mapped to this document type and document no. it not working as expected.
If anyone have any resolution please reply.
Regards,
Saurav Dhyani
Date
Votes
6 comments
-
Jet Reports Historic Posts If you're trying to put an NL(Columns) function inside an NL(Rows) function, that won't work since NL(Columns) always gets expanded BEFORE NL(Rows). You could probably use another NL(Rows) to replicate the comments for each purchase header. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Not sure if this is still needed, but thought I'd post it anyway…
This is the formula I use to join my comments within Items on the Inventory.
=NP("Join",NL("Rows","Comment Line","Comment","No.",$E6,"Line No.",NL("AllUnique","Comment Line","Line No.","No.",$E6,"Comment","<>''"))&" "&IF(NL("Rows","Comment Line","Comment","No.",$E6,"Line No.",NL("Last","Comment Line","Line No.","No.",$E6,"Comment","<>''"))=NL("Rows","Comment Line","Comment","No.",$E6,"Line No.",NL("AllUnique","Comment Line","Line No.","No.",$E6,"Comment","<>''")),"",NL("Rows","Comment Line","Comment","No.",$E6,"Line No.",NL("Last","Comment Line","Line No.","No.",$E6,"Comment","<>''"))))
Where "Comment Line" is the table used to store the comments of the Items (You may need to use "Purch. Comment Line" (that is the table we use in Nav 2009 RTC)), and "No." is the item number (which has been cell referenced in this case). the IF logic in this formula is calculating the value of the last comment line and calculating it against the comment line before it, and if they match (I.e. one line of comment), then it igonres the second part of the join.
Hope that helps
Thanks
Ben -
Jet Reports Historic Posts Hi Ben,
I think your formula has some problems that will probably cause performance issues as well as just mis-using some types of Jet formulas. First you should never put an NL(Rows) inside other Excel functions. NL(Rows) should only be used when replicating rows. I think in this case you want either NL(First) or NL(Last). Secondly, I think your NP(Join) is completely unnecessary. You're not actually joining an array inside it and you don't specify a join character in the formula, so it seems to be doing nothing. Then it seems like you have some nested formulas like that first NL(AllUnique) which aren't doing anything. It seems like you could simplify your formula down to this:=NL("First","Comment Line","Comment","+Comment","*","No.",$E6,"Comment","<>''")&" "&IF(NL("Last","Comment Line","Comment","+Line No.","*","No.",$E6,"Comment","<>''")=NL("First","Comment Line","Comment","+Comment","*","No.",$E6,"Line No.","Comment","<>''"),"",NL("Last","Comment Line","Comment","+Line No.","*","No.",$E6,"Comment","<>''"))
It's possible I got the simplification of some of the embedded NL(Last) functions wrong, but hopefully not. Hopefully that will work better for you and be faster as well.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thank you for this. I have only been working with Jet Reports for about 6 months now, and I am comparatively new. Ergo, my first question would be, could you please explain why 'you should never put an NL(Rows) inside other Excel functions'. Whilst I don't do it often (obviously this is what 'AllUnique' is for), I think I may commit to this method every now-and-then. It would be interesting to understand why it shouldn't be done?
Secondly, please let me explain the logic of my work, and then perhaps you could impart your knowledge from within?
1) The NP(Join) function is used because some comments have more than 1 or 2 lines, and may have more than one comment. I needed to join all the comment lines together, otherwise the output of the report creates duplicate lines for each item, depending on how many comment lines there are (i.e. Item '1' has 5 comment lines, then Item '1' is replicated 5 times, once for each comment line).
2) Because of the above, my join character is specified by " " (space). I did not want to specify a colon, or comma, etc. because if the comment runs over more than one line, the grammer and flow of the comment may not require any specific character, therefore I left it "blank".
3) Having read through my working again, I think the NL(Rows) should be changed as you noted, and it does seem a little convoluted… However, it worked so clearly it was luck more than judgement!!! :D The logic was; if the line number of the last line that has a comment, is the same as the line number of the first line with a comment (i.e. one line comment), then join the first line of comment with "" (i.e. nothing - blank), if the two line numbers are not the same then join all the comment fields of the line numbers that do not have a blank (i.e. no) comment, and join all these lines with a space " ".
Let me know what you think
Regards
Ben -
Jet Reports Historic Posts Ben,
The reason you never use NL(Rows) inside another function is because (a) it doesn't work and (b) it doesn't perform well. So NL(Rows) is designed to replicate Excel rows but it won't do that if you put it inside another function. If you embed NL(Rows) in another function, it's just going to return the 1st value of the set it would normally replicate. However, inside the data source, it will still retrieve the whole set, so if all you actually want is the first value, then you should use NL(First) because it should be much faster. If you want an array of values for the outer function to work on, then you should use NL(Filter) or in some limited cases NL(AllUnique). So putting NL(Rows) inside another function is just never a good idea.
Now I will address your previous function.
1) Your previous function isn't actually using the NP(Join) functionality. You never actually specified a join character. You are manually using Excel string concatenation to join the results of the NL(Rows) (which will only be the first value as I stated above). But if you were to open your original formula in the Jet Function Wizard (Jfx), you would see that you actually didn't specify the join character at all. Thus NP(Join) is doing nothing in this case except returning the value you have specified.
2) Your join character is not a space. You are not specifying a join character at all. See my previous comment. All you are doing in that formula is concatenating 2 comment lines using Excel string concatenation.
3) So after reading your description of what you're trying to do, I think the formula you want looks more like this:=NP("Join",IF(NL("Last","Comment Line","Comment","+Line No.","*","No.",$E6,"Comment","<>''")=NL("First","Comment Line","Comment","No.",$E6,"+Line No.","*","Comment","<>''"),NL("First","Comment Line","Comment","+Line No.","*","No.",$E6,"Comment","<>''"),NL("AllUnique","Comment Line","Comment","+Line No.","*","No.",$E6,"Comment","<>''"))," ")
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
What you said makes complete sense, and what you have written works perfectly. When I first started using the NL("Join") function, I think I was placing the join character in the wrong place (between each function), hence why it wasn't working. Therefore I created a work around, which I obviously thought was correct!!! :oops: Obviously Excel was doing the job but Jet wasn't!!!
Anyway, thanks for clarifying my points, and thanks for the NL("Join")
Regards
Ben
Please sign in to leave a comment.