deleting old data inc. trans and master data

[DannyC]

Two questions.
I have some Jiwa databases which have been in use for well over 10 years., some running to 16 years.

I want to retain only data relevant to the last 7 years, so anything older that 1/7/2007 I want to delete out of the database altogether so if a debtor or creditor or inventory item has not had any transactions since 1/7/2007 then they can be deleted along with all the associated transactions.

With several new keys created on tables for referential integrity, it is difficult to develop a script to remove transactions from (e.g. for debtors) DB_AdjustmentLines, DB_Adjustments, DB_TransAlloc, DB_Trans, DB_Main.

How would we be go about this?

Would it be easier to do this task in Jiwa 6 prior to upgrading?

Cheers

Danny

[Mike.Sheen]

Hi Danny,

Removing transactions such as debtor, creditor and sales orders should be done via the archiving functionality - it will preserve integrity for you.

Manually deleting transactions or even master records will cause, as you discovered, referential integrity conflicts. Even if you temporarily remove those constraints and then remove the data you want, you'll have batches which totals no longer match the journal set posted - which will cause reconciliation issues.

You could create an archive master item - for example a debtor named "archived" and point all the records of selected debtors to that instead - that way you eliminate the debtor master records. Having said that, you can always just mark the debtor as trading status inactive and then it won't clutter up the users searches.

The cleanest way is to create a new database, and then import only the data you want - debtors, creditors, inventory, transactions, etc. It's not a small job.

Performing deletions in v6 then upgrading will probably cause the upgrade to fail when it creates the referential constraint - so I don't suggest doing that.

Mike