I am trying to query for which expenses have been applied to a customer invoice (and therefore billed for). The BillableStatus field in the TxnExpenseLineDetail table seems like a logical place to get this info, however it doesn’t seem to work. Some even with most expenses having been invoiced, most of the BillableStatus values in this table are still Billable (after successful table re-populate). I can’t figure out what this field is telling me, or where else to go to find the items which have been invoiced.
If the billing status is empty (that is, if no billing status appears in QuickBooks), then no BillableStatus value will be returned.
I’m not receiving empty values, I continue to receive a Billable value for records which have been applied to invoices, so they should come through as HasBeenBilled.
On that table, you have CustomerRef_ListID and CustomerRef_FullName. Just query these fields.
CustomerRef_ListID and CustomerRef_FullName tell me the job that the expense applies to. These are set when the expense is entered. What I want to know is if the expense has been applied to an invoice.
I’m trying to duplicate the Unbilled Costs by Job report that Quickbooks generates. But the Billed/Unbilled selector seems to be the BillableStatus field, and it’s just not updating to HasBeenBilled.
Yudel, we have determined that a change in the QB database on the BillableStatus field of the TxnExpenseLineDetail table simply does not trigger an update in the OpenSync SQL table. We have found that if we subsequently modify the Memo – by adding a character on the end – then OpenSync will update the record will update in SQL. After it updates the value for BillableStatus matches the value in the QB database. For instance, we had a record which was showing in SQL BillableStatus = Billable, which had been applied to an invoice, therefore should have had value HasBeenBilled. We modified its memo by adding a character. After the next OpenSync refresh, the new memo appeared in SQL, along with the BillableStatus change to HasBeenBilled.
great to hear that.
After clearing the contents of the txnexpenselinedetail table, and performing a full Re-polulate on Transaction, the txnexpenselinedetail did not refill. All we are getting is incremental updates of the latest data. Which repopulate action does this table go with?
with all the tables that have expenselinedetail.
Which version of OpenSync are you running? because I am assuming that you are running the latest one. QuickBooks version?
You must be logged in to reply to this topic.