While the 'Data Detective' has been on a long, well deserved, vacation on a remote Greek island, following the encounter with his last adversary, Mrs. Moffat', he has kindly lent me his 'by-line', so I may enlighten you with tails of woe from the QuickBooks desktop.
One of the most common forms of ‘data corruption’ that QuickBooks users report occurs with Memorized Reports and the Memorized Report List. There really are several forms and causes of these corruptions: (1) Formatting Errors, (2) List Index Errors, and (3) Socket Errors.
In the case of Formatting Errors, the actual ‘memorized report’ is corrupted; in cases of List Index Errors and Socket Errors, the database itself is corrupted.
Memorized Report Formatting Errors
Sometimes memorized reports are ‘corrupted’ during the memorization process, but more likely than not they become corrupted when attempting to open them without the necessary formatting being available. In many instances you click on the memorized report, or attempt to open it, and absolutely nothing happens. Later if you run the verify utility, the QBWin.log will report a formatting error for the memorized report like the one shown below:
Corrupted Memorized Reports
You notice that the log indicates ‘corrupted formatting data’. So the question is, what kind of formatting data has become corrupted? Unfortunately, the answer is, ‘almost any kind of formatting data’. Memorized Reports are just that ‘memorized’, what makes them unique is that you take a standard QuickBooks report and then you begin to change it. Perhaps you select fields of data to add to the report from fields not normally appearing on the report; or perhaps you choose to filter the report in a particular fashion. You may just want the report to look differently, so you might just simply change the font being used for a particular section of the report. Each of these ‘formatting changes’ you make is prone to potential corruption.
The memorized report feature was designed to be 'global', allowing one user to ‘memorize a report’, and then allow another user to run that report. There is only one problem with this concept, not all users have the same 'formatting capabilities'. Because of QuickBooks Enterprise security granularity two users may have just a single security difference that can result in memorized report format conflict (so we tend to see this problem more in Enterprise).
But let’s look beyond the complexities of QuickBooks Enterprise issues and look at some other formatting problems that can corrupt memorized reports. What do you think happens if you create a 'memorized report' and you have used a particular 'font' for certain data in the report and another user attempts to open the report and they don't have that font installed in their computer? Well, the report becomes corrupted while attempting to open due to formatting errors. In simple, there is a formatting conflict, the ‘memorized instructions’ say ‘use this font’, but the computer is telling QuickBooks we don’t have that font to use. (Note: In days long past when computers were pretty well standard, with only a single operating system (version) running at the time, and typically everyone had the same 'add-in applications', there wasn't nearly as much of this form of corruption. Today we see several different 'OS' versions and several different editions of programs like 'Office' all within the same networked environment, making conditions ripe for 'formatting differences' and errors)
As I mentioned we tend to see memorized report formatting errors more in the QuickBooks Enterprise environment than Pro/Premier, and one reason is ‘information conflict’ (arising out of security granularity). If you memorize a report based upon data you have access to, but another user attempts to open the report and their security rights' preclude them from access to just a single ‘bit’ of data in that report, the memorized report will ‘corrupt’ due to formatting errors. Of course the real answer for this would be that the Memorized Report List would only display information for which security granularity identifies access, but we know that is ‘way too much’ for Intuit to consider making possible; heck there are many simpler things with security that still don’t work, even in QuickBooks Enterprise.
We have even seen that ‘formatting errors’ for memorized reports can occur when one user creates a report and they are on a different (typically higher) product-version release than other users. Even though most QuickBooks releases do not preclude access to data if you are not 'up with the rest of the pack' (9 Users are on R9 but one User is still on R8) it can result in memorized report formatting errors.
There probably are a wide variety of other ‘formatting’ based causes, but these are the major ones we have identified over the past many years.
Another cause for the error stems not so much from the real ‘internal formatting’ as report identification (naming convention) conflicts. For some odd reason, Intuit has chosen to allow users to create memorized reports with the same name, just so long as they put those reports into different Memorize Report Groups. There might be a ‘Special Profit and Loss’ report under Jerry’s Memorized Report Group, and the same report name in the Memorized Report list not in a group at all (or in a different group). In some instances, they are identical reports, but in many cases we have seen that someone took one of these reports and then ‘modified and memorized’ it further. Almost invariably both reports with the same name are rendered corrupted because even though they are under different groups in the memorized report list (which shouldn't be allowed at all) the Database Server considers their formatting requirements identical especially during the verify utility, and the conflicting formatting results in corruption identification.
Unfortunately, regardless of the causative mechanism, it is almost always impossible to recover the missing or corrupted memorized report without sophisticated data file recovery procedures, and most Users simply prefer to recreate the report after the corruption is resolved. In such cases the fix involves deleting the listed ‘memorized report’ entry and forcing QuickBooks to re-index the Memorized Report list, after taking the appropriate safety precautions of course.
Memorized Report List Index and Socket Errors
While these are nowhere near the same, they do have a relationship. The concept of ‘database sockets’ is a highly complex topic associated with the structural aspects of database design, and is way beyond the scope of this publication; however, we will review it in the context of why some memorized report lists become ‘so’ corrupted they are best ‘abandoned’ unless professionally repaired. But to start we will look at ‘memorized report’ list index errors such as the one shown below:
In cases where the memorized report list itself has become corrupted, the cause is typically an out-of-bounds index. This usually results from the list being out of ‘internal order’ due to a deletion to the list where QuickBooks did not re-index the list order (we see this where QuickBooks will delete reports during Rebuild but fail to re-index the list as part of that process).
In cases of ‘Memorized Report List Display Index’ error(s) it is not unusual for a different memorized report to open when you click on the memorized report you want, because the associated index actually points to a different report location.
For Memorized Report List Display Index errors the fix is simple enough, it involves forcing QuickBooks to re-index the Memorized Report list using the list 'Re-sort' function.
When the allocated portion of the database structure to which a Memorized Report has been recorded becomes corrupted, a ‘socket’ error exists. There are more than 40 common database error identifiers associated with such errors ranging from ‘object’ based errors to ‘parameter’ based errors, from ‘bad address’ to ‘invalid argument’ errors, along with ‘index’ and ‘address’ errors. (Of course you are not going to see any of these messages with the limited reporting that the QuickBooks Verify utility provides.)
Regardless of the causative mechanism, the result is always the same, the database has a potentially serious integrity issue. In these cases, you may find two common symptoms associated with memorized reports:
- The QBWin.log file will display a series of corrupted reports, generally with progressive sequential numbers associated with each of the reports. (recnum 230, 231, 232, 233, 234, etc.).
- The QBWin.log file will display a corrupted report (let’s say it was ‘Special P&L recnum 52), you delete that report, then re-sort (re-index) the memorized report list. When you run Verify again the QBWinlog file shown another memorized report as being corrupted, not previously reported, and this time it is an entirely different report like ‘Special Balance Sheet’ but the recnum is 52 (once again). This is a good sign that there is a structural problem with the database.
These are most generally cases that need to be referred to professional file repair whether you use Intuit Data Services or one of the ‘private’ file repair professionals.
So what about the general steps you should take in cases of ‘memorized report’ errors? This is the sequence my good friend, the Data Detective recommends:
- Make a ‘windows copy’ of your QuickBooks file and the QBW.tlg file, and store them in a 'safety directory' you create.
- Run the Verify Data utility from the File>Utilities menu
- Open the QBWin.log and note all of the damaged memorized reports.
- Select the Reports menu and select Memorized Reports > Memorized Report List.
- Individually, one by one, highlight each damaged memorized report that you noted from the QBWin.log file.
- Click the Memorized Report button, and then Delete Memorized Report and click OK.
- Repeat steps 5 and 6 to delete each damaged memorized report that was displayed in the QBWin.log.
- Click the Memorized Report button once again at the bottom of the Memorized Report List.
- Select the option to Re-sort List, and click OK; now close the Memorized Report List.
- Repeat steps 8 and 9 one time.
- Run the Rebuild Data utility from the File>Utilities menu.
- Run the Verify Data utility from the File>Utilities menu.
- If Verify Data completes successfully, recreate the deleted memorized reports as needed.
- If Verify Data fails with any message other than “QuickBooks detected No problems with your data”, open the QBWin.log and note any newly listed damaged memorized reports. Many times the QBWin.log will only report 3 or 4 damaged reports, and a subsequent Verify may return additional corruptions. In these cases, repeat procedures 3 through 14. [Important Note: Where additional memorized report errors show up in subsequent Verify runs, be sure to look for 'socket errors' by comparing the report 'recnum' (record numbers) shown in the log with the prior log results; if you find this to be the case then consult a 'file repair' professional.]