Archiving data

[ED02]

Is there a way to Archive old data our macola has data back from 96. We only reference back to 03 so is there a way to do this so that our data file will only contain data no older then 03?

 

[dgillz]

If you are talking about purging data so that Macola reports do not take so long, you can purge most files from the reports menu. You will see a "purge file" checkbox at the bottom of many of the reports screens.

So if you put in a start date of 01/01/1990 and an end date of 12/31/2002, and click the "purge file" checkbox, those records will be deleted from the file/table.

In terms of archiving the data. Burn a CD and throw it in a safe. If needed, you can always retrieve it.

Also, you did not say if you are on the Pervasive or MS SQL platform. One of the characteristics of a Pervasive file is that even if you purge data from it, it will remain the same size until it is rebuilt. So if you are looking to save disk space as a part of this exercise, then be advised you will need to rebuild the .btr files as well.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ED02]

We are running 7.5.300spb and on SQL server. I know that i can backup the entire data. What i need is a way to just purge system of all data up to 03 maybe a way to save the old data into another database so it can be accessed. Also i dont have purge on report screens ... maybe i am looking in wrong pace?

 

[keain]

You can't purge every report, only some. Take a look at the Open Item by History Report. If you check the 'Print Distributions' check box, the 'Purge File' check box becomes active. After you purge, you may want to look into DBCC Shrinkfile to reclaim some space. They syntax is:
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)

If you want to move data from one table to another, you'll have to look into DTS. It can be used to move data from tables into a new db that's created on the fly. It would just be simpler to back up the database right before you do your purge. It's quicker to set up and there's less room for error.

 

[keain]

Allow me to rephrase. Look at an Open Item History Report, not an Open Item by History Report. ;-)

 

[dgillz]

What modules do you have and I will provide a list of the reports where there is a "purge file" option.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ED02]

Thanks for the help we got just the basics : GL, AP and AR.

 

[dgillz]

The reports that allow you to purge are as follows with the table name in parentheses.

AP Open Item History Report By Doc Number (APOPNHST_SQL)
AP Open Item History Report By Vendor (APOPNHST_SQL)
AP Open Item History Report By Journal (APOPNHST_SQL)
AP Open Item History Report Audit Trail (APOPNHST_SQL)
AP Vendor Audit Trail Report (APVENADT_SQL)
AP Distribution to GL Report (APDISFIL_SQL)
* AP Purge Open Item File (APOPNFIL_SQL)
AR Commission Due Based on Invoices (ARCOMDUE_SQL)
AR Commission Due Based on Cash Receipts (ARCOMDUE_SQL)
AR Distribution to GL (ARDISFIL_SQL)
AR Open Item History by Document (AROPNHST_SQL)
AR Open Item History by Customer (AROPNHST_SQL)
AR Open Item History by Journal (AROPNHST_SQL)
AR Open Item History Audit Trail (AROPNHST_SQL)
AR Service Invoice History (ARSRVHST_SQL)
AR Open Item History by (AROPNHST_SQL)
AR Customer Audit Trail (ARCUSADT_SQL)
* AR Purge Open Item File (AROPNFIL_SQL)
GL Transaction History by Account (GLTRXHST_SQL)
GL Transaction History by Journal Source (GLTRXHST_SQL)
GL Transaction History by Journal Number (GLTRXHST_SQL)
SY Journal History Report (SYJNLHST_SQL)
SY System Account Audit Trail (SYACTADT_SQL)
SY System Tax Histort (SYTAXHST_SQL)

The items above with and asterisk * are actually processed instead of reports.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[bruggles]

This is an area\feature Macola needs to improve on, there should be a built in utility that does below, many others packages this feature built in.

Burning to CD is not a prudent thing to archive your data as once you install a Macola update it changes the tables and the program, the newly updated program will make it unusable.
In order to read the archive CD you would be forced to resetting your server and workstation to the version of Macola that was installed when the CD was created.

I reccomend that you setup a new company in Macola and Copy Company 1 to the newly created company. Unfortunately Macola should allow you to name a company more than 3 numbers this would allow you to name it something meaningful such as "Archive February 1st 1997 - June 30 2003" instead you can name it 002 and keep a note of what date range this Company contains. Next use SQL Enterprise Manager on your SQL server to restore Company 001 to the newly created company 002. Once this is done you now have a copy of Company 001. Next login to Company 001 and Purge records to the date you desire.

Now when you do an update of Macola, Company 2 data is also updated to reflect changes of the new version of the program and tables. This gives you an usable archive that you can use and access.

Hope someone in Macola's Product Development is thinking to add this feature in future release.