Knowledgebase: TroubleShooting
[QODBC-Desktop] How to Create a Credit Memo from an Existing Invoice Using QODBC INSERT
Posted by William - QODBC Support on 12 October 2026 12:54 PM

[QODBC-Desktop] How to Create a Credit Memo from an Existing Invoice Using QODBC INSERT

Problem Description:

When using QODBC to write data back to QuickBooks Desktop, you may need to create a Credit Memo based on an existing Invoice. Directly inserting into the CreditMemo table creates a standalone credit memo that is not linked to any invoice. To properly associate the credit memo with its source invoice and ensure the invoice shows a "Fully Invoiced" or adjusted balance, you must use specific columns and parameters during the insert operation.

Affected products: QODBC Desktop, QuickBooks Desktop
Expected outcome: After executing the correct INSERT statements through QODBC, a credit memo is created linked to the source invoice, and payment can be applied to reconcile the amounts.

Symptoms:

  • An existing invoice needs to have a partial or full credit memo created against it from an external application.
  • Inserting into the CreditMemoLine table without proper parameters creates a credit memo that is not linked to any source invoice.
  • The customer wants to replicate QuickBooks Desktop's native "Refund/Credit" functionality on an invoice via QODBC.
  • Attempts to create a credit memo result in the original invoice balance remaining unchanged.

Cause:

In QuickBooks Desktop, when you create a credit memo from an existing invoice using the QuickBooks UI, it automatically links the credit memo to the source invoice and applies the credit. When writing via QODBC, this linkage must be established explicitly by inserting data into the CreditMemoLine table with specific column values that reference the source invoice's line items.

The key parameters required are:

  • FQSaveToCache = 1 on the first line item - this tells QuickBooks to save the transaction and establish the link to the source invoice.
  • FQSaveToCache = 0 (or omitted) on subsequent line items - this adds additional lines to the same credit memo without creating a duplicate.
  • Proper mapping of line item references from InvoiceLine to CreditMemoLine columns.

Environment:

ProductVersion / Detail
QODBC Desktop All versions
QuickBooks Desktop All editions (Enterprise, Pro, Premier)
Data Source Tools MS Access, Excel VBA, SQL Server linked tables, any ODBC consumer supporting INSERT

Solution:

Step 1: Retrieve and Verify the Source Invoice

First, query the invoice you want to create a credit memo from to confirm its details and obtain the necessary ListIDs.

Step 2: Insert Credit Memo Lines Using SELECT from InvoiceLine

Use an INSERT INTO ... SELECT pattern to copy line item data from the source invoice into a new credit memo. The FQSaveToCache parameter controls whether this is the first line of the credit memo (value = 1) or a subsequent line (value = 0).

Step 3: Apply Payment / Credit to the Invoice

After creating the credit memo, insert it into ReceivePaymentLine to apply the credit to the source invoice.

SQL Examples:

Example 1: Verify the Source Invoice

SELECT * FROM Invoice WHERE RefNumber = '011'

Example 2: Verify the Created Credit Memo

SELECT * FROM CreditMemo WHERE RefNumber = '012'

Example 3: Create Credit Memo - First Line Item (FQSaveToCache = 1)

This inserts the first line of the credit memo and establishes the link to the source invoice. Replace the InvoiceLineTxnLineID value with the actual ID from your invoice's first line item.

INSERT INTO "CreditMemoLine" (
    "CustomerRefListID",
    "ARAccountRefListID",
    "TxnDate",
    "RefNumber",
    "CreditMemoLineItemRefListID",
    "CreditMemoLineDesc",
    "CreditMemoLineRate",
    "FQSaveToCache"
)
SELECT CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber,
       InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, 1
FROM InvoiceLine
WHERE InvoiceLineTxnLineID = 'CE16-1892033022'

Example 4: Create Credit Memo - Second Line Item (FQSaveToCache = 0)

This adds a second line to the same credit memo. The FQSaveToCache value is 0, so it does not create a duplicate transaction.

INSERT INTO "CreditMemoLine" (
    "CustomerRefListID",
    "ARAccountRefListID",
    "TxnDate",
    "RefNumber",
    "CreditMemoLineItemRefListID",
    "CreditMemoLineDesc",
    "CreditMemoLineRate",
    "FQSaveToCache"
)
SELECT CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber,
       InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, 0
FROM InvoiceLine
WHERE InvoiceLineTxnLineID = 'CE17-1892033022'

Example 5: Apply Credit Memo Payment to Source Invoice

After the credit memo is created, apply it to the source invoice using ReceivePaymentLine. Replace the TxnID values with actual IDs from your transaction.

INSERT INTO ReceivePaymentLine (
    CustomerRefListID,
    ARAccountRefListID,
    AppliedToTxnTxnID,
    AppliedToTxnPaymentAmount,
    AppliedToTxnSetCreditCreditTxnID,
    AppliedToTxnSetCreditAppliedAmount
) VALUES (
    '8000008F-1892011138',
    '50001-896816252',
    'CE14-1892033022',     -- Source Invoice TxnID
    435,                        -- Payment amount
    'CE38-1892034028',      -- Credit Memo TxnID
    435                         -- Applied credit amount
)

Example Scenarios:

Scenario 1 - Full refund from a single-line invoice:

A customer returned an item billed on invoice #005 (single line). The user queried the InvoiceLine table to get the first line's TxnLineID, then executed the INSERT statement with FQSaveToCache = 1. A credit memo was created and linked to invoice #005. The payment was applied via ReceivePaymentLine to zero out the invoice balance.

Scenario 2 - Partial refund from a multi-line invoice:

A customer returned only one of three items billed on invoice #011. The user executed two INSERT statements: the first with FQSaveToCache = 1 for the returned item (first line), and the second with FQSaveToCache = 0 for additional reference lines. The credit memo was created with partial amounts and applied to the invoice.

Additional Notes:

  • FQSaveToCache is critical: Always set FQSaveToCache = 1 on the first INSERT statement. This flag tells QuickBooks to commit the transaction to the company file. Setting it to 0 on every line will queue all lines in memory without saving.
  • Subsequent lines use FQSaveToCache = 0: All additional line items inserted into the same credit memo must use FQSaveToCache = 0, otherwise each INSERT creates a separate credit memo.
  • Column values come from InvoiceLine: The SELECT portion of the INSERT pulls CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, and rate information directly from the source invoice's line items. This ensures consistency with the original transaction.
  • InvoiceLinkedTxn table: When a credit memo is created from an existing invoice in QuickBooks Desktop, it automatically generates a record in the InvoiceLinkedTxn table with LinkedTxnTxnType = 'CreditMemo' and LinkedTxnTxnID pointing to the source invoice.
  • For additional guidance on receiving payments, see the QODBC knowledge base article on How to Apply Payment to an Invoice or Credit Memo.

How to Create Credit Memos via QODBC (KB Article 2068) How to Receive a Payment in QODBC (KB Article 359) Understanding CreditMemoLine Tables Understanding InvoiceLine TableUsing FQSaveToCache Parameter in QODBC INSERT Statements

Keywords:

Credit Memo Invoice INSERT INTO CreditMemoLine ReceivePaymentLine FQSaveToCache InvoiceLinkedTxn Refund/Credit Apply Payment Partial Refund Customer Credit QuickBooks Desktop InvoiceLineTxnLineID Write Back Credit Memo Linking
(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).