Exporting to An Archive

[jenn804]

Hi all-

I have built a relational database of about 20 tables, and am looking to figure out a way to archive records that represent "old jobs"; that is, completed, paid in full, and not to be seen again (unless i decide i need to re-import them).

Essentially, I would like to be able to select a client whom i want to remove from my database. The script will then locate every record related to that client (every job, every order made to outside vendors, every invoice element, every invoice, etc), export that data into another file, then delete it from my current database.

I realize this is a broad topic and there are probably several ways to approach this. If anyone could point me in the right direction it would be greatly appreciated.

Thanks!

Jennifer

[Genx]

It would essentially be one giant looping find script where you could add all data into one field and then export that field at the end. i.e.

GoToRelatedRecords[RelatedJobs; Whatever Layout]

Loop

SetVariable[$Temp ; $Temp & ¶ & RelatedJobs::Field1]

SetVariable[$Temp ; $Temp & ¶ & RelatedJobs::Field2]

... etc.

GoToRecord[Next ; Exit After Last]

GotoLayout[ClientLayout]

GotoRelatedRecords[RelatedInvoices ; Whatever Layout2]

loop

setvariable

bla bla bla

finish the loop

do as manny times as required

SetField[someTable::SomeGlobal ; $Temp]

Exportfield[someTable::SomeGlobal]

and so on and so forth.

Depending on whether you want to import this again or just want to view the txt files later, you might format the output differently.

[Fenton]

I would think more of just creating a clone of your working database, all the relevant tables. Then creating a master script to go through all the tables, finding those older client records, and importing them, 1 table at a time, into the archive table. Then deleting the records in the master file.

After finding older clients (not sure exactly what criteria you'd use), you could use the new Go To Related Record option matching [x] All records in found set. That would get you the found set for all the older clients in the other tables.

If your relationships are set up correctly to [x] Delete matching records in related table (the child table), then you could just delete the clients at the end of the script, and all their related records would be gone also (it can take a while).

Obviously you want to experiment on test files until it works flawlessly, and backup immediately before running, every time. It's a little dangerous.

Another question is whether you really need to do this. Are the old clients never coming back? Are there really so many? etc.. But if they're never coming back and there are so many, it may be worth it. Lean and mean is good.

[Genx]

Alternatively, do as Fenton suggested, but rather than making a clone, export the records to be archived from your primary file and trigger a script in the secondary fm file to import those files, then proceed to delete... or export delete, export delete, export delete, import. Up to you really.

~Genx