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 first to save the data to the child record. The child record for each parent/child pair has all the data required by the parent record.
You cannot insert into the Bill table without inserting the child table(s) like BillExpenseLine / BillItemLine. Please do not close the connection between the Insert Bill / BillExpenseLine / BillItemLine. You will not be able to get the 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 ('370001-909762877', 436.07, 'Expense Line Memo Test 1', '4C0000-1040154668', 1)

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

Second Line

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "FQSaveToCache") VALUES ('370001-909762877', 436.06, 'Expense Line Memo Test 2', '4C0000-1040154668', 1)

Shared Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('390000-1039739488', 'C0000-896817249', {d'2021-12-15'}, '1', '50000-898307888', {d'2021-12-31'}, 'Expense Memo Test')

Result in QuickBooks

Special Instructions and Examples 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 on 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 'D%.'

Example of Creating Bills using ExpenseLine without ExpenseLineCustomerRefListID

Note: You can leave out ExpenseLineCustomerRefListID altogether; you can't say you will 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 ('370001-909762877', 436.07, 'Expense Line Memo Test 100', 1)

Header Information

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('390000-1039739488', 'C0000-896817249', {d'2021-12-15'}, '100', '50000-898307888',{d'2021-12-31'}, 'Expense Memo Test')

Result in QuickBooks

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 the 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 BillItems and BillExpenses into one Bill, but you need to create either the Expense or Item lines and then add the Item or Expense lines afterward, like below.

Create the Expense Lines

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

Write the Bill Header

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber",
"TermsRefListID", "DueDate", "Memo")
VALUES ('390000-1039739488', 'C0000-896817249',{d'2021-12-15'}, '905',
'50000-898307888', {d'2021-12-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: 8C19-1481810539

INSERT INTO "BillItemLine" ("TxnID", "ItemLineItemRefListID",
"ItemLineDesc", "ItemLineCost", "ItemLineAmount")
VALUES ('8C19-1481810539', '8C0003-1044564690',
'Building permit 1', 1.00000, 1.00)

Result in QuickBooks

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

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

That would look something like this:

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: account listed