Problem Description 1:

How to connect to QuickBooks Data with Power BI using QODBC Desktop?

Solutions: 

Open the QODBC setup screen and change the QODBC Compatibility mode from "Default" to "3.8."

To change, please follow the steps below:

Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go To "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

Start QuickBooks and log in to the QuickBooks company file as the QuickBooks user Admin.

Start Microsoft PowerBI Desktop (32-bit).

Navigate to the "Get Data" option, and from the drop-down list, select "More."

A pop-up with the title "Get Data" should appear.

Select "Other" --> "ODBC" and click the "Connect" button.

Select the "QuickBooks Data" from the Data source name (DSN) list and click the "OK" button.

Note: If you are using QODBC version 19.0.0.333 or lower, use the "Advanced options">> Write the SQL statement for the table you want to fetch data from.

For example, "select * from customer" and click the "OK" button.

It would be best if you got the preview of the table. Click the "Load" button to continue.

Select the fields you want to display on the report. This will add the fields to the information and display the data.

If you are facing "Driver does not support this parameter," please switch QODBC, QRemote 32-Bit DSN, and QRemote 64-Bit DSN to ODBC Compatibility 3.8.

To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

If you are using Power BI 64-bit, you need to change to QODBC and QRemote 64-bit clients. To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

To change compatibility in QRemote 32-bit, follow the path below:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

For QODBC POS, select DSN "QuickBooks POS Data QRemote."

For QODBC Online, select DSN "QuickBooks Online Data QRemote."

To change compatibility in QRemote 64-bit, follow the path below:
Start >> All Programs >> QODBC Driver for use with QuickBooks (64-Bit)>> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data 64-bit QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

For QODBC POS, select DSN "QuickBooks POS Data 64-Bit QRemote."

For QODBC Online, select DSN "QuickBooks Online 64-Bit Data QRemote."

Try connecting again. It should fix the issue you are facing.
If you are using the older version of Power BI, please download the latest and try connecting with QODBC.

Problem Description:

I am using QODBC for working with QuickBooks. I can delete the VehicleMileage record. 

After deleting a record from the VehicleMileage table in QuickBooks, the deleted record continues to appear when querying the table through QODBC.

For example:

SELECT * FROM VehicleMileage;

The deleted record is still returned in the query results.

However, the following UNOPTIMIZED query returns the correct data:

SELECT * FROM VehicleMileage UNOPTIMIZED;

Solution:

This behavior in the "VehicleMileage" table is due to a limitation in the QuickBooks SDK.

QODBC's Optimizer relies on the TxnDeleted information provided by the QuickBooks SDK to identify and remove deleted transaction records from its optimized cache. Unfortunately, the QuickBooks SDK does not report deleted VehicleMileage records through the TxnDeleted mechanism. This issue is limited to VehicleMileage table and does not affect other tables.

As a result, QODBC is unable to detect that a VehicleMileage record has been deleted, causing the Optimizer to continue returning the deleted record.

VehicleMileage table issue is a QuickBooks SDK issue and is outside the control of QODBC. The issue has been reported to Intuit for resolution.

Resolution:

To work around this QuickBooks SDK limitation, QODBC Version 18.0.0.326 introduced a change to the VehicleMileage table.

Beginning with QODBC v18.0.0.326:

• The VehicleMileage table bypasses the Optimizer.
• Data is retrieved directly from QuickBooks.
• Deleted VehicleMileage records are no longer returned in query results.

No changes to your SQL queries are required after upgrading to version 18.0.0.326 or later.

Workaround for Earlier Versions:

If you are using QODBC v18.0.0.325 or any earlier version, use the UNOPTIMIZED keyword when querying the VehicleMileage table to retrieve the latest data directly from QuickBooks.

Example

SELECT * FROM VehicleMileage UNOPTIMIZED;

Using the UNOPTIMIZED keyword bypasses the Optimizer and returns the current records from QuickBooks, including the correct deletion status.

Summary:

• Affected Versions: QODBC v18.0.0.325 and earlier
• Cause: QuickBooks SDK does not report deleted VehicleMileage records through the TxnDeleted API.
• Fixed In: QODBC v18.0.0.326 and later
• Workaround: Use the UNOPTIMIZED keyword or upgrade to QODBC v18.0.0.326 or later.

Problem Description:

I can access the database, but when I added another table & fetch table data, it generally hung up. Not sure if I need to change anything. Any thoughts?

Solution:

When accessing any table for the first time, QODBC will bring table-specific data to the optimizer file to increase query retrieval performance.

Once the optimizer is built, accessing the same table will be much faster compared to accessing it for the first time or without using the optimizer.

Please refer to How Optimizer Works for more details.

If you see the QODBC icon (not the hourglass) in the System Tray, it means QODBC is connected to QuickBooks, and currently, there is no query executing.

When QODBC hangs up, or your application goes to Not Responding mode. You can check what QODBC is performing from the Windows System Tray.

You will notice the QODBC icon in the Windows System Tray showing an hourglass.

If you want to see what QODBC is performing, then right-click on the QODBC icon & click "Driver Status Panel."

QODBC will open the UAC window, and please click on "Yes" in the UAC window to open the "Driver Status Panel."

There will be a status panel at the lower right corner of your screen, showing a window with information on what QODBC is working on.

If you see "Waiting On QuickBooks," It means QuickBooks is taking time to process the request.

If you want to permanently enable the "Driver Status Panel, " refer to How to view details on what QODBC is performing / Display Driver Status / Display optimizer Status.

Suggestion:

Suppose you have a large QuickBooks company file with several years of data.
The QuickBooks SDK has to scan and process many records to extract data, resulting in performance issues and slowness.
Historical data may not be required in day-to-day transactions/reports.
It is advisable to reduce the size of large QuickBooks company files for better performance.
To reduce the size of the QuickBooks company file, you can consider archiving old data.
To archive old data, you can create a copy of the company file and save the company file under a new name.
Condense the new QuickBooks Desktop company file.
You should notice performance gains when using a condensed QuickBooks company file.
You can always refer to the archived data at any time using the QuickBooks application or QODBC.

Refer to
https://quickbooks.intuit.com/learn-support/en-us/help-article/data-utilities/use-condense-data-utility/L7zprPIcj_US_en_US
https://quickbooks.intuit.com/learn-support/en-uk/other-questions/quickbooks-desktop-2020-condense-data-utility/00/532124
https://quickbooks.intuit.com/learn-support/forums/searchpage/tab/message?advanced=false&allow_punctuation=true&q=condense%20data
https://quickbooks.intuit.com/learn-support/en-us/other-questions/condensing-a-large-file/00/573043
https://quickbooks.intuit.com/learn-support/en-us/other-questions/best-way-to-split-one-company-file-into-two-originally-used-for/00/810101

If the Above Solutions don't work, please post a ticket to us, and we will investigate your issue further.

Also, refer to the following:
Troubleshooting - Cannot load Customer table records.

Suggestion:

Suppose you have a large company file with multiple years. QuickBooks and QuickBooks SDK would require more time to process the data required.

We would suggest splitting the company file to make it smaller. You can keep the historical data in one company file and the latest one in another.

This way, your company file will be smaller and with fewer records.

Refer to 

https://quickbooks.intuit.com/learn-support/en-us/other-questions/best-way-to-split-large-company-file/00/1081335

https://quickbooks.intuit.com/learn-support/en-us/account-management/create-a-new-company-file-from-the-existing-company-file/00/203213

Keywords: QB odbc slow, multiple web queries into Excel slow running, Excel running background query slow, qodbc and Excel connection speed

Problem Description:

How can a payment be applied to multiple invoices?

Scenario 1

- 3 Invoices are created.

- A payment is received that covers 2 of the invoices, but not necessarily in the order in which they were created.

How can I apply the payment to the invoices of my choosing?

Scenario 2

- 2 invoices are created

- payment received in an amount higher than the two invoices

I want to apply the payment to the two existing invoices and then use the remaining unused amount for the next Invoice that will be created later. How can I achieve this?

Solution:

You have already received Payments & Invoices created. Now, you want to apply the received payment to the desired Invoice.

As you want to link the Payment with the Invoice, you need to insert records in the ReceivePaymentLine table.

As per Scenario 1, you have 3 Invoices created, & A payment is received that covers 2 of the Invoices. Now, you want to apply the amount to the desired Invoice.

Please refer to the steps below for applying the payment to multiple Invoices using QODBC. I am using the MS Access application for this example. You can use your desired application.

First, please create a new MS Access database & link the Invoice, InvoiceLine, receive payment, and ReceivePaymentLine tables.

Please refer to How to Use QODBC with Microsoft Access for more details.

After linking the table, we need to execute the query in the MS Access query editor:

MS Access >> Create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Here, we first get the TxnID of the ReceivePayment using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, TotalAmount FROM ReceivePayment where CustomerRefFullName = 'Easley, Paula.'

We have details for Customer payments.

Now, we will get the Invoice details from the InvoiceLine table using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, SubTotal FROM InvoiceLine where CustomerRefFullName='Easley, Paula'

We have details for Customer Invoices.

We will use the above details & apply a payment to the 2 Invoice# 1108 & 1109 using the below sample query.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24373-1639567874', 140.00 )

The payment was applied to Invoice# 1108.

We will apply a payment to Invoice# 1109 using the sample query below.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24379-1639567907', 90.00 )

The payment was applied to Invoice# 1109.

Related Data Location:

TxnID = TxnID from the ReceivePayment table for the payment you received.

AppliedToTxnTxnID = TxnID from the Invoice table for which you want to apply the payment.

AppliedToTxnPaymentAmount = Payment Amount that you want to apply to the Invoice.

Also, when the payment amount is higher than the Invoice amount & you have applied it to the Invoice. The remaining payment will be shown in the UnusedPayment or UnusedCredits fields of the ReceivePayment & ReceivePaymentLine table.

Problem Description:

How to use M-Files with QODBC?

Solution:

Please follow the steps below for using M-Files with QODBC.

1. Open M-Files Admin.

2. Expand the desired connection to the M-Files Server in the left-side tree view.

3. In the left-side tree view, expand the document vault of your choice.

4. Still, expand the Metadata Structure (Flat View) node in the left-side tree view.

5. Select the Value Lists node for defining a value list to use an external database connection. The value list is opened in the right pane.

6. In the right-pane listing, right-click the value list of your choice and select "Properties" from the context menu.

7. Select the "Connection to External Database" tab & Enable the option "Use a connection to an external database to import and modify objects that reside in the external database." Click the "Define..." button next to the OLE DB connection string (from the server) field.

8. On the Provider tab, select "Microsoft OLE DB Provider for ODBC Driver" from the list and click "Next >>."

9. Select "QuickBooks Data" DSN from the dropdown list & click "Test Connection."

Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote".
Please refer to how to configure QRemote.

Test Connection successful.

10. Click "OK" to close the Data Link Properties dialog.

11. Back on the "Connection to External Database" tab, enter the SELECT statement for retrieving properties from the database to the SELECT statement field, & Click the "Refresh Columns" button to fetch the data defined in your SELECT statement to the Columns listing.

For Example Select * from Customer

12. Map the Source Column properties with properties in your M-Files vault (listed in the Target Property column).

QuickBooks Data is added to M-Files using QODBC. You can make changes to the application as per your requirement.

Problem Description:

I am working in Excel. I have set up three pass-through queries and had them work on referencing cells for the DateFrom and DateTo parameters. The queries were SummarizeColumnsBy by Month.

The queries suddenly stopped working, only returning Amount_1 and not Amount_1 through Amount_12.

If I manually put the date in the command text, all 12 months will return.

Solution:

Please ensure that you pass both the dates (i.e., start date and end date) in the parameter value. There should be 12 months difference between the two dates to get the 12-month amount.

Also, please ensure that you pass the date in the correct format through the parameter.

Please use the format below & check again:

YYYY-MM-DD

For Example:

DateFrom: 2017-01-01

DateTo: 2017-12-31

Problem Description:

I recently ran into an issue when querying the inventory for each lot number. In QuickBooks, there is a report called "Lot Numbers in Stock." You can click on the Lot Numbers on that report, and it opens a Quick View that shows the On Hand QTY for each Lot Number.

I've searched through the tables with the help of QODBC, and the only table I could find that appears to offer the information was the "BuildAssembly" table. However, the Quantity On Hand does not match what shows when I run the report in QuickBooks.

Could you please point me in the right direction to where I can query this information using QODBC?

Solution:

I am not sure how the "Lot Number In Stock" or "Serial Number In Stock" report is showing QuantityOnHand & which table it is using. You can verify QuantityOnHand from BuildAssembly through QuickBooks UI & QODBC, which will be identical.

