I am trying to sort an NL("Rows"..) function by a calculated value. This is at the second level (so inside another Row replicator) and needs to refer to the results of the main replicator.
The row replicator is in E16, the calculated filter as text in D16 and the result of the first replicator in C16 (copied down). The cell values are:
E16: =NL("rows","Vendor",,D16,"*")
D16: +=NL("Sum","32","5816","5",c16,"4","Sale","3",$g$5,"Link=","27","1","=2","31",nf(,"No."))-NL("Sum","32","5816","5",c16,"4","Sale","3",$g$6,"Link=","27","1","=2","31",Nf(,"No."))-NL("Sum","7134","11","3",$h$6,"4",$g$6,"7",C16,"8",NF(,"No."))
The syntax for D16 works fine. The problem is the reference to C16. As this is a text field this reference isn't updated by the row replicator, thus only the first set of values sorts correctly.
I have already had to shorten the expression to just table and field numbers to get within the 255 character limit. I can't find a way of keeping it under 255 characters and allowing the row replicator to update the reference to C16.
The closest I came was to split the expression up into a number of cells then combine it in the NL function using an expression similar to P16&""&C16&""&Q16 etc. This allows the row replicator to update the C16 cell reference, but it generates an error when the report is run.
If anyone has any ideas on how I can get this working I would be grateful. However I suspect the required expression may just be too long.
Many thanks,
Garry.
Date
Votes
5 comments
-
Jet Reports Historic Posts If I'm understanding you correctly, C16 is correct to reference in the first row, but not in the subsequent rows.
Is C16 your first ROWS replicator?
C16: = NL("ROWS=2",…..)
E16: = NL ("ROWS",….)
D16: = NL("SUM",….)
If that is the case, then move E16 and D16 down one row, and use C16 just to pull the data for the row 17 replicator. (see below) You can hide this row if you want.
and in C17, you want to just use = C16, which will replicate with the rest of the row, and always reference the row above it for the sake of the E17 command.
C16: = NL("ROWS=2",…)
C17: = C16
E17: = NL("ROWS",…)
D17: = NL("SUM",…) <- this needs to use a link to C17, not C16
If I'm wrong, please let us know, and I'm sure we'll try to dig into it further, a screenshot or copy of your report would help in that case. -
Jet Reports Historic Posts Hi Heather,
Thanks for your reply. The report isn't quite as you thought.
The NL("Rows=2",…) replicator is in E15 (the row above).
Then in E16 there is a second NL("Rows",…) replicator which refers to the filter expression I am having problems with. This filter is in D16 as text, for the reason that it is already 240 characters long. This works in D16, but I can't include it in the main replicator as all the double "" marks required make it too long.
The filter in D16 then refers to C16 which copies down the value from the NL("Rows=2",…) expression in the line above. But because D16 is a text field the reference to C16 isn't updated by the row replicator, so the expression always refers to the value in C16.
I have attached a copy of the report so you can see this in detail.
Thanks again for looking at it.
Garry.
Jet Help Example Report.xlsx -
Jet Reports Historic Posts Garry - I'm sorry, I don't see an easy solution to the issue you are presenting.
Sort by sum is probably my least favorite operation, and generally I determine a way around it - by resorting at the end, adding filters or some other method.
Hopefully someone else will come along that has tried to stubborn something like this out before. -
Jet Reports Historic Posts As far as I know you can have more then 255 characters of text as a formula result by using concatenate.
Something like
="abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234"[size=150]&[/size]"abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234abcdefghijklmnopqrstuvwxyz1234"
makes it possible to have longer strings as a result (300 in this case)
HTH
rmw -
Jet Reports Historic Posts Hey Gary,
Check out this link. Think it'll be able to help you out!
https://jetsupport.jetreports.com/hc/en … namics-NAV
Please sign in to leave a comment.