Splitting History

[PiedPiper]

Our files are getting too large. It takes forever to search and much of it isn't necessary anymore. But sometimes we need to view old data so it must be available. What I would like:

A layout which contains radio button list of years that will display that archived file in a portal - or even take me to that database. Our Promotions need to be split into several files by year. File currently has 930,000 records. How can I display the year selected in a portal if they come from different files? I want each year in a new file and I want to somehow automate it so that it's easy when I split off a year to have it displayed on the layout as a radio selection.

How do you all handle history files? Do you need to manually add the year and manually export then delete the data, etc or is there a way to handle it all by script to 'archive old year'?

Pete

[CobaltSky]

Hi Pete,

Portals are based on relationships and relationships point explicitly to a specific file. So a single portal will not readily display records from more than one file. While there are a few work-arounds and simulation techniques, they present problems of their own.

There are several other alternatives however, the simplest of which is to archive records to a clone of the file and provide an 'archive directory' layout in the main file which allows you to launch (via script) the archive file(s) for a given year. The scripted links could be hand coded, or - if you have access to a plug-in such as LaunchIt or Troi File (or any of the various equivalents) - could be made dynamic based on the system date.

A preference field (to identifiy files as archives) which triggers a few adjustments to the layouts will make it immediately obvious to the users that they are in an archive file rather than the current data - and which year the records in the file they are viewing relate to. The addition of a button which becomes active only in the archive files, which runs a script which closes the file and returns the users to the current file set should make the process of accessing old records relatively painless. A global status field and a bit of work with the 'portal visibility trick' (ref JMO at www.databasepros.com) would do this for you.

[PiedPiper]

Hi Ray, thank you for your ideas here. You said "A preference field (to identifiy files as archives) which triggers a few adjustments to the layouts will make it immediately obvious to the users that they are in an archive file rather than the current data - and which year the records in the file they are viewing relate to." Can you say just a bit more about what you mean here? I think I get the rest of it but adjustments to the layouts? Are you suggesting I use a container background to change the color of archived files, for instance? What would be in the preference file and what would it do?

I think i see how I could display the year being displayed, if I understand right. A calculation with Year (datefield placed anywhere would work, right? I will also check out those other programs you suggested. I would rather not require a programmer to add the year in layout mode to the current file layout (i hope that made sense). i would like a person to click a buton to archive previous year and the file be created AND added on the layout for access.

Otherwise, everything yiou said makes sense to me but when people open an archived db, if it looks the same as the current one, might be trouble - although I can't imagine how - unless they try to create a new record. oh right. that would be a problem because I would never know it. They need to be clear they are in an archived set. Please say more about how I can protect from adding records in archives or make it clear to users using your preferences idea. Thank you.

[CobaltSky]

Hi Pete,

I suppose my suggestions were a little cryptic, but you seem to have interpreted them more or less as intended.

I was indeed suggestion that you set a few well placed calculating containers to modify the appearance of the layouts so that users are reminded of where they are. Background color, colored header bars, etc - whatever is in keeping with the overall design schema you have in place.

How I suggest that you achieve it is to add a global text field called gFileStatus and in the script which imports archived data to a clone, conclude with a step along the lines of:

Set Field ["gFileStatus", " "archive " & Year(RecordDateField)"]

- where RecordDateField is a field which contains the date of each record (assuming you've archived by years.

You can then place this global field (with 'allow entry' turned off and trawnsparent fill attributes) in a prominent place on all your layouts. In the main file, this will have no effect since the glonal field will be blank, but the archive copies will all be clearly labelled, and this will occur automatically as the achive is generated.

Some additional calculating container fields which reference a globa container 'gSwatch' that holds a small swatch of color, with formulae along the lines of:

Case(LeftWords(gFileStatus, 1) = "archive", gSwatch)

could then be placed in the background and given graphic format settings to 'enlarge' and not 'maintain proportions'. These will add to the effect, making the archive copies instantly identifiable (and again, this will be triggered automatically when the status field is set).

Some other options to consider would be to set record level access privileges for editing and deleting the the formula:

not LeftWords(gFileStatus, 1) = "archive"

- and add the following at the start of a 'new record' script (and make sure your files are set up so that users create new records using this script)

If ["LeftWords(gFileStatus, 1) = "archive""]

Show Message ["This is an arhive file. You cannot create new records in this file."]

Exit Script

End If

The above are only suggestions, and you could vary them according to the way your files are set up and the ways they are used, so that the end result is one which will provide easy guidance and support to the users, while giving them ready access to the archive info when they need it.

As for the creation of archive files, I'd suggest that you script that too so that it is also automatic. To have the index of available archives created and updated as a part of this process, I'd suggest that you list the arvhices in a (related) reference file and display them in a portal. The script that creates the an archive file can then also add a record to the reference file, so that the archive (along with its file name and the path to it etc) is listed in the portal index. You could then set up an additional script which runs when a portal row is clicked on and invokes an external function (LaunchIt, Troi File etc) to launch the corresponding archive file.

[PiedPiper]

Hi Ray, I got much of this set up and then accidently locked myself out when i set up the privleges so now I have to do it again on my backup copy. I'll know how really well this time.

thank you for your help. I created manual archives cus I haven't downloaded those programs yet and I couldn't wait to try this but the file shows the years in portal. I used a 1 to join them using auto entry. I hope that's right. A hugh bold 2002 appears at the top and the background is yellow. Well, it was yellow. And it will be when i redo everything. but that bacground covers some of the fields. its not a real problem because when I go in any field they all apear. I think I did something wrong there. Did you say that global color field should be enlaged or reduced? Is that what I have wrong and now it won't reduce to let the fields show through?

And i use the Urban dictionary you told me about. It's too bad that a dictionary doesn't exist to explain the wording I read on all these posts like multi-line, intigers and stuff.

[PiedPiper]

I was going to figure it out with new files so I understand it but my fields won't show still. I checked it over and over and don't know why it won't. Is it okay for you to look? Its set reduce and enlarge to show fields. the button also shows up when it's archive. I hope the key I used is right.

The button works (no close scipt yet) and it disappears but the background covers stuff and stays. It won't display the title at all but I know the title is working because I moved the background and can see it. If your busy I understand. I was further along before because I had the reference years in it. oh the first layout is just my stuff. the second layout is what will represnet my main file layout but it won't look like that. script is there which activates archive and stuff (once I get a plugin).

file didn't stick. may it will now. ok and i also didn't want you to see it ugly but I can't really continue until the background shows what is under and I understand this. I don't want to do it in my real file without understanding it forst. My real main layout has lots of stuff on it.

Pete

Archive.zip

[CobaltSky]

Hi Pete,

Try selecting the field(s) with the background swatches of color and then choosing the 'Send to Back' command from the 'Arrange' menu.

[PiedPiper]

Holy smokes that did it. Thanks for teaching me this Ray. I'm on a roll now.

Pete