The BuildAssembly table will show QuantityOnHand details when the assembly was built. It will not conduct the current Quantity as shown in the report.

Also, I'm sorry to inform you that the "Lot Number In Stock" or "Serial Number In Stock" report is 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 the SDK, QODBC cannot do it either.

Refer: How to export QuickBooks data not available in QODBC

Problem Description:

I am working on the transactions table, but I do not see a "class" field. How would I produce a transaction list that includes the class field?

For example, I can run a GL report in QBE 18 that includes the class field and wish to do the same with transactions.

What tables have the class field?

Could you please point me in the right direction to where I can query this information using QODBC?

Solution:

I'm sorry to inform you that the Class information in the Transaction table is 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 the SDK, QODBC cannot do it either.

Refer: How to export QuickBooks data not available in QODBC

You can use reports such as GeneralLedger, CustomTxnDetail, TxnDetailByAccount, TxnListByDate, etc.

For Example:

sp_report GeneralLedger shows Blank, TxnType, Date, RefNumber, Name, Class, Memo, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'All.'

Refer to how to use the QuickBooks Reporting Engine with QODBC for more details.

Problem Description:

I'm trying to extract QuickBooks budget data to SQL for reporting purposes. However, I don't see any tables that would hold the budget. My research is telling me this may not be possible. Can someone please tell me how I can extract Budget data?

Solution:

We are sorry to inform you that the Budget table is unavailable through the Intuit SDK, so budget-related information is unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. QODBC uses the Intuit QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose the field/table/feature to the application in the SDK, QODBC cannot do it either.

Refer: How to export QuickBooks data not available in QODBC

You can refer Budget related reports for Budget details.

You can refer to the reports below.

sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

Refer to how to use the QuickBooks Reporting Engine with QODBC for more details.

Problem Description:

I need a report by a particular Customer showing the beginning balance, payments made by the Customer, invoices with details showing the expense, and the ending balance. QuickBooks does not provide enough detail in the customer statements so parents can read them. I would also like to be able to email the statement to an email address.

Solution:

You need to query different tables & reports to get the required data. Once you get the data, you need to combine all the data.

You need to query the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine tables for a particular Customer.

You can use MS Excel for this & can write VBA code in MS Excel which will query the above table & report for particular Customers & store results on a separate sheet. Once you get the impact on the separate sheet, you need to combine all sheet data & paste it into the new sheet.

We have created seven full sheets (i.e., Sheet0 to Sheet6). Sheet 0 will be the input form, & Sheet1 to 5 will contain data from the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine tables for particular Customers, and Sheet 6 will combine data from Sheets 1 to 5. We have created a sample MS Excel macro that will fetch the required data.

Sheet 0 will be the input form. To use this, you need to enter Customer FullName, StartDate & EndDate & click on the "Generate Report" button. In this example, I have used "Abercrombie, Kristy: Remodel Bathroom" as Customer FullName & 1/1/2016 as the start date, and 12/31/2022 as the end date.

On clicking the "Generate Report" button, we will query the report & table for a particular Customer & store the results in Sheet1 to Sheet5.

Sheet 1 will contain the result of the CustomerBalanceDetail report.

The query used for fetching data for Sheet1 is:

sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account, Amount, RunningBalance as Balance parameters DateFrom = {d'2016-01-01'}, DateTo = {d'2022-12-31'}, EntityFilterFullNames= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 2 will contain the result of the InvoiceLine report.

The query used for fetching data for Sheet2 is:

Select CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, AppliedAmount, Memo, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 3 will contain the result of the CreditMemoLine report.

The query used for fetching data for Sheet3 is:

Select CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, TotalAmount, Memo, CreditMemoLineItemRefFullName, CreditMemoLineDesc, CreditMemoLineQuantity, CreditMemoLineRate, CreditMemoLineAmount from CreditMemoLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 4 will contain the result of the ReceivePaymentLine report.

The query used for fetching data for Sheet4 is:

Select CustomerRefFullName, TxnDate, RefNumber,'", ' TotalAmount, Memo, UnusedPayment, UnusedCredits, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnTxnType, AppliedToTxnTxnDate, AppliedToTxnRefNumber, AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 5 will contain the result of the CheckExpenseLine report.

The query used for fetching data for Sheet5 is:

Select PayeeEntityRefFullName, TxnDate, RefNumber,'", 'Amount, Memo from CheckExpenseLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and PayeeEntityRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Note: The ReceivePaymentLine & CheckExpenseLine table does not have the TermsRefFullName field. To maintain the data structure in Sheet6, which contains combined data, I have added '" ' in the query of ReceivePaymentLine & CheckExpenseLine.

Now, we have the required data in Sheet1 to Sheet5. We will combine these data into Sheet 6.

Sheet 6 will contain the combined result of Sheets 1 to 5.

Please click here to download the Sample Macro.

Application Source Code:

Private Sub CommandButton1_Click()

Dim cn As Object
Dim rs As Object

Dim strCon As String
Dim strSQL, CustName As String, Date1 As Date, Date2 As Date

Dim mainworkBook As Workbook

Set mainworkBook = ActiveWorkbook

strCon = "DSN=Quickbooks Data;OLE DB Services=-2;"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Dim a As String

Date1 = ThisWorkbook.Sheets("Sheet0").Cells(7, 3).Value
Date2 = ThisWorkbook.Sheets("Sheet0").Cells(8, 3).Value
CustName = ThisWorkbook.Sheets("Sheet0").Cells(6, 3).Value

strSQL = " sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account,Amount, RunningBalance as Balance " & _
"parameters DateFrom = " & fncqbDate(Date1) & ", DateTO = " & fncqbDate(Date2) & ", EntityFilterFullNames=  '" & CustName & "'  "


Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet1").Cells.ClearContents
Sheets("Sheet1").Cells.ClearFormats
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,AppliedAmount,Memo,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount from InvoiceLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet2").Cells.ClearFormats
Sheet2.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,TotalAmount,Memo,CreditMemoLineItemRefFullName,CreditMemoLineDesc,CreditMemoLineQuantity,CreditMemoLineRate,CreditMemoLineAmount from CreditMemoLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet3").Cells.ClearContents
Sheets("Sheet3").Cells.ClearFormats
Sheet3.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,'""',TotalAmount,Memo,UnusedPayment,UnusedCredits,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnTxnType,AppliedToTxnTxnDate,AppliedToTxnRefNumber,AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet4").Cells.ClearContents
Sheets("Sheet4").Cells.ClearFormats
Sheet5.Range("A1").CopyFromRecordset rs
rs.Close

strSQL = "select PayeeEntityRefFullName,TxnDate,RefNumber,'""' ,Amount,Memo from CheckExpenseLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and PayeeEntityRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet5").Cells.ClearContents
Sheets("Sheet5").Cells.ClearFormats
Sheet6.Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

Set cn = Nothing


Sheets("Sheet6").Cells.ClearContents
Sheets("Sheet6").Cells.ClearFormats
ActiveWorkbook.Sheets("Sheet1").Select

  Dim N As Long
  N = Cells(1, 8).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A1:H2")
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 0).Select
  Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Sheets("Sheet2").Select

  
  N = ThisWorkbook.Sheets("Sheet2").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet2").Range("A1:N" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet3").Select
  
  N = ThisWorkbook.Sheets("Sheet3").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet3").Range("A1:L" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet4").Select
  
  N = ThisWorkbook.Sheets("Sheet4").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet4").Range("A1:M" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet5").Select
  
  N = ThisWorkbook.Sheets("Sheet5").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet5").Range("A1:E" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet1").Select

  
  N = ThisWorkbook.Sheets("Sheet1").Cells(1, 8).End(xlDown).Row
  If (N > 4) Then N = N - 2
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A" & N & ":H" & N + 1)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


End Sub

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function
Function fncqbDate(myDate As Date) As String

myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"

End Function

Problem Description:

How to Auto-Add a relationship Between two tables in QlikSense Desktop using QODBC?

Solution:

Please create a new application in QlikSense Desktop & connect to QuickBooks Data through QODBC. Please refer to How to use QlikSense Desktop with QODBC for connection details.

I am assuming that you have referred to the above link & connected to QuickBooks from QlikSense Desktop using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the QlikSense Desktop & click the "Add data" button. In this example, I am selecting the Customer & Invoice table.

Adding data is in progress.

The Customer & Invoice table data is added to the QlikSense Desktop.

Please click the Relationship icon to associate table data according to the recommendation.

The relationship is automatically added between the Customer & Invoice tables.

Please click on the join icon to see the relationship details.

You can see that the Customer table's "ListID" is related to the Invoice table's "CustomerRefListID" field.

Please click the "Load data" button on the QlikSense Desktop.

Loading data is in progress.

The data is loaded successfully. Click on the "Edit the sheet" button to add data to the sheet.

The empty sheet is open in the QlikSense Desktop. Click on the "Fields" icon to add table fields to the sheet.

In this example, I have added the Customer table's Phone, Email field & Invoice table's CustomerRefFullName, RefNumber, and SubTotal field in the sheet. Please click on the "Done" button to finish editing the sheet.

QlikSense Desktop displays data in the sheet.

Now, you can perform any operation on table data using QlikSense Desktop.

To use QlikSense Desktop with QODBC, please follow the steps below.

Open QlikSense Desktop & click on the "CREATE A NEW APP" button.

Enter the app name and click the "Create" button.

A New application in QlikSense Desktop was created successfully. Click "Open app" for the opening application.

The application has two options: "Add Data" & "Data Load Editor." In this example, I am using the "Add Data" option. Click the "Add Data" button to update QuickBooks Data to QlikSense Desktop.

QlikSense Desktop will show a list of the data sources. Please select the "ODBC" data source.

In the Create new connection (ODBC) window, select "System DSN" & enable the "32-bit" option, select DSN & click on the "Create" button. You can change DSN as per your configuration. I am connecting to the default DSN, "QuickBooks Data," in this example.

The connection was made with QuickBooks through QuickBooks Data DSN & you can see the list of tables and the list of tables available.

Select the table you want to add to the QlikSense Desktop & click the "Add data" button. In this example, I am selecting the Customer table.

Adding data is in progress.

The customer table data is added to the QlikSense Desktop. Now, you can perform any operation on table data using QlikSense Desktop.

You can view table data by double-clicking the "Customer" table icon.

Please click the "Load data" button on the QlikSense Desktop.

Loading data is in progress.

The data is loaded successfully. Click on the "Edit the sheet" button to add data to the sheet.

The empty sheet is open in the QlikSense Desktop. Click on the "Fields" icon to add table fields to the sheet.

In this example, I have added the Customer table's FullName, CompanyName, TermsRefFullName, Phone, and Email fields on the sheet. Please click on the "Done" button to finish editing the sheet.

QlikSense Desktop displays data on the sheet.

Now, you can perform any operation on table data using QlikSense Desktop.

Also, Refer:

How to Auto Add relationship Between two tables in QlikSense Desktop using QODBC

Problem Description:

I am using QODBC to access QuickBooks Data. I have linked all QuickBooks tables using QRemote DSN.

I want to use the Customer table to be Optimizer OFF and the rest of the tables with Optimizer ON.

How can I achieve this?

Solution:

It would be best if you had the Customer table with Optimizer OFF and the rest of the tables to be Optimizer ON. It would be best if you used a separate DSN with Optimizer Off for the Customer table.

You need to create a new QODBC DSN through Configure QODBC Data Source.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

Click on the "System DSN" tab to create a DSN that both Windows applications and services can call. Click on the "Add" button to create a new DSN.

Select the "QODBC Driver for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QuickBooks Data-OPT-OFF."

Switch to the "Optimizer" tab/window and uncheck the "Use Optimizer" option, & Click "Apply" followed by "OK" to save the DSN configuration.

New DSN created. Close the ODBC Manager by clicking "OK."

After this, you need to create a new QRemote DSN through Configure QODBC Data Source 64-bit.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit.

Note: If you are using a 32-bit application & using QRemote, then you need to open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source & create a new QRemote DSN from 32-bit ODBC Manager.

Click on the "System DSN" tab to create a DSN that both Windows applications and services can call. Click on the "Add" button to create a new DSN.

Select the "QRemote for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QRemote-OPT-OFF."

·         Server: IP Address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

