Hi.

We were wondering about the InvoiceLinkedTxn table and how it could/should be utilized -- that is, in what situation might one query it and how would the results be helpful?

Thanks so much.

Bob

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, for example:

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 !

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 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

Very cool! ...and quite useful.

Thanks, Tom!

Bob

This query will extract infromation 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

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

sp_optimizeupdatesync Invoice

sp_optimizeupdatesync InvoiceLinkedTxn