Storing QuickBooks Desktop data in your app

You're going to keep a copy of your customer's books. Sooner or later that copy will disagree with QuickBooks, and QuickBooks will be right. This is what to store so you can tell when that's happened, and what QuickBooks does with ids, dates and names that its reference never quite gets around to mentioning.

Every link below is something I watched a real company file do. Usually right after I'd assumed it wouldn't.

Ids

Every list record has a ListID and every transaction a TxnID. Store it next to your own record: it's how you name that record in a Mod, a delete, or a reference from some other request. Store which company file it came from, too. An id means nothing in another file, and you will eventually have a customer with two.

Then resist the urge to treat a ListID as globally unique. It isn't, and the way it isn't is a good one to learn from someone else's mistake.

EditSequence

QuickBooks hands you an EditSequence with each record and wants the current one back on a Mod. Store the one you last read. It's QuickBooks' way of asking "are you sure you're looking at the same thing I am?", and when the answer is no, it refuses.

It is tempting to use it as a change detector as well. Don't lean on that too hard: plenty of things change a record's numbers without touching its edit sequence.

Finding what changed

A query filtered by modified date gets you what changed since you last looked. Dates and time zones are involved, so naturally there's a surprise in there.

Deletions are their own chore. A deleted record isn't there to be returned, so you have to go and ask for them separately, with ListDeletedQuery and TxnDeletedQuery. If you never ask, your copy keeps its ghosts.

Names

A name is what your users see, and the only handle you've got before you know an id. It's also where QuickBooks is at its most particular. Customers, vendors, employees and other names all share one namespace, so the name your customer already gave a vendor can't be given to a customer (status 3100). Your users will not know this. They will find out through your app.

Invoices, payments and credits

An invoice's balance is the sum of everything that's ever been applied to it, and the only one who knows that sum for certain is QuickBooks. Read balances; don't calculate them. And after anything that applies or removes money, read both sides again, because the other side moved too.

This is the longest list on the page for a reason. If your integration touches payments, read all of it. If it doesn't yet, read it anyway, so you know what you're agreeing to when someone asks.

Amounts, text and lines

Store amounts as decimals, never floats, and write them back with two decimal places every time. Keep text exactly as QuickBooks returned it, and compare loosely when you're matching what you sent against what got stored. QuickBooks has opinions about characters, and it does not always share them with you first.

Custom fields

Custom fields are "data extensions": a definition says which kinds of record carry the field, and each record holds its own value. Somebody will ask you to sync one in the first month. It's more reasonable than it sounds, once you know these.

Still here?

Then you're committed. Here's every finding and every error code, for the days that need them. And if you'd rather not be the one keeping all of this straight, QuBe Sync is a JSON API for QuickBooks Desktop built on exactly these lessons.