·         Encryption Key/Password: (By Default, Encryption Key/Password is set to "QODBC#QRemote$1". In case you want to change your Encryption Key/Password, you can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

·         Encryption: (By Default, Encryption is set to Off. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, you can configure encryption on QRemoteServer and use the same on QRemote Client DSN) 

·         Remote DSN: select QuickBooks Data-OPT-OFF DSN from the list. (Make sure QRemote Server is running)  

Click "OK" to save the DSN configuration.

Start QuickBooks login to the Company File as Admin. Start QuickBooks login to the Company File as Admin. New DSN created. Close the ODBC Manager by clicking "OK."

Switch to your MS-Access application. Right-click on Linked tables and select "Linked Table Manager." I have linked all tables in the MS Access Database using the "QuickBooks Data" DSN with the Optimizer ON option.

From the list of tables, select the Customer table & enable the "Always prompt for a new location" option, and click "OK."

From the DSN list, select the newly created DSN from "Machine Data Source." Instead of using "QuickBooks Data 64-bit QRemote", Select "QRemote-OPT-OFF."

The linked table has been refreshed.

Customer table DSN is changed to "QRemote-OPT-OFF" from "QuickBooks Data 64-bit QRemote". Close "Linked Table Manager" by clicking the "Close" button.

This way, MS Access will create a new Connection to QuickBooks Data-OPT-OFF (Optimizer Off - when accessing the Customer table). For the rest of the tables, MS Access will use QuickBooks Data (Optimizer On).

Problem Description:

I'm having trouble finding where an Item's serial numbers are stored in the QuickBooks database. I have gone through your table schema online here http://doc.qodbc.com/qodbc/usa/, but I cannot find where the serial numbers for a given inventory item are stored.

Can you please advise?

Solution:

You can find SerialNumber or LotNumber from the Transaction module you have assigned.

For Example, if you have assigned a SerialNumber or LotNumber in the Invoice, then you need to refer to the InvoiceLine table.

Below is the list of table & column which contains the serial number.

SerialNumber Details:

LotNumber Details:

Problem Description:

I am using QODBC for creating Invoices in QuickBooks. I am using CustomField in QuickBooks.

It works fine if I insert null strings into the two custom fields like this.

INSERT INTO InvoiceLine ( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache ) VALUES ( '8000001B-1436399889', 'TW Markup,' 15, 0.26, '20000-1051544872', 1 )

INSERT INTO Invoice ( CustomerRefListID, ARAccountRefListID, RefNumber, CustomFieldDBInvoice, CustomFieldFinalBuyer, Memo, TemplateRefListID ) VALUES ( '80000CFB-1334284223', '3A0000-1121110183', '22111', '', '', 'Import Date: 11/23/2017', '80000021-1483576709' )

But if I include any characters in the text string for either custom field, like this, I get the attached error.

INSERT INTO InvoiceLine ( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache ) VALUES ( '8000001B-1436399889', 'TW Markup,' 15, 0.26, '20000-1051544872', 1 )

INSERT INTO Invoice ( CustomerRefListID, ARAccountRefListID, RefNumber, CustomFieldDBInvoice, CustomFieldFinalBuyer, Memo, TemplateRefListID ) VALUES ( '80000CFB-1334284223', '3A0000-1121110183', '22111', 'x,' 'x,' 'Import Date: 11/23/2017', '80000021-1483576709' )

Below is the error in the QODBC & QuickBooks SDK logs.

Error: 3180 - There was an error when saving a data extension named "Final Buyer."QuickBooks error message: Attribute with the specified name already exists.

20171123.123634 E 11176 DataExtStorage::DoAdd Error saving: data extension named "Final Buyer."QuickBooks error message: Attribute with the specified name already exists.

Solution:

You will face this issue when you set the default value in the Custom Fields.

The default strings take precedence over the new values set in the insert statement, & issue happens.

If you edit the custom fields in the Invoice from the QuickBooks Application, it will ask to confirm the edit.

You must answer 'No' to the next question about having this "new information appear next time."

If you answer 'Yes,' it will set the defaults in the customer record and prevent text strings from being written to those fields in the Invoice.

As of Date: 2007-07-25

QuickBooks can handle a maximum of 2 billion transactions. The maximum number of transactions is limited more by your computer's disk space and memory than by QuickBooks.

Each list in QuickBooks has a maximum number of items it can contain, as shown in the following table:

Note: Some of the items mentioned in the chart above are not available in earlier versions of QuickBooks.

You can see the total number of customers, items, and names in your company data file by pressing Ctrl+1 in QuickBooks to open the Product Information screen.

Practical limitations

QuickBooks is typically designed for small businesses with 20 or fewer employees and an annual revenue of fewer than two million dollars. The ideal use of QuickBooks is to keep at least two years of complex transactions in a company data file so that you can run comparative reports and have prior-year project information.

The rate of growth of QuickBooks company data files varies significantly from company to company. How quickly a file grows depends on the number of transactions, the amount of information entered per transaction, and the number of "links" per transaction. Since businesses track different information, there is no "average" or "typical" data file size.

For example, someone who enters 500 1-line invoices per month might find their data file smaller than another person who enters 100 5-line invoices per month. In comparison, someone who usually receives five separate payments per invoice would have a larger file than someone who receives only one payment per invoice.

To estimate if QuickBooks is right for your small business, take the average number of monthly transactions (remember, an invoice, payment, and deposit would be three separate transactions, and a bill and bill payment would count as two) and multiply by 2 KB to determine how much the data file will grow each month.

For example, if your company enters an average of 300 transactions per month, the data file would grow approximately 600 KB per month (300 x 2 KB = 600 KB) or 7200 KB per year (600 KB x 12 = 7200 KB). If the annual data file size is less than 15,000 KB, then QuickBooks should be more than sufficient for the company. (Present QuickBooks users can check the size of their QuickBooks file by pressing Command+1 on the keyboard.)

Note: The 2 KB multiplier does not include list information (which will grow as new names are added to the file) and is only an approximation that may not be a suitable estimate for all QuickBooks company data files.

If the performance of QuickBooks slows down, or you decide that you do not want to keep detailed transactions for prior years, you can condense a QuickBooks company data file. For QuickBooks 2007 - 2006 users, condensing the company data files is known as "Cleaning up data." For more information on cleaning up the company data file, refer to the article Cleaning up data. For QuickBooks 2005 users, condensing the company file is known as "archiving and condensing data." For more information on archiving and condensing the company file, refer to the article Archiving and condensing data.

QuickBooks Enterprise Solutions can handle a maximum of 2 billion transactions. The maximum number of transactions is limited more by your computer's disk space and memory than by QuickBooks.

Each list in QuickBooks Enterprise Solutions has a maximum number of items it can contain, as shown in the following table:

Problem Description:

Is it possible to add a line to a check that already exists?

Here is an example from the QuickBooks Sample file. Below is a check for $500.00

Now I want to add additional items to the check, so the check now looks like this:

I said $200.00, and now the total is $700.00.

How do I do this in QODBC?

I know how to make a check with multiple lines using FQSaveToCache, but here I want to add a line to a statement that already exists.

Solution:

You need to add the field TxnID to the insert query. The field "TxnID" is available in the insert query only when the TxnID value points to an existing entity in QuickBooks.

The transaction was found by executing the following query:-

Here is an example of adding one line item to an existing Check, "2432F-1639575481," in our test company file.

For Adding Expense Line in existing check:

INSERT INTO CheckExpenseLine (TxnID, ExpenseLineAccountRefListID, ExpenseLineAmount, ExpenseLineMemo, ExpenseLineCustomerRefListID, ExpenseLineBillableStatus) VALUES ('2432F-1639575481', '3C0000-933270542', 200.00, 'Add 2nd line to check using QODBC', '800000B0-1197755238', 'Billable')

For adding an item line to an existing check:

INSERT INTO CheckItemLine (TxnID, AccountRefListID, PayeeEntityRefListID, ItemLineItemRefListID, ItemLineDesc, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus) VALUES ('2432F-1639575481', '20000-933270541', '630000-1071521572', '3C0000-1071513137', 'Building permit,' 100.00, 100.00, '250000-933272658', 'Billable')

Please notice that all values in the query come from our test company file. Please replace them using your value when running it on your end. You can add other line item columns to this query according to your request.

Note: The FQSaveToCache does support the existing Check query.

Problem Description:

Does anybody know which table the Online Banking Aliases data is stored in QuickBooks?

To see what I'm talking about, go to the "Additional Info" tab of the "Edit Vendor" window and click on "Manage Aliases."

Solution:

I am sorry to inform you that the Online Banking Aliases are currently not exposed in Intuit's QBXML SDK, so they can't be seen using QODBC. Online Banking is an add-on that requires exclusive access to the QuickBooks company file.

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 the SDK, QODBC cannot do it either.

Please refer to How to export QuickBooks data not available in QODBC.

Problem Description:

Is it possible to retrieve an employee's emergency contact information? If so, from what table?

Solution:

Employee Emergency Contact Information is added in QODBC V320. If you are using QODBC V319 or an earlier QODBC version, you need to download & install the latest QODBC version to access Employee Emergency Contact Information.

You can download & install QODBC's latest version by clicking here.

It would be best if you referred to the fields Employee table for Emergency Contact information.

1. EmergencyContactsPrimaryContactName

2. EmergencyContactsPrimaryContactValue

3. EmergencyContactsPrimaryContactRelation

4. EmergencyContactsSecondaryContactName

5. EmergencyContactsSecondaryContactValue

6. EmergencyContactsSecondaryContactRelation

Problem Description:

Is it possible to register an item's arrival without the purchase order?

If yes, can I insert the rows in the "ItemReceiptItemLine" table using the reference to the Bill?

Solution:

Receiving Item does not require a Purchase Order. You can receive it directly using the BillItemLine & ItemReceiptItemLine tables.

If you want to reference the Receive Item with Bill, you need to create a Bill using the BillItemLine table.

Please refer to How to create Bills using QODBC for more details.

You can Receive Items without a purchase order by inserting records in the "ItemReceiptItemLine" table.

Examples of Creating Item Receipts using QODBC

First, please create a new MS Access database & link the ItemReceiptItemLine table.

Please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 32-bit for more details.

If you have 64-bit MS Office, please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 64-bit for more details.

After linking the table, we need to execute the query in the MS Access query editor:
MS Access >> create tab >> Query Design

It should pop up for you to choose the table. Please click the "close" button.

Now, please click on "SQL" from the left top corner below the File menu.

Create an Item Receipt with 2 Lines

     Here we create an Item Receipt with two lines using 2 ItemReceiptItemLine INSERT commands (with the FQSaveToCache caching flag set true), which is automatically designed to pull in all cached ItemReceiptItemLine records and save immediately.

First Line

INSERT INTO ItemReceiptItemLine (VendorRefListID, RefNumber, Memo, ItemLineItemRefListID, ItemLineDesc, ItemLineQuantity, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus ,FQSaveToCache) values ('B0000-933272656', '1', 'Received items (bill to follow),' '110000-933272656', 'Cabinets: Cabinet Pulls,' 2,50,100,' 7D0000-1071526869', 'Billable,' 1)

Second Line

INSERT INTO ItemReceiptItemLine (VendorRefListID, RefNumber, Memo, ItemLineItemRefListID, ItemLineDesc, ItemLineQuantity, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus,FQSaveToCache) values ('A60000-1197760912', '1', 'Received items (bill to follow),' '130000-933272656', 'Hardware: Doorknobs Std,' 3,60,180,' 940000-1071506775', 'Billable,' 0)

Result in QuickBooks

Related Data Location Information

VendorRefListID Find ListID from Vendor Table

ItemLineItemRefListID Find ListID from Item Table

ItemLineCustomerRefListID Find ListID from Customer Table

Problem Description:

How to Auto-Add a relationship between two tables in Crystal Report using QODBC?

Solution:

A relationship can be extracted using the ODBC function "SQLForeignKeys" or "SP_FOREIGNKEYS."

Usage: SP_FOREIGNKEYS tablename tablename

Instruction: Returns a record set of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Crystal Reports application automatically uses the relationship method and provides a user-friendly graphic interface to view the relationships between two or more tables.

To Auto Add a relationship between two tables in Crystal Report, please create a new report in Crystal Report & connect to QuickBooks Data through QODBC. Please refer to How to use QODBC with Crystal Reports for connection details.

I am assuming that you have referred to the above link & connected to QuickBooks from Crystal Report using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the Crystal Report & click the ">" button. In this example, I am selecting the Customer & Invoice table.

The Customer & Invoice table data is added to the Crystal Report. Please click the "Next" button for link tables.

Please select the "By Key" option & Crystal Report will automatically link tables according to the Primary Key & Foreign Key relationship between the two tables of QODBC.

You can see that the Invoice table's "CustomerRefListID" is related to the Customer table's "listed" field.

Suppose you select the "By Name" option. In that case, Crystal Reports will not consider the Primary Key & Foreign Key relationship between two tables of QODBC & it will link according to a similar column name.
For example, Invoice table's ClassRefListID with the Customer table's "listed."

The table fields are now available for selection in your report. Now, you can perform any operation on table data using Crystal Reports.

In this example, I have added the Customer table's Phone, Email, and Balance fields & Invoice table's CustomerRefFullName, RefNumber, and SubTotal fields in the report.

Problem Description:

I am using QODBC & trying to modify data in QuickBooks. In a few tables, I can't insert/update records.

I am getting a "[QODBC] Not supported" error when inserting records.

Also, I have noticed "Error Modify not supported on this table" in QODBC Logs when trying to update records.

Solution:

You may get a "[QODBC] Not supported" error if you try to insert the record in the Header/Master table directly without inserting the form in the Child/Detail table.

For Example, you may get a "[QODBC] Not supported" error if you try to insert a record directly into the Invoice/Bill/SalesOrder/PurchaseOrder, etc, table without inserting a document, the child table InvoiceLine/BillExpenseLine/BillItemLine/SalesOrderLine/PurchaseOrderLine.

OR

You may get the "[QODBC] Not supported" error or "Error Modify not supported on this table" in QODBC Logs if you are trying to insert/update the record in the Read-Only table.

Below is the list of Read-Only, which does not support insert/update operations. 

To know which tables/fields support insert/update. Please visit QODBC Data Layouts.

Click on the region of the QuickBooks edition you are using. Click "ALPHABETICAL LISTING OF ALL TABLES" under the "TABLE OVERVIEW" label & find the TableName for which you want to check the schema, and click the "Detail" link.

You will find QUERYABLE, UPDATEABLE, INSERTABLE, and REQUIRED ON INSERT columns against each field.

This will help you determine which fields are allowed in select, update, and insert queries.

Problem Description:

When pushing data from the Access database to QuickBooks, the QODBC message indicates waiting on QuickBooks, and both Access and QuickBooks freeze (Not Responding). This is happening with a new user on this PC. Other users can log in, which usually works, but this user cannot push data from Access to QuickBooks. We have reviewed permissions for the user in QuickBooks and Active Directory, and they are all the same as those of working users. Is any special permission needed for this to work? This issue is urgent. Please advise.

Solution:

You may face QuickBooks and Access and QuickBooks freeze (Not Responding) issues when accessing the table for the first time because QODBC is building an optimizer for that table in the background.

Please refer to How Optimizer Works for more details.

Please rebuild the optimizer file & check again. Refer: Getting the limited number of records, or a Few of the documents is Missing in the QODBC Table result Rebuild Optimizer file section.

Please also enable the QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options. Refer to how to view details on what QODBC is performing for more information.

After building the Optimizer file, when you run the query, QODBC will check for any changes to QuickBooks Data & update it in the Optimizer file. So you will not face a Not Responding issue in QuickBooks & your application.

Problem Description:

I am using MS Excel to access QuickBooks Data through QODBC. I have Estimate & some of the Estimates are converted to QuickBooks. I want to know how many Estimates are converted to Invoice & what the conversion time is.

I need the Estimate RefNumber, Date, Customer, Value, Invoice RefNumber, Date, Value, and Days to Convert.

How can I find an Estimate for Invoice conversion time?

Solution:

Please click here to download the Sample Excel file.

You can find the Estimate for Invoice conversion time in the steps below.

Please start Microsoft Excel & open a new Workbook. An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "From Other Sources-> From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

It will take some time to get the DSN list. And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source, "QuickBooks Data," with a sample company file open. And check to use the Query Wizard to create/edit queries. :

Please Note: If you have 64-bit MS Office, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote."

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select the "Yes, always" option, and click the "Continue" button in the next window. Click the "Done" button to finish authorizing.

After you grant permission for QODBC, you will be guided by the Query Wizard. In the Choose Columns window, please click "Cancel" to write the query in Microsoft Query.

Please click "Yes" to write the query in Microsoft Query.

In the Add Tables window, a list of tables is available. Please click "Close" to open the MS Query editor.

Click the "SQL" button to write an SQL Statement.

We will use the query below to find the Estimate to Invoice conversion time & paste it into the "SQL Statement" box and click "OK."

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount as EstimateValue, Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo," InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "OK" to continue the execution of the query.

The result is available in the Microsoft Query editor window.

Please select "File" >> "Return Data to Microsoft Excel" to export the result to Excel.

Please select "Existing Worksheet" and click "OK" to export the result to Excel.

The result is available in an Excel sheet. As you see, the column alias is not working.

We will add an alias by editing the query by clicking the "Data" & "Connection" buttons.

Please select the query & click the "Properties..." button.

Please navigate to the "Definition" tab & paste the below query in the "Command Text" box and click "OK."

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount as EstimateValue, Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo," InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "Close" to close the workbook connection window.

The result is available in an Excel sheet with a column alias.

Note: Case When a statement is not working in MS Query. Alternative to Case: When information is an IIF statement, but IIF statements are not supported in QODBC.

Also, Refer:

Troubleshooting - Cannot use alias in MS Query

Problem Description:

We don't have an operational problem, just a SQL/technical question -- we are currently pulling invoices like this:

SELECT * FROM Invoice WHERE RefNumber ='221206' (works great)

But it does NOT tell which QuickBooks user CREATED/ENTERED the Invoice/SalesOrder. (We do NOT use Sales Reps, we want to know the actual user who typed in the order.

Where can we find this information? Can I use the TxnID from the Invoice or SalesLine in another table to determine who entered that Invoice or line item?

Solution:

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 the SDK, QODBC cannot do it either.

I'm sorry to inform you that the QuickBooks user details who created/entered the Invoice/SalesOrder are unavailable through the Intuit SDK, so they are not available through QODBC.

As a workaround, you can create the custom field with the name (QBUser) in the customer center & you can use it on Invoice/SalesOrder. You need to make the custom field in the customer & define this field in the Invoice template.

Once you define the custom field in Invoice/SalesOrder, you can store QBUser details on the custom field, which is also available in the QODBC Invoice/InvoiceLine table with the name CustomField+(Field name you created). If you made the custom field with the QBUser in QODBC, you could find the column "CustomFieldQBUser."

You can get the custom field in QODBC after defining custom fields in QuickBooks.

Note: You need to disconnect the connection between QODBC & QuickBooks and Reconnect to get the Custom Field. Custom Field will display in QODBC after resetting the connection.

Problem Description:

I am using QODBC to fetch QuickBooks Data in SQL Server. I have created a Linked Server in SQL Server.

We are receiving the following error when trying to fetch data.

The SQL is:
SELECT * FROM [QREMOTE].[QODBC].[Company]

The error is:
Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for the linked server "QREMOTE" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Line 4
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "QREMOTE." A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Solution:

You have used the wrong SQL statement. It would be best if you used LinkedServerName & table name in the query.

For Example:
Select * from YourLinkedServerName...TableName
SELECT * FROM [QREMOTE]...[Company]

Problem Description:

I am using QODBC to access QuickBooks Data. I am using the QODBC optimizer, and I want to know more about how the optimizer works.

I can connect to QODBC, but when I fetch any table, it takes a lot of time.

I can fetch records from a few tables, but for other tables, QODBC is just processing and not returning any records.

Solution:

The optimizer brings some data to a local cache to increase query retrieval performance. Below is the default setting of QODBC Optimizer.

The QODBC Optimizer is designed to improve performance when accessing QuickBooks data by caching results locally. This article explains how the optimizer works by default, step-by-step.

When you run a query through QODBC (for example, SELECT * FROM Invoice), QODBC communicates with the QuickBooks SDK to retrieve the requested data. Since the QuickBooks company file is not a traditional database, but rather a flat file system, data retrieval and conversion are handled in a specialized way to optimize speed and reliability.

Here is how the optimizer works with the default configuration.

Step-by-Step Process

Step 1: Query Execution

You execute a query such as:

Step 2: Check for Optimizer Data

QODBC checks the Optimizer Table to see if data for the requested table (in this example, Invoice) already exists.

Step 3: First-Time Data Retrieval

Assuming there are 50,000 records in the Invoice table.

If this is the first time accessing the table:

• QODBC retrieves all records from QuickBooks, including data from related child tables (for example, Invoice, InvoiceLine, and InvoiceLinkedTxn).

• The QuickBooks SDK returns data in XML format, in batches of 500 records per request.

• QODBC converts this XML data into a table format for use by your application.

Note: Since this is a full data retrieval, it may take some time. Please do not close or terminate the process during this initial load.

Step 4: Data Storage in Optimizer

After retrieving the data:

• QODBC saves the data into the Optimizer File.

• The optimizer records the timestamp of the data retrieval.

Step 5: Data Returned to Application

Once the optimizer is updated, QODBC reads from the optimizer file and returns the results to your application.

Step 6: Subsequent Queries

The next time you execute the same query (e.g., SELECT * FROM Invoice):

1. QODBC again checks the Optimizer Table for Invoice.

2. It finds that the data already exists and retrieves the last synchronization timestamp.

3. QODBC requests only the changes (added, modified, or deleted records) since the last timestamp using the QuickBooks SDK.

Step 7: Delta Update

Because QODBC now retrieves only changed data, the process is much faster.
For example:

• Out of 50,000 total records, only 60 have changed (40 modified, 10 added, 10 deleted).

• QODBC processes only those 60 records instead of the full 50,000.

Step 8: Optimizer Update

QODBC applies the delta changes to the optimizer file to keep it up to date.

Step 9: Return Updated Data

Finally, QODBC reads the updated data from the optimizer file and returns the complete result set to the calling application.

Key Points to Remember

• The first query on any table will take longer because QODBC must retrieve and cache all records.

• Subsequent queries are significantly faster, as only new or changed data is fetched.

• Do not interrupt the first-time data load process to avoid incomplete optimizer data.

Question:

I am fetching the top 10 records from Invoice (or any other table), and QODBC is still reading all the records from QuickBooks.

Answer:

If you are not using the optimizer or if you are querying the table for the first time, QODBC will have to build the optimizer before returning the records.

Even if you have requested the top 10, QODBC has to fetch all the records from QuickBooks to show the result matching the criteria.

Problem Description:

I am using QODBC to access QuickBooks Data. I have linked all QuickBooks tables using QODBC.

I want the Customer table to be Optimizer OFF and the rest to be Optimizer ON.

How can I achieve this?

Solution:

It would be best if you had the Customer table to be Optimizer OFF and the rest of the tables to be Optimizer ON. I suggest using a separate DSN with Optimizer Off for the Customer table.

You need to create a new QODBC DSN through Configure QODBC Data Source.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

Click on the "System DSN" tab to create a DSN that both Windows applications and services can call. Click on the "Add" button to create a new DSN.

Select the "QODBC Driver for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QuickBooks Data-OPT-OFF."

Switch to the "Optimizer" tab/window and uncheck the "Use Optimizer" option, & Click "Apply" followed by "OK" to save the DSN configuration.

Start QuickBooks login to the Company File as Admin. Start QuickBooks login to the Company File as Admin. Start QuickBooks login to the Company File as Admin. New DSN created. Close the ODBC Manager by clicking "OK."

Switch to your MS-Access application. Right-click on Linked tables and select "Linked Table Manager". Right-click on Linked tables and select "Linked Table Manager". Right-click on Linked tables and select "Linked Table Manager". I have linked all tables in the MS Access Database using the "QuickBooks Data" DSN with the Optimizer ON option. 

From the list of tables, select the Customer table & enable the "Always prompt for a new location" option, and click "OK."

From the DSN list, select the newly created DSN from "Machine Data Source." Instead of using "QuickBooks Data," Select "QuickBooks Data-OPT-OFF."

The linked table has been refreshed.

Customer table DSN is changed to "QuickBooks Data-OPT-OFF" from "QuickBooks Data." Close "Linked Table Manager" by clicking the "Close" button.

This way, MS Access will create a new Connection to QuickBooks Data-OPT-OFF (Optimizer Off - when accessing the Customer table). For the rest of the tables, MS Access will use QuickBooks Data (Optimizer On).

Problem Description:

I am attempting to extract data from a client's hosted QuickBooks Enterprise system. I need every transaction in the system as we are transitioning to a different approach. I have removed all of the records from the "Transactions" table, but they don't appear as a "balance." I think the amount column should net to zero, as the credits (negative) Amount should be offset against the debits (positive) amounts.

Solution:

In the Transaction table, the total of Credit (negative) will not match the sum of Debit because the transaction table has additional rows for the subtotal.

You have to exclude all such records/rows having AccountRefListID/AccountRefFullName as empty. You can exclude blank AccountRefListID/AccountRefFullName by clicking the column filter, unchecking "Blanks," & click "OK."

You may also have to format the Amount column to Number (show two decimal values) to avoid any round-off issues in Excel.

You can format cells by right-clicking on the "Amount" column & click "Format Cells."

After applying the above changes, you will notice that the Amount appears in balance.

Problem Description:

I am running the QODBC report "CustomSummary" and comparing it to what should be the identical report in QuickBooks. I am encountering an issue where the Balance Sheet accounts report zero amounts (vs. the values I see in QuickBooks). In contrast, the Income Statement accounts report the correct amount.

Could you advise if there is a parameter I need to change when running the stored procedure?

The procedure I am running is as follows:

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All'

I have set up the parameters below in the QuickBooks UI.

Solution:

You are facing a mismatch issue because QuickBooks SDK is passing the parameter AccountFilterType='IncomeAndExpense' by default in the report query & you have removed the Account Filter parameter from the QuickBooks UI.

There is no option to remove the default account filter from the report query.

This is a limitation of the QuickBooks SDK, which does not support removing the default account filter.

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 the SDK, QODBC cannot do it either.

We have raised an enhancement request, QBWG-47383, at Intuit's end to support removing the default account filter through the QuickBooks SDK.

As a workaround, you need to follow the procedure below.

Run the report twice & export it in two separate Excel sheets.

Refer: How to create sp_reports using Microsoft Excel

1st using the filter AccountFilterType= 'BalanceSheet' (returning all rows and columns)

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'BalanceSheet'

2nd, use the filter AccountFilterType='IncomeAndExpense' (returning all rows and columns).

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'IncomeAndExpense'

Then join the two resulting datasets for the amount fields & update based on the logic of the 1st dataset amount minus the 2nd dataset amount (as the IncomeAndExpense amounts need to have their signs reversed). This produces the same result as running the Custom Summary report out of the UI with no AccountType filter.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Microsoft Access.

The attached invoice is similar to what we need to export to Access. It doesn't have to be exact, but it needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into an MS Access report format using QODBC.

The first step is to create a new MS Access database & link the InvoiceLine table to MS Access.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office.

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then, click "Create" to create a blank database.

After the new database is created, navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button on the External Data menu:

Under the Get External Data window, you will get two options. We will use "Link to the data source by creating a linked table":

After selecting the access method and clicking OK, you will get a Select Data Source window.

Select the QuickBooks Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the Example.)

For 64-bit MS Office, change QRemote DSN to QuickBooks Data. 64-bit QRemote is the default DSN created when installing QODBC.

Access will return a list of available tables in the Select Tables window. Please select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

The linked table was added to MS Access.

Now, we will design a new report from Create->Report Wizard.

The Report Wizard shows the linked table & available fields.

Select the fields you want to show in the report & click "Next."

Report Wizard showing selected fields. Click "Next" for more configuration.

You can sort records in ascending or descending order up to four fields & click "Next."

Select report Layout & click "Next."

Insert the report name & select "Modify the report's design," & click "Finish" to design a report.

The report design window is opened, & Drag the table field into the report as per your requirement & design the information.

Select the table field assigns a group to the table fields by clicking the "Add a Group" button.

The select field for Group. In this example, I have selected "TxnID" all group fields are grouped for a particular TxnID.

The Group is created. Click "More."

Select "With a header section" & "With a footer section" to enable Group Header & Footer.

If you want to print one invoice per page (i.e., only one invoice on a single page), click on the group footer for TxnID & set the Force New Page property box drop-down arrow, and select Before Section.

The report is designed. You can add labels & assign a group to table fields. You can view the information by clicking on "View."

The actual report will look as follows in View mode.

You can select "Print Preview" mode to see all Invoice reports.

The Invoices in MS Access report format are shown below.

Please click here to download the Sample Report.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Crystal Reports.

The attached invoice is similar to what we need to export to Crystal Reports. It doesn't have to be exact, but it needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into Crystal Report format using QODBC.

The first step is to create a new report.

The Database Expert Window will be open. Expand the "Create New Connection" Folder:

Expand the "ODBC (RDO)" Folder:

The following screen will appear. Select "QuickBooks Data" DSN and click "Next," finally click "Finish" on this screen:

There is no UserID and Password required for connecting to QuickBooks Data using QODBC. So you can click "Finish" directly.

A list of QuickBooks tables will appear, and is ready for selection:

Crystal Report will return a list of available tables in the Select Tables window. Please select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

For the table added to Crystal Report, click "Next" to add table fields to the report.

The Report Wizard shows the table & available fields.

Now, select the field which you want to show in the report by clicking the ">" button & click "Next."

Add group information to the report. We will group data by selecting the TxnID field and clicking the ">" button, then clicking "Finish."

The report designer will show selected fields.

Now, we will add Customer details, Billing & Shipping details, TxnDate, RefNumber, Terms, and DueDate in the Group Header section. We will add InvoiceLine information (i.e., Item, Description, Quantity, Rate & Amount field) in the detail section. We will add SubTotal in the Group Footer section.

You can add the caption in the report data using Text Object from the Menu Insert >> Text Object.

In this example, I have added the following Text Objects.

You can draw the box in the report using Menu Insert >> Box.

In this example, I have drawn a box around the "Ship To" details.

You can draw a line in the report using Line from the Menu Insert >> Line.

I have drawn the line below in the "Ship To" Box.

In the same way, I have drawn Box & Line on other report details.

If you want to print one invoice per page (i.e., only one invoice on a single page), then select "Section Expert" & click on the Group Footer, enable the "New Page After" drop-down arrow, and click "OK."

You can view the report by clicking on the "Preview" tab.

The actual report will look like the sample below in Preview mode.

You can add the RefNumber parameter to the report. So you can see the invoice for a particular Invoice#. You can add the RefNumber parameter in the information to limit the records or view a single invoice.

You can add parameters by right-clicking on "Parameter Fields" & click "New."

Insert the parameter name, choose "Static" as a list of values & choose "RefNumber" in the value field and click "OK."

Add a parameter to the report by dragging and dropping.

We need to link the RefNumber parameter with the RefNumber value in the report.

For linking, Right-click on "RefNumber" & click "Select Expert...".

In the select expert window, select "is equal to" & "{?RefNumber}" parameter & click "OK."

When you run the report, the Crystal Report will prompt for RefNumber, as shown in the sample below. Enter a RefNumber value and click "OK."

The report is available for a particular RefNumber.

Please click here to download the Sample Report.

Sample Application:

Please click here for downloading Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using VB.NET.

Run the application & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

Imports System.Data.Odbc
Imports System.Drawing

Public Class _Default
    Inherits Page
     Private Shared _cn As OdbcConnection

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        lblErrorMessage.Visible = False
    End Sub
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing) Then
                    lblConnectionStatus.Text = "Disconnecting...."
                    _cn.Close()
                    _cn.Dispose()
                    btnConnect.Text = "Connect"
                    lblConnectionStatus.Text = "Not Connected"
                    lblConnectionStatus.ForeColor = Color.DarkRed
                End If
            Else
                If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                    lblConnectionStatus.Text = "Connecting...."
                    _cn = New OdbcConnection(String.Format("DSN={0}", cboListOfDSN.Text))
                    _cn.ConnectionTimeout = 60
                    _cn.Open()
                    btnConnect.Text = "Disconnect"
                    lblConnectionStatus.Text = "Connected"
                    lblConnectionStatus.ForeColor = Color.Green
                End If
            End If
        Catch ex As Exception
            btnConnect.Text = "Errorred"
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
        
    End Sub

    Protected Sub btnInsertInvoiceDescription_Click(ByVal sender As Object, ByVal e As EventArgs)

        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
            btnConnect.Text = "Connect"
            lblConnectionStatus.Text = "NotConnected"
            lblConnectionStatus.ForeColor = Color.DarkRed
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            lblErrorMessage.Text = "Invoice Refer Number is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            lblErrorMessage.Text = "New Line Description is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("New Line Description is required")
            Return
        End If

        InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text)
        DisplayInvoiceInGrid(txtDInvoiceNo.Text)
    End Sub

    Protected Sub btnInvoiceInsertItem_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
                btnConnect.Text = "Connect"
                lblConnectionStatus.Text = "NotConnected"
                lblConnectionStatus.ForeColor = Color.DarkRed
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                'MessageBox.Show("Invoice Refer Number is required")
                lblErrorMessage.Text = "Invoice Refer Number is required"
                lblErrorMessage.Visible = True
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                'MessageBox.Show("New Line Item Full Name is required")
                lblErrorMessage.Text = "New Line Item Full Name is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIQuantity.Text)) Then

                'MessageBox.Show("Invoice Refer Quantiy is required")

                lblErrorMessage.Text = "Invoice Refer Quantiy is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIRate.Text)) Then

                'MessageBox.Show("New Line Rate is required")
                lblErrorMessage.Text = "New Line Rate is required"
                lblErrorMessage.Visible = True
                Return
            End If
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, Integer.Parse(txtIQuantity.Text), Integer.Parse(txtIRate.Text), txtIDescription.Text)
            DisplayInvoiceInGrid(txtIInvoiceNo.Text)

        Catch ex As Exception
            'MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
    End Sub
    Private Sub InsertInvoiceLineItem(invoiceRefNumber As String, itemFullName As String, quanity As Int16, rate As Int16, description As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then

            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty
        End If
        QBdrdr.Close()


        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub InsertInvoiceLineDescriptionOnly(invoiceRefNumber As String, newItemDescription As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String
        query = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then
            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty

        End If
        QBdrdr.Close()

        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub DisplayInvoiceInGrid(invoiceRefNumber As String)
        Dim query As String = String.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)
        ProcessQuery(query)
    End Sub
    Private Sub ProcessQuery(query As String)
        Dim cmd = New OdbcCommand(query, _cn)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        Dim myTable As DataTable = New DataTable()
        myTable.Load(reader)
        grvData.AutoGenerateColumns = True
        grvData.DataSource = myTable
        grvData.DataBind()
    End Sub


End Class

Keywords: sample .net, sample, .net, Dot Net, VB.Net

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for inserting InvoiceLine to an existing Invoice using VB.NET.

You can change DSN as per your configuration. In this example, I am connecting to the default DSN QuickBooks Data. Run the application, select DSN & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionalities:

1. Append the existing Invoice with a new Description Line, which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

A new Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing  Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Full Item Name, Quantity, Rate & Description which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

A new Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

Imports System.Data
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data.Odbc
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Windows.Forms.VisualStyles

Public Class Form1

    Dim _cn As OdbcConnection
    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        Try
            Application.DoEvents()
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing OrElse IsDBNull(_cn)) Then
                    lblConnectionStatus.Text = "Disconnecting...."
                    _cn.Close()
                    _cn.Dispose()
                    btnConnect.Text = "Connect"
                    lblConnectionStatus.Text = "Not Connected"
                    lblConnectionStatus.ForeColor = Color.DarkRed
                End If
            Else
                If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                    lblConnectionStatus.Text = "Connecting...."
                    _cn = New OdbcConnection(String.Format("DSN={0}", cboListOfDSN.Text))
                    _cn.ConnectionTimeout = 60
                    _cn.Open()
                    btnConnect.Text = "Disconnect"
                    lblConnectionStatus.Text = "Connected"
                    lblConnectionStatus.ForeColor = Color.Green
                End If
            End If
            Application.DoEvents()
        Catch ex As Exception
            Application.DoEvents()
            btnConnect.Text = "Errorred"
            MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
        End Try
    End Sub

    Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
        Me.Close()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListOutDSN()
    End Sub

    Private Sub ListOutDSN()

        cboListOfDSN.Items.Clear()
        Dim myOdbcKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next

        myOdbcKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next


        If (cboListOfDSN.Items.Count > 0) Then
            For Each item As String In cboListOfDSN.Items
                If (item.Equals("QuickBooks Data")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data POS")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data Online")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                cboListOfDSN.SelectedIndex = 0
            Next
        End If
    End Sub



    Private Sub btnInsertInvoiceDescription_Click(sender As Object, e As EventArgs) Handles btnInsertInvoiceDescription.Click
        'If (IsNothing(_cn) Or _cn.State = ConnectionState.Closed) Then
        'btnConnect_Click(Nothing, Nothing)
        'End If
        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            MessageBox.Show("New Line Description is required")
            Return
        End If

        InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text)
        DisplayInvoiceInGrid(txtDInvoiceNo.Text)

    End Sub

    Private Sub InsertInvoiceLineDescriptionOnly(invoiceRefNumber As String, newItemDescription As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String
        query = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then
            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty

        End If
        QBdrdr.Close()

        If (String.IsNullOrEmpty(txnID)) Then
            MessageBox.Show("No Invoice Found")
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub DisplayInvoiceInGrid(invoiceRefNumber As String)
        Dim query As String = String.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)
        ProcessQuery(query)
    End Sub

    Private Sub ProcessQuery(query As String)
        Dim cmd = New OdbcCommand(query, _cn)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        Dim myTable As DataTable = New DataTable()
        myTable.Load(reader)
        grvData.AutoGenerateColumns = True
        grvData.DataSource = myTable
    End Sub


    Private Sub btnInvoiceInsertItem_Click(sender As Object, e As EventArgs) Handles btnInvoiceInsertItem.Click

        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                MessageBox.Show("Invoice Refer Number is required")
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                MessageBox.Show("New Line Item Full Name is required")
                Return
            End If

            If (String.IsNullOrEmpty(txtIQuantity.Text)) Then

                MessageBox.Show("Invoice Refer Quantiy is required")
                Return
            End If

            If (String.IsNullOrEmpty(txtIRate.Text)) Then

                MessageBox.Show("New Line Rate is required")
                Return
            End If
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, Integer.Parse(txtIQuantity.Text), Integer.Parse(txtIRate.Text), txtIDescription.Text)
            DisplayInvoiceInGrid(txtIInvoiceNo.Text)

        Catch ex As Exception
            MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
        End Try

    End Sub

    Private Sub InsertInvoiceLineItem(invoiceRefNumber As String, itemFullName As String, quanity As Int16, rate As Int16, description As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then

            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty
        End If
        QBdrdr.Close()


        If (String.IsNullOrEmpty(txnID)) Then
            MessageBox.Show("No Invoice Found")
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

End Class

Keywords: sample .net, sample, .net, Dot Net, VB.Net

Problem Description:

I could link all tables to an MS Access DB from the QuickBooks Database.

But I got the following error when I tried to open the ItemAssembliesCanBuild table.

ERROR [00000] [QODBC] Error: 3120 - Object "X" specified in the request cannot be found.

Solution:

ItemAssembliesCanBuild Query does require ItemInventoryAssemblyRef(ListID or FullName) and TxnDate in the WHERE Clause to perform the query in QuickBooks SDK.

You will get this error when you execute a query directly without specifying ItemInventoryAssemblyRef(ListID or FullName) and TxnDate.

So it would be best to execute the query with ItemInventoryAssemblyRef(ListID or FullName) and TxnDate through a custom query.

For Example:
SELECT * FROM ItemAssembliesCanBuild where ItemInventoryAssemblyRefFullName='Inventory Door Kit' and TxnDate={d'2017-12-15'}

It would be best if you wrote the query in MS Access from MS Access >> create tab >> Query Design.

Select Pass-Through >> Write query >> Execute the query using the Run button.

Select DSN & execute the query.

Query executed successfully & result available.

Problem Description: 

I have changed the setting in QODBC Setup Screen from Open mode to the same as the company file, but my Excel still does not take the changes and needs the company file to be in multi-user mode.

When I refresh the Excel sheet, I get the following error:

Solution:

MS Excel is storing the connection string & due to this, you are facing this error.

You need to update the connection string in Excel to solve this issue.

Please open the MS Excel file and go to the "Data" tab >> Click on "Connection."

Now select the connection & click on "Properties."

Navigate to the Definition tab, & You will find the Connection String box. You will notice that the connection string contains OpenMode=M, which will try to open the company file in Multiuser mode.

You need to change OpenMode=M to OpenMode=F in the connection string box & click "OK."

By changing it to OpenMode=F, the Excel connection will follow the company file mode, whatever is selected in QuickBooks.

Problem Description:

We would wish to inactive items that have not sold after a certain date. Is this possible? If so, any help would be appreciated.

Solution:

1) Get the list of Sold Items by exporting the SalesByItemSummary report for a certain period in MS Excel as below

