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.

Answer 2

You might be able to use a General Detail report, and you could even filter for each customer to get the transactions that make up their open balance. I've not used the reports from the SDK, but they should mimic the report in QuickBooks. The Open Invoice report should show any 'open' transaction that make up the balance of the customer.

IGeneralDetailReportQuery report = MsgRequest.AppendGeneralDetailReportQueryRq();
report.DisplayReport.SetValue(false);
report.GeneralDetailReportType.SetValue(ENGeneralDetailReportType.gdrtOpenInvoices);
report.ORReportPeriod.ReportDateMacro.SetValue(ENReportDateMacro.rdmAll);
report.ReportOpenBalanceAsOf.SetValue(ENReportOpenBalanceAsOf.robaoToday);

// Use this to filter for just a specific customer
report.ReportEntityFilter.ORReportEntityFilter.FullNameWithChildren.SetValue("CustomerName");
report.ReportEntityFilter.ORReportEntityFilter.EntityTypeFilter.SetValue(ENEntityTypeFilter.etfCustomer);

Answer 3

Note: This appears to work for my use case, but I can't say with confidence that it's a catch-all solution.

Since I need to present itemised statements to customers, I'm using a TransactionQuery to import all the transactions from QuickBooks.

Subsequently filtering the transactions for the account name "Accounts Receivable" seems to accurately list all the transactions which contribute to the customer's balance and can be used either to calculate the balance by summing the "Amount" column or to further query the same transaction table for individual line items. See the example queries below:

/* Calculate balance from transactions */
SELECT SUM(Amount)
FROM quickbooks_transaction
WHERE EntityRefListID = '$customer_listid'
  AND AccountRefFullName = 'Accounts Receivable';

/* Retrieve line items for a transaction */
SELECT *
FROM quickbooks_transaction
WHERE TxnID = '$transaction_id'
  AND TxnLineID != '';

I suppose you could even SUM the amounts of the line items to calculate a balance if you really wanted to, but you'd have to make sure not to include the parent transactions in the SUM.