One to Many and Lookup Question

[jbullydawg]

Here's the scenario:

Filemaker pro 7

Table - Instructor master (contains the contact info for the instructor)

Table - Courses (contains course infromation/details)

I have the tables related as such - SSN in Instructor master is the unique value....SSN in Courses is the foeign key.....in the Relationships section of the database I have them set up as a one-to-many (One instructor, many courses)

My problem:

I created a Courses layout that is based on the Courses table. I have included elements from the Instructor table at the top because I want the end-user to type in a last name or SSN for the instructor which will pull in more instructor informa tion like phone numbers, etc as well as all of the courses that instructor currently teaches. I'm having a difficult time figuring this out. I have tried look-ups, changing the table the layout is based on, removing SSN as the unique value, etc. Nothing.

Any help or direction is greatly appreciated.

Jason

[Wim Decorte]

if you want to see what courses an instructor gives, you need to be on a layout anchored to the instructor table. You can then do a find on the instructor you want and a portal to the courses table will show you all his courses.

if you want to assign a course to an instructor, you need to be on a courses layout and have the instructor SSN be a value list of instructor SSN values. As you pick one the SSN value for the instructor will be in the "instructor SSN" field in courses and it will trigger a relationship between the two tables.

If you are on the courses layout, looking at the course details (included what instructor) and you want to see right there what other courses the instructor gives you'll need a self join relationschip (from courses to courses) based on the instructor SSN field. With that, add a portal for that self join on the courses layout.

[jbullydawg]

Your first thought is what I want to do: "if you want to see what courses an instructor gives, you need to be on a layout anchored to the instructor table. You can then do a find on the instructor you want and a portal to the courses table will show you all his courses."

However, is there a way to accomplish without using a portal? Portals are clumsy and, well, ugly. Besides, some of the information in the Courses table is a bit long for a portal.