[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 must create either the Expense or Item lines first and then add the Item or Expense lines afterward, like below.

Note: The primary rule is first to save the data to the child record. The child record for each parent/child pair includes all the data required by the parent record.
You cannot insert into the Bill table without inserting the child table(s) like BillExpenseLine / BillItemLine.

Create the Expense Line

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

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


Write the Bill Header

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

Determine the TxnID of the new Bill

sp_lastinsertID Bill

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

Use the TxnID to append Item lines to the existing Bill

Note: The LastInsertID returned was: 8C47-1481796786

INSERT INTO "BillItemLine" ("TxnID", "ItemLineItemRefListID",
"ItemLineDesc", "ItemLineCost", "ItemLineAmount")
VALUES ('8C47-1481796786', '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:

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