I have a school database file with 12 tables and 1000+ student records. The main table is Students and the other tables include Parents, Classes, Teachers, Enrollments…etc. What is the best way to structure the file relationships so that only the 350 currently enrolled/active students and their related records show when working in the database. I don’t want to have to archive/export the inactive students to another “Archive” file. I’m thinking that I should probably flag each record in the Students table as “active” or “Inactive”, but not sure how the related records from the other tables will show for only the active students. Any advice will be helpful. I’m using FileMaker 9 Advanced on Windows XP.

If you don't want any of the inactive parent or child records to ever show in the found set, then you'll have to disable certain commands (like show all records) disable find mode, so that you can specifically script queries so to control the found set, and create your own record navigation so you can hide the status area.

I wouldn't make separate active/inactive flags; just a single "Active" field that is formatted as a checkbox, with a value list of 1. In your other files, make a field called let's say "One" where the calc is simply 1.

Now you can add the One-to-Active criteria to your relationships as needed.

Reed has some good points too. It depends what you need, how secure it has to be etc.

Create a separate table, called ActiveStudents, and populate it with only those students that are active. The primary key for ActiveStudents is the same as the primary key for Students. You can relate ActiveStudents to Students based on this key and you can even copy/paste your existing layouts based on Students to a new layout based on ActiveStudents.

This method allows you to keep all of the native FMP functionality you are accustomed to and yet still have a reduced set of student records to those for the current academic year.

Once you have this basic structure down you can extend the model to include other temporal data (such as school enrollment and class registration information) as well.

School Administration, including Admissions and Registration, is a particularly complicated domain to model. If this solution is mission-critical to your school (they often end up that way) then you may want to consult a developer that specializes in the field.

(shameless plug: like us )