I have built a database that keeps track of students, courses, teachers, and enrollment.

Both the courses database and the teacher database contain a field for which university they belong to. (We track about 15 different universities.)

Within the Courses layout, I have a popup selection button for the teacher of the course that opens a layout with a portal listing all the teachers in the database.

I would like to have the portal containing the teachers just show the teachers that teach at the university selected in the Course layout. Is it possible to constrain the portal field to just show the university specific teachers?

Any idea?

Edited August 19, 200718 yr by Guest

You could use either a global field with the University ID (or name, but really it should be an ID),* or the University ID field of the current course record.

You say "opens a layout with a portal", but you don't tell us who owns that layout (which table occurrence, hence base table it is attached to), nor which table the the portal's relationship is targeted to, nor what the key fields are.

If the portal's layout belongs to Courses, then you could use the University ID to filter the portal. If the portal's layout belongs to Teachers, then you'd set a global field to the University ID of the Course, before leaving (then Commit Record to refresh the portal).

If you don't have a Universities table, then you should; 15 is a lot to be just using their name, kind of bloats the other tables as a foreign key. An acronym would also do, instead of an ID; universities seem to always have those. As long as there is no possibility of a duplicate, ever.

Thanks for your quick response. I actually did what you said before I read your reply. I found the answer by studying the attached file from this post: http://fmforums.com/forum/showpost.php?post/261396/

I agree that I should have a table for universities and I probably will eventually. Just trying to get the database completely functional before school starts.