Hi There,
Sorting by Sum of amount sold = No problem.
What im posting below i could do in several row statements next to each other but i was really hoping for a sollution to include all these filters in one and thus one neat self generating and self formatting table. It just seems that as long as you cant get it in one neat table its just not very convenient to use the data you generate.
I wanted to try something like this. (i already know it wont work this way, but i figure it makes it easier to understand)
=NL("rows";"Sales Invoice Line";"No.";"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Document No."";""1*"";""No."";NF(;""No."");""=NL(;""""Sales Invoice Header"""";""""No."""";""""Sell-to Country/Region Code"""";$E$8;""""posting date"""";$E$6;""""No."""";NF(;""""Document No.""""))"";""*"")";"*")
I need to sum and sort by item Nos from sales invoice line. but i also need country and posting date filters which cannot be found in teh sales invoice line table, these need to be retreived from the sales invoice header. is there another way to refer to this information in one line?
I figured if you could put a filter in a filter, you might be able to put a filter in a filter in a filter, but you cant :P, i allmost went cross eyed just trying to come up with the propper format :P.
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi!
I think you're confusing sort by sum with Link=. You want to sort by the sum of the amount for each No. which you are doing correctly with a calculated filter field. But then you want to filter by values on a different table, and to do that you should be using Link=, NOT a calculated filter field. So it might look something like this:=NL("rows";"Sales Invoice Line";"No.";"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Document No."";""1*"";""No."";NF(;""No."");""Link="";""Sales Invoice Header"";""No."";""=Document No."";""Sell-to Country/Region Code"";$E$8;""posting date"";$E$6)";"*";"Link=";"Sales Invoice Header";"No.";"=Document No.";"Sell-to Country/Region Code";$E$8;"posting date";$E$6)
The only tricky thing going on here is that I put the Link= into both the main formula and the calculated field formula. You may only need it one place, but I'm not positive so I put it in both places.
Regards,
Hughes -
Jet Reports Historic Posts Thanks Very much, i didnt know a feature such as link= existed , so i would have never figured that out on my own. This is going to be so usefull for many projects to come, YEAY! ^^
Also sorry bout the multiple posts for whoever had to clean that up, i had posted changed the title and posted again and once more for luck all the while it seemed like nothing was happening, afterwords i noticed there was 3 posts but i was unable to even open them, the website was verry slow last night. -
Jet Reports Historic Posts Haha, no worries about the multiple posts. The website was being slow for me earlier as well. I cleaned up the extra posts though, no big deal. :-)
Regards,
Hughes -
Jet Reports Historic Posts Hmm ive been trying to get this done, im getting the error "filter field must not be empty" but im fairly certain this isnt really the issue, i read on some sites that the character limit beyond 255 can cause issues, and seeing as this line is 360 characters long im thinking this might be the cause of the issue instead. ive tried putting "@@"& in front of those fields that might return an empty value but to no avail im thinking this report might not work out in the end, or maybe im just going about it in the wrong way, the "=link" function should work with jet essentials right? Also before i run the report i actually get a value returned rather than a #VALUE message in the field. Something peculiar is going on and ive no idea what it is.
currently trying ot use
=NL("rows";"Sales Invoice Line";"No.";"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Document No."";""1*"";""No."";NF(;""No."");""Link="";""Sales Invoice Header"";""No."";""=Document No."";""Sell-to Country/Region Code"";$E$8;""posting date"";$E$6)";"*";"Link=";"Sales Invoice Header";"No.";"=Document No.";"Sell-to Country/Region Code";$E$8;"posting date";$E$6) -
Jet Reports Historic Posts So I tried this is a CRONUS database and what I found was that there can be blank values for the "No." field in the Sales Invoice Line table. This would make the sort by sum formula blow up with the error you are seeing, so we need an @@ in front of the nested NF(;""No."") like this:
=NL("rows";"Sales Invoice Line";"No.";"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Document No."";""1*"";""No."";""@@""&NF(;""No."");""Link="";""Sales Invoice Header"";""No."";""=Document No."";""Sell-to Country/Region Code"";$E$8;""posting date"";$E$6)";"*";"Link=";"Sales Invoice Header";"No.";"=Document No.";"Sell-to Country/Region Code";$E$8;"posting date";$E$6)
And to answer your questions, yes Link= works just fine in Jet Essentials. Also, you shouldn't have to worry about the 255 character limit if you are using Jet 2009 or later (which you certainly are if you are using Jet Essentials). We can now replicate NL formulas as long as Excel allows them to be (1024 characters in Excel 2003, 8192 in Excel 2007 and 2010).
Does this fix the problem for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks for the Reply, im gonna check this out just as soon as i can, i set this project aside for a little while and worked on some things that i actually could figure out ^^, and thanks for the extra info, i was worried about those limits before, its good to know that i dont have to anymore :D.
I will get back to you later as to whether that did the trick or not. Its been so long ago now that ill have to go back and examine what it was i was trying to do before i can make any sense of it :P, ill be in touch though :) -
Jet Reports Historic Posts Hello mlem,
Since the NL formula has already lots of work to do with that Link=, I propose to use Excel to sort this List. While designed in Spanish, I hope that the report attached clarifies my point on how to use the power of Excel in benefit of Jet Reports.
Reply if you need any clarification.
Happy Reporting!
Mario.
Please sign in to leave a comment.