Archive Record

[Gabzilla]

In my student management database, I keep two identical tables. One for active records, one for an "archive". The archive is used to semi-permanently store data for a student that has moved, graduated, etc and the informaion needs to be kept, but kept seperately. Fairly frequently one of these students is needed back in the main table.

Over the years I have accomplished this several ways. Currently this is what I am doing:

"Users" can mark a student that needs to be archived by clicking a button that sets a match field. That student(s) then shows up in a portal and an "admin" can click a button to archive the student. What happens when the button is clicked:

1. it goes to that related record in the database

2. omits it

3. shows omitted only

4. exports it to the desktop (set via variable - database runs on FM Server)

4. deletes the record

5. changes to a layout showing archive data

6. imports the data.

To take a student out of the archive, it basically works in reverse.

Now... is there a better way to be doing this?

[Ender]

Greetings Gabzilla.

Now... is there a better way to be doing this?

Yes! Don't move the records!

It is safer and more efficient to keep both sets of records in the same table. Simply mark the records with a Status field.

You would then need to add filters to your Finds, reports, and relationships to only show the Active records (or default to showing the active records, allowing the user to override in some situations).

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.

[Gabzilla]

I agree that keeping them in the same table would be better in many ways.

However, I originally attempted that, and it confused the end users on a regular basis. Many of the folks that use the database are, um, less than capable.

Since moving them to a seperate table (previously a seperate file) I have quite a few less headaches over it.

I have not had many issues with running reports. The only reports that I have needed to create that include archived students I have been able to do with them in the seperate table. Typically it is different types of counts.

[sbg2]

"However, I originally attempted that, and it confused the end users on a regular basis. Many of the folks that use the database are, um, less than capable."

I hate to throw this back on you but this sounds more like the fault of the programmer than the end user. It can sometimes be hard for programmers, myself included, to see the interface from a users perspective. You might want to ask yourself why was the interface confusing to the users.

[Gabzilla]

I agree with it being the programmer's fault.

But... in my own defense, it is a fairly complex database (3600 fields in the main table and nearly 100 layouts) that is used by a wide variety of people in many different organizations who all use it slightly differently and need different things out of it. If I were designing it for one person or one organization, I would definitely set it up to keep the records together because it would be MUCH easier for ME.

I myself use it on a daily basis, so I am also a "user". And I supervise a dozen other people that use it, and get lots of feedback from them about it.

The way I had done it originally, keeping the current and old records in the same file worked well for 95% of the users. It is the 5% of users that it confused that I am concerned about. I strive to design it so that from the wisest to the slowest can get what they need with as little confusion as possible. This makes their work easier and keeps them happy, which ultimately benefits me in a variety of ways.

Were I able to take control of everything they do through buttons/scripts/etc I would be much more confident in keeping the records together. But... It would be nearly impossible for me to do so because everyone wants/needs something a little different. What works for one person would drive the next one crazy.

And... I appreciate the comments!