6

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.

morbiD
  • 214
  • 1
  • 11

3 Answers3

4

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.

Keith Palmer Jr.
  • 27,666
  • 16
  • 68
  • 105
  • That might work. There won't be more than around 400 customers in any given company file. Is a Customer subject to the same issue you described for an Invoice, i.e. could the Customer Balance change without the Customer changing? – morbiD Sep 20 '13 at 13:49
  • With only 400 customers, this is definitely the way to go - at least as far as the balance goes. Just go ahead and query all 400 customers every X minutes, and you'll be all set - they shouldn't take more than a minute or so to all transfer up to your website. – Keith Palmer Jr. Sep 20 '13 at 13:58
  • The thing is, I still need to figure out what contributes to the balance because I need to show a statement of transactions; not just the balance amount. May I ask what you think of the proposition in my edit on the main question? – morbiD Sep 20 '13 at 14:37
  • I'm honestly not sure. Generally when we build something like this, we pull the individual transactions and cache them, but then show the balance from the actual customer. – Keith Palmer Jr. Sep 20 '13 at 15:02
4

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);
Hpjchobbes
  • 1,309
  • 1
  • 8
  • 11
  • 1
    Ah, that's an interesting idea. Looking at the documentation, I could also use the GeneralDetailReportQuery to grab the CustomerBalanceDetail report, or even build my own transaction report with CustomDetailReportQuery. – morbiD Sep 26 '13 at 10:43
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.

morbiD
  • 214
  • 1
  • 11