If you're a QuickBooks ProAdvisor serving Desktop (Pro, Premier or Enterprise) clients, chances are you have encountered "data issues." Some of these issues take the form of accounting irregularities arising from user errors, while others are data corruptions involving how the data is either scripted to, or stored within, the QuickBooks database (aka: the Company file).
The Desktop Diagnostics and Repair course I will be teaching at this fall's Woodard "Master Climber" Boot Camps is designed to help you identify and troubleshoot some of the most common, and not-so-common, data issues I've seen over my long career in working with QuickBooks Data.
Much of my QuickBooks practice over the past 10 years has involved "data services." My firm provides consultation in the area of data file analysis, QBWin.log review and data file repair. Each year, we see lots of files that contain routine errors, including those originating from how QuickBooks posts data when users fail to follow best-practices.
In those cases, users believe the database is corrupted, but the irregularities encountered are "human factor" based, coupled with QuickBooks design limitations. In other cases, an individual's QuickBooks Company file may have either data- or data structure-related corruption.
While a QuickBooks database can show no symptoms of corruption and still be corrupt, it usually will show some symptoms. Obviously, if you get an error message while attempting to open or verify a file, you probably have significant corruption of your QuickBooks data-file.
Similarly, if your Company file cannot be accessed without getting a system error crash of QuickBooks, there is a good possibility something is wrong with your data-file. To note, such a crash also may indicate a problem with QuickBooks itself, or even an operating system error. There also are a lot of other, more subtle, indicators that can result from extreme data corruption, yet never preclude you from opening or using your Company file.
In this piece, I want to look at a excerpt from a QBWin.log file recently submitted to my office for review. While this excerpt only shows one of the errors associated with the file, it stands on it’s own – that is, it isn’t related to any other errors identified within the log.
OK, let me stop and say right here, even though QuickBooks displayed a message at the end of the verify utility telling you to run the Rebuild Utility, this is absolutely not an error that Rebuild can resolve. You could run Rebuild a dozen times and it wouldn’t come close to fixing this error.
Excerpt from a QBWin.log file
MC Aug Tip 1
Just looking at this error, the first detail that stands out is the "Invalid Customer ID number."
MC Aug Tip 2
But there is no customer name or other identification information provided, or is there? Maybe the "Record = 303" reference is telling you this is Customer List ID (Record #) 303, but these record numbers are not displayed in any list you can access from the QuickBooks User Interface.
If you call the Customer Center, the Record # doesn’t appear in there. If you look at the Master (Entity) Names List, the Record # isn’t in there either. If you look in the Add/Edit Multiple List Entries feature, the Record number cannot be found. Even if you export the Customer List to Excel from the Reports menu, the Record number isn’t there.
How about if you use the Export iiF feature to export the Customer list, and then open that list in Excel? When you do, you find that the Record List ID number isn't on that list either.
Excerpt of iiF Customer List export to Excel
MC Aug Tip 3
Finally, you have a record number to match to a name. But there's just one problem, when you search the exported list from top to bottom, you don’t find Record # 303. There were Records smaller than 303, and Records larger than 303, but no 303.
Does this mean the customer’s record was "deleted?"
Well, if that record was deleted, this is a very serious error, because it appears that QuickBooks still thinks the record exists.
But now that I think about it, I don’t believe you're really seeing the error for what it truly is. You have been fooled into thinking this has something to do with customers.
Looking back at our original QBWin.log excerpt...
What else is there in this error log message that you may have overlooked by focusing on the "customer" portion of the error?
I'll give you a hint – there's another QuickBooks "list" reflected in this error. Do you see it?
MC Aug Tip 5
That's the Account list. Is it possible that Record 303 refers to the Account list, and not the Customer list at all?
Now, the Account Ref ID Record Number is no different than the Customer Record #. You won’t find it in any of the lists you can view within the QuickBooks User Interface, but you can find the number if you export the Account list using the iiF Export feature.
So, after you open the exported iiF of the Account list in Excel, it doesn't take long to find Record 303.
Excerpt of Account iiF Export to Excel
MC Aug Tip 6
In this case, Record # 303 in the Account list happens to be a credit card type account. Now, you might tend to think that the entire "account" is corrupted, or perhaps the structural portion of the database containing that data record. But the problem isn't with the Account as a whole or the data structure. The problem is with the specific "field" of data in the affected Account record, and the QBWin.log error message tells us exactly what field it is.
MC Aug Tip 7
The culprit is the field called the "Customer ID number." Because this is a Credit Card type account, it had been set up for bank feeds (on-line banking downloads), and the "customer ID number" is one of the required fields for bank feeds. No matter how many times you have set-up bank feeds for your clients, I'd bet you didn't remember that the Customer ID number field, referenced in the QBWin.log file, was associated with bank feeds.
Edit Account window - Bank Feed Settings tab
MC Aug Tip 8
When you look at the "Bank Feeds Settings" tab of the Edit Account window for our Credit Card account (Record 303), you'll find that the numbered entered into the Account Customer ID field (shown in the red box above) doesn't conform to the field "data schema" to match the on-line banking securing requirements. (By the way, we've changed the information recorded here for confidentiality purposes.)
In this case, the solution is easy. You simply need to change the information in the Account Customer ID field to an on-line banking ID that conforms to the bank security requirements. When you do that, you'll find that your QuickBooks Company file will conform to the data schema for this field. You won't see this problem in the future. If you run Verify again, this error will no longer exist.
We have done some pretty good sleuthing, we have taken a QBWin.log error that seemed archaic, and with some keen Data Detective work, and differential diagnosis, we were able to rule out what the error could not be, and that led us to the only possibility left. As the Data Detective’s mentor, Sherlock Holmes, said “when you have eliminated the impossible, whatever remains, however improbable, must be the truth.”
If you want more of this kind of error identification and troubleshooting for QuickBooks, sign up for my newest rendition of QuickBooks Super Geek, which will be offered this fall at the "Master Climber Boot Camps" in the cites shown below:
MC Aug Tip 9A
For more information or to register, visit the Woodard website.