|
[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 StatementsProblem 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) Symptoms:
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:
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:
Related Articles: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
| |
|
|


