Ports & Many-to-Many Relationships

[mnm]

Okay here is the situation, I have four tables; events, group, students and schools. I want a portal on the events layout to display information based on data from the group, students and schools tables. Where lationships between the tables flow like this:

{ events -> groups -> students -> schools }

events::Id -> group::Id

group::studentId -> students::studentId

students::locationNo -> schools:locationNo

Here is a sample of the table schemas

EVENTS:

eventId

eventName

...

GROUP:

eventId

studentId

studentName

currentAge

currentGrade

currentSchool

...

STUDENTS:

studentId

studentName

studentDateOfBirth

studentGrade

schoolNo

...

SCHOOLS:

schoolId

schoolName

schoolNo

...

I'm trying to get a portal on the events layout to display the student's Name, current age, current grade and school name. The portal is getting the student's name and calculating the age and grade fine, but I can't seem to get the school name in there. Right now the user would just enter the student's Id and the name, grade and age pops right in. I would like the same to happen with the student's school name. Any ideas of what I'm missing or where I'm going wrong?

Thanks.

Version: v6.x

Platform: Mac OS X Panther

[Fitch]

If I follow what you're doing, you should pull the school name into the student file, so that it can then be pulled into groups. Either that or make a relationship from groups to schools so that the name can be calculated or looked up.

[mnm]

Is there any way to get that information using a calculation using certain functions? Say if I get the schoolNo through the lookup function and then use tha value and another function to get the school name. I'm trying to make it so that the fields calculate the values once and that is it, hopefully without having to use scripts and buttons.

Really what I would like to do is pull the schoolsId value into the groups table instead of the school name for data integrity purposes. From year to year the school name or number may change and in doing so I would have to update all the other information using those fields as links. At least with the schoolId I have a field that never changes that I can use as a link. Does filemaker do cascades on updates?

[Fitch]

This whole issue goes away with FM7. But in FM6 is, if you want to show a field in a portal, then that field has to exist in the file to which the portal relates. When the field in question is more than one file away, you have to pipeline the data with a calculated field.

In your case, make a calc field in Students, cSchool, where the calculation is simply school::schoolname. Make another calc in Groups that is student::cSchool. Now you have a school field that you can display in the Events portal.

Another way to do it is to pipeline the ID rather than the data. Play around with it and you'll get the idea.

[mnm]

Ok I'm going to go the FMP7 route, everything seems to be working BUT the fields that contain the name of the student and school do not display the data of the relationships unless (1) I click on those fields specifically after entering the students Id or (2) I click outside the portal.

The other calc fields that com from the direct events -> group relationship are displayed as soon as I enter the student Id and move to the next student Id field. Any suggestions on how to fix this? I found some documents about using a modification trigger and a self join on the record, but that didn't seem to work.

Thanks

Version: v7.x

Platform: Mac OS X Panther