Hi, I am trying to ultimately make an AR report by invoice date. Using QODBC V207 and QB V7 with CR xi, I do a basic pull of all invoices using the invoice database for a customer and get 15 invoices including 2 open invoices.  The pull is correct. The sql listing is:

 SELECT "Invoice"."RefNumber", "Invoice"."TxnDate", "Invoice"."CustomerRefFullName", "Invoice"."AppliedAmount", "Invoice"."BalanceRemaining", "Invoice"."IsPaid"
 FROM   "Invoice" "Invoice"
 WHERE  "Invoice"."CustomerRefFullName" LIKE 'XXXXXXXXXXX Corporation'

Since I need to know when balances have been paid, I want to use the invoicelinkedtxn database.  Here is the basic sql for  this:

 SELECT "InvoiceLinkedTxn"."TxnDate", "InvoiceLinkedTxn"."RefNumber", "InvoiceLinkedTxn"."IsPaid", "InvoiceLinkedTxn"."AppliedAmount", "InvoiceLinkedTxn"."BalanceRemaining", "InvoiceLinkedTxn"."CustomerRefFullName"
 FROM   "InvoiceLinkedTxn" "InvoiceLinkedTxn"
 WHERE  "InvoiceLinkedTxn"."CustomerRefFullName" LIKE 'XXXXXXXXXXXCorporation'

In this case, the run produces 14 invoices and is missing one of the open invoices. It seems to me the database should pull the same list as I have no other filters on other than the Corporation.

Any help would be appreciated.

Jim

Basically the InvoiceLinkedTxn tables shows Payments, Credit Memos, and Deposit Line Items but doesn't include reimbursements, transfers from Sales Orders or Estimates (depending on your QuickBooks version). But in QuickBooks you probably understand it better as the History for a Invoice. The number of InvoiceLinkedTxn lines to Invoice lines won't match where multiple payments or deposits have been done.

To better understand, see: Examples using an InvoiceLinkedTxn query?