Purging records from Peachtree

by Oct 10, 2011Technical, Tips & Tricks

Purging is the process of removing inactive Peachtree “list” data (i.e., customers, vendors, employees, items, jobs, general ledger accounts, etc.) and closed “transaction” data (i.e., quotes, invoices, purchases, payments, etc.) from the Peachtree database.

Since the key words are inactive and closed, prior to purging it is very important to make all list data that is to be removed (customers, vendors, employees, chart of accounts, inventory items, etc.) “inactive”, and all transaction data that is to be removed “closed” prior to running a purge. Contrary to what many Peachtree users believe, running a year-end close does not remove any data from the system; it merely “tags” transactions as belonging to a “closed” fiscal year, and also puts them into “read only” mode, making further editing impossible.

This can be problematic in that records that are not “closed” prior to running the year end close are stuck in the database, unable to be purged because they are not “closed” and unable to be closed because they are not in an open fiscal year. In this unfortunate “Catch-22” the best recourse is frequently an outside data repair service that can use developer tools to remove the “stuck” records.

Once you have determined the set of records to be purged, we recommend that you define a “purge sequencing strategy” to determine discrete, limited sets of records to purge over multiple passes through the Peachtree database. This is particularly important if the number of journal row records in your database exceeds 250,000 (you can check this within Peachtree by going to Help | Customer Support and Service | File Statistics, and scrolling down in the “Number of Records” column to check the size of the “Jrnl Row” table).

Quantum users can efficiently process a considerably larger volume of journal row records, but even for Quantum users we recommend purging when the volume of records exceeds 750.000.

A typical “purge sequencing strategy” would first seek to identify any transaction records older than 12 to 24 months prior to the first day of the earliest open fiscal period. Thus if your earliest open fiscal period were January 2010, you would be looking for records older than 1/1/2009 (or 1/1/2008 if you want to keep more records available).

To determine the oldest records in your system, open the transaction screen into which you enter the largest number of records (sales offers, vendor invoices, purchase orders, etc.), and click on the “List” button on the toolbar. Change the drop-down filter so that it shows “All Transactions” and note the date of the earliest record displayed in the list. That is your oldest transaction.

Do this same date lookup for each transaction type for which you want to perform a purge. With this information, it should be possible to establish a “phased” approach to transaction purges. If your earliest transaction date is April of 1999 (perhaps when you initially installed Peachtree) you might want to set three (or four, depending on transaction volume) distinct date ranges for the purge within each transaction type. Thus your sequencing might look like this:

Phase # Transaction Type Date range Completed

1

Sales Orders Earliest – 12/31/2003  

2

Sales Orders 1/1/2004 – 12/31/2006  

3

Sales Orders 1/1/2007 – 12/31/2008  

4

Invoices Earliest – 12/31/2003  

 

Invoices 1/1/2004 – 12/31/2006  

Etc.

Etc.

   

Once you have removed all of the transactions that are no longer needed in the system, one or more additional iterations of the purge routine can be made, to remove so-called “list records” (customers, vendors, etc.) that have been tagged as “inactive”. If there are no transactions attached to these inactive records, they will be quickly and easily removed from the database.

Two additional items to consider prior to initiating a purge:

  1. Consider creating an “archive database” prior to purging, so that all of the data you are about to remove from the system can still be easily accessed, in read-only mode, from the archive database;
  2. After each successful step in the overall purge sequence, make a Peachtree backup of the database. Thus if your purge fails for some reason after Step #5, you will not have to start all over again from step #1. Thus seems blatantly obvious, and yet is all too often ignored. Peachtree backups are fast and easy to perform, and once the full purge is completed all of those interim backup files can be safely deleted.

We have performed hundreds of purges for clients, and when followed carefully these steps will never fail to produce the desired results (sometimes with the aid of a few additional tricks we have learned over the years!). If you feel squeamish about running a purge yourself, give us a call at 610-941-2116 to discuss your database maintenance needs and request a fixed price quote.

0 Comments