For Example:
sp_report SalesByItemSummary show Text, Label, Quantity, Amount, Percent, AveragePrice parameters DateFrom = {d '2016-06-01'}, DateTo = {d '2016-12-31'}

Please refer to How to create sp_reports using Microsoft Excel for exporting reports into Excel.

By running the above query, you will get ItemType + Parent Item Name in the Text column & ItemName + description in the Label column.

We will split ItemName from the description using the "Text to Columns" option. We will select the "Label" column & click on "Text to Columns."

Choose "Delimited" and click "Next."

Select "Other" and write "(" (i.e., Opening Brace) and click "Next." We are splitting data into different columns from Opening Brace.

You can see split data from the Data preview. Now click "finish."

Click "OK" to replace destination cell contents.

Data is split into two columns.

Now we will add a new column & use the formula for trimming whitespace from the Child Item name & removing the prefix "Total" from the Parent Item name.

=TRIM(SUBSTITUTE(C2,"Total","",1))

The formula is applied to all rows. Now we have furnished the Item Name.

2) Now we have the Item Name & Item type for sold items. Now we need to export Item tables depending on Item Type (i.e., ItemInventory, ItemNonInventory, ItemService, etc.).

In this example, we will export the ItemInventory table in sheet2 & we will make inactive ItemInventory that is unsold for a certain period.

