Data Cleanup
This topic discusses identifying and removing unwanted items, accounts, jobs, and vendors from either the database or selection listings.
Why do data cleanup?
Items, accounts, jobs, and vendors make up very little of your overall data, so data cleanup on these types of records isn’t necessary for disk or database usage reasons. Cleanup for these types of records is really only necessary if the abundance of unwanted data is causing operational issues. Side effects of too much data can be very long selection listings or reports, for example. In other words, this is only necessary if the number of records is causing some type of problem for your users.
Remove or Disable?
There are two (2) options for solving operational issues caused by too many records: removing the data or disabling the data. The second choice, disabling, is preferable for many reasons. Disabling doesn’t remove any history for the records and it’s easier to do (can be done globally or with support assistance).
Removing data involves either deleting or merging data. This is much slower and a manual process. In addition, only some items will be eligible for deletion where others may require merging (due to activity/history). Removing data also removes or changes other data regarding the records. This is not recoverable except by retaining and restoring from a database backup prior to the removal of the data.
Identifying Records with Little or No Activity
There are a few ways to find records which you may want to disable or remove including:
•Custom or Built-in Reports
•SQL Queries (SQL Management Studio)
Using Last Activity Dates
Items, accounts, jobs, and vendors maintain information such as “Last Sale Date” or “Last Purchase Date,” for example. These dates can be useful in identifying the last time an item or account was involved in a sale, or a vendor was used for a purchase.
Using Status Codes
Accounts, jobs, and vendors have a “status” associated with them. The “active” and “inactive” statuses are set automatically based on “inactivity” days parameters for customers and vendors. Automated updates to “active” records are done on a monthly basis only. This compares the inactivity days to the account, job, or vendor record’s last activity (sale, purchase, etc.) and changes the “active” status to “inactive” if no activity has occurred within the time frame determined by the parameters. “Active” status can be automatically reset for currently “inactive” customers automatically when activity, such as a sale, occurs. All other status codes (disabled, closed, etc.) are never automatically updated.
Inventory items have no status per se, but do have individual settings for “disabled” and “discontinued.” Discontinued doesn’t prevent selection but can produce warnings or errors. In addition, discontinued items are not included when processing automated purchase order builds.
For all of these record types, the status “disabled” prevents the record from appearing in most selection listings and many reports.
Other Criteria to Consider
Totals are also maintained with item, account, job, and vendor records and can also be used as factors.
In addition, you may want to also consider factors such as sales totals, usage, account balances, last payment, etc.
It’s important to consider the alternate use of the records as well…
•A vendor may be a “pay-to” or “installed sale” or other type that is never linked with purchases of inventory.
•Items may be used for manufacturing or other reasons, but never sold directly.
Disabling Data
Records can be disabled by updating their status manually using Maintenance forms found on the Database menus, by using a Global Modify utility, or by contacting support and requesting that we run a SQL update based on criteria you select. Disabling is usually the best choice when a record has prior activity and you want to prevent its use in the future.
Deleting Data
Deleting permanently removes the record but does not typically remove or change other data such as documents. You may not always be allowed to delete a record. Below are some of the conditions that must be met before a record can be deleted. Deleting is a good choice when a record has no activity.
Item Records
To Delete an Item, the following must be true for ALL branches where the item exists:
✓Current Year Cost must be Zero
✓Current Year Sales must be Zero
✓Current Year Usage must be Zero
✓Previous Year Cost must be Zero
✓Previous Year Sales must be Zero
✓Previous Year Usage must be Zero
✓Current On-hand must be Zero
-- and --
✓No Open Ticket Documents Containing the Item
✓No Open Customer Order or Online Order Documents Containing the Item (and Zero Remaining on-order)*
✓No Unsold Quote or Online Quote Documents Containing the Item
✓No Transfer (Not Received Only) Documents Containing the Item
✓No Unapplied Price Update Documents Containing the Item
✓No Unapplied Sale Price Documents Containing the Item
✓No Purchase Order Documents Containing the Item unless the Item is Closed*
✓No Open Item Returns
*This effectively stops the deletion of open Direct Ship orders since the Direct Ship purchase order cannot be closed or voided (deleted) without also removing or completing the Direct Ship transaction.
Restrictions are in place in order to maintain the integrity of the database and to prevent issues accessing current data by users. Deleting an item removes the item from the inventory tables (InventoryStore and InventoryCommon), but doesn't remove the item from most other tables that may have references to the item. Documents should not be affected unless the document references data from the inventory tables directly. It is more likely for reports to be affected since they use current data and do tend to reference inventory data directly. Deleting items shouldn't cause errors; however, in the unlikely event that it does, you can temporarily re-add an item.
Accounts and Jobs
To delete an account, there can be no activity. This includes any non-zero balances as well as any outstanding finance charges and unapplied credits. In addition, the account cannot be associated with any of the following documents: invoices (except "canceled" transaction), customer orders, open credits, receivables posting, and receivables adjustments. In the event that an account cannot be deleted for any of these reasons, you can change the status of the account to "closed." Closing an account does not remove any balances or activity.
To remove an individual job, there can be no invoices (except canceled types), orders, or quotes associated with the job. The job cannot be linked with any receivables adjustments, payments, or posting details. If the job maintains balances (meaning the account is assigned a billing type of “job”), the balances must all be zero.
Vendors
A vendor may not be deleted under the following circumstances:
•The vendor is currently assigned as the pay-to for another vendor.
•The vendor has any activity for either the current or prior year.
•There are open Purchase Order documents associated with the vendor.
Merging Data
Merges take one record’s information and combines that data with another record’s existing information. Typically, the original record remains, but is disabled after the merge and can usually be manually deleted once the merge has completed. Some of the information about the original record may be removed during this process. Due to the consolidation of totals and other information, it is not possible to undo a merge. Merging is preferred when one record is permanently replacing another (item substitution, for example). Merging affects most, if not all, data that refers to the original record including document data. Due to the nature of a merge, it can affect reporting, even for prior periods.
It may not always be possible to merge certain records together. Restrictions apply. These are outlined in the Help for the merge features.
Items
Item records can be merged (or renamed) using the Merge/Rename Utility found on the Utilities menu, Maintenance (sub-menu) located in the Inventory area.
Accounts
Customer accounts can be merged or renamed using the Account Merge (Rename) utility found on the Utilities menu in Receivables. Please read the documentation regarding this utility before merging or renaming any accounts.
Vendors
Vendors can be merged or renamed from the Vendor Maintenance form. This option is available from the menu marker’s context menu. User security may prevent a user from being able to view menu markers and their associated options.
Conclusion
We have posted several Crystal Reports® report files on our web site that can be helpful in identifying items, accounts, jobs, and vendors that may no longer be active. These reports include a graph displaying a status comparison for all records as well as sub-reports listing details for some or all statuses. Item (inventory) records don't have a built-in status, so the report offers a parameter that can be set (defaults to 6 months). The other reports use the status values used with accounts, jobs, and vendors.
In order to download report templates or view samples, you must have a web site account for our support site (https://help.ecisolutions.com). It's possible to request a user account when visiting the site.