Relationship and report question

[Mike Janus]

For starters, I'm in FMP8.0 on a PC with 1 gig of ram and lots of storage.

I know there has to be an easy solution to this, but I'm drawing a blank.

I have three databases for a school-type setup:

Participants

Offerings

Enrollments

Participants keeps the student information including ParticipantID, LName_FName, phone, and email. There is only one record for each participant.

Offerings has the class information, including Offering and Offering_Year_Semester (a single field which with the concatenation is a unique field). There is only one record for each Offering_Year_Semester.

Enrollments has the sign-up information including classID, studentID, and status (participant or facilitator--a person may be a participant in one or more offerings and a facilitator in one or more offerings). There is only one record for each combination of classID and studentID.

Relationships:

Enrollments is related to Participants (studentID to ParticpantID)

Enrollments is also related to Offerings (classID to Offering_Year_Semester)

Participants is also related to Enrollments (ParticipantID to studentID)

Offerings is related to Enrollments (classID to Offering_Year_Student)

I need a separate report for each Offering which shows the name, email, phone number, and status of each participant in that Offering.

In Offerings, I can do a layout which will give me a separate report showing the offering at the top and then, in a portal, shows the name, email and phone number of each person in that offering--because those three items are all in the participant database to which the offering database is linked.

However, I can't add the status since that information comes from the Enrollments database. When I put the Status field from the Enrollments database into the portal, it shows only the status of the first record in enrollments (I think). What it shows is that all participants in the Offering are participants and not picking up the fact that one or two of them are indicated as facilitators as entered in Enrollments.

Any of this make sense?

[comment]

The layout should be based on the Enrollments table. Place the relevant related fields from Participants in the body part, and the related fields from Offerings in a leading sub-summary.