Searching for records from multiple files

[Jonathan Z]

Hello,

I am very new to FileMaker and to databasing in general. I have started a new job in a school where we keep student discipline records in a FileMaker database. Each new school year we clone a new file. We currently have 7 years of files. When we get a request to check if a student has a discipline history we have to open each file, go into Find mode and search for the student's last name.

I've just begin playing with FileMaker and have made a much more visually appealing layout for us to work with next year, and now my very impressed supervisor would like me to find a way to search those past years' files from within the current year's file.

I started by trying to set up a relationship to just one of the past year's files, and then making some fields on a new layout from that relationship. However, that only shows the newest record from related file, and only if the student is already in the current file.

Can anyone give me some advice on how to proceed, and if this sort of thing is possible?

Thank you,

Jonathan

[Ender]

Welcome Jonathan!

I've found it's much easier to manage a solution involving multiple years' data by simply keeping it all in one set of files. You would need to add a School Year field to each file to know which records belong to which years, and then filter any Finds and relationships by that School Year, but this effort is worth it. It is then an easy matter to create records for the new enrollment year, without interfereing with the existing ones.

When you start thinking about doing this, you should be able to find some entities (files) that need not contain 'historical' data for each year, but instead contain only one record for each Student or each Teacher. With those as the anchor tables, you can then add historical join tables that contain the information that's specific to each School Year, or each Enrollment.

[LelandLong]

I agree. Either combine all into 1 file, or at the least, have the current year in one file and all archived years in another single file.

At the end of the year, export from the current file and import into the archive file, then "Replace" the Archives "Year" field with the appropriate year for all the imported records. Once all looks intact then delete all records from the current file to start afresh.

[Ender]

Hey Leland,

I'm afraid I'm not a fan of the whole "Archive" file idea. I'm guessing that it derives from what some businesses do with real files (putting the old files in the back file cabinet to make room for the new ones up front). But this separation shouldn't be necessary when you're dealing with data unless you generate such a huge amount of data each year, that it affects performance. If protecting the past years' data is a concern, this can be done by adding record level access privileges (FM5.5 or later), or by carefully scripting the user interface so that those records are not available for users to edit.

As I told Gabzilla just a couple days ago, there are several reasons why it's better to keep everything together:

There are a couple good reasons for doing it this way instead of using a separate table:

1. Maintaining a duplicate structure is difficult to do. You have to remember to add every field change twice.

2. During the Export/Import process, an error could cause the deletion of a record before the new one has been created, or almost as bad (and harder to error trap): if the Import order changes, but you don't update the script step, you can end up with records moved to the Archive with half of their data in the wrong fields.

3. Combined reports are not possible if the data is in separate tables. If everything is together, you can quickly run a report for all Students that were enrolled during the year, or whatever.

From: http://www.fmforums.com/forum/showtopic.php?tid/179144/

[LelandLong]

I mostly agree with you. That's why I said "Either combine all into 1 file, or at the least, ..."

I used this 2 file system years ago (FMP3 & earlier) with a Real Estate office. When we had agents searching for addresses in certain area's or tied to certain Agent names we didn't want old, closed transactions tainting the resulting set. The boss didn't want agents to have to learn to be better database searchers, he wanted it to be easy for a 6 year old to do :

We needed the Archives only rarely, so a seperate file seemed best and [don't know truely how it affected performance] logically seemed to add performance boosts to everything we did to trim the file size; but consider the hardware we used at the time (late eighties)

Since Jonathan stated that he was dealing with students in a school I thought if he/supervisor was hesitant to change to a 1-file system then they could at least move to a 2-file system for the same reason's I did way back when; would solve the 7 file search headache.

I guess my question to you is, do you automatically say 1 file only, or does the application justify maybe 2 or more file systems?

Students come and go, we had agents come and go, newest, freshest data in the everyday file, archives filed away in the corner.

[Ender]

Hee hee,

I guess my question to you is, do you automatically say 1 file only, or does the application justify maybe 2 or more file systems?

Yeah, I pretty much do say to stick with a one-file-per-entity solution. For me, this is just a part of normalization.

However, performance of large files can degrade in FM6 and below, so depending on how many records are involved, your 2-file-per-entity solution may indeed be better in Jonathan's case. Case in point, every year we generate some 250,000 attendance records. In FM5/6 we would purge the previous year's records to get ready for the new year (otherwise operations in that file would slow down significantly). But now with FM7/8, we've noticed no performance issues with having hundreds of thousands of records in the file, and it's more convenient to keep multiple years' records together so that reports can be run on any year.