[QODBC-Desktop] Troubleshooting - Expenses written to BillExpenseLine are not showing as Billable when viewed in QuickBooks
Posted by Jack - QODBC Support on 09 March 2016 12:39 PM

Troubleshooting - Expenses written to BillExpenseLine are not showing as Billable when viewed in QuickBooks

Problem Description:

The field ExpenseLineBillableStatus contains the status of the BillExpenseLine and uses the following stored procedure "sp_columns" command in QODBC Test Tool:

sp_columns BillExpenseLine

shows the: ExpenseLineBillableStatus valid values as: |Billable|NotBillable|HasBeenBilled|

However, the following insert failed:-

INSERT INTO BillExpenseLine (VendorReflistid, APAccountRefFullName,txndate, RefNumber, ExpenseLineAccountRefFullname, ExpenseLineAmount, ExpenseLinememo, ExpenseLineBillableStatus, ExpenseLineTaxCodeReffullname, FQSaveToCache ) VALUES ('290000-1045709633', 'Accounts Payable', {d'2006-02-01'}, '111111', 'Boosters COGS', 244, '2 of 222233433 aaaaaabbbbbca', 'Billable', 'NCG',0)

With an "Insert on a linked table, "BillExpenseLine' failed" error.


For the Billable, NotBillable, and HasBeenBilled flags to be used on a Bill Expense Line within QuickBooks, a Customer Job "ExpenseLineCustomerRefListID" must also be assigned.

To create a BillExpenseLine with a "Billable" status using two SQL insert statements:

Statement 1

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

Statement 2

INSERT INTO "Bill" ("VendorRefListID," "APAccountRefListID," "TxnDate," "RefNumber," "TermsRefListID," "DueDate," "Memo") VALUES ('200000-1045537156', '3D0000-1045609540', {d'2006-02-02'}, '20061', '20000-1045536343', {d'2006-03-02'}, 'QODBC Bill Expense Memo Test')

The key to this process is in the field named "FQSaveToCache." This field is not part of the table but is used as a flag to the QODBC driver. In the sequence above, you should note that the value of "FQSaveToCache" is set to 1 (trustworthy) in the first statement.

A proper (1) setting of "FQSaveToCache" instructs QODBC to take the values from the INSERT statement and hold them for later processing but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 (false) or not specified, as shown in the second statement, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection and saved as a batch into QuickBooks.

Note: The BillExpenseLine.ExpenseLineAccountRefListID must be an asset type. You cannot use a COGS (cost of goods sold) type account.

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