Calculating customer balance from QuickBooks data (QBXML)

Question

I'm working on building a simple website where my company's customers can view a statement for the last 12 months and therefore, their current balances. To achieve this, I'll be using the QuickBooks Web Connector to export the necessary data from our QuickBooks company files to a MySQL database (via ConsoliBYTE's PHP DevKit).

However, I'm not terribly well-versed in QuickBooks itself so my question is: Which fields from which QBXML responses would I need to store in the database in order to calculate an accurate customer balance?

I don't know which types of transaction can contribute to or affect the balance (other than Invoices), so I don't know which QBXML requests to make beyond InvoiceQuery and CustomerQuery.

I am also conscious of this post by Keith Palmer which suggests that an Invoice balance by itself is not necessarily reliable:

"an invoice balance can change WITHOUT the invoice itself changing (because the balance is a field calculated from other records, not a field on the invoice itself)"

I don't know whether I'd be better off replacing all the Invoice data in my database every time the Web Connector ran, or calculating the Invoice balance myself from related records, in which case what data would I require to calculate the Invoice balance?

Answers

@consolibyte's answer seems to be the simplest method of obtaining a customer balance so I've accepted it even though it's not verbose enough for my specific usage.

@Hpjchobbes' answer could be useful for obtaining QuickBooks' built in Balance Detail report.

I've also added my own answer since I needed to present a fully itemised list of transactions. It appears to work ok but there could be circumstances where it fails.

Answer 1

Calculating balances based solely on transaction data can be difficult with the QuickBooks API. There's a whole lot of stuff you'll potentially need to take into account:

• invoices balances
• credit memo balances
• checks
• customer starting balances
• payments
• payment discounts
• probably some other stuff that I can't think of off the top of my head right now

Depending on how many customers you have in QuickBooks, you may be better off querying the customers directly, and then using the Balance node returned with the customer record instead.