OpenSync

Home Forums OpenSync Items List and Account Association Changes – Where are they stored?

This topic contains 5 replies, has 2 voices, and was last updated by  Tom Crawford 3 years, 2 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #14385

    gbronson
    Premium Member

    In 2015 the client created an Item and associated it with a certain Account. Then in 2016 they changed the Account for this Item. In QB it keeps track of all the 2015 accounts associated with the old account and all new Items that are now associated with the new account. The problem I have is when i generate queries in SQL for my reports i need to know which Accounts are being used but in the SQL tables I cannot find any way to link to table to see if the Account was changed or not. The query only returns the results using the latest account that it was changed to and is throwing off all my numbers. Is there some sort of historical change table regarding Items and Accounts?

    #14387

    Tom Crawford
    Keymaster

    Unfortunately, QB does not provide API access to the change history. You may be able to abtain the information by dumping a report to a DB table though.

    #14389

    gbronson
    Premium Member

    Boy i wish I would have known that earlier. I have lots of hours wrapped up in this project. But Ok. I had to dump reports for the budget data so why not this. Any idea what report i would think about running to get the change history of at least the items and account associations?

    Thanks.

    #14392

    Tom Crawford
    Keymaster

    I’d look at the audit trail first. You may also be able to use the transaction detail by account report.

    #14404

    gbronson
    Premium Member

    A solution for this is looking like it is going to get a bit complicated so I wanted to make sure i was clear on the problem i was having. I don’t need to really look up when an account assigned to an item was changed and by who or anything like that. I just need to run a SQL query and have the results match what i see in a report in QB. If i run a report for 2015 in QB it will accurately show the amounts and somehow know that an account was assigned to an item differently in 2015. When i run a report for 2016 or at any time after the account was reassigned to a different account, QB also knows that and displays the proper amounts and account information in the details. I assumed this historical entries would be stored in the transaction table but it doesn’t seem to be the case. I just thought i needed to link to another table to match up the right item and account. Shouldn’t the transaction table hold all the historical data?

    #14421

    Tom Crawford
    Keymaster

    The transaction table will not hold this information. Your best bet is to dump the corresponding sales report to a database table and use that as the source of your information. That way, you can be sure your information will match what QB reports.

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

You must be logged in to reply to this topic.