GP File Maintenance
Data corruption happens. Network disconnections, power failures, crashes, and bad data imports can all interfere with the integrity of your accounting data. The effects of data corruption can range from critical system errors to unnoticeable. If your GP users encounter these types of disruptive events, routine file maintenance is a wise choice to avoid greater issues due to data integrity in the future. A payables invoice with a missing key record may appear normal inside GP, but it will interrupt the posting of a payment applied to the invoice, requiring careful intervention. Check Links and various module-specific Reconcile utilities are available in GP to detect and repair data integrity issues. Both types of tools should be run regularly, be it monthly, quarterly, or annually prior to year-end closing. However, these maintenance routines are not a panacea for all data integrity issues, and precautions should be taken when using them.
Data for a single transaction or master record in GP is stored across multiple related tables in a company’s database. E.g., payables transactions have keys records reserving voucher numbers, header data, distributions, apply records, and potentially other data all stored in separate tables. Check Links maintenance compares data in these related tables for specific transactions, master records, or setup options, and attempts to make the data in all related tables agree.
Check Links is accessible via the Microsoft Dynamics GP menu >> Maintenance >> Check Links. The options are grouped by GP series, and multiple options can be selected with the “Insert” button, and run in the same maintenance.
While we do recommend running file maintenance regularly, you should take a conservative approach. Check Links will create, destroy, or modify records in an attempt to restore data integrity in the related tables. Deleting all payables table records pertaining to a specific damaged transaction may improve the data integrity in payables, but cause an AP-GL reconciliation issue if the transaction had posted through the general ledger.
With that in mind:
- If you can run file maintenance on a test copy of the live data, do so. This is especially true if you are running Check Links for the first time, or when running it to address a specific problem, and will give you a preview of the changes Check Links will make in the live company.
- Have a known good backup of the company prior to running Check Links against your live data. This is less important if you are able to run Check Links against current test data.
- Schedule a maintenance window so there are no users in the company that is being maintained. This is to prevent any loss of work if you need to restore the live data from a backup, and is less important if you have previewed the changes Check Links will make on test data.
- The amount of time Check Links takes to run will vary greatly based on the logical tables being checked, and the quantity of data in those tables. Getting an estimate of the time required by running against test data can help here.
- The higher the volume of transactions in the company, and the higher the frequency of disruptive events such as network disconnections, the more frequently maintenance should be run. It is hard to validate changes made by Check Links when the error log is dozens of pages long. That being said, a monthly maintenance schedule should be sufficient for even the busiest of companies, and smaller companies may be fine with including file maintenance annually as part of their preparation for year-end closing.
- Keep copies of the maintenance reports that print after completion of maintenance. If incongruencies with transactions get noticed in the future, you can review the reports to see if Check Links had made any corrections to the transactions in the past.
- Be particularly wary of running Check Links against tables that are tied in with third-party add-ons. Check Links may result in the GP table data disagreeing with data in the third-party tables.
Summary balances for multiple transactions are typically stored in their own tables and fields, rather than being re-summed every time a user opens a summary inquiry window; batch totals are stored in fields in the batch header, and GL account summaries are stored in their own table.
The same types of issues that cause data corruption may result in inaccurate summary balances. Additionally, Check Links itself may cause inaccuracies by deleting records or adjusting outstanding balances. Any time you run Check Links against transactional tables, please run Reconcile against the summary records and batches in the appropriate series for those transactions.
In addition to the recommendations in the Check Links section:
- Some modules include the option to only print a report of the calculated changes without having to apply those changes to the summary balances. In the Purchasing series reconcile utility, this is accomplished by unmarking the Reconcile checkbox, but leaving the Print Report checkbox marked, then processing the reconcile. Reconcile utilities typically process much quicker than Check Links, so use this option if it is available. If the changes seem reasonable, though, remember to re-run the utility with the Reconcile option marked.
- Do not reconcile summary amounts for any historical years where transactional detail has been removed. They will re-sum to zero. In this era of inexpensive drive space, history removal is rare, but some long-time GP clients may have historical years that only have the summary data.
- Inventory relies on accurate data in both Sales Order Processing (SOP) and Purchase Order Processing (POP) series. You should first reconcile SOP, then POP, and then Inventory.
Should you have any questions, email our Microsoft GP support team or give us a call at 204-982-9890.