Show related records to found set?

[Grant Cox]

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

[oilman]

Hi Grant

Use a portal to dispaly the records you want.

Lionel

[Grant Cox]

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

[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.

[Ugo DI LUCA]

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