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

An Example of Creating a Sales Order

Note: This example creates one Sales Order with two order lines. Note how the FQSaveToCache field is set to True except on the last line.

First Line

INSERT INTO "SalesOrderLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "SalesOrderLineItemRefListID", "SalesOrderLineDesc",             "SalesOrderLineQuantity", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID","FQSaveToCache") VALUES     ('120000-1045537156','C0000-1080110273','1015', '90000-1045537150', '4m Steel Ladder', 1.00000, 1.00000, 150.00, '90000-1045536338', 1)

Second (Last) Line

INSERT INTO "SalesOrderLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "SalesOrderLineItemRefListID", "SalesOrderLineDesc", "SalesOrderLineQuantity", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID","FQSaveToCache") VALUES ('120000-1045537156','C0000-1080110273','1015', '150001-1045625669', 'Paint Brush:Big', 1.00000, 1.00000, 11.60, '90000-1045536338', 0)

Related Data Description

CustomerRefListID is the existing Customer ListID found by using:

     Select * from Customer

TemplateRefListID is the Sales Order Template ListID found by using:

     Select * from Template

SalesOrderLineItemRefListID is the existing Inventory Item ListIDs found by using:

     Select ListID, FullName, Description, Type from Item

Sample Data Description for SalesOrderLineItemRefListID

     In my case, my Australian QuickBooks 2004 sample file returned the following items:

     In the Sales Order inserts above I used the following Inventory Items:

     Description: "4m Steel Ladder"      ListID: '90000-1045537150' (in Row 17 above)   and
     Description: "Paint Brush:Big"       ListID: '150001-1045625669' (in Row 25 above)

SalesOrderLineSalesTaxCodeRefListID is the SalesTaxCode ListID found by using:

     Select * from SalesTaxCode

Note: Outside USA, SalesOrderLineSalesTaxCodeRefListID is SalesOrderLineTaxCodeRefListID instead to make as follows.
SalesOrderLineTaxCodeRefListID the TaxCode ListID found by using:

     Select * from TaxCode

(167 vote(s))
Helpful
Not helpful

Comments (5)
Shonda Hector
29 January 2015 11:09 PM
So prior to this don't you have to write to the table 'SalesOrder' also?
Jack
23 February 2015 01:55 PM
Hi Shonda,

I would like to inform you that SalesOrderLine table is a child table SalesOrder table. When you add/update records in SalesOrderLine table it will automatically add/update to SalesOrder table. So you need to add/update InvoiceLine table.
Andrew Wang
10 September 2015 07:09 AM
When I ran the above 2 query lines, the first line is not imported, but the second line is. If I run both query with FQSaveToCache = 0. I can see that there are 2 lines in shown in Reckon Accounts.

Did I ran those two lines incorrectly?
Jack
10 September 2015 11:24 AM
Hi Andrew,

Multi-line query require a series of SQL statements to complete a single Transaction.
The key to this process is the field named "FQSaveToCache". This field is not part of the table, but is used as a flag to the QODBC driver.

A TRUE setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 or FALSE, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.

The “FQSaveToCache” should be 1 or 0. For multiple lines insert you sould set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the query until the last one setting FQSaveToCache to 0. For single line insert, if you want to use “FQSaveToCache”, you should set it to 0.
Andrew Wang
11 September 2015 12:00 AM
Thanks Jack. I ran each query using the VB demo application, that's why I didn't get desired result.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).