An Example of Converting a Sales Order into an Invoice

Note: To understand the process for everyone, I will do a simple one-line estimate example, make it a Sales Order, and then make it into an Invoice.

TO CREATE THE ESTIMATE

Query in QODBC

The following SQL statement will create a new estimate:

     INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID",
     "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('3D0000-1040150817', '201', '6F0003-1049234090',
     'Building permit No 201', 100.00000, 100.00, '20000-1011136881', 0)

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Result in QuickBooks

The above query results in the following estimate in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:

CREATE A SALES ORDER FROM AN ESTIMATE

Query in QODBC

When we're ready to create the Sales Order, we can read the EstimateLine table and insert it into the SalesOrderLine table like this:

      INSERT INTO "SalesOrderLine" ("CustomerRefListID", "RefNumber", "Memo", "SalesOrderLineItemRefListID",
      "SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
      "FQSaveToCache")
      Select "CustomerRefListID", "RefNumber", {fn CONCAT('Estimate ', "RefNumber")} as "Memo","EstimateLineItemRefListID",
      "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
      "FQSaveToCache" from EstimateLine where "RefNumber"='201' and "EstimateLineSeqNo"=1

Note: This is one complete SQL statement. For multiple estimate lines, you would set FQSaveToCache to1 instead (using:1 "FQSaveToCache") and loop the EstimateLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The estimate now appears as a Sales Order in QuickBooks with "Estimate 201" in the memo field, the same way QuickBooks creates a Sales Order from an Estimate.

CREATE AN INVOICE FROM THE SALES ORDER

Query in QODBC

When we're ready to create the Invoice, we can read the SalesOrderLine table and insert it into the InvoiceLine table like this:

      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",
      "FQSaveToCache" from SalesOrderLine where "CustomerRefListID"='3D0000-1040150817':and "RefNumber"='201'
      and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement. For multiple estimate lines, you would set FQSaveToCache to 1 instead (using:1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The Sales Order now appears as an Invoice In QuickBooks. But is unlinked to the Sales Order line at this stage.

CREATE A LINKED INVOICE FROM THE SALES ORDER

Query in QODBC

When we're ready to create the Invoice, we can read the SalesOrderLine table and insert it into the InvoiceLine table as a linked Invoice instead like this:

      INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
      "InvoiceLineLinkToTxnTxnID", "InvoiceLineLinkToTxnTxnLineID",
      "FQSaveToCache")
      Select "CustomerRefListID", {fn CONCAT('SOLink', "RefNumber")},
      "TxnID", "SalesOrderLineTxnLineID",
      0 as "FQSaveToCache" from SalesOrderLine
      where "CustomerRefListID"='3D0000-1040150817':and "RefNumber"='201'
      and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement; for multiple estimate lines, you would set FQSaveToCache to 1 instead (using:1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

Result in QuickBooks

The query above results in the following linked Invoice in the QuickBooks 2022 Enterprise USA Edition - Sample company file:

And because there was only one line in the example Sales Order, the Sales Order has also been marked "INVOICED IN FULL."