Archiving old records while maintaining their availibility to users

[Joe_Schmo]

What's the best way to go about archiving records that still allows the user to view them when needed? I don't need the archived records available in the DB for features like performing finds or browsing to previous records, but there does need to be a way to view them at some point. I was thinking of just creating a copy of the DB and deleting all older records in the current version. Then the copy would be the archive and have all records, but the current version of the DB would only have the last few years worth of records.

Is there a better way to limit the number of records in the current DB, while still maintaining access to the archive on occasion? The archived records don't have to be included on finds or sorts but must be available somehow.

[LaRetta]

The archived records don't have to be included on finds or sorts but must be available somehow.

Hi Joe,

Predictions based upon data modelling is key to business success just like with civilizations ... know history or be doomed to repeat it; it is true in business as well. Patterns produced from data is very valuable and I would think long and hard before archiving any data. If structured properly, most businesses can go several years before even considering the need to cut off part of their hind-sight.

If you do this ... it will be difficult to pull it back if needed (I can provide reasons why it will be difficult if you wish). If you do this, reporting will be more complex. I am not saying you should not do it, but I am saying that I doubt you should do it so be sure it is going to solve the reason you are wanting to do it ... poor sentence but it fits here, LOL.

May I ask what is making you consider this idea? If your solution is slow, I would instead address those reasons directly - it is rarely the volume of data which causes dawg-down but rather data structure itself coupled with network speed and server box configuration.

[Joe_Schmo]

That's good advice. I definitely see your point. Speed is the main reason I am considering archiving the majority of the records, and as you pointed out, there's probably a real reason to the sluggish operation of the DB that could be addressed.

The other reason is that there were some changes to the DB a few years ago before I started working on it that changed/added/deleted some fields. It makes it very difficult to account for all cases when scripting and working with relations. Removing THOSE older records will make future DB modifications a lot easier. But that's only the first few years worth, and I was going to remove all but the last few years (there are about 8 total).

As far as addressing the speed issue directly, rather than deleting records to compensate, that's probably going to require a lengthy explanation and another thread. Basically it comes down to sorting all records on opening of the DB, and the records being displayed in a portal rather than list view, which is also sorted. There may be a way to keep the records displayed properly without the constant sorting, but for now, with the older records that are missing some of the data, it's the only way.

[LaRetta]

As far as addressing the speed issue directly, rather than deleting records to compensate, that's probably going to require a lengthy explanation and another thread.

I suggest instead that you find a Developer that you know is very good and hire them to evaluate your system and make recommendations. And then take their advice. And no, I'm not saying that to get business; I am not interested. But you simply CANNOT know how to structure your system like an expert knows and it WILL NOT run fast without taking advantage of all capabilities within FileMaker. If you are designing such a large, complex system and the business is trusting someone to handle their data that is not a professional Developer, surely the data is important enough to invest in a review-and-recommendation by a professional! Print and show them http://fmforums.com/...s-us-seriously/

Basically it comes down to sorting all records on opening of the DB, and the records being displayed in a portal rather than list view, which is also sorted. There may be a way to keep the records displayed properly without the constant sorting, but for now, with the older records that are missing some of the data, it's the only way.

Drop the constant sorting and sorting upon open. Why are you doing that? As said, you need to take the advice of professionals and follow it. I suggest that you get new shoes instead of cutting off your toes because the shoes do not fit. We can only help so much through posts even though we surely do care about helping ...

ADDED: Sentences in blue ...

[Joe_Schmo]

Well, I know my employer is not going to pay to hire a developer just to speed up the loading of the DB when it works and does everything we need it to do. Otherwise I wouldn't be on here asking. If this DB were vital for our operations and was not functioning properly, then it would be a different story.

Anyway, thanks for the advise, and the link. That pretty much sums it up. Although in my case, we do have someone who knows FM well enough to be doing this instead of me. But they are also the resident expert in 15 other things and have too much on their plate to spend as much time developing as I have been. I'm learning it on my own and taking over the DB admin of a few of our more simple DBs, as well as building one of my own. I volunteered out of interest in FM and building DBs in general as opposed to being the low paid guy tasked with it like in the thread you linked to.