Knowledgebase
[QODBC-Desktop] Convert a Sales Order to an Invoice Using QODBC SQL Statements
Posted by William - QODBC Support on 12 October 2026 08:57 AM

[QODBC-Desktop] Convert a Sales Order to an Invoice Using QODBC SQL Statements

Problem Description:

You have created a SalesOrder (or SalesOrderLine) record via QODBC and now need to convert that sales order into an invoice. In QuickBooks Desktop, this normally happens through the "Convert to Invoice" button, but when working programmatically through QODBC, you must execute two separate INSERT statements: one to create the invoice line (mirroring the sales order line data) and a second to link it back to the original sales order.

Affected products: QODBC Desktop, QuickBooks Desktop (all editions)
Expected outcome: A valid invoice is created in QuickBooks that references the original sales order, with proper linking so QuickBooks tracks fulfillment status.

Symptoms:

  • You insert records into InvoiceLine, but no linked record appears in InvoiceLinkedTxn - the invoice exists but is unlinked from the sales order.
  • You need to create an invoice from a sales order programmatically (not via the QuickBooks UI).
  • Multiple sales order lines need to be converted, and each invoice line must reference its corresponding sales order line.

Cause:

QuickBooks requires two distinct records to represent a converted sales order: the InvoiceLine (the actual invoiced data) and the linking metadata (InvoiceLineLinkToTxnTxnID + InvoiceLineLinkToTxnTxnLineID). Inserting only InvoiceLine creates an orphan invoice line; without the link fields, QuickBooks does not know which sales order it fulfills.

Resolution:

  1. Create the InvoiceLine by SELECTing from SalesOrderLine. Copy the relevant columns (CustomerRefListID, RefNumber, Memo, Item ref, description, rate, amount, sales tax code) from the source sales order line.

    For a single-line sales order:
    INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
      "Memo", "InvoiceLineItemRefListID",
      "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount",
      "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
      SELECT "CustomerRefListID", {fn CONCAT('I', "RefNumber")} as "RefNumber",
      {fn CONCAT('Sales Order ', "RefNumber")} as "Memo",
      "SalesOrderLineItemRefListID", "SalesOrderLineDesc",
      "SalesOrderLineRate", "SalesOrderLineAmount",
      "SalesOrderLineSalesTaxCodeRefListID", 0 as "FQSaveToCache"
      FROM SalesOrderLine
      WHERE "CustomerRefListID"=''<CUSTOMER_LISTID>'
        AND "RefNumber"='<SO_REF_NUMBER>'
        AND "SalesOrderLineSeqNo"=1
  2. Create the link record. Insert a second InvoiceLine that references the original sales order's TxnID and line ID.

    For a single-line sales order:
    INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
      "InvoiceLineLinkToTxnTxnID", "InvoiceLineLinkToTxnTxnLineID",
      "FQSaveToCache")
      SELECT "CustomerRefListID", {fn CONCAT('SOLink', "RefNumber")},
      "TxnID", "SalesOrderLineTxnLineID",
      0 as "FQSaveToCache"
      FROM SalesOrderLine
      WHERE "CustomerRefListID"=''<CUSTOMER_LISTID>'
        AND "RefNumber"='<SO_REF_NUMBER>'
        AND "SalesOrderLineSeqNo"=1
  3. For multi-line sales orders: For all lines except the last, set FQSaveToCache to 1. On the final line, set it to 0. Loop through each SalesOrderLineSeqNo in order.

    For n lines:
    Line 1: FQSaveToCache = 1 (accumulate)
    Line 2: FQSaveToCache = 1 (accumulate)
    ...
    Line n: FQSaveToCache = 0 (commit to QuickBooks)

SQL Examples:

Single-line example - complete convert:

