Is it time to delete old financial data in Dynamics SL?

This blog is part of our Customer Care series, where our experts address technical implementation and service questions across the Tribridge product portfolio to assure that customers have the support they need to optimize their solution most effectively and efficiently.

If you have been on Microsoft Dynamics SL for a number of years, it is very likely you have financial transactions that are beyond set retention dates. These dates are configured in each module GL Setup, AR Setup and AP Setup and should coincide with desired retention policy and needs.

Hanging on to data long term is not a bad thing, but should be weighed against other factors such as performance and available drive space. You also need to ask yourself, "How often do we access this legacy information and in what context to I need it?" Depending on the answer to these questions, it may be time to consider deleting out-of-date transactions using the provided processes in SL.

The desire to access all data, even if removed from the live operating system, is very understandable so we never want to eliminate data but rather archive it. It has been my experience that many will opt to create a copy of the production databases and save them as an "archived" version, generally removing all Update/Edit/Delete rights to the data in order to secure it and ensure it remains unchanged. By doing this, SL users can access the Archive or Legacy copy when needed without risk of data alterations.

Another option is to archive only certain high volume tables within the live database(s). This works well if there is plenty of space and the objective is to improve application performance and keep access centralized. An example would be to Archive GLTran where all GL transactions are housed. Old records can be moved into a new Archive table such as GLTran archive. As it would not be accessed by canned SL screens or reports, it could greatly improve performance. To access the data however, special reports need to be customized or created for use on an as-needed basis. The benefit to this approach is that users don't have to switch systems in order to look up an older transaction. Also, data can still be unified across the Live and Archive tables as needed.

Regardless of how you choose to complete a deletion and archive effort, it is always highly recommended that you leverage the assistance of an SL Consultant to provide guidance and planning that best suits your needs. It is always a good idea to run relevant reports prior to, as well as complete a full SQL backup of the subject databases. The reports would be used to compare data integrity and expected results. The backup would be used to recover if results were not as expected. Thus, it definitely needs to be a planned event.

Having covered some of the higher points of the process, the first steps will be to review the retention settings in GL, AP and AR setup screens to ensure they are accurate and suit needs. Below are some notes and specifics about these processes.

Delete AP Detail

Delete AP Detail winodw

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 less 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.

Only details on CLOSED documents will be deleted, no matter how old they are. If a voucher is still open and has a balance from an old pre-retention 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 also not be deleted.

In order for a Vendor needs to be deleted, all of its details have to be deleted in this process or earlier deletion processes. If you enter a date that falls between the "Delete Detail From" and the current period or between the "Delete History From" value and the current year, nothing will be deleted. You must set this to a date prior to those two fields since they take precedence.

Delete AR Detail

Delete AR Detail window

The function of this screen is essentially the same as the Delete AP Detail. Only closed documents and detail are deleted. If an invoice has a partial payment and remaining balance, 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 Controller and deleting it from AR would not delete the associated project, but could cause issues if the project is re-opened and there is now an invalid/deleted Customer ID assigned to it. In fact, Project Controller has its own deletion process that you will want to consider leveraging before running the Delete AR Details process.

Delete GL Detail

Delete GL Detail window

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 going 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, as well as the 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 the Trial Balance report. If you delete the details but not the history, the SL system will maintain each month’s balances but will 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 success. In a nutshell, you will want to: Review the retention dates, run relevant reports, backup the database(s), consider archiving the data before deleting and confirm the deletion results are what you expected.

If you have any questions or desire help with any of the information above, please feel free to reach out to us

Please note that Main Stream Support for SL 2011 ended July 2016; consider upgrading today. All Support for SL 7.0 ends October 2017, so be sure to check out the new features in SL 2015.

Next Post