Knowledgebase
[QODBC-ALL] How to export QuickBooks data not available in QODBC
Posted by Jack - QODBC Support on 30 December 2015 06:59 AM

How to export QuickBooks data not available in QODBC

Problem Description 1:

I have searched a lot of the tables connectable thru the QODBC and cannot find customer list information such as the customer's website, facebook, linked in, multiple phone numbers, alternative emails, etc.

Problem Description 2:

I'm trying to find the field that keeps track of attachments for a particular item. I can see the physical files are stored on the file system but I'm looking for the reference to those files. 

Solution:

I'm sorry to inform you that the field website, facebook, linked in, multiple phone numbers, alternative emails, attachment reference for a particular item are not available through the Intuit SDK so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn’t expose one feature to the application in SDK, QODBC could not do it either.

QODBC will, of course, support it when this feature is available through SDK. Please get in touch with Intuit with your request.

As a workaround, you can export same data from QuickBooks UI export feature & check if QuickBooks UI is providing that field or not.

You can export the data from QuickBooks UI and get rest of the information from QODBC and integrate both the information and can generate the report/application you are looking for.

For Example:

QuickBooks Desktop:

Exporting Customer data to Excel:

Navigate Customer Center from QuickBooks UI & click on Excel drop-down, click on Export Customer list...



Please check Excel file & look for field/data which you want.

QuickBooks Online:

You can export data from QuickBooks Online Web UI also.

Exporting Customer data to Excel:

Click on Customer & click on export to Excel icon.



Customer list will be downloaded in Excel file. Please check Excel file & look for field/data which you want.

Problem Description 3:

We are considering setting up the ODBC to allow us to customize a specific report. Before we do, we wanted to make sure that the ODBC database will allow us to filter by the "end date" field under customers/jobs. We need to create a P&L by Job report and filter on a range of end dates. I could not see that we could access this field.

When I look at the links on QODBC there are some with listings of fields. None of the fields include "End Date." We need to be able to run a P&L by Job report that filters on job date ended (only include jobs that were complete within a date range). Note: The end date is not the same as the transaction date filter, rather, it is located under customer center, then job info. Is end date (not transaction date) available to filter results?

Solution:

There is no parameter Job End Date is available from the QuickBooks SDK for ProfitAndLossByJob report.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn’t expose one feature to the application in SDK, QODBC could not do it either.

You can find JobEndDate from Customer table & You can export report data & table data in Excel sheet & can merge report data & Customer table data and can build report as per your requirement. 

First, You need to export the report in MS Excel sheet. 

For Example:

sp_report ProfitAndLossByJob show Amount_Title, Text, Label, Amount parameters DateFrom={d'2016-01-01'}, DateTo={d'2016-08-30'}

Please refer below mentioned link for exporting sp_report in MS Excel:
How to create sp_reports using Microsoft Excel

 

 

After that, You need to export Customer table data to other Excel sheet.

Please refer below mentioned link for exporting QuickBooks data in MS Excel:
How to Use QODBC with MS Excel 2016 / 365 / 2013 32-Bit

 

 

After that, You need to add the new column in sheet1 for showing JobEndDate data from sheet2. By using Excel vlookup formula you can get JobEndDate in sp_report.

 

(1 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).