How Do I Consolidate Transaction History?

The Consolidate Transaction History feature provides you with a way to keep your database manageable by creating summary records from the detail records and then deleting the detail records from your database. These summary records allow you to continue to report on prior periods.

Consolidation of history can only be performed after a fiscal year has been closed and begins with the oldest year that contains information. It is ideal to perform the process when you are comfortable that you will not need to access detail for the fiscal year you are going to consolidate. You should perform the consolidation only after making sure you have made a backup of your data. This way you will always have the detail on a backup should you need to refer to it later and as a protection in case you run into problems during the consolidation process.

To assure the integrity of your database before and after the consolidation, you must have the ability to download and run SQL scripts with the MIPQry utility. If you have questions about the procedures, we strongly recommend that you consult your MIP Authorized Business Partner. If you do not have a relationship with a MIP Authorized Business Partner then contact the MIP Nonprofit Professional Services group.

The following is a checklist for consolidating transaction history:

BEFORE you consolidate:
  1. Plan to consolidate at a time when you can have exclusive use of the database for 24 hours.

    2. If you own the MIP Advance system and get the message that you do not have exclusive access, be sure to review the System>Manage Concurrent Users and System>Manage Services forms; sending emails to the users accessing the system and explaining the need for exclusive access, remind them to save their changes and log out of the system quickly. Afterwards, if you still need to establish exclusive system access, open System>Manage Services. Click the Set Maintenance Mode button and set the organization and system databases offline. This will set the system into maintenance mode and prevent others from logging on to the system but it will also kick the logged on user's out of their databases; thus losing any unsaved work. Once the Consolidation Transaction History is complete, you will need to clear these check boxes on the Set Maintenance Mode form and place the organization and system databases back online.

  2. Make and archive a backup of the database. For steps, see article 1056 in the Knowledgebase.
  3. Run all Rebuild scrips. See articles 916, 841, 1206, 1261, 961 for GL, AP, AR, Budget, and ENC respectively in the Knowledgebase.
  4. Run all Data Integrity Checks (Organization>Data Integrity Checks). Contact Customer Support for assistance in resolving any Data Integrity Check failures.
  5. Print a trial balance for the end of the year you are closing using Reports>General Ledger Analysis>Normal Trial Balance.
  6. Print out Summary A/R Ledger (Reports>Accounts Receivable>Summary A/R Ledger), Summary A/P Ledger (Reports>Accounts Payable>Summary A/P Ledger), and Summary Encumbrance Ledger reports (Reports>Encumbrances>Summary Encumbrance Ledger).
  7. Compare the reports to your audit reports or other reports you know to be correct. Make sure you are satisfied with the reconciliation before consolidating.
To consolidate:
  1. Consolidate your transaction history using Organization>Consolidate Transaction History.
  2. The system displays the oldest fiscal year (opened or closed) that has not been previously consolidated. This date range cannot be edited.
  3. Select the method to consolidate your data-Monthly, Quarterly, or Annually. If you consolidate on a monthly basis, you may not get a reduction in the size of your database. as you might expect because, for example, an account with a single transaction is it for a month will have that single transaction replaced with another monthly "Consolidated" transaction. However, if you consolidate the same database on a yearly basis, you would see more reduction in size as the same account that had a single transaction in each of 12 months would replace the 12 transactions with a single yearly "consolidated" transaction.
  4. Click Start to begin consolidating data.
AFTER you consolidate:
  1. Run all Rebuild scrips. See articles 916, 841, 1206, 1261, 961 for GL, AP, AR, Budget, and ENC respectively in the Knowledgebase.
  2. Run all Data Integrity Checks (Organization>Data Integrity Checks). Contact Customer Support for assistance in resolving any Data Integrity Check failures.
  3. Print a trial balance for the end of the year you just closed using Reports>General Ledger Analysis>Normal Trial Balance.
  4. Compare it to the Before consolidate report.
  5. Print out Summary A/R Ledger (Reports>Accounts Receivable>Summary A/R Ledger), Summary A/P Ledger (Reports>Accounts Payable>Summary A/P Ledger), and Summary Encumbrance Ledger reports (Reports>Encumbrances>Summary Encumbrance Ledger).
  6. Compare them to the Before consolidate reports.
  7. Compress the consolidated database using File>Compress.

    8. If you own the MIP Advance system, you must place the organization and system databases back online. With Administrative user rights, open System>Manage Services. Click the Set Maintenance Mode button and clear these check boxes to place the organization and system databases back online.

If you run into any problems:
  1. Backup the consolidated database. For steps, see article 1056 in the Knowledgebase.
  2. Restore from the Before consolidate backup using File>Restore.
  3. Log a support call.
  4. Do all of the above before attempting to consolidate the database again and prior to allowing users to reenter the database for production.
  5. Users should not be allowed to reenter the database until you have gone through all of the above without error.