How Do I Purge Notes?

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





    • Related Articles

    • How to Retrieve Tracking Number for an Order from Notes

      Release Date: 12/13/2017 Q - Can you let me know how the tracking number is stored in Elliott so I can use query to retrieve the information? A - Regarding how Elliott stores tracking numbers, please refer to the following Knowledge Base article: ...

    • Elliott V7.1 Release Notes: What's New Since Elliott V7.0

      What’s New Since Elliott V7.0 System Wide Features eContact Manager After V7.1, an eContact can be set up even if you don’t have a contact’s email address. The eContact database has been expanded to include Company Name, Birthday, Created By and ...

    • Elliott V7.4 Release Notes: What's New Since Elliott V7.3

      What’s New Since Elliott V7.3 Use ATP as MRP The ATP (Available to Promise) feature has been in Elliott since V6.7. Initially, ATP only supported parent items and was mainly used by importers to view scheduled sales orders and purchase orders ...

    • Elliott V7.3 Release Notes: What's New Since Elliott V7.2

      What’s New Since Elliott V7.2 Customer Relationship Management (CRM) CRM and beyond The center of Elliott’s Customer Relationship Management is eContact. eContact is an extension to the Customer database, as well as Vendor, Employee, Salesman, Sales ...

    • Feature - Document Notes

      Release Date: 4/28/23 Version: Elliott 8.6 Introduction Elliott users are familiar with the availability of Notes in many places in Elliott. Whenever a particular record is being processed (a customer, a sales order, etc.), pressing F6 will bring up ...