Portals to data in a Many to Many Join File

[Kirsten]

Hello,

I am currently updating a number of databases that use many to many relationships. I've made use of the pipelining portal to streamline and simplify data entry, but have run into a snag. I know this should be simple, but I can't figure out what I am missing.

I have three files: Courses, Instructors and CourseInstrJoin. Each course can have several instructors, and each instructor can teach many courses, hence the Many to Many reln. Here's where I'm stuck. I need to place data in the join file itself because there are instances where I need to record a piece of data for an instructor for a specific course. Like hours of teaching, for example.

How do I show the correct record in a portal such that it matches the other data?

Example:

John Doe teaches Admin 100, Admin 200 and Admin 300. I can set these courses with a value list from the Courses dB, and using a pipeline relationship, show other relevant data like times, rooms, etc. What I need to do is show only John's teaching hours, which may be different from Tony's, who also teaches in Admin 200.

I have a unique ID in the Join file. I just don't know how to copy it over, or link it, from the Join file to the Instructor file (or the course file). Since I don't have two pieces of data to compare, I can't build a relationship.

What am I missing? Suggestions most welcome.

Thanks in advance,

Kirsten Masse

[Vaughan]

You need another file -- let's call it Dates -- that represent each time the course is scheduled.

Courses -> Dates -> CourseInstrJoin -> Instructors

[Kirsten]

Thanks again for responding Vaughn.

Because so many things change for each offering of a course, each course has its own record. I don't think I need a Dates file for this reason.

For one single class, how do I, for example, represent the fact that John teaches 5 hours and Tony teaches 10? I have used a multi-key field in the past, as well as a repeating field for the teaching hours in the Instructor DB. I want to replace this structure with portals.

I hope that is a clearer description of what I'm trying to accomplish.

Thanks,

Kirsten Masse

[RalphL]

You have a many to many relationship between Course and Instructor. You need a join table between them, Course Instructor. The hours each instructor teaches each course are recorded in the join table.

Assuming Course ID is the primary key field in the Course table and Instructor ID is the primary key in the Instructor table, these two fields will be foreign keys in the join table and a concatenation of these fields will be the primary key.

It is now possible to have a portal in the Instructor Table to the join table.

[Kirsten]

Thanks for the feedback, Ralph.

If you reread my message, you'll see I do have a join table, called CourseInstrJoin. Turns out I had an invalid relationship for the instructor fee within the portal. Relationship fixed, fee is picked up along with all the pipelined data from the Course file.

Thanks all for the assistance.

Kirsten Masse