OpenSync

Home Forums OpenSync Out of date BillableStatus on TxnExpenseLineDetail table

This topic contains 10 replies, has 2 voices, and was last updated by  Yudel Rosales 1 day, 11 hours ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #65906

    kenbrey
    Participant

    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.

    #65909

    Yudel Rosales
    Keymaster

    If the billing status is empty (that is, if no billing status appears in QuickBooks), then no BillableStatus value will be returned.

    #65910

    kenbrey
    Participant

    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.

    #65912

    Yudel Rosales
    Keymaster

    On that table, you have CustomerRef_ListID and CustomerRef_FullName. Just query these fields.

    #65913

    kenbrey
    Participant

    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.

    #65964

    kenbrey
    Participant

    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.

    #65968

    Yudel Rosales
    Keymaster

    great to hear that.

    #66001

    kenbrey
    Participant

    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?

    #66112

    Yudel Rosales
    Keymaster

    with all the tables that have expenselinedetail.

    #66113

    Yudel Rosales
    Keymaster
    #66125

    Yudel Rosales
    Keymaster

    Which version of OpenSync are you running? because I am assuming that you are running the latest one. QuickBooks version?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.