Talk With an Expert
    March 24, 2023

    Is It Time to Purge Data in Dynamics SL?

    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

    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

    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

    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.

    Tag(s): Dynamics SL

    Paulette St George

    Paulette St George is a Senior Technical Professional, Customer Support, for Enavate Managed Services, a DXC Services Partner. She has more than 25 years of experience in back office technology with a primary focus in Microsoft Dynamics SL and related solutions. She has served as a solution architect and designer, as well as developer. Currently she functions as a Senior Technical Consultant for Enavate by supporting Microsoft Dynamics, Microsoft SQL Server as well as varied Procurement solutions and Business Intelligence reporting technologies. She enjoys spending time cooking and creating healthy, clean and exciting meals, and spends much of her free time reading and researching many different topics of interest from Astronomy, Food Science and even Homeopathic Medicine. She lives in Southern California splitting her time between Orange County and Big Bear Mountain, enjoying the beautiful and varied weather SoCal has to offer.

    Enavate Recent Posts

    March 27, 2024

    The Costs and Benefits of Microsoft Dynamics 365 Business Central

    Microsoft Dynamics 365 Business Central is an agile ERP platform that allows small to medium-sized businesses (SMB) to enhance, grow, and scale their operations, allowing for... Read More
    March 15, 2024

    How to Reduce Warehouse Management Costs with Automation

    Way back in 2015, Supply Chain Management Review claimed that warehouse automation is one of the few remaining areas where companies can still significantly reduce their... Read More
    March 13, 2024

    How Business Central Helps You Stay on Task

    Anyone who has ever traveled by plane knows how stressful it can be to have a connecting flight. You must wait your turn to get off the first plane, figure out where your... Read More

    Subscribe to Receive Email Updates