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 many of the tables connectable thru the QODBC. I 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, and attachment references for a particular item are unavailable through the Intuit SDK. Hence, 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 the same data from the QuickBooks UI export feature & check if QuickBooks UI is providing that field.

You can export the data from QuickBooks UI, get the rest of the information from QODBC, integrate the knowledge, and 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 the Excel drop-down, and click on Export Customer list...



Please check the Excel file & look for the fields/data 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.



The customer list will be downloaded in an Excel file. Please check the Excel file & look for the fields/data 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 ensure 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 areas include "End Date." We need to be able to run a P&L by Job report that filters on job date ended (only have jobs that were completed within a date range). Note: The end date is not the same as the transaction date filter; instead, it is located under the customer center, then job info. Is the end date (not the transaction date) available to filter results?

Solution:

No parameter Job End Date is available from the QuickBooks SDK for the 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 the Customer table & export report data & table data in an Excel sheet & you can merge report data & Customer table data and build reports 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 must export Customer table data to another Excel sheet.

Please refer below to the link mentioned 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 sheet 1 to show JobEndDate data from sheet 2. By using the Excel lookup formula, you can get JobEndDate in sp_report.

 

 

 

How to contact Intuit: http://qodbc.com/links/3058

 

 

Tags: QuickBooks Online, QBO

 

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