1 of 3
Chunk Corruption
2 of 3
Database Table
3 of 3
There is some slang going around in which various applications (3rd party software) designed to work with QuickBooks are referred to as ‘chunks’. Like pieces of a jigsaw puzzle, some proponents of these chunks promote the idea that you can connect a whole variety of these ‘chunk applications’ to your QuickBooks Company file (via various methods) and not have any problems.
The idea of ‘plugging-in’ a variety of applications to perform tasks that QuickBooks either doesn’t perform, or doesn’t perform well, or doesn’t perform sufficiently, may seem very appealing especially since many of these ‘chunks’ are rather economical. But potential problems are lurking in the dark like a strong wind ready to blow the pieces of that jigsaw puzzle to the far beyond. The mechanisms of serious data corruption lie in this cluster of co-mingled data.
I realize that not all of my readers are database developers, software programmers or even 3rd party product users, so I will strive to keep this discussion somewhat limited and therefore will summarize a lot of highly technical concepts.
There are at least 3 popular mechanisms for connecting other applications to QuickBooks. The SDK which is a direct connection in which the application and QuickBooks talk back and forth as often as configured. When this software is first set-up with QuickBooks the QuickBooks Administrator gives the applications ‘rights’ of access under the Integrated Application preferences within QuickBooks.
The QuickBooks Web-connector is a Microsoft Windows application that enables specially designed web services (web-based applications) to exchange data with QuickBooks by allowing qbXML to pass between the developers’ web-based application and QuickBooks.
The Intuit Partner Platform is an API (application programming interface) that can be used to develop add-on web-based programs for QuickBooks; it provides an easy way for developers to create internet applications to interface with your QuickBooks data via the copy of that data Intuit maintains in ‘their cloud’ using the Sync Manager utility. This mechanism can overcome some of the potential problems associated with the other two formats, but not all. We will talk a little more about those improvements at the very end of this article (see the ‘Sidebar’.)
Understanding QuickBooks (data) Tables
QuickBooks has various types of tables that compose the database; these include List tables and Transaction tables, as well as Information tables. List tables contain detailed information about customers, vendors, payroll items, classes, etc. Transaction tables represent the real guts of the QuickBooks database; these tables are the invoices and invoice details; bills and bills details, checks, payroll, and payroll-liabilities and their details, etc.
Within each list, a unique field or number serves as the identification upon which this table is referenced. For example, in the Item List, the Item Name/Number field is one of the reference fields for each item in the list. But the primary reference is the ‘internal List ID number’ (ListID). Both list and transaction tables have 'reference ID numbers' as can be seen in the first column of each row shown in Figure 2.
How data from a 3rd Party Application posts to QuickBooks
When a SDK based application for example gets ready to send data to QuickBooks, like a transaction, say an Invoice for example, it sends a series of SQL insert statements to the program like the one shown in Figure 3. Take notice in this example of the “CustomerRefListID” listed at the very start of the first line within the parenthesis; this is referencing one of those ‘internal table ID numbers’ I mentioned just a moment ago; in this case for the specific table containing your Customer information.
This is one of the places where these 3rd party applications can be ‘corruptive’ of your QuickBooks data. Even though there are supposed to be ‘rules’ for using the SDK, the application of those rules is in the hands of the developer. So, unless the developer first queries all of the tables they intend to reference, immediately prior to issuing an SQL statement, to bring your most current data from QuickBooks into their own database to check for new identifiers, duplicates, or conflicts, the potential exists for corruption each time an exchange of data takes place.
One form of this corruption looks to database repair personnel more like ‘scrambled eggs’ than a relational database. It is possible that the data that previously was tied to a specific Customer RefListID is replaced by some other set of data since the existing data may be modified with the new data exactly as if you had selected a customer to edit and then manually modified all of the information in that record. When you open an Invoice previously issued to “John Smith’ the invoice may now appear as though it was issued to ‘Judy Wright’. As far as QuickBooks is concerned the real thing that ties an Invoice to a specific customer is the “CustomerRefListID” hidden in the invoice behind the scenes, and that ID ties to the information in the Customer table even if it is now different.
Compounding the problem with ‘multiple chunks’.
We have just seen how a single 3rd party application can potentially change (corrupt) your QuickBooks data; but what would happen if QuickBooks was connected to just one more 3rd party application via the SDK? Well the next time that second application exchanges data with QuickBooks the potential exists, if that application queries QuickBooks for all the most recent changes to the various tables it normally uses, to change the data in that application as well. You see the Customer information changed by the 1st App in QuickBooks will now change the Customer information in the 2nd App.
While some applications, using the SDK rules have settings that tell them that QuickBooks data will always ‘take preference’ over their own information, many do not. Even if they do, in this situation the wrong data in QuickBooks would now be transferred to the 2nd App because it believes QuickBooks to ‘always be right’ (QB data takes preference). As a result all of the customer information in that 2nd App is now wrong, and any transactions within that App will be wrong in the future since they will no longer contain the correct customer information.
Where does this ‘scramble end’?
You may be asking what happens if you are one of those with a ‘jigsaw puzzle’ of ‘Chunks’ all connected to QuickBooks. The answer is ‘a real mess’ results, it’s now not so much like scrambled eggs as ‘scrambled everything’ (like one of those egg scrambles that some breakfast joints loving refer to as ‘garbage’. You never really know what all they are dumping into the eggs on any given morning.) Your QuickBooks data soon resembles a bit of this application data, a bit of that one, some of this and some of that.
Unfortunately problems like this don’t always happen in the ‘blink of an eye’, a little data changes, it isn’t noticed, that changes other data, it still isn’t noticed, historical transactions are changed, and you still don’t notice, then the other application data is being changed and the transactions there as well. Before long someone finally notices ‘something a little odd’, then you start looking and more oddities appear, perhaps some even in front of your eyes.
Have you ever tried to ‘unscramble’ your scrambled eggs? You can’t ‘un-break some eggs’, and once the data scramble is well underway it is difficult at best, and impossible at worst, to resolve. Even finding a reliable ‘restore point’ for each and every ‘piece of the puzzle’ may seem impossible; you might have to go back ‘months’ to find uncontaminated backups.
For those of you familiar with the QuickBooks QBW.TLG (transaction log) file, you probably know that in some cases it can be used to restore missing or corrupted data, but the problem will be that this file will also contain many of the same ‘corruptions’ since it records each edit and insert into the data tables. Some data may possibly be extracted from the TLG file and some non-contaminated data from other ‘contaminated files’; however, even if recovered it requires exhaustive efforts to insure that it is restored in exactly the correct sequence so as to ‘match-up’ with all of your other applications, once they are similarly restored.
Of course that is another problem all together. While there are several of us ‘data guys’ who do this kind of work for QuickBooks data, very few people are out there who work on the proprietary databases of those other 3rd party applications. Some of those applications may not even offer access to their table structure directly, nor do their support staffs deal with these forms of ‘data disasters’.
In many cases you may simply have to perform ‘by hand’ reconstructions and then ‘link to new databases’ from those other applications if you choose to return to your former ways of the ‘dark side’. So just beware, the more ‘Chunky App’ pieces of the puzzle you connect to your QuickBooks the greater the potential for ‘scrambled garbage’ data.
[Sidebar: The IPP has the potential to overcome some of this type of corruption since all of your QuickBooks data is sent to the Intuit cloud where 3rd party developers connect to it. As part of that process, error checking and data synchronization standardization is much more stringent than has been seen in SDK and Web-connector based applications. Even with that said, there are known (and experienced) issues with Sync-manager based processes and applications, but those are best left to another article.]