Blog | Enavate

Is It Time to Purge Data in Dynamics SL?

Written by Paulette St George | Mar 24, 2023 8:00:00 AM

If you have a long history of using Microsoft Dynamics SL, it is likely you have financial transactions that are well beyond set retention dates. These dates should coincide with desired retention policy needs, and are configured in each module: GL Setup, AR Setup and AP Setup.

Holding on to data long-term is not necessarily a bad thing. But, keeping it will need to be weighed against other factors such as performance and available space. You also need to ask yourself, “How often do we access this information and in what context do I need it?”. Depending on your answers, it may be time to consider purging out-of-date transactions using the provided processes in Dynamics SL.

Know Your Options for Data Deletion and Archive Efforts

The desire to access all data even if removed from the live system is understandable. We never want to eliminate data, but rather archive it. A popular option I see in everyday practice is businesses will create a copy of the production databases and save them as an archived version, removing all Update/Edit/Delete rights to the data to ensure it remains unchanged. By doing this, users can access the archived copy without risk of data alterations.

Another option is to archive only certain high-volume tables within the live database. 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_arc. To access the data, reports would need to be customized or created. However, the benefits are worth it. This option would improve performance as it would not be accessed by built-in screens or reports. Additional benefits to this approach are that users don’t have to switch systems in order to look up an older transaction, and data can still be unified across the Live and Archive tables.

Regardless of how you choose to complete a deletion and archive effort, it is recommended that you leverage the assistance of a trusted partner to provide guidance and planning appropriate for your organization’s needs. It is also recommended to run relevant reports prior to, as well as complete a full SQL backup of the subject databases. The reports are used to compare data integrity and expected results. The backup would be used to recover the original data if the results were not as expected.

Identify the Best Retention Settings for Your Needs

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 your needs. Below are some 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 less than 12-1998. The AP History, total purchases and payments found in the Vendor History screen will be deleted from 1997 and back. The option to delete vendors with no activity is also available.

Only details on CLOSED documents will be deleted, no matter how old they are. So, 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.

For a Vendor to be deleted, all of their transactions have to be deleted during this process or would have already been in an earlier deletion process. 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

The function of this screen is essentially the same as the Delete AP Detail. Only closed documents and transactions are deleted. If an invoice has a partial payment and remaining balance, neither the invoice nor the payment is deleted.

This screen also can delete customers that have no activity. A customer may be used in another module, such as the Project Controller. Deleting it from AR would not delete the associated project but could cause issues if the project is re-activated and there is now a 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

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.

At times you may want to delete the transaction detail 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, SL 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 GL Integrity Check.

Don’t Let Old Data Slow You Down, Move-on With Confidence

Whether you are considering deleting data for performance gains, statutory rules or just basic clean up in preparation for migration to another ERP solution, a well thought out plan is key to the success and Enavate can help you put that plan together.

To get started on this task, you’ll want to:

  • Review retention dates
  • Run relevant reports
  • Backup the database
  • Consider archiving the data before deleting
  • Confirm the deletion results are what you expected

If you have any questions or are seeking help with any of the processes noted above, please reach out to us! We will gladly help.