It was a beautiful fall day, and the telephone rings at the 3rd floor flat of The Data Detective. On the other end a hysterical bookkeeper reports that when she attempted this morning to perform her middle of the month back-up of QuickBooks it failed with an error message reading "something about data corruption." The Data Detective asks her if she has done anything since the back-up failed, and she says she shut down QuickBooks and began looking for someone to call.
Upon arrival, the Data Detective makes a ‘windows’ safety copy of the client’s *.QBW (Company) file, and the companion *.QBW.tlg file. QuickBooks then is opened in single-user mode with the Administrator log-in and password, and the Verify Data Utility is run; upon completion the Data Detective reviews the QBWin.log file which displays several messages similar to the error shown in the headline figure of this article (above).
The Data Detective knows that these errors usually result when the data-relationship between a Sales Order (or an Estimate) and the Invoice associated with the transaction, usually reflecting partial billed quantities, is broken. Typically the link(s) between the detail lines of each of the transactions become disassociated.
Our sleuth also knows that the solution to fixing this type of data corruption is to trick QuickBooks into re-establishing the link(s) between the Invoice and the Sales Order (or Estimate) components. In order to do this, our Data Detective (or you) must identify and locate the transaction within the Company file; in order to do this it is necessary to review the information listed in the error message to get the transaction details (type, number and date). To repair the corruption you should follow these steps:
Step 1 - Use the QuickBooks Find feature to locate the transaction. Note: In the case of this example you can search by the Sales Order transaction number (110269-260), this is the assigned sales order number generated by QuickBooks which can be seen on the transaction, it is NOT the ‘internal transaction ID’. While it is easy to find the transaction in this error, Intuit has chosen to display only the ‘internal ID information’ for some errors; in those cases it is almost impossible for a user to track down an error using that ID number.
Step 2 - Edit the transaction and add a new line to the end of the transaction, without changing the amount of the transactions:
- You can accomplish this by adding characters such as a three periods (…) or three asterisks (***) in the description field of an empty detail line, and then saving the transaction.
- In some instances it is beneficial to advance the transaction date, by 10 years, and then save the transaction; then return to the transaction and restore the date to the original.
Step 3 - Perform a Rebuild of the Company File (File >> Utilities >>Rebuild Data), and then
Step 4 - Run the Verify utility (File >> Utilities >> Verify Data) to insure that the transaction links have been repaired, and that no other data issues exist.
After the Verify utility, the Data Detective reviewed the most recent QBWin.log file; sure enough it was clear; the errors were resolved. Another Case Solved by the Data Detective!
Now that you know the secrets of “invalid open quantities” and the steps to repair this form of data damage, you can easily resolve this form of corruption; after all, “it’s elementary My Dear Watson.”