Knowledgebase
[QODBC-Desktop] How to convert an Estimate to a Sales Order using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Converting an Estimate to a Sales Order

CREATE AN ESTIMATE WITH ONE LINE

Query in QODBC

     The following SQL statement will create a new estimate:

     INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID",
     "EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
     "FQSaveToCache") VALUES ('470001-1071525403', '201', '250000-933272656',
     'Building permit No 201', 100.00000, 100.00, '20000-999022286', 0)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

Result in QuickBooks

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

See: How to create Estimates using QODBC for more about Estimates Creating.

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 to 1 instead (using:1 as "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.

 

(126 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).