Examples of using InvoiceLinkedTxn query in QODBC

Instruction of table InvoiceLinkedTxn

     Basically, the InvoiceLinkedTxn tables show Payments, Credit Memos, and Deposit Line Items but don't include reimbursements, transfers from Sales Orders, or Estimates (depending on your QuickBooks version).

In QuickBooks

     In QuickBooks, you probably understand it better as the History for an Invoice, for example, as below:

In QODBC

     If I run the following query for Invoice Number 51:

     SELECT LinkedTxnTxnType as Type, LinkedTxnTxnDate as Date,
     LinkedTxnRefNumber as Number, LinkedTxnAmount as Amount,
     BalanceRemaining as Balance FROM InvoiceLinkedTxn
     where RefNumber='51'

     You will see the Transaction History for Invoice #51 using QODBC too!

LinkedTxnTxnType and LinkedTxnTxnID

     In the InvoiceLinkedTxn table, The TxnID is the same TxnID as the Invoice. The LinkedTxnTxnType tells you what the line is linked to, which can be any one of the following:

Bill, BillPaymentCheck, BillPaymentCreditCard, BuildAssembly, Charge, Check, CreditCardCharge, CreditCardCredit, CreditMemo, Deposit, Estimate, InventoryAdjustment, Invoice, ItemReceipt, JournalEntry, LiabilityAdjustment, Paycheck, PayrollLiabilityCheck, PurchaseOrder, ReceivePayment, SalesOrder, SalesReceipt, SalesTaxPaymentCheck, Transfer, VendorCredit, YTDAdjustment, or ARRefundCreditCard

and the LinkedTxnTxnID is the TxnID of the record it is linked to.

     The InvoiceLinkedTxn table is read-only. So you have to insert a ReceivePaymentLine record that points to the Invoice.TxnID and then display the InvoiceLinkedTxn table, and you will see the record inserted as a line with a LinkedTxnTxnType of ReceivePayment and the TxnID of the ReceivePayment.

     For example, here you can see two payments against the one invoice:

     SELECT TxnID, LinkedTxnTxnType, LinkedTxnTxnID, LinkedTxnTxnDate as Date,
     LinkedTxnRefNumber as Number, LinkedTxnAmount as Amount,
     BalanceRemaining as Balance from InvoiceLinkedTxn

Examples of Single Query with InvoiceLinkedTxn Table

     Note: The quickest way to do anything using QODBC is to run a single query (and in the case of MS Access - run the query as a pass-through query).

A Simple Query with Optimizer for Maximum Speed

     This query will extract information from the Invoice and InvoiceLinkedTxn tables using the QODBC optimizer (by use of the NOSYNC tag) for maximum speed:

     SELECT Invoice.CustomerRefFullName, Invoice.RefNumber, InvoiceLinkedTxn.TxnID,
     InvoiceLinkedTxn.LinkedTxnTxnType, InvoiceLinkedTxn.LinkedTxnTxnID,
     InvoiceLinkedTxn.LinkedTxnTxnDate as Date, InvoiceLinkedTxn.LinkedTxnRefNumber as Number,
     InvoiceLinkedTxn.LinkedTxnAmount as Amount, InvoiceLinkedTxn.BalanceRemaining as Balance
     from Invoice NOSYNC, InvoiceLinkedTxn NOSYNC
     where Invoice.TxnID=InvoiceLinkedTxn.TxnID
     and Invoice.TxnDate >= {d'2007-01-01'}
     order by Invoice.CustomerRefFullName

A Simple Query with Optimized Tables ReSync

     If you don't see recent information, you can resync the optimized tables by running:

     sp_optimizeupdatesync Invoice

     sp_optimizeupdatesync InvoiceLinkedTxn