-- Step 1: Create the invoice line data
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
  "Memo", "InvoiceLineItemRefListID",
  "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount",
  "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
  SELECT "CustomerRefListID", {fn CONCAT('I', "RefNumber")},
  {fn CONCAT('Sales Order ', "RefNumber")},
  "SalesOrderLineItemRefListID", "SalesOrderLineDesc",
  "SalesOrderLineRate", "SalesOrderLineAmount",
  "SalesOrderLineSalesTaxCodeRefListID", 0
  FROM SalesOrderLine
  WHERE "CustomerRefListID"=''80000FBD-1705490688''
    AND "RefNumber"='201'
    AND "SalesOrderLineSeqNo"=1

-- Step 2: Link it to the original sales order
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
  "InvoiceLineLinkToTxnTxnID", "InvoiceLineLinkToTxnTxnLineID",
  "FQSaveToCache")
  SELECT "CustomerRefListID", {fn CONCAT('SOLink', "RefNumber")},
  "TxnID", "SalesOrderLineTxnLineID",
  0
  FROM SalesOrderLine
  WHERE "CustomerRefListID"=''80000FBD-1705490688''
    AND "RefNumber"='201'
    AND "SalesOrderLineSeqNo"=1

Multi-line example (pseudo-code logic):

-- For each SalesOrderLineSeqNo from 1 to @totalLines:
--   Step 1: Insert InvoiceLine data with FQSaveToCache = (seqNo < @totalLines ? 1 : 0)
--   Step 2: Insert link record with FQSaveToCache = (seqNo < @totalLines ? 1 : 0)
--   The last sequence number commits everything to QuickBooks

Example Scenarios:

Scenario 1 - Single-line SO converted to invoice:

A sales order (RefNumber=201) with one line item is invoiced. William executed the two INSERT statements above via the QODBC Test Tool, and Daniel confirmed: "Thank you! It works." The resulting invoice appears in QuickBooks linked to SO #201.

Scenario 2 - Multi-line sales order:

A company has a 5-line sales order. They loop through SalesOrderLineSeqNo 1 through 5. For seqNos 1–4, FQSaveToCache is set to 1 (buffer). On seqNo 5, FQSaveToCache is set to 0 (commit). QuickBooks processes all lines as a single invoice.

Additional Notes:

  • The link INSERT is mandatory. Without InvoiceLineLinkToTxnTxnID and InvoiceLineLinkToTxnTxnLineID, the invoice appears in QuickBooks but is not linked to the sales order - no fulfillment status updates on the SO.
  • FQSaveToCache controls batching. Setting FQSaveToCache to 1 queues the row in QODBC's cache; 0 commits it. For multi-line conversions, all lines except the last use 1.
  • RefNumber format: The example prepends "I" to the sales order number for the invoice RefNumber. You can customize this prefix or use your own numbering scheme.
  • Payment status: This process handles conversion before any payment is entered. If a payment has already been applied to the sales order, additional steps are needed to carry forward the payment to the invoice.
  • ItemGroup / Grouped Items workaround: When a Sales Order contains ItemGroup lines (grouped items), the standard two-INSERT approach does not work because the SDK does not support linking grouped line items. Instead, insert a single InvoiceLine with only the SalesOrder TxnID into LinkToTxnID1 and CustomerRefListID - no caching, no additional link records. QuickBooks creates the full invoice including all ItemGroup header and expanded child lines automatically.
    INSERT INTO "InvoiceLine" ("CustomerRefListID", "LinkToTxnID1", "FQSaveToCache")
    VALUES ('<CUSTOMER_LISTID>', '<SALESORDER_TXNID>', 0)

Creating a Sales Order via QODBC INSERT QODBC Optimizer CustomFieldOther on SalesOrderLine Official KB Article: How to Convert a Sales Order into an Invoice

Keywords:

SalesOrder InvoiceLine Convert Sales Order FQSaveToCache InvoiceLinkedTxn InvoiceLineLinkToTxnTxnID Multi-line Sales Order to Invoice INSERT + SELECT QuickBooks API
(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).