Hi,
First of all, what I am using here works like a charm, but I need your opinion about the why.
The question asked is about counting contacts that meet a set of criteria from different tables, all related to each other back and forth.
In human language:
I want to count the contacts that live in a certain city, that follow classes of a certain price and type in the given period.
And I want them listed by city and then by price.
In Excel it comes down to this.
B4 holds the Price code filter value.
B5 holds the period of reporting.
B6 holds the City filter value.
All available prices are generated by a NL(Rows) in D8: =NL("Rows";"Price";"Code";"Code";$B$4)Starting with the Contact table I first filter on City in E8: =NL("CountUnique";"Contact";"No.";"City";$B$6;"Link=";$F8)F8 holds a NL(Link) function that links back to the contact table and filters on the given price: =NL("Link";"Seminar Line";;"Participant";"=No.";"Price";$D8;"Link=";$H8;"Link=";$G8)H8 and G8 are holding additional NL(Link) functions, both linked back to Seminar Line.
G8: =NL("Link";"Seminar";;"No.";"=DocumentNo.";"Class Type";"Period")H8: =NL("Link";"Classes";;"DocumentNo.";"=DocumentNo.";"LineNo.";"=LineNo.";"Date";$B$5)
It was quite a puzzle but the results are as expected.
What I don't understand is that I can't switch the double link= parameters in F8 ("Link=";$H8;"Link=";$G8).
When I first link to G8 and then to H8, JetReports tells me (only when I run the report) that it encounters and unknown field 'DocumentNo.'
Although the results are looking good, I still am not sure if I did it correct, because of the error.
I can't find any documentation on the double link parameter I use in F8.
Also I am not sure of the double field link I use in H8 ("DocumentNo.";"=DocumentNo.";"LineNo.";"=LineNo.").
This is because the tables Seminar Line and Classes are related not just on one field but on two.
Looking forward to your opinion about this and hoping that there is some documentation on the NL(Link) that you can share.
rmw
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi rmw!
So when you do 2 Link= the way that you are doing, what you are actually doing is first linking from Seminar Line to Classes and then from Classes to Seminar. The links are being nested. Thus you cannot link from Seminar Line to Seminar directly using these formulas because the DocumentNo. field does not exist on the Seminar Line table; it exists on the Classes table. Hence the order of the 2 links matters.
I think the confusions comes because you are assuming that multiple Link= will both be linked to the parent table, but by default this is not the case. By default they are nested, so you are linking from the main table to the first linked table and then from the first linked table to the second linked table.
You CAN actually do multiple links from the main table. The main formula would look something like this:
=NL("Link";"Seminar Line";;"Participant";"=No.";"Price";$D8;"Link=";$H8;"Link=Seminar Line";$G8)
Notice that I changed the 2nd Link= to Link=Seminar Line which tell Jet Reports to link from the Seminar Line table rather than doing a nested link from the Classes table. Of course, in this case, this would not work in your formula because the DocumentNo. field is not on the Seminar Line, so you need the nested links the way you have them.
Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts I think the confusions comes because you are assuming that multiple Link= will both be linked to the parent table, but by default this is not the case. By default they are nested, so you are linking from the main table to the first linked table and then from the first linked table to the second linked table.
Makes perfect sense.
Thx.
rmw
Please sign in to leave a comment.