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.
- A ListID is unique within its list family, not within one list or across the file - customers, vendors, employees and other names share one sequence, every item type another, each other list its own - so records in two families can hold the same ListID
- An add whose *Ref names a record that isn't there answers 3120 when the ref gives a ListID, and 3140 when it gives a FullName
- A query that looks a record up by ListID, FullName or TxnID answers 500 Warn when there is no such record, not 1 and not an error
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.
- A Mod with an out-of-date EditSequence is refused with 3200 and still answers the record as it stands, its current EditSequence included
- A payment applied to an invoice, a credit applied from a credit memo, a deposit taking a payment, or an InvoiceMod changing an amount or the date moves the other transaction's TimeModified but leaves its EditSequence
- Deleting a payment gives the invoices it paid their balance back, and moves their TimeModified to the deletion but leaves their EditSequence
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.
- TimeModified is local wall-clock time with the standard-time offset all year (-08:00 in Pacific daylight time), and a modified-date filter reads an offset the same way, so a TimeModified sent back as FromModifiedDate finds its own record
- A ListMerge's merged-away record is listed by ListDeletedQuery as an ordinary deletion, with a TimeDeleted one second earlier than the merge's own answer gave, and the surviving record keeps its TimeModified and EditSequence
- Voiding an invoice keeps it, sets its amounts and its lines' quantities to zero, marks it paid, and puts "VOID: " in front of its memo
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.
- An invoice's AppliedAmount comes back negative (-80.00 for an invoice paid 80.00), so BalanceRemaining is the total plus AppliedAmount, not minus
- A ReceivePaymentQuery answers what each payment was applied to (AppliedToTxnRet) only with IncludeLineItems true
- A payment's AppliedToTxnRet lists as LinkedTxn every other payment and credit applied to that invoice, in the order applied, and reads the invoice as it is now; the invoice's own LinkedTxn lists them all, and a credit memo's lists the invoice its credit went to
- A ReceivePaymentAdd may apply less than its TotalAmount, or nothing at all with IsAutoApply false, and QuickBooks keeps the rest as the payment's UnusedPayment
- A ReceivePaymentAdd whose PaymentAmount is more than the invoice's balance is refused with 3210, "You cannot pay more than the amount due.", and nothing is saved
- A ReceivePaymentAdd of TotalAmount 0.00 that only applies credits creates no payment, and answers a ReceivePaymentRet holding nothing but each applied transaction's TxnID and TxnType
- A payment's UnusedCredits is the credit the customer's credit memos still have when the payment is read, whether or not the payment applied any
- Voiding an invoice that a payment paid in full succeeds, and the payment stays with its whole amount unused
- A ReceivePaymentAdd without a RefNumber saves a payment with no RefNumber, while an InvoiceAdd without one is numbered
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.
- An amount written without its decimals, such as <TotalAmount>40</TotalAmount>, is refused with 3040 though the XSD allows it; quantities and rates are taken as integers
- A character Windows-1252 has above 0x7F (é, ©, –, ’, a no-break space) fails the whole document with 0x80040400 when written as itself, and is stored exactly when written as a reference (é); a character Windows-1252 doesn't have is stored as its nearest match, or ?
- A journal entry's debit and credit lines come back in the order they were entered, not every debit line before every credit line
- A JournalEntryAdd with an entry-level Memo is saved without it and answers 530 Warn, "The field "Memo" is not supported by this implementation."
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.
- A DataExtDefQuery with no OwnerID answers 1, "did not find a matching object", even when definitions exist
- A DataExtMod sets a custom field's first value on a sales order that never had one; no DataExtAdd is needed
- A custom field added on Customer answers with AssignToObject listing Customer and five sales forms, DataExtID 0 and DataExtFormatString "Any text", and takes values on a sales order
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.