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

Example of Creating Bills using BillItemLine Table

Create One Bill with Three Lines

Note: This creates one bill with three lines. Note the FQSaveToCache field, set to True except on the last line.

Note: The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it.
You cannot insert into Bill table without inserting the child table(s) like BillExpenseLine / BillItemLine.  Please do not close connection between the Insert Bill / BillExpenseLine / BillItemLine otherwise you will not be able to get last inserted ID.

First Line

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, 1)

Second Line

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, 1)

Third (Last) Line

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, 0)

Create Three Bills with One Line Each

Note: This creates three bills with one line each. Note the FQSaveToCache field is not specified (or can be set to false)

First Bill

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00)

Second Bill

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00)

Third Bill

INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00)

Create One Bill with Three Item Lines with Shared Header Information

Note: This creates one bill with three item lines. Note the FQSaveToCache field, set to True. Saving the Bill saves the lines with it.

First Line

INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000, 1.00, 1)

Second Line

INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000, 2.00, 1)

Third Line

INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, 1)

Shared Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('10000-933272655', 'C0000-933270541', {d'2002-10-01'}, '1', '10000-933272658', {d'2002-10-31'}, 'Memo Test')

 

Example of Creating Bills using BillExpenseLine Table

Create One Bill with Two Expense Lines with Shared Header Information

Note: This creates one bill with two expense lines. Note the FQSaveToCache field, set to True. Saving the Bill saves the lines with it.

First Line

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "FQSaveToCache") VALUES ('120000-933270541', 436.07, 'Expense Line Memo Test 1', '6E0000-1071509585', 1)


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

 

Second Line

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "FQSaveToCache") VALUES ('120000-933270541', 436.06, 'Expense Line Memo Test 2', '6E0000-1071509585', 1)

Shared Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('720000-1071512482', 'C0000-933270541', {d'2002-10-01'}, '1', '20000-933272658', {d'2002-10-31'}, 'Expense Memo Test')

Result in QuickBooks

Special Instructions and Example for Australian Users

Note: For Australian users, use the following SQL Statements using ExpenseLineTaxCodeRefListID (for GST) and these modified values to create a single line expense bill for "Optical Phone Networks" in the sample Stadium Construction company file supplied with QuickBooks in Australia located at :

       C:\Program Files\Intuit\QuickBooks Premier\Stadium Construction and Hardware Pty Ltd QB Premier.qbw

Single Expense Line

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineTaxCodeRefListID", "FQSaveToCache") VALUES ('270000-1045536344', 500.00, 'QODBC Expense Line Memo Test 1', 'C0000-1045536338', 1)

Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('200000-1045537156', '3D0000-1045609540', {d'2004-11-17'}, '1', '20000-1045536343', {d'2004-12-31'}, 'QODBC Bill Expense Memo Test')

Related Data Location

Note: Never enter a tax value as QuickBooks generates the tax amount based upon the ExpenseLineTaxCodeRefListID being used. ExpenseLineTaxCodeRefListID is the ListID for GST found in the TaxCode Table.

      Use the following SQL Statement in VB Demo to locate the ListID:

      SELECT * FROM TaxCode

Also Note: The ExpenseLineCustomerRefListID or ExpenseLineAccountRefFullName is found by doing something like:

      select ListID, Fullname from customer where FullName like 'V%'

 

Example of Creating Bills using ExpenseLine without ExpenseLineCustomerRefListID

Note: You can actually leave out ExpenseLineCustomerRefListID all together, you just can't say you are going to use it, and then try to insert a null.

Create a Single Line Expense Bill without ExpenseLineCustomerRefListID

The following example also works:

Single Expense Line

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('120000-933270541', 436.07, 'Expense Line Memo Test 100', 1)

Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('720000-1071512482', 'C0000-933270541', {d'2002-10-01'}, '100', '20000-933272658', {d'2002-10-31'}, 'Expense Memo Test')

 

ListID and FullName References

Note: When doing INSERTs you need to use either the ListID or FullName reference, so if you know the Vendor's name and Account Name you can select to use the FullName like this:

INSERT INTO "Bill" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "RefNumber", "DueDate", "Memo")
VALUES ('ABC', 'Accounts Payable', {d'2006-06-26'}, '100', {d'2006-06-30'}, 'Expense Memo Test 5' )

instead of the ListIDs like this:

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "DueDate", "Memo")
VALUES ('10000-933272655', 'C0000-933270541', {d'2002-10-01'}, '100', {d'2002-10-31'}, 'Memo Test')


Example of a VB Script Code about Bill Insert

code in vbscript

Option Explicit
Const vbNormal = 1

const adOpenStatic = 3
Const adLockOptimistic = 3
Const AdUseClient = 3

Dim oRecordset3, oConnection3, sSQL, ssSQL

Set oConnection3 = CreateObject("ADODB.Connection")
oConnection3.Open "DSN=Quickbooks Data;OLE DB Services=-2"
sSQL = "SELECT * FROM BillExpenseLine WHERE TxnId = 'X'"

