I have thousands of invoicelines. I place the correct  invoice in memory.

Select * from InvoiceLine where RefNumber = '101'.

I need to see if there is a partial payment on the invoice?  If there is one, I will need to reinsert according to my last post.

LAST POST- ? I would like to receive a partial payment for an invoice. I do a receivepaymentline for the invoice and everything looks good. However,  When I need to receive another payment for the same invoice. What do I need to do. The driver does not allow another insert with the same 'AppliedToTxnTxnID' or does not allow me to update the record.

LAST POST ANSWER-
After the payment is received, get the TnxID: ReceivePayment TxnID: 5C9E-1197769962
  ***the payment was made a month ago and is not in memory now***
Add a second payment line for the remaining $200 using the ReceivePayment TxnID as the TxnID:

INSERT INTO ReceivePaymentLine (TxnID, CustomerRefListID, DepositToAccountRefListID, TotalAmount,
AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)
Values ('5C9E-1197769962', '800000AC-1197769434', '80000-933270541', 200.00, '5C9A-1197769588', 100.00,
{d'2007-12-15'}, 'Payment Line 2 for Invoice #QODBCcip1').

The real question is how do I get the receivepaymentline into memory?

  The query I will need to run will be the below.
  Select * from ReceivePaymentLine where (AppliedToTxnTxnID = 'invoice.TXNID' 
  This takes over 15 minutes to run (sometimes locking the server) and there is no good jumpin.

Please help.

In QODBC v8.00.00.228 (and higher) we added a ton of new indexes on all tables on the optimizer backend (only). AppliedToTxnTxnID is now a optimized index: ReceivePaymentLine_FQSPECIAL06.

So queries on AppliedToTxnTxnID now work well when structured correctly.

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

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