I have two files, Schools and Students, joined by a one-to-many relationship on a field SchoolID. I am performing a Find on the students on some given criteria, and now I want to list all the schools with these found students.

Go To Related Record seems close, except I need to show all schools that have any of the found students, not just the current school for a single student.

One way I can think of is to have a global repeating field for found_school_ids in the Schools db, and then do a Find for any schools whose SchoolID is in that repeating field... However, I can't think how to actually perform this Find.

Regards,

Grant Cox

FileMaker Version: 6

Platform: Windows XP

Hi Grant

Use a portal to dispaly the records you want.

Lionel

Thanks for the reply oilman.

Unfortunately, each of these school records needs to show a bunch of information, which includes a portal showing all the student details for that school. So what I want is to be able to show a selection of schools in whatever Layout is appropriate; just like a Found Set (from a normal Find) would do.

The problem is that I can't seem to construct a Find that will get any school whose ID appears in a list of IDs. It seems so simple (and would be in any other database), I just can't figure out how to do it in FileMaker.

Regards,

Grant Cox

Well, I've got it working now by "simply" running the find in the Students to get the right ones, and for each student executing a find in Schools to get that school, each time adding to the existing Found Set in the Schools.

With just a dozen test students that works fine, but we'll see how it holds up when it has a thousand or so students. Hopefully only a few dozen would be indentified in each search, so it'd be only a few dozen School searches.

Grant Cox said:

One way I can think of is to have a global repeating field for found_school_ids in the Schools db

No, use a standard global text field and isolate an ID field in its own layout.

Then, when you're done with your find, move to this layout and execute a CopyAllRecords.

Then, paste to the global.

This global would be used as the left key of a relationship

FileMaker Version: 6

Platform: Mac OS 9