When it comes to connecting multiple applications together, either using desktop SDKs or cloud-based sync tools such as Intuit’s Sync Manager, there is quite a bit that goes on behind the scenes. While many of the behind-the-scenes functionality should be hidden from users, those who evaluate, set up, configure, and troubleshoot software systems will find great value in understanding the plumbing of each connection between “chunks” in these systems.
The short story is that we had a list corruption problem. We think it was caused by some combination of a bug in a QuickBooks add-on (which uses the QuickBooks SDK) and possibly the QuickBooks SDK itself. However, there is no way to know exactly how the file got corrupted. Trying to recover our data led to a cascading series of problems that I’d like to describe so that you can be prepared for a similar situation.
The Long Story…
We regularly import sales receipts into our QuickBooks file using a custom-developed data import tool (it uses the QuickBooks SDK). This had been working fine, but then we noticed that with recent imports the sales receipts showed the proper customer name in the “name” field, but they had the address for a different customer.
If we deleted those transactions and re-imported them, watching the exchange of information between the custom program and QuickBooks, we could see that the information being sent to QuickBooks was using the correct customer name. The address should be populated based on the customer name, but we were clearly seeing DIFFERENT customer addresses in the sales receipts than were stored in the customer record.
Whenever I see things like this, I immediately think the data file is corrupted. I have a list of steps to take to correct a corrupted data file:
- First run the Verify Data function, and then the Rebuild Data function.
- Resort the Lists.
- Do a round-trip to a Portable company file (that is, save as a Portable company file and then restore from that).
Even after performing each of these steps, the problem persisted. So I started to panic…
Do we have a backup? Of course, but how far back would we need to go? Before we went to the extreme step of restoring from a backup, we sent the file to the master of data file corruption, Matt Clark of QB or not QB.
Matt has several tools that allow him to fix, convert, delete, clean up, and reset transactions inside a QuickBooks file. With our fingers crossed, we sent the file to Matt.
Alas, the file was broken in a way Matt had “never seen before.” As a simple test he tried an IIF import of a customer list, and that blew the file up. This was a good indication that the file was irretrievably broken.
Back to plan B (actually the last resort!) of restoring from the backup. The problem is, we had to go back 6 months, because each of our more recent backups were also similarly corrupted!
OK, so we should be able to use Karl Irvin’s Data Transfer Utility, which can read selected transactions from one file (our corrupted file) and add them to the restored backup. Although that seemed to work perfectly, (thanks to Nancy Gomez for helping us get that done) there are several near-game-over problems we found when we finished.
After restoring the backup and transferring over the transactions to bring the file up to date, all list items and transactions that are transferred will have NEW INTERNAL IDs! Records in QuickBooks have a unique ID that you don’t normally see in the user interface. This is why you can have duplicate invoice numbers, and can easily change the visible ID for any list or transaction record. The “real” identifier is this internal ID. When you use a transfer tool like the Data Transfer Utility, each record is assigned a new unique internal ID.
Why should that matter? Because the critical link between QuickBooks add-on programs and QuickBooks data relies on these IDs.
Since our process changed all of the internal IDs for the transferred records, all of our add-on programs can no can no longer connect THEIR data with the matching data in the updated/recovered QuickBooks file. These add-on products (either SDK or IPP based) need to have a unique reference to each QuickBooks record, so they will store the list and transaction IDs in order to access them. When we transferred the records from the corrupted file to the older backup this changed the internal ID’s, so the other add-on products that we use can no longer find their mommies. And these are children, so when they cannot find their mommies, it’s very sad.
Here is why it’s so bad:
- Our CRM program (which connects to QuickBooks) can no longer find the customer names, so on the next sync it will assume it has to add new names to the QuickBooks database (or get new names from QuickBooks), thereby creating duplicates. Big problem!
- Bills and payments in Bill.com cannot find their matching transactions in QuickBooks, so they must create new ones (again, duplicates!).
- Documents stored in SmartVault are no longer listed in the folder displayed on the toolbar for that data set.
Note that for some QuickBooks add-on programs creating a new file in this manner is not a problem. QQube, XpandedReports, and other read-only add-ons generally won’t have a problem and they will resync just fine. The general rule is that any program that stores data outside of QuickBooks, and associates this data with the data in QuickBooks, will probably have a sync issue when you reconnect after restoring or removing data.
So, What To Do?
The only way to “Fix” this problem is to do some type of “resync” of the data in the back-end of the add-on software. This isn’t simple.
First, you’ll need a table with three columns (Record Type, Old ID, New ID) for each record that was transferred in by the Data Transfer Utility. I call this my “resync table.” Luckily, Karl Irvin was able to provide us with this as a special favor, but you could create it yourself by connecting to QuickBooks from
Of course resyncing the data in all of your QuickBooks add-on programs cannot be done by normal humans, if at all. Most likely you’ll need the help from the add-on developers. In our case, Bill.com was nice enough to do a re-sync for us after we provided them with the resync table. But for the developer to do this, they might have to write a special script to reach into their databases and update the IDs as prescribed in the resync table. The big point here is that this asking a LOT of the add-on developers. Especially when the original “corruption” problem was in QuickBooks, not their database.
The Lessons To Be Learned
Here are some tips that will help minimize the chance that you will run into problems like this:
- If you suspect any problem with the connection between and add-on and QuickBooks (for example, if QuickBooks quits with an “unrecoverable error” followed by a message to restart the program) you should immediately stop and check for corruption and correct the problem. We saw an error message somewhere during our imports, but nothing seemed wrong so we continued working with this data. That may have been our fatal mistake.
- Another important step is to implement a good backup system that lets you archive your data, rather than just writing over the one backup you have. We minimized our problem by being able to go back in time to a backup copy that did not have the corruption problem.
- Periodically check your backup system by doing a test restore to be sure that the backups are valid.
- Make regular backups using the internal QuickBooks backup feature, as that does some “house cleaning” in your data file (make sure you have the “verify” option turned on). Periodically perform a Rebuild, and do before/after comparisons of your financial statements to see if there are changes. Clean up any errors you find before they build up and corrupt your file.
So be forewarned. If your QuickBooks file is connected to other apps that sync with lists or transactions, and you plan to do any of the following, you should carefully consider the issues in this article:
- New setup of a QuickBooks file for the same company, using existing QuickBooks data.
- Remove customers, vendors, items, or transactions from QB file using the QB condense function, or any other utility.
- Restore a backup and then re-enter data that was once in a newer version of that same file.
QuickBooks can’t provide ALL of the features that a business needs, so we have to rely on QuickBooks add-on products. The products that we use are high quality products, but the inherent nature of a bi-directional sync with QuickBooks and outside data means that we have to manage our files carefully.
I welcome your comments and experiences. Please share them here and we’ll do our best to help you through the forest. We’re still in the wild, wild west, so these are the types of issues that developers and platforms must perfect in the future. Developers, I hope you’re listening. This IS the issue when it comes to integration with QuickBooks.
