Find turning up unrelated records

[Tony Morosco]

I am encountering some puzzling behavior when performing a find in a portal.

To back up and give the groundwork of the situation, I have a database that is a fairly typical students/classes/enrollments database. The database tracks student's enrollments in classes, among other things.

It is a multi-year database, and thus has enrollment information from the past as well as the present. To facilitate working with current years enrollments, there are several relationships between tables so that I can isolate this year's enrollments.

Specifically for the current enrollments table occurrence, the Students table is related to Enrollments by the relationship

Students::Student_ID = Current_Enrollments::Student_ID 

AND 

Students::Current_Year = Current_Enrollments::Class_Year.

Thus, a portal on the Student's details screen (Student Table) using Current Enrollments displays only the classes of the current year. (Another portal to All_Enrollments table occurrence on another tab displays all enrollments). This works flawlessly.

The current problem is that sometimes the same classes are offered year to year, so they have the same title, but different Class_ID, Instructors, Start_Date, Class Fee, etc.

Normally when we search from the Student Details screen (Students table) in the Current Enrollments portal for a class title, the results only include students who are enrolled in a current class.

Well we now have an instance where if we search for Introduction to Bryophytes (FYI Bryophytes are mosses, and we're offered it in 2008 and 2010), the results include students in this year's class, as well as students from the same class in 2008. The Class table records have identical Class titles, but different other information, including Class Year.

When paging through the results, students that are enrolled in this year's class have Current Enrollments portal details as expected.

For those students who were enrolled in the 2008 class, there are no listings in the Current Classes portal at all! (also expected, but why did FileMaker find a resulting match??)

So how is it that a search is producing results that are unrelated?? It sure has me puzzled. I just rechecked the search field, and it is using the correct table occurrences in the portal.

Any suggestions on where to look would be appreciated. I can post a relationship diagram or other info as needed. I've temp fixed it by abbreviating the old class title so it isn't found in a longer name search. Ack! bad workaround!

Thanks!

Tony

[IdealData]

Your search is not specific enough. You did not include the year for the class in the search, consequently FM returned all the STUDENTS who have taken the class in any year.