Hi,

I know this issue has come up many times in this forum, but I've
searched through and still can't find the solution to do something
fairly simple.

Here's the situation:
I have a table of students and a table of classes that are relationally
linked in a many-to-many relationships (one student can be in many
classes; one class can have many students).

I am trying to print an individual list of classes for each student.

It's easy to do if I create a layout based on the Student data and
stick in a portal for related classes.

But how should I do this without using a portal?

Specifically:
- which database should I base my layout on?
- how should I make sure that the list of classes is specific to
individual students (related only)?
- do I have to write any scripts? If so, can you describe?

Thanks!

Okay I think I have this half-way figured out but not entirely. I
created a script saying "Go to related record" in my Student database
and also told the script to go to the Classes database and show it in
the Preview mode. What this achieved was that when I am in any
student's individual record, if I run the script, it will generate a
list of classes that they are taking.

Now, I want to change the layout of this list so that in the header
part, I can put in the student's name... essentially so it can say
"List of Classes for John Doe". I'm having trouble getting it to put
in the "John Doe"... any ideas? I read somewhere that I would need to
create a reverse relationship but I don't know how...


nbarman (AT) gmail (DOT) com ne likha tha:

Anyhow, a 'many to many' relationship needs a third intermediate table,
let's call it 'Student-Class', that will have one record per couple
student-class.
Then you do all your editing and printing from that third table.
Would you need more explanations ?
Remi-Noel


<nbarman (AT) gmail (DOT) com> a écrit dans le message de news:
1165855511.792498.243740 (AT) n67g20...oglegroups.com...

Yes, I do have a "join table" that has unique combinations of Students
and Classes, but the problem is that I'm trying to print a report where
the header has each student's name, and then is followed by a list of
courses that student is enrolled in.

In order to do this, I cannot base it on the Join table... I have to
create my layout on the Classes table and then create a script in the
Student table that "goes to related record" and shows it in the Classes
printing layout. The only problem now is that I need to display the
Student's name from the master table (Student) in the header of the
child table (Classes). In order to do this, I have read multiple
places that I need to create a reverse relationship but I don't know
how...

Thanks!


Remi-Noel Menegaux ne likha tha:

There's several ways to achieve what you want. If you want to stick
with the GTRR from Students to Classes, you can create a global field
that will just be used to display the name of the student on your
printing layout. You set that field to the students name via script.

A better design would be to GTRR to the join table. You can include
the related Name field from the Students table and the related class
descriptions from the Classes table on the layout and they will display
correctly.

G


nbarman (AT) gmail (DOT) com wrote:

I may add that in the joint table you can :
- have a small 'Header' part with a global that you fill by script, or no
Header at all.
- create sub-summaries one above the 'body' part and one under it, both
being 'sub-summary after sort'. In it you put the student name and his ID.
- make a sort on student name (or his ID) - that's the key part -.
- in the body part you put the classes attended.
- then in preview and in printing you have all what you want...

relationships with Student ID as a link (both sides) with the student table
and with ClassID as a link (both sides) with the Class table.
Then if your record definition of the join table is only 1) the studentID
and 2) the classID, you get by the relationships all informations you want
from the related tables.

I hope to be clear enough.
If needed you may ask me in private to make you a small example.
HTH.
Remi-Noel


<nbarman (AT) gmail (DOT) com> a écrit dans le message de news:
1165864813.089123.162180 (AT) 73g200...oglegroups.com...
Yes, I do have a "join table" that has unique combinations of Students
and Classes, but the problem is that I'm trying to print a report where
the header has each student's name, and then is followed by a list of
courses that student is enrolled in.

In order to do this, I cannot base it on the Join table... I have to
create my layout on the Classes table and then create a script in the
Student table that "goes to related record" and shows it in the Classes
printing layout. The only problem now is that I need to display the
Student's name from the master table (Student) in the header of the
child table (Classes). In order to do this, I have read multiple
places that I need to create a reverse relationship but I don't know
how...

Thanks!


Remi-Noel Menegaux ne likha tha:

Hi Grip,

The suggestion you provided worked wonderfully. I did a GTRR to the
join table. I created my printing layout based on the Join Table and
was able to include the Student Name as well as the list of Courses.

Now, I have another problem. So you'll remember that there was a
many-to-many relationship set up between Students and Courses (hence
the join table). In addition to this, there is a one-to-many
relationship set up between Courses and Schedules. That means that one
course is linked to multiple Schedule ID's.

What I want to do is create a print layout (again, without using
portals) where I can show the student's name at the top, and then show
their list of courses, along with the schedule for each course.

The reason this is challenging is because the relationship from Student
to Schedule is from a many-to-many through a one-to-many (Students are
in many classes; each one class has many schedules). What happens is
if I use the Schedule table for my layout, the *name of the student*
changes to something else halfway down the list. The reason for this
is that any one schedule has a single parent, but that parent has
multiple other parents (the Students), so when FM tries to map up from
Schedule to Student, it cannot land at one and only one value.
Alternatively, if I use the join table you suggested above, it only
shows the first schedule corresponding to any given course (instead of
ALL of the schedules associated with it).

Any suggestions?

You need to base your layout on Schedule and GTRR to it. The name of
the student will only change if you show the name on each record. If
you have the name in the title header, then it won't change. Or you
could do the global set field I mentioned earlier.

G

Grip wrote:
Actually, I don't know if that's true. The name may in the header may
actually be different than the name to GTRR'd from. It depends on the
sort order of the relationship.