Set oRecordset3 = CreateObject("ADODB.Recordset")
oRecordset3.Open sSQL, oConnection3, adOpenStatic, adLockOptimistic

oRecordset3.Addnew()
oRecordset3.Fields("ExpenseLineAccountRefListID").Value = "8C0001-1197740053"
oRecordset3.Fields("ExpenseLineAmount").Value = 150000
oRecordset3.Fields("ExpenseLineMemo").Value = "TESTMEMO0000"
oRecordset3.Fields("ExpenseLineCustomerRefListID").Value = "890000-1047591941"
oRecordset3.Fields("FQSaveToCache").Value = 1
oRecordset3.Update()

ssSQL = "SELECT * FROM Bill WHERE TxnId = 'X'"

Set oRecordset3 = CreateObject("ADODB.Recordset")
oRecordset3.Open ssSQL, oConnection3, adOpenStatic, adLockOptimistic

oRecordset3.Addnew()
oRecordset3.Fields("VendorRefListID").Value = "2F0004-1197735443"
oRecordset3.Fields("APAccountRefListID").Value = "C0000-896817249"
oRecordset3.Fields("TxnDate").Value = "2/16/2007"
oRecordset3.Fields("RefNumber").Value = "TEST123NUMB"
oRecordset3.Fields("TermsRefListID").Value = "20000-898307885"
oRecordset3.Fields("DueDate").Value = "3/16/2007"
oRecordset3.Fields("Memo").Value = "TESTMEMO2"
oRecordset3.Update()

oRecordset3.Close
oConnection3.Close

msgbox "end of process"
WScript.Quit

Data Description

'********************************//****************************************
'*TABLE DETAIL REFERENCE: BillExpenseLine
'*
'*FIELD RELATIONSHIPS: (BillExpenseLine ) . ExpenseLineAccountRefListID
'*TABLE FIELD CATEGORY
'*Account ListID List
'*("ExpenseLineAccountRefListID").Value = "8C0001-1197740053"
'*
'*FIELD RELATIONSHIPS: (BillExpenseLine ) . ExpenseLineCustomerRefListID
'*TABLE FIELD CATEGORY
'*CustomerListID List
'*("ExpenseLineCustomerRefListID").Value = "890000-1047591941" stern
'*
'*TABLE DETAIL REFERENCE: Bill
'*FIELD RELATIONSHIPS: (Bill ) . VendorRefListID
'*TABLE FIELD CATEGORY
'*Vendor ListID List
'*("VendorRefListID").Value = "2F0004-1197735443" german
'*
'*FIELD RELATIONSHIPS: (Bill ) . APAccountRefListID
'*TABLE FIELD CATEGORY
'*Account ListID List
'*("APAccountRefListID").Value = "C0000-896817249" german
'*
'*FIELD RELATIONSHIPS: (Bill ) . TermsRefListID
'*TABLE FIELD CATEGORY
'*Terms ListID List
'*("TermsRefListID").Value = "20000-898307885" net 30
'********************************//****************************************

Result in QuickBooks




Example of Insert Both BillItems and BillExpenses into One Bill

Note: You can insert both billitems and billexpenses into one bill but you need to first create either the Expense or Item lines and then add the Item or Expense lines afterwards like below.

Create the Expense Lines

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount",
"ExpenseLineMemo", "FQSaveToCache")
VALUES ('120000-933270541', 436.07,
'Expense Line Memo Test', 1)

Write the Bill Header

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber",
"TermsRefListID", "DueDate", "Memo")
VALUES ('10000-933272655', 'C0000-933270541', {d'2007-10-01'}, '905',
'10000-933272658', {d'2007-10-31'}, 'Test Expense and Item Lines')

Determine the TxnID of the new Bill

sp_lastinsertID Bill

Use the TxnID to append Item lines to the existing Bill

The LastInsertID returned was: 5CAD-1197762987

INSERT INTO "BillItemLine" ("TxnID", "ItemLineItemRefListID",
"ItemLineDesc", "ItemLineCost", "ItemLineAmount")
VALUES ('5CAD-1197762987', '250000-933272656',
'Building permit 1', 1.00000, 1.00)

Result in QuickBooks

This results in the following Bill in QuickBooks with both a Expense and Item line:


 


Example of Creating a Bill with One Item on QuickBooks Online Edition

That would look something like:

INSERT INTO "BillExpenseLine" ("VendorRefListID", "TxnDate", "RefNumber",
"ExpenseLineAccountRefListID",
"ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache")
VALUES ('2', {d'2007-11-22'}, 'TestRefNum_1',
'22', 436.07, 'QBOE Expense Line Memo Test', 0)

Note: Where "VendorRefListID" is found from the Vendor table and "ExpenseLineAccountRefListID" is found from the Account table

 

Keywords : accountlistid

(161 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).