Please refer to Using QuickBooks Data with Excel for exporting the ItemInventory table into Excel.

The ItemInventory table is exported to sheet2.

Now we will go to sheet 3 & use the VLOOKUP formula for getting a list of unsold Items.
=IF(IFERROR(VLOOKUP(TRIM(Sheet2!E1), Sheet1!B: B,1, FALSE), "ERROR")=" ERROR," Sheet2!E1,"")

The formula is applied to all rows. Now we have the name of the unsold ItemInventory.

3) Now, we have the name of the unsold ItemInventory. You can make it inactive by running the following update query.

Update ItemInventory set IsActive = False where Name = 'Cabinet Pulls'

You can also use VBA to make all unsold Items inactive. Please refer to Using QuickBooks Data with VBA and write a VBA script per your requirement.

Result in QuickBooks.

You can repeat the above steps for Item Type like ItemNonInventory, ItemService, etc...

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using the application for inserting InvoiceLine to an existing Invoice using C#.NET.

Run the application & click on the "Connect" button.

The application should get connected to QuickBooks.

The application has two functionalities:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

A new Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, Full Item Name, Quantity, Rate & Description, which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

A new Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    private static OdbcConnection _cn;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            //ListOutDSN();
            lblErrorMessage.Visible = false;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {

        }
    }
      protected void btnConnet_Click(object sender, EventArgs e)
    {
        try
        {
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }

        }
        catch (Exception ex)
        {
            btnConnect.Text = "Errorred";
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));            
        }
    }
    protected void btnInsertInvoiceDescription_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtDInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtDDescription.Text))
            {
                lblErrorMessage.Text = "New Line Description is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Description is required");
                return;
            }
            InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text);
            DisplayInvoiceInGrid(txtDInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }
    protected void btnInvoiceInsertItem_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtIInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIItemFullName.Text))
            {
                lblErrorMessage.Text = "New Line Item Full Name is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Item Full Name is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIQuantity.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Quantiy is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Quantiy is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIRate.Text))
            {
                lblErrorMessage.Text = "New Line Rate is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Rate is required");
                return;
            }
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, int.Parse(txtIQuantity.Text), int.Parse(txtIRate.Text), txtIDescription.Text);
            DisplayInvoiceInGrid(txtIInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }

        private void InsertInvoiceLineDescriptionOnly(string invoiceRefNumber, string newItemDescription)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                    lblErrorMessage.Text = "Insert Successfully(Description Only)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }

        }


        private void InsertInvoiceLineItem(string invoiceRefNumber, string itemFullName, int quanity, int rate, string description)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();
                    lblErrorMessage.Text = "Insert Successfully (Inventory/Service)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }
        }


        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);
            //OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            //adapter.Fill(dataSet);
            OdbcDataReader reader = cmd.ExecuteReader();
            //if (reader.HasRows)
            {
                DataTable myTable = new DataTable();
                myTable.Load(reader);
                grvData.AutoGenerateColumns = true;
                grvData.DataSource = myTable;
                grvData.DataBind();

            }

        }

        private void DisplayInvoiceInGrid(string invoiceRefNumber)
        {

            string query = string.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            ProcessQuery(query);
        }
    }

Keywords: sample .net, sample, .net, Dot Net, C#,C#.Net, ASP.NET, Web Server, IIS, C#.NET

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using the application for inserting InvoiceLine to an existing Invoice using PHP.

Run the application.

The application has two functionalities:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

A new Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Full Item Name, Quantity, Rate & Description which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

A new Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>QODBC PHP Script To Display SQL Results</title>

</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">


<table>
	<tr>
		<td>
			Append existing Invoice with a new Description line
			<form name="frmDescriptionLine" id="frmDescriptionLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtInvoiceNo" id="txtInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter New Item Description</td>
					<td><input type="text" name="txtDescription" id="txtDescription"style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnDescriptionLine" id="btnDescriptionLine" value="Insert New Invoice Line (Description Only)" onclick="InsertRecord()"/></td>
				</tr>
				</table>
			</form>
		</td>		
		<td>
			Append existing Invoice with a new  ItemInventory/ItemService Line
			<form name="frmItemLine" id="frmItemLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtItemInvoiceNo" id="txtItemInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter Item FullName</td>
					<td><input type="text" name="txtItemRefDescription" id="txtItemRefDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Quantity</td>
					<td><input type="text" name="txtItemQuantity" id="txtItemQuantity" value="1"/></td>					
				</tr>
				<tr>
				<td>Rate</td>
				<td> <input type="text" name="txtItemRate" id="txtItemRate" value="1"/></td>
				</tr>
				<tr>
					<td>Enter Description</td>
					<td><input type="text" name="txtItemDescription" id="txtItemDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnItemLine" id="btnItemLine" value="Insert Invoice Line (Inventory/Service)" /></td>
				</tr>	
				</table>
			</form>
		</td>
	</tr>
</table>


    
</body>
</html>



<?php
if(isset($_POST['btnDescriptionLine']))
{

	$invoiceNo= $_POST['txtInvoiceNo'];
	$invoiceDescr = $_POST['txtDescription'];	
	if($invoiceNo == "" || $invoiceDescr == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Invoice No. and Invoice Line Description both.")';
		echo '</script>';
	}
	else
	{
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);
		//print("                        $sFieldName\n");
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		//print("\n");
		}				
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineDesc) values('$sFieldValue','$invoiceDescr')";
		$oResult = odbc_exec($oConnect, $sSQL);
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount  FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
		
		
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("                    <tr>\n");
			print("                        <td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


if(isset($_POST['btnItemLine']))
{
	$invoiceItemNo= $_POST['txtItemInvoiceNo'];
	$invoiceItemRef = $_POST['txtItemRefDescription'];
	$invoiceItemQuantity = $_POST['txtItemQuantity'];
	$invoiceItemRate = $_POST['txtItemRate'];
	$invoiceItemDescr = $_POST['txtItemDescription'];	
	if($invoiceItemNo == "" || $invoiceItemDescr == "" || $invoiceItemRef == "" || $invoiceItemQuantity =="" || $invoiceItemRate =="" )
	{
		echo '<script language="javascript">';
		echo 'alert("Fill the Details properly")';
		echo '</script>';
	}
	else
	{		
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceItemNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		//echo $oResult;
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);		
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		print("\n");
		}		
		
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('$sFieldValue','$invoiceItemRef',$invoiceItemQuantity,$invoiceItemRate,'$invoiceItemDescr')";
		/*if($oResult = odbc_exec($oConnect, $sSQL)){
			echo '<script language="javascript">';
			echo 'alert("Success")';
			echo '</script>';
		}
		else
		{
			echo $oResult; exit();
		}*/		
		//print($oResult);
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount FROM InvoiceLine Where txnid='$sFieldValue'";
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt= $lFldCnt+1;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("<tr>\n");
			print("<td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


?>	

Problem Description:

In QuickBooks Enterprise, the company information form shows me the company's business#.

In a custom report, the field is displayed as blank.

In VBDEMO, the field shows as blank.

I double-checked my odbc setup, which allows personal data to be accessed.

Solution:

The company information is on the Company table, i.e.

Select * from the company.

The information you see is what is allowed by Intuit's QBXML SDK. Specific columns are only available in their respective country versions of QuickBooks, while "specialized to country" company information may not appear. '

Problem Description:

I am trying to query the Customer table. The Title Bar of the Access Application says Not responding. Still, it never opens for me in the datasheet view, either by double-clicking the table or running a query; no error message is ever returned, but the screen maintains an hourglass.

I am getting the following error in QODBC logs:

2016-07-19 13:55:05 QODBC Ver: 16.00.00.313 *********************************************************************************************************************
service: False
SQL Statement: SELECT * FROM Customer
Error Getting XML in BuildAndLoadXMLForNextRecord

Solution:

Please enable the QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options.

After enabling the Status Panel, run the query again. If you see “Waiting for QuickBooks,” it means that QuickBooks is still processing the request. The Status Panel, located in the lower-right corner of your screen, displays information about the operation QODBC is currently performing.

This issue may also occur if your company file contains a large number of records in the CustomerShipToAddress table.

When working with large record sets, you may need to adjust the Iterator/Chunk Size setting in the Advanced tab of the QODBC Setup Screen to accommodate the largest record set returned by QuickBooks. The default value is 500, but it can be increased to any value up to 100,000.

To resolve the issue, please download and install the latest version of QODBC (build 361 or later) from the QODBC Download page.

After downloading the latest QODBC version, please change the iterator chunk size to 2/10/50/100 on the QODBC Setup Screen->Advanced tab.

After changing the chunk size, please query the customer table again. You should be able to get records in the Customer table.

Problem Description:

Can you give me some insights on optimizing an update query to the PriceLevelPerItem table?

I am trying to update all of my existing price levels, and I'm looping through each inventory item in a specific category and updating the item in the PriceLevelPerItem table. Here is my SQL update

UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 38.35 WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '800004B9-1175625788'

I believe the ListID and the ItemRefListID are both jumps in points, but my driver status counts through the records and then stops at the record to update, and appears to hang in Access and VBDemo. I have gotten it to update at times, but others go into limbo, and with 22,000 items to update, I am hoping you can help me make this work. 

Solution:

The Intuit QBXML SDK requires us to modify the whole PriceLevelPerItem table with every insert or update, so the more levels and items you have, the longer it will take. The problem is that Intuit requires the entire list to be updated with every operation, not a single row like the rest of QuickBooks!

Problem Description:

I entered a series of purchase orders with queries that look like this:

INSERT INTO PurchaseOrderLine ("VendorRefListID", "RefNumber", "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "FQSaveToCache") VALUES ('8000002F-1480957977', '1485', '800002C0-1480957977', '50-pc briquette set for hp 94751', 500, 8.00, 4000.00, 0);

I ran queries for c. 400 line items for 48 po's.

This resulted in two distinct po's for each PO number, twice as many line items as I had intended.

I first thought to start over and try again. To that end, I ran this:

Delete FROM PurchaseOrderLine where TxnID='2770-1071519624'



ERROR: [QODBC] Requires at least one child. I cannot delete the last line. This results in this error: This results in this error: This results in this error:

So, I'm out of ideas.

Please tell me how to get rid of the duplicates. 

Solution:

You have only one line item in a PurchaseOrderLine table for that particular TxnId. You cannot use the last line item, which is associated with the header row of PurchaseOrder. You cannot delete the previous line.

You need to delete the header row from the PurchaseOrder table, so please try to delete the row for that particular TxnID from the PurchaseOrder table. Please verify the same & try again.

Please refer to the sample query & check again.

For Example:

Delete FROM PurchaseOrder where TxnID='2770-1071519624'

Problem Description:

Hello, I am trying to refund CC payments in QuickBooks. I can create the credit memo. However, when attempting to add the refund using the following, it fails:

Insert into ARRefundCreditCardRefundAppliedTo (CustomerRefListID, CustomerRefFullName, RefundAppliedToTxnTxnID) values('80001E51-1445438531', 'Dude, Aaron - Homes Financial,' 'C5B2E-1479513412 ')

According to the following: http://doc.qodbc.com/qodbc/usa/TableDetails.php?tableName=ARRefundCreditCardRefundAppliedTo

The only fields required on the insert are CustomerRefListID, CustomerRefFullName, and RefundAppliedToTxnTxnID.

And yet the insert above fails. I ensured that the three fields exist where RefundAppliedToTxnTxnID is the txnID of an existing credit memo. 

What am I doing wrong? 

Solution:

You need to include RefundAppliedToTxnRefRefundAmount in your query & test again.

Please refer to the sample query & test again.

Insert into ARRefundCreditCardRefundAppliedTo (CustomerRefListID, CustomerRefFullName, RefundAppliedToTxnTxnID, RefundAppliedToTxnRefRefundAmount) values('800000D0-1182061376', 'Allard, Robert,' '1B3D0-1513329686',123321)

To process a credit card refund, you must include credit card-related information.

For Example:

Insert into ARRefundCreditCardRefundAppliedTo (CustomerRefListID, CustomerRefFullName, RefundAppliedToTxnTxnID,RefundAppliedToTxnRefRefundAmount, CreditCardTxnInfoInputCreditCardNumber,CreditCardTxnInfoInputExpirationMonth,CreditCardTxnInfoInputExpirationYear,CreditCardTxnInfoInputNameOnCard,CreditCardTxnInfoResultResultCode,CreditCardTxnInfoResultResultMessage,CreditCardTxnInfoResultCreditCardTransID,CreditCardTxnInfoResultMerchantAccountNumber,CreditCardTxnInfoResultTxnAuthorizationTime,CreditCardTxnInfoResultTxnAuthorizationStamp,CreditCardTxnInfoResultAVSStreet,CreditCardTxnInfoResultAVSZip,CreditCardTxnInfoResultCardSecurityCodeMatch,CreditCardTxnInfoResultReconBatchID,CreditCardTxnInfoResultPaymentGroupingCode,CreditCardTxnInfoResultPaymentStatus,CreditCardTxnInfoResultClientTransID) values('800000D0-1182061376', 'Allard, Robert', '1B3D0-1513329686',123321, '1234567890123456',5,18,'Test',1,'Success','a-436432','45522522',{ts '2016-05-05 00:00:00.000'},1,'Pass','Pass','NotAvailable','ss,1,'Completed','S-12347')

Note: Execution of the above requires the Company file to be subscribed to QuickBooks Merchant Account Services.

Problem Description:

I am unable to load all customer records through QODBC.

I have a large number of customers with ship-to addresses.

I have tried to execute the query using the UNOPTIMIZED keyword. But still didn't get the records.

I have noticed the following error in QODBC logs.

SELECT * FROM Customer UNOPTIMIZED

Error Getting XML in BuildAndLoadXMLForNextRecord 

Solution:

Please follow the steps below & test again.

1. Open the QODBC Setup Screen from the Advanced tab. Set the iterator size to 10.

Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >> Go To "User DSN" or "System DSN" Tab (i.e., Whatever DSN you are using)>> select your DSN >> click "configure" >> switch to Advanced tab >> Set Iterator or Chunk Size to 10 & apply changes.

2. Execute the query below in the Customer table.

Select * from Customer UNOPTIMIZED

OR

Query the table Customer with filter ListID or FullName.

For Example:

Select * from Customer UNOPTIMIZED where fullname='ABC.'

OR

Attempt to optimize the customer table through QODBC Test Tool using the below query & check again. (Retry if fail).

SP_OPTIMIZEFULLSYNC Customer

Problem Description:

How should the following SQL query be written to work with QODBC, using the "like" filter to restrict records?

SELECT Sales.RefNumber, Sales.BillAddressAddr1, Sales.Type, Sales.TxnDate FROM Sales WHERE Sales.BillAddressAddr1 = 'Customer Name' AND Sales.TxnDate <= {d '2006-04-09'} AND LOWER(Sales.Type) LIKE 'c%' ORDER BY RefNumber

Solution:

There's no problem with LIKE any value. However, that's not how we support the LOWER function. This query executes without error:

SELECT Sales.RefNumber, Sales.BillAddressAddr1, Sales.Type, Sales.TxnDate FROM Sales WHERE Sales.BillAddressAddr1 = 'Customer Name' AND Sales.TxnDate <= {d '2006-04-09'} AND {fn LCASE(Sales.Type)} LIKE 'c%' ORDER BY RefNumber

But I don't have any "Credit "data that qualifies. However, running the following query shows how "i%" works to find "Invoice" type sales:

SELECT Sales.RefNumber, Sales.BillAddressAddr1, Sales.Type, Sales.TxnDate FROM Sales where {fn LCASE(Sales.Type)} LIKE 'i%' ORDER BY RefNumber

Problem Description:

We use QOBDC Read Write 8.00.00.234 with QuickBooks Enterprise 2007.

When trying to save a SalesOrder with three SalesOrderLines, return this error:

[QODBC] Error: 3180 - There was an error when saving a Sales Order. QuickBooks error message: The credit limit for the name on this transaction has been exceeded.

This is by the credit limit of the Customer in QuickBooks or transaction values in the insert query.

Some options to resolve this problem?

Solution:

The credit limit for the customer on the Sales Order has been exceeded. Correct the customer's credit limit using the QuickBooks user interface.

Problem Description:

I'm having a problem running this SQL statement in QODBC Test Tool. The application returns an error message: [QODBC] Not supported.

UPDATE Check SET IsToBePrinted = 1 WHERE TxnID = 'B9A6A-1197976027'

I'm using QuickBooks Premier: Manufacturing and Wholesale Edition 2007/08

Solution:

You will need to upgrade to QuickBooks QuickBooks Series 2008/09 for this to work. The Australian version of QuickBooks 2007/08 uses SDK v3.0 from the USA version of QuickBooks 2004, which doesn't support modifying checks (cheques).

Problem Description:

I cannot find a table with the retained earnings listed in it.

Furthermore, I found no transactions for this account, yet QuickBooks reports two closing entries.

In the account table, I have a zero value. On the balance sheet, it is quite NON-ZERO. Drilling in shows a single-line balance.

And for the previous two years, there has been a posted amount with a closing date.

It takes a WHILE when you double-click the account, so it looks like it is calculating these two values.

Is it STORED or calculated? Because it is not in the account table.

Solution:

I assume you mean "Retaining Earnings" on your balance sheet. The value is in the Account table.

Retained Earnings are calculated from the Profit & Loss statement and are the current profit or loss. The current value appears in the Account table:

Problem Description:

How can we update the Insert PayrollItemNonWage field as Auto Allowance, Benefits, Federal Income, etc.?

Suppose we add, Item Name -- Amount ---- limit. For time-based employees, how to calculate it. 

Solution:

Sorry, but you can't run payrolls from within QODBC. They are run from within QuickBooks. None of the columns in PayrollItemNonWage are insertable.

Problem Description:

I want to make a report to show me which items are selling quickly and slowly. I figure I need to look at the length of time between the item receipt date and the item invoice date. I made a report in Crystal Reports that does this, using the InvoiceLine and ItemReceiptItemLine tables, and I think it works, but it takes so long to run that I'm not sure. It's never finished. I guess I'll start going when I leave tonight and see if it will be completed overnight.

Is there another way to do this? Am I on the right track? Is there an existing report that will give me this information? It seems like it would be an everyday thing that many people would want to know.

For the most part, each item I sell is unique and has a unique name. So, for a given item, there is only one receipt and one invoice, then no more receipts of that item ever again. Does that make it any easier to track?

Mostly they are received with a bill through. Occasionally, on just an item receipt w/o a bill.

How do I filter results by account? (income, asset, or COGS account)  

Solution:

This approach will only work if all stock parts are item receipted without a bill. There's no tracking of each piece received and when it's sold. So it will take a bit of work to make this work well.

If that's the case, you can start with this:-

Select BillItemLine.ItemLineItemRefFullName, BillItemLine.RefNumber as BillRef, BillItemLine.TxnDate as PurchaseDate, InvoiceLine.RefNumber as InvRef, InvoiceLine.TxnDate as SoldDate, {fn CONVERT(InvoiceLine.TxnDate, SQL_DATE)} - {fn CONVERT(BillItemLine.TxnDate, SQL_DATE)} as DaysHeld From BillItemLine NOSYNC, InvoiceLine NOSYNC where BillItemLine.ItemLIneItemRefListID = InvoiceLine.InvoiceLineItemRefListID and InvoiceLine.TxnDate >= BillItemLine.TxnDate

To filter results by account, use the query below.

SELECT Item.ParentRefFullName as ParentItem, AVG({fn CONVERT(InvoiceLine.TxnDate, SQL_DATE)} - {fn CONVERT(BillItemLine.TxnDate, SQL_DATE)}) as AvgDaysHeld FROM BillItemLine NOSYNC, Item NOSYNC, InvoiceLine NOSYNC WHERE BillItemLine.ItemLineItemRefListID = Item.listed and BillItemLine.ItemLIneItemRefListID = InvoiceLine.InvoiceLineItemRefListID and BillItemLine.TxnDate >= {d'2007-01-01'} and InvoiceLine.TxnDate >= BillItemLine.TxnDate GROUP BY Item.ParentRefFullName

Refer: Oracle JDBC-ODBC Bridge

Troubleshooting - QuickBooks Dashboards, Java, JDBC - ODBC

Problem Description:

I am evaluating the QODBC driver for a client who wants to create a dashboard using QuickBooks data. I am trying to use the JDBC ODBC bridge.

As you see, the dashboard has multiple charts and queries with drills. I can add queries one by one, and they work fine, but when I do a dashboard refresh, only the first portlet to fire returns the rows, whereas the remaining portlets either return an error or do not return any rows.

Also, is there a time-out in the QuickBooks connection? I noticed that if I let the connection remain idle, i.e., I switch to some application and return after, say, 10-15 minutes to the dashboard, and then try to refresh a query, the application crashes with a Java HOTSPOT error.

Any idea how to resolve the above situation? 

Solution:

There's no actual QODBC connection time-out. I often have QuickBooks and a QODBC connection running for days. However, browser-based and threaded interfaces will have their time-outs. These time-outs require you to add QODBC disconnect and reconnect routines within your code or script. You will also need to use an STA (single-threaded-apartment) threading model, which your COM components may rely upon when returning calls.

Problem Description:

This used to work, and I have no idea why it now blinds all reads of the CreditCardInfoCreditCardNumber field in the Customer table.

I previously used QODBC to transfer an entire QuickBooks 2006 Canadian company into a new QuickBooks USA 2008 file. I had no problem reading the actual credit card number from an MS Access application using an ADO connection at that time. Now, when trying to read from the QuickBooks USA 2008 company or the latest one. I either get NULLS in this field, or the information is blended with the Asterix characters, as in the subject above (the same happens using the supplied QODBC Test Tool application). This depends on what optimizer flags I use (VERIFY, NOOPTIMIZE, etc.) I haven't found a combination that gets me the whole credit card number.

Before you ask why I need this, it's because we do our credit card billing through the Authorize.net gateway, and I don't want to have to maintain a separate store of CC information for security reasons.

What happened?

Solution:

Sorry, but Intuit no longer allows the full credit card number to be seen using QuickBooks 2008 USA Edition and the QBXML SDK v7.0. The API is changing the Credit Card to "xxxxxxxxxxxx1234", not QODBC.

Problem Description:

I am using Alpha 5 version 10.5 basic scripting to export our data to QuickBooks Enterprise 2016.

I have successfully exported our customer and inventory data without any issues. All data looks complete and is fully functional.

My problem is exporting our Supplier Orders. Following your tutorial on How to create a Purchase Order using QODBC

It will not work correctly. It creates the PO in QuickBooks. However, it will only add the last line item to the PO. I am using the FQSaveToCache field set to 1 for all lines except the final, which is set to 0 and will only write the last line.

When I generate the SQL code from my A5 basic script to a text file and copy/paste the SQL code into Alpha 5's SQL command window, I get the same results, and only the last line item is written to the PO. When I paste it into the QODBC Test Tool program, it works correctly, generating the PO with all line items as expected.

These are SQL lines generated from A5.

INSERT INTO PurchaseOrderLine (VendorRefListID, RefNumber, PurchaseOrderLineItemRefListID, PurchaseOrderLineQuantity, FQSaveToCache) VALUES ('80000172-1402174943', '14731', '80004374-1474850327', 10, 1)

INSERT INTO PurchaseOrderLine (VendorRefListID, RefNumber, PurchaseOrderLineItemRefListID, PurchaseOrderLineQuantity, FQSaveToCache) VALUES ('80000172-1402174943', '14731', '8000435E-1474850314', 10, 1)

INSERT INTO PurchaseOrderLine (VendorRefListID, RefNumber, PurchaseOrderLineItemRefListID, PurchaseOrderLineQuantity, FQSaveToCache) VALUES ('80000172-1402174943', '14731', '8000437A-1474850332', 10, 0) 

Solution:

I think your connection is dropping during insertion & due to this issue. So please verify the same & test again. So please verify the same & test again. Please ensure that you are executing all queries in the current connection. If the connection is reset, all lines will not be inserted.

Problem Description:

How to create PriceLevelPerItem & PriceLevelFixedPercentage using QODBC.

Solution:

To create a new PriceLevel and PriceLevelPerItem item (at the same time), use the below query:

INSERT INTO PriceLevelPerItem ("Name," "IsActive," "PriceLevelPerItemItemRefListID," "PriceLevelPerItemCustomPrice") values ('Door Bell Special,' 1,'8C0003-1044564690',20)

Where the Name 'Door Bell Special' is the new PriceLevel.

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Result in QuickBooks

The result looks like this in QuickBooks 2022:

To add another item to the 'Door Bell Special' price level, you must first determine the ListID for the PriceLevel you want to add. This can be found by doing the following:

SELECT ListID, Name, PriceLevelType from PriceLevel where Name='Door Bell Special'

To add another item to this PriceLevel, we use the following query:

INSERT INTO PriceLevelPerItem ("ListID," "IsActive," "PriceLevelPerItemItemRefListID," "PriceLevelPerItemCustomPrice") values ('80000006-1481799565', 1,'250003-1044564090',150)

Result in QuickBooks

The result looks like this in QuickBooks 2022:

To create a new PriceLevelFixedPercentage, use the query below:

Insert into PriceLevel (Name, PriceLevelFixedPercentage) values ('40.000', -40)

Result in QuickBooks

The result looks like this in QuickBooks 2022:

Problem Description:

I routinely create new sales orders with queries similar to this:

INSERT INTO SalesOrderLine ("CustomerRefListID," "RefNumber," "SalesOrderLineItemRefListID," "SalesOrderLineDesc," "SalesOrderLineQuantity," "SalesOrderLineRate," "SalesOrderLineAmount," "FQSaveToCache")
VALUES ('CustomerRefListID,' 'RefNumber,' '80000095-1351453721', 'spark generator,' 50, 2.39, 119.5, [0 for the last line, 1 for other lines])

Can I insert a new line into an EXISTING sales order with a similar query?

If so, what value do I use for FQSaveToCache?

Solution:

You need to add the field TxnID to the insert query. The field "TxnID" is available in the insert query only when the TxnID value points to an existing entity in QuickBooks.

If you want to add a line item record to an existing SalesOrder, please remove header information fields like "CustomerRefListID" and "RefNumber" from your query.

Here is an example of adding one line item to an existing SalesOrder "4912-1071512816" in our test company file:

INSERT INTO SalesOrderLine (TxnID, SalesOrderLineItemRefListID, SalesOrderLineDesc, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineAmount) VALUES ('4912-1071512816', '160000-933272656', '4m Steel Ladder', 1.00000, 1.00000, 150.00)

Please notice that all values in the query come from our test company file. Please replace them using your value when running it on your end. And you can also add other line item columns to this query according to your request.

Note: The FQSaveToCache does support existing SalesOrder queries.

Problem Description:

I am trying to link the ReceivePaymentToDeposit table through the linked table in MS Access. I can connect the ReceivePaymentToDeposit table. But I am getting an ODBC-Call failed error in MS Access when opening the ReceivePaymentToDeposit linked table.

Solution:

When you open the linked table from MS Access at that time, MS Access is passing queries like "SELECT "TxnID,"..., "UnusedCredits" FROM "ReceivePayment" where "TxnID" = ?" to QODBC.

QODBC passes one-by-one XML requests to QuickBooks SDK using the TxnID of the table, and QODBC gets all records one by one from QuickBooks SDK.

The "ReceivePaymentToDeposit" table does not support the TxnID filter in QuickBooks SDK & this issue happened.

Using the Pass-Through query in MS Access would be best as a workaround.

You can find pass through from: MS Access >> Create tab >> Query Design >>



Select Pass-Through >> Write query >> Execute query using Run button



Select DSN, click "OK," & execute the query.



You will get the result.

Open Visual FoxPro from the Start Menu:

FoxPro started:

Open "Command Window" from Windows Menu:

"Command Window" Opened. You can execute FoxPro commands from the "Command Window."

You need to write FoxPro commands in the "Command Window" & execute them by hitting the "Enter" key on the keyboard.

First, we will connect to QuickBooks from FoxPro using the SQLCONNECT command. Write the command below & hit the "Enter" key on the keyboard.

CN = SQLCONNECT('QuickBooks Data', '', '')

Now, we will execute the query to QuickBooks from FoxPro using the SQLEXEC command. Write the command below & hit the "Enter" key on the keyboard.

SQLEXEC(CN , "SELECT TOP 10 Name FROM Customer","csrCust")

Note: The above query is for testing purposes only. You can change the query as per your requirement.

Now, we display query results in FoxPro using the BROWSE command. Write the command below & hit the "Enter" key on the keyboard.

BROWSE

Query Result:

You can disconnect the connection to QuickBooks from FoxPro using the SQLDISCONNECT command. Write the command below & hit the "Enter" key on the keyboard.

SQLDISCONNECT(CN)

Problem Description:

In the BillToPay Table - How can we select invoices for payment using QODBC?

We would like to have the Checkmark Selected for the respective invoice.

Solution:

You will have to insert BillPaymentCheckLine, marking the Bill as Paid.

First, you need to query the BillToPay table for the bill you want to pay.

For Example:

SELECT * FROM BillToPay where BillToPayRefNumber='7893'



Bill in QuickBooks UI.

It would be best if you used the query below to mark the Bill as Paid.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('90000-933272656', 'C0000-933270541', '20000-933270541', '7893', 'DF4-933785047', 1250)



Related Data Description

PayeeEntityRefListID = '90000-933272656' (Required) VendorRefListID from Bill table.

APAccountRefListID = 'C0000-933270541' (Required) APAccountRefListID from Bill table.

BankAccountRefListID = '20000-933270541' (Required)

RefNumber = '7893' (RefNumber of Bill)

AppliedToTxnTxnID = 'DF4-933785047' (TxnID from Bill Table)

AppliedToTxnPaymentAmount = 1250 (AmountDue from Bill Table)

Bill paid in QuickBooks.

Apply Vendor Credit to the Bill using Bill Payment.

Assuming you already have a VendorCredit.

Get the TxnID of the Vendor Credit.

Select * from VendorCredit where VendorRefFullName='YourVendorFullName'

Use the following SQL statement to apply Vendor Credit.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID,
APAccountRefListID,
BankAccountRefListID,
RefNumber,
AppliedToTxnTxnID,
AppliedToTxnPaymentAmount,
AppliedToTxnSetCreditCreditTxnID,
AppliedToTxnSetCreditAppliedAmount
) VALUES ('90000-933272656',
'80000020-1730714268',
'20000-933270541',
'7893',
'8DA-1763656441',
21,
'8F0-1764072149',
21)

Related Data Description

PayeeEntityRefListID = '90000-933272656' (Required) VendorRefListID from Bill table.

APAccountRefListID = '80000020-1730714268' (Required) APAccountRefListID from Bill table.

BankAccountRefListID = '20000-933270541' (Required)

RefNumber = '7893' (RefNumber of Bill)

AppliedToTxnTxnID = 'DF4-933785047' (TxnID from Bill Table)

AppliedToTxnPaymentAmount = 21 (AmountDue from Bill Table)

AppliedToTxnSetCreditCreditTxnID= '8F0-1764072149' (TxnID from VendorCredit Table)

AppliedToTxnSetCreditAppliedAmount= 21 (Amount you want to adjust towards Vendor Credit available from VendorCredit Table)

Problem Description:

Using QuickBooks Accountant, QODBC, and MS Access Linked table.

Sometimes, e.g., at present, working with the InventoryAdjustment table, a table view or query returns the right number of records, but many of them have "#Deleted" in every field. On this occasion, which is typical, I have nine records full of "#Deleted," then a normal, valid record, then four more full of "#Deleted," then one more valid record. A total of 15 records, which is what QuickBooks shows me. I can't see anything significantly different from the ones that appear.  

Solution:

To resolve this, you need to relink your linked table in MS Access.

Refer: How to Re-Link QuickBooks Data in MS Access using QODBC