[QODBC-Desktop] How to Import Bill with Expense line and Item line using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Importing a Bill with Item Line and Expense Line

Note: It's simple, but you need to first create either the Expense or Item lines first and then add the Item or Expense lines afterward like below.

Note: The primary rule is to save the data to the child record first. The child record for each parent/child pair has all 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.

Create the Expense Line

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

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


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

Note: Please do not close connection between the Insert Bill / BillExpenseLine / BillItemLine otherwise you will not be able to get last inserted ID. 

Use the TxnID to append Item lines to the existing Bill

Note: 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 an Expense and Item line:

(149 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).