[QODBC-Desktop] How to use InvoiceLinkedTxn query using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM
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 you probably understand it better as the History for an Invoice, for example as below:
If I run the following query for Invoice Number 51:
SELECT LinkedTxnTxnType as Type, LinkedTxnTxnDate as Date,
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:
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 a read-only table. So you have to do things like inserting 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,