If you have been on the Microsoft Dynamics SL system for a number of years, it is likely you have financial transactions that are beyond the retention dates for such data. These dates are set in each of your application modules (e.g., the GL Setup, the AR Setup, and the AP Setup screens) and should coincide with your company’s retention policy and needs for accessing such information.
Having this data, in and of itself, is not a bad thing but will need to be weighed against other questions. Are you encountering performance issues? Has adding memory to your servers not helped with slow screens or reports? How often do you need to access this older information? Depending on the answer to these questions, it may be time to consider deleting older data using the system provided processes.
Now…I’m an accountant at heart and understand the desire to be able to get at this older data even if we delete it from the live system. Many of our clients will create a copy of the databases (call it a snapshot in time) and save this as an “archived” version of data. The users of SL can access this backup when needed to retrieve or view these records. Generally, we recommend limiting this access to certain users and consider taking away rights to update or create new records.
Another option is to archive only certain tables of data, and do so within your live databases. For instance, we recently assisted a client with deleting older GL transactions (stored in the table GLTran) and stored the old ones into a new table called GLTran_archived. We then created a special report that could access these archived transactions on an as-needed basis. The benefit of this approach is that the users did not have to leave their current system in order to look up an older transaction.
Regardless of whether or not you archive data, or how you accomplish it, it is always highly recommended that you run reports prior to deleting data and make a backup of the databases. The reports should be used to compare data integrity and expected results before and after the deletion. The backup would be used to recover if the results were not what you expected, or if the process doesn’t complete properly.
So you are now prepared to actually take the plunge and delete some older data. The first step is to review your retention settings in the GL, AP, and AR modules’ setup screens. Next, you may want to consider using the period closing delete process, or the function in each financial module that allows you to remove detail and history information from the database based on your retentions. In GL, it’s called Delete GL Detail; in AP, it is Delete AP Detail; and in AR, it is Delete AR Details. I would suggest using the stand alone processes because it shows more information on what exactly is being deleted. Below are some additional notes and specifics about these processes.
Delete AP Detail
This screen shows that all details (i.e., documents including checks, transaction level details, and associated batch records) will be deleted for periods equal to or earlier than 12-1999. The AP History, total purchases, and payments found in the Vendor History screen will be deleted from 1998 and back. The option to delete vendors is also available, which is not found on the GL Close screen.
Only details of OPEN documents will be deleted, no matter how old they are. If a voucher is still payable from that period, it will not be deleted. Less obvious is if the check that paid the associated voucher is still outstanding (and you are “keeping checks for reconciliation”), then the voucher details will not be deleted.
In order for a Vendor to be deleted, all of its details have to be deleted in this process or earlier deletions. If you enter a date that falls between the “Delete Detail From” field value and the current period or between the “Delete History From” field value and the current year, nothing is deleted. You must set this to a date prior to those two fields since they take precedence.
Delete AR Detail
The function of this screen is essentially the same as the Delete AP Detail. If an invoice, memo, payment, or finance charge is posted in the current Accounts Receivable period or a future Accounts Receivable period, the data is not deleted. Only closed documents and detail are deleted. If an invoice has a partial payment, neither the invoice nor the payment is deleted.
This screen also has the ability to delete Customers that have no activity. A customer may be used in another module, such as the Project Accounting series, and deleting it from AR would not delete the associated project. In fact, that module has its own deletion process that you will want to consider before running the Delete AR Details process.
Delete GL Detail
This screen has no additional functions than the Delete Detail button on the Closing process screen. However, it does give you more information on what is about to happen.
Many times you may want to delete the detailed transactions but leave the account history for financial reporting. The detail is information found in the detail drill-down of the Account History window, any of the Account Inquiry windows, and information on the Detail General Ledger report. The account history is the Period-to-Date and Year-to-Date account balances stored in the Account History window, the Account Inquiry windows, and on the Trial Balance report. If you delete the details but not the history, the SL system will maintain each month’s balances but have no details on how it changed from one month to the next, which is okay unless you run a Data Integrity Check.
Whether you are considering deleting data for performance gains, statutory rules, or other reasons, a well thought out plan is key to the success. Review your retention dates, run reports and back up the database, consider archiving data before you delete, and confirm the deletion results are what you expected.
Feel free to contact us at Boyer for additional questions or assistance in moving on a project like this one.
For additional tips and tricks regarding Dynamics SL and other ERP products, visit our blog.
