Although I don't see the requirement using sp_columns BillExpenseLine, try adding the ExpenseLineAccountRefListID as per the following example which creates one bill with three expense lines. Note the FQSaveToCache field, set to True. Saving the Bill sets FQSaveToCache field to False and saves the BillExpenseLine lines with it.
BillItemLine
This creates one bill with three lines. Note the FQSaveToCache field, set to True except on the last 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)
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00, 1)
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00, 0)
This creates three bills with one line each. Note the FQSaveToCache field is not specified (or can be set to false)
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00)
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 2', 2.00000, 2.00)
INSERT INTO "BillItemLine" ("VendorRefListID", "RefNumber", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount") VALUES ('10000-933272655', '1', '250000-933272656', 'Building permit 3', 3.00000, 3.00)
This creates one bill with three item lines. Note the FQSaveToCache field, set to True. Saving the Bill saves the lines with it.
INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000, 1.00, 1)
INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000, 2.00, 1)
INSERT INTO "BillItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, 1)
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')
BillExpenseLine
This creates one bill with two expense lines. Note the FQSaveToCache field, set to True. Saving the Bill saves the lines with it.
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "FQSaveToCache") VALUES ('120000-933270541', 436.07, 'Expense Line Memo Test 1', '6E0000-1071509585', 1)
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "FQSaveToCache") VALUES ('120000-933270541', 436.06, 'Expense Line Memo Test 2', '6E0000-1071509585', 1)
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')
and results in the following Bill in QuickBooks:
or 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
INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineTaxCodeRefListID", "FQSaveToCache") VALUES ('270000-1045536344', 500.00, 'QODBC Expense Line Memo Test 1', 'C0000-1045536338', 1)
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')
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
Please Note: The ExpenseLineCustomerRefListID or ExpenseLineAccountRefFullName is NOT required on all BillExpenseLine inserts.
|
