Print Friendly, PDF & Email

How to use the Database Checkup Utility

This topic will cover the use of a tool called Database Checkup Utility.

WARNING

This tool is usually used by our technical support specialists to detect and fix troubles with your company file. This tool should only be used by accounting professionals or others only at the direction of Total Office Manager’s technical support.

You should maintain good backups of your Total Office Manager data (company file). Be sure that you test your backup procedures and files to make sure the backup is actually good.

Form Access

  • From the main menu, click Tools | Utilities.  Select Database Checkup from the list.
Database Checkup File Path
Database Checkup File Path

Using the Database Checkup Utility

The Database Checkup Utility will scan your company file looking for conditions that can cause you problems such as an out of balance condition, incorrect account balances, or erroneous data.

Database Checkup will often suggest a fix and perform the necessary procedures to make the fix. Other times, the fix will need to be made manually with the help of a qualified support professional.

Note: This tool is only available to users with the accounting or administrator permission.

Example Usage

Your general journal indicates that Total Office Manager is out of balance. You suspect you may have a transaction, like a check, that is causing the problem. The Database Checkup can locate any check with a total amount that is not equal to the sum of all of the items on it. These will be displayed on a list so that you can quickly open and fix them. You can rerun the Database Checkup Utility until all problems are solved.

Field & Button Definitions

Database Checkup Form
Database Checkup Form
  • Scan (Drop-Down List) – Select which record set you wish to examine for problems.
    • For example: If you suspect that you have a check that did not save properly or is out of balance, select Out Of Balance Transactions. See below for definitions of each record set.
  • Scan (Button) – After selecting a record set from the Scan drop-down list, click the Scan button to initiate the scanning process.
  • Select All / Unselect All – After a scan the output will be displayed in the results area of the form. The Select All/Deselect All buttons are used to check or un-check all of the checkboxes in the results area.
  • Fix Selected – Depending on the situation, the Fix Selected button may be available.  To use this functionality, first select the desired records from the results area of the form, then click the Fix Selected button.

Record Set Definitions

Follows are the available record sets available from the Scan drop-down list.

Database Checkup List
Database Checkup List
  • Checks/Bills with Missing Dates – Searches for both checks and bills having missing date fields. This is not a common problem.
  • Customers – Conducts a search through all of your contacts looking for problems. This is not a common problem.
  • Find Orphaned Records – It is possible for a record to become disassociated with related records in a database. This is rare but it can happen to any program that uses a database. An example could be a customer that has a corrupted index. Total Office Manager may not show this customer on any list, but it is still there; perhaps causing problems. This selection will locate orphaned records allowing you to delete them (that is the only way to deal with this problem).
  • Invoices/Sales/Credits – Allows you to validate the average cost on Invoice Items where changes to inventory have impacted the inventory valuation for that date.
  • Items – Searches all your invoice items (like parts, sales tax items, etc.) looking for problems. These problems include incorrect account type selections. Example: You have selected a liability account when you should have selected an income account (a common problem).
  • Missing GJ Entries – Searches for missing entries in the General Journal.
  • Out of Balance PR Hours – Searches payroll for any out of balance timesheet hour entries.
  • Out of Balance Refund Checks – Searches for refund checks having out of balance amounts.
  • Out of Balance Serial Numbers – Reviews purchase transactions to make sure the number of serial numbers purchased matches the number of serial numbers entered.
    • Please Note: There could be legitimate reasons why items would show up in this scan. This tool looks for transactions where a serialized item is listed but there is no serial number. Use this tool with discretion. An example of a legitimate reason for a serialized item to be out of balance is the transaction lists a serialized item with zero quantity. If the quantity is zero, there shouldn’t be a serial number.
  • Out of Balance Transactions – Sometimes records are saved that do not balance. Total Office Manager usually catches these problems and forces you to correct the condition, but it can still happen. An example may be a check with a total amount that is not equal to the sum of all of the items on it. If you suspect this or wish to check for this condition, select this option.
  • Please Note: Generally, when there is a material rounding adjustment on the balance sheet, it can be located using this scan. Out of balance transactions offer a simple fix: just go in and re-save the transaction.

Results Grid Definitions

The output of a search will be displayed in the results area of the form.  Columns of the grid are defined as follows:

Database Checkup Grid Results
Database Checkup Grid Results
  • Name/Number – Depending on the scan run, this column will display either a name, the record number, or the database ID of the transaction.  This could be the name of a vendor or customer or it could be a check number. It depends on the type of record. If you are running a scan which is analyzing the general journal (aka register), the number displayed will be the record’s primary table ID.
  • Type – This is the type of record. Example: Check, vendor, customer, journal entry, etc.
  • Problem – Total Office Manager displays the problem with the record here.
  • Suggested Fix – Whenever possible, a fix will be suggested.

Step-By-Step

  1. In the Scan drop-down list, select one record set in which to scan.
  2. Click the Scan button to begin.
    • Note: No changes will be made your data from the scan process alone.
  3. If problems are detected, they will be listed in the results grid area. Check the checkbox for each problem you wish to fix.  Sometimes the program will offer to fix the problem.  Other times you may only receive suggestions on what you can do to remedy the problem.
  4. Click the Fix button if available. There are details on how to fix most of the common issued found below.
  5. Repeat as needed.

Fixing the Most Common Issues

Out of Balance Transactions

Occasionally a transaction may become out of balance if it was not properly saved to the General Journal.  Many factors can contribute to this behavior.  There may have been a network disconnect in the middle of the save procedure, incomplete data, a power surge, etc.  Total Office Manager has built in utilities to scan for these types of transactions.  The Database Checkup utility is located under Tools | Utilities | Database Checkup.  Run the scan for Our of Balance Transactions.

Locate any Invoices, Credits, or Payments and double click to open.  Once in the transaction, right click and choose the option Find In GJ.  A review of the General Journal entry may indicate why this transaction is out of balance.  Typically, typing a space on the Internal Memo and resaving the transaction will update the General Journal entry.  Once you have reviewed and resaved all located out of balance transactions, rerun the scan again to validate you have in fact corrected these entries.

Missing GJ Entries

For the same types of reasons as the out of balance scan, entries may also be missing from the General Journal.  Running the scan for Missing GJ Entries will locate any records that are missing from the General Journal that impact accounting.

Locate any Invoices, Credits, or Payments and double click to open.  Review each transaction and resave by typing a space on the Internal Memo to input into the General Journal.  Once you have reviewed and resaved all located missing entries, rerun the scan again to validate you have in fact corrected these entries.

Missing GJ Accounts

For the same types of reasons as the out of balance scan, entries may appear in the General Journal without the transaction Chart of Account.  By navigating to Banking | General Journal, you can right click and select the option Find Missing Accounts. Any entries located will appear bolded and highlighted in red. If the list does not display the affected record and you did not receive a message indicating no missing accounts were found, you will need to expand your filtered results.  To easily identify entries with missing accounts, create a filter to search for records with the field Account = 0 or Account = “”. Double click the record and review the transactions to determine why the line entry is missing the account.  To correct these, you may have to enter a new line and reselect the item for the offending entry.

Updating Item Average Cost Calculation

If you enter a bill after you sell the items on that bill or if you sell item into negative quantity on-hand, the average cost calculations for inventory part item types can become in accurate. We have built a unique scanner to find invoices where the average cost calculation for Inventory Part types can be optimized.

If you do have a list of invoices that you wish to recalculate, please call technical support and we will walk you through the process. We can set every up for you so that you can run the recalculation utility at the very end of the day. Support is available Monday through Friday from 8:30AM to 4:30PM.

Once the recalculation has completed, the results will be displayed in Notepad and saved to your database for future reference. Notes will also be entered into the audit trail.

Important Notes
  1. The Fix Average Cost Utility will not recalculate the retail price of an item. The retail price of your items will not change.
  2. The Fix Average Cost Utility will change the cost of an item (if needed). This might affect gross profit and Use Tax.
  3. The Fix Average Cost Utility will ignore Serialized Items, Item Reservation Items, and other item types.
  4. If an invoice has sales tax that is calculated based on the direct cost of an item (Use Tax), the retail amount of the invoice could change. This could result in an overpayment or balance due. It could also result in a greater or lesser sales tax amount payable. Once this utility has finished, a report will be opened in Notepad. You will see if any retail pricing was affected.
  5. After this utility has recalculated your invoices and sales, it is possible that sales commissions added to an invoice (if any) will no longer be accurate. This utility will not add or modify sales commissions on an invoice or sale. However, if you were to manually calculate sales commissions, they could be inaccurate.

Tips

  • This tool should be used prior closing the books for any month or year. It should also be used anytime a problem is suspected by a knowledgeable Total Office Manager user.
  • If this tool is not available in the menu, you probably don’t have permissions to use it. Please see your company’s own Total Office Manager administrator for help.

Related Content

Fix Address Fields Utility