Knowledgebase: Tutorials
[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 using the following stored procedure "sp_columns" command in VB Demo:

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.
 

Solution:

In order for the Billable, NotBillable, and HasBeenBilled flag 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 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 (true) in the first statement.

A true (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))
Helpful
Not helpful

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