I'm starting to build a new Enrollment system in one FM7 file (as an exercise at this point), and I love how easy it is to view related data from several entities away. But now that I can do this, I seem to be stuck on how to create filtered portals to thin this data down.

The problem is that there is no direct relationship between the file I have the portal in and the data I'm viewing through the portal, it's joined through a series of relationships.

Specifically, what I'm trying to do is find a way to filter the related child Enrollments by School Year (from the Staff layout). This way for any given staff member, I can just look at the children enrolled in a selected School Year. I have attached my sample Enrollment file. You should be able to figure out what's going on from the Staff layout and the Table Occurrence graph.

Is it something simple that I'm not seeing? Am I missing a table?

It seems like filters on portals through multiple relations like this will be a handy thing to use in other places as well, so I hope to get this figured out.

Thanks for your efforts.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

One (lame) thing I tried was putting a global field "gYearFilter" in Class, then setting it to the value to filter the school by, and creating a relationship for it to Enrollment's year. This works, but doesn't refresh itself when the value is changed, I had to move to the next record and back to get it to update properly.

Also, there isn't an easy way to specify "no filter" that way. It also smells an awful lot like pipelining data, I hope someone else has a better idea.

I added a button that refreshes the portal by doing set fields on all 4 key fields. (including the global I added for filtering) I also added a calc field in the enrollment table that creates a multi-key. The "All" button sets calls the same refresh script, but with a parameter that causes the global to be set to "All" and thus unfilters the portal, showing all records.

Yaaay, my first script parameter!

FileMaker Version: Dev 7

Platform: Mac OS X Panther

Enrollment.fp7.zip

Thanks guys, that helps. Though it would be nice if I didn't have to use a refresh to get the portal to update. Maybe if I have this portal on a layout with a different context it will work (I usually don't have multiple portals on a layout anyways.)

Looks like there are a number of things in your relationships that need changing. As shown, classes go on forever and students never change. The classes need to have dates associated with them. There's more. But the graph needs substantial rework, it seems to me, based on a quick look.

BruceR said:

As shown, classes go on forever and students never change.

Thanks Bruce. In my school, a "class" doesn't change much. It is always the same room, same days, same sessions. I'm thinking that I might like to have class records for each school year, in which case I would add School Year to the Class table, but in our current system we don't, and it hasn't been needed.

The teachers for a class change sometimes, and that's where the Room Assignment table comes in. HR assigns staff to a room through the Room Assignment table, and that changes which teachers are assigned to that room's classes.

Students can have many Enrollment records. I didn't get around to putting enrollment dates and status fields in the enrollment file, but the idea is a student would have an enrollment record for each school year and for each different classroom attended.

Thanks for checking it out. If you still see an issue with the graph, let me know.

FileMaker Version: Dev 7

Platform: Mac OS X Panther