How Do I Purge Notes?
Release Date: 3/1/2024
Q: The Note file is very large. How do I purge old records?
A: Purging can be a double-edged sword, so you should be very careful when purging. The first step we recommend is to backup your data.
Backup Your Data
It is difficult to reverse purged notes unless you have a good backup. We recommend making a backup copy of the data before running the purge. The name of the notes table is NOTE.BTR and the physical file resides in your <ElliottRoot>\DATA folder. You can use the following command to make a backup:
COPY NOTE.BTR NOTE.OTR
See sample screen below:
You can use any method you feel comfortable to backup your data.
Purge Notes Utility
We have a utility that will purge Notes that can be found in System Utilities, Notes Processing, Notes, Utilities, and Purge Notes. Select the option and it will ask you to confirm that you want to purge the Note file.
Respond with "Y" and the parameter screen will appear.
File Name Range: Since a Note can be attached to different record types, we provide a way to purge each record type through a range of files. Each file corresponds to a specific record type.
ARCUSFIL - Customer record
APVENFIL - Vendor record
IMITMFIL - Item record
CPORDHDR - COP Order Header record
POORDHDR - Purchase Order Header record
BMORDFIL - Bill Of Material Order record
SFORDFIL - Shop Floor Order record
EMPFILE - Employee record
ARSLMFIL - AR Salesman record
CPSHPFIL - COP Ship-To record
CPINVHDR - COP Invoice Header record
IMLSHST - IM Lot/Serial History record
COMMENT - Comment record
SYUSRFIL - Elliott User record
You can press Enter to select all files, but we do not recommend it. By being more precise with the parameters you can determine what notes are less valuable to you.
Reference No Range: The Reference No fields refer to the key data for each record type. For the customer record, the reference would be the customer number. For the item record, the reference would be the item number. For the COP Ship-To record, the reference will be the customer number and the ship-to number. You can find this information in the bottom right of each Note.
Folder Range: You can specify a Folder range, although the Folder is seldom used. You can press Enter on the Starting Folder field to skip the selection.
Create Date Range: You can specify a Create Date range. This is very helpful if you are wanting to purge all Notes prior to a certain date. You can use a starting date of 01/01/51 (01/01/1951), which is the earliest date in Elliott 8.6. On the other hand, a date of 12/31/50 refers to the year 2050 and is not suitable as a starting date. The ending date becomes your cutoff date and all notes prior to this date that meet the criteria will be purged.
Note Type Range: You can specify a Note Type range. This is helpful if you are only wanting to purge shipment notes or similar.
Created By Range: You can specify a Created By range. Starship and other shipping software create tracking notes with the Created By user hardcoded as "STARSHIP", so you can select "STARSHIP" as a range to purge only shipment tracking notes. This option can also be helpful to purge notes created by a person who is no longer with the company.
What Notes to Purge?
You should only purge historical notes that are not as valuable as time goes by. The biggest question is how do you identify those notes to purge. Normally, you can use the following parameters to narrow down the notes to purge:
- Note File Name:
- Note Created Date:
- Note Type
- Note Created By:
The Majority Rules
You should focus on purging notes that take up the most disk space. You can find out which file has the most notes by going to PSQL Control Center and using the following SQL statement:
SELECT NOTE_FILE_NAME, COUNT(*) FROM NOTES GROUP BY NOTE_FILE_NAME;
See sample screen below:
You can see in the above example the file name CPINVHDR (Invoice Header) has the largest number of notes. You can use the following SQL statement to find out what kind of notes are related to file name CPINVHDR:
SELECT * FROM NOTES WHERE NOTE_FILE_NAME = 'CPINVHDR'
If the result, for example, shows most of the notes in CPINVHDR are shipping manifest tracking notes, then you might decide to purge all tracking notes more than one year old, You can use the note purge utility with the following parameters:
All shipment manifest tracking notes will have the Created By user as "STARSHIP".
It is your responsibility to identify the precise parameters so you only purge notes you no longer need. Purging can be a double-edged sword. If you use the wrong parameters, the result can be devastating. Always backup your data before purging.
MAD