OpenSync

Home Forums OpenSync Accessing QuickBooks Report Stored Procedures

This topic contains 10 replies, has 3 voices, and was last updated by  nrg 4 years, 9 months ago.

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

    nrg
    Participant

    We are considering using OpenSync. We currently run QB sp_report stored procedures to download information to an Access database. The procedure is, every 15 minutes, an Access table matching the SP’s fields is cleared, and the SP is run and its data appended to the empty table. The users use this to get customer accounting information while in our Access program.

    Can something similar be done with OpenSync? If we created a report query with certain parameters, would OpenSync be able to keep it synced with its database, and then we would pull the data from the synced database?

    Or, if not, is there a way at all to access QB’s stored procedures with OpenSync?

    Thanks!

    #16781

    Tom Crawford
    Keymaster

    Sorry for the delayed reply. The stored procedures in the ODBC driver are only simulated, they are not true stored procedures in QB. OpenSync provides similar functionality be enabling you to create tasks that dump the report data to database tables.

    #16788

    nrg
    Participant

    OK, so right now we have a few queries to pull data from the simulated stored procedures. They are:

    sp_report CustomSummary show RowData , Amount_1 parameters DateFrom = {d”}, DateTo = {d”}, ClassFilterFullNames = ‘MB – TXM’, SummarizeRowsBy = ‘Customer’, SummarizeColumnsBy = ‘TotalOnly’, Calendar = ‘FiscalYear’, ReturnRows = ‘ActiveOnly’, ReturnColumns = ‘ActiveOnly’, AccountFilterType = ‘CurrentAssetAndExpense’ WHERE RowType=’DataRow’

    sp_report CustomerBalanceSummary show Text, Label, Amount_1 parameters ClassFilterFullNames = ‘MB – TXM’, DateMacro = ‘All’, SummarizeColumnsBy = ‘TotalOnly’

    sp_report TxnListByCustomer show Text as CustomerJob, TxnType as Type, Date, RefNumber as Num, Memo, Account, BillingStatus, Amount, ModifiedTime parameters DateMacro = ‘All’, ReportDetailLevelFilter = ‘All’, ClassFilterFullNames = ‘MB – TXM’, TxnFilterTypes = ‘Bill’, ‘Check’, ‘CreditCardCharge’, ‘ItemReceipt’, ‘VendorCredit’

    And so on.

    You’re saying that I can create tasks that use that same code to dump the report data into database tables?

    Or are you saying that I can’t use the sp_report code, but would have to rebuild the results they produce manually by querying the database tables where your product stores QB data?

    Note that each of these simulated stored procedures pulls data from multiple tables. So recreating the resultsets manually would be a lot more work than just calling an sp_report simulated stored procedure.

    #16794

    Tom Crawford
    Keymaster

    In OpenSync you would need to create three tasks to dump the Custom Summary Report, the Customer Balance Report, and the Transaction List By Customer report to database tables. You would then need to write SQL to query these tables based on your parameters.

    #16820

    nrg
    Participant

    The reports do more than provide raw data. They also summarize data by customer for a date range. How would that be accomplished if we’re simply pulling down all data from the report without giving it the date range?

    #16840

    Yudel Rosales
    Keymaster

    You can select the date range in the select report windows? Have you tried it?

    #16848

    nrg
    Participant

    My questions are pre-purchase questions. And, as you can see by the parameters I’m including in the report queries we’re using, we’re parameterizing it by more than date range. I’m trying to discern with the same parameters can be used with your downloaded report data, and, if so, then how? Thanks.

    #16854

    Yudel Rosales
    Keymaster

    Have you used OpenSync? You can have a 30 days trial.

    #16858

    nrg
    Participant

    I work for a client who is currently using QODBC to integrate QuickBooks with Access, but is having some problems with it. I am exploring your product as a potential replacement for QODBC. But one of the things the client insists on is that we’re able to get info from the reports as we do now. He doesn’t want to have to manually query multiple QuickBooks tables to produce the same data.

    So take, for example, this query:

    sp_report TxnListByCustomer show Text as CustomerJob, TxnType as Type, Date, RefNumber as Num, Memo, Account, BillingStatus, Amount, ModifiedTime parameters DateMacro = ‘All’, ReportDetailLevelFilter = ‘All’, ClassFilterFullNames = ‘MB – TXM’, TxnFilterTypes = ‘Bill’, ‘Check’, ‘CreditCardCharge’, ‘ItemReceipt’, ‘VendorCredit’

    It filters for ‘Bill’, ‘Check’, ‘CreditCardCharge’, ‘ItemReceipt’, ‘VendorCredit’. To get that data from QuickBooks, we’d have to query multiple tables. But the report combines the data for us.

    So, if we use your product to get data from the TxnListByCustomer report, can we do the same thing? Whether the filtering is through your product, or it’s done through Access, doesn’t matter. The point is we want to run a single query to get this information, not have to query multiple tables. Can your product do that?

    I need to get a clear answer on that before we can install your product on a trial basis.

    Thanks!

    #16878

    Tom Crawford
    Keymaster

    OpenSync will dump the Transaction List By Customer report to a database table. When you configure a task to do this you can apply filters for date, class, transaction type, and customer. If you do not apply these filters, then you can use SQL in Access to filter the results from the resulting table.

    #16895

    nrg
    Participant

    OK, that’s the clearest answer I’ve gotten so far, and is what I was looking to know. Thank you.

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

The forum ‘OpenSync’ is closed to new topics and replies.