Forwarding to: Frequently Asked Questions About QODBC

Q: How can I tell if the driver has been properly installed?
Q: What are the limitations of the evaluation version of QODBC?
Q: What causes an "Invalid Username" error when trying to use QODBC?
Q: I have registered QODBC, but I have not registered QuickBooks and I get an error?
Q: I have registered QODBC; can I install it on more than 1 computer?
Q: I have a previous version of QuickBooks rather than 2003, can I use QODBC with this version?
Q: What is the difference between your ODBC driver and other tools for accessing QuickBooks data?
Q: I am getting a QODBC Create instance error (error = 80004002) when I use VB demo or excel to access your sample data or my local company data?
Q: My application is not directly listed in your help files or manual, and I'm having trouble getting it to run?
Q: There is no data source for QuickBooks in my control panel ODBC option after I install?
Q: I installed a new version of QODBC and it is running as an evaluation version?
Q: I can't install the QODBC driver; it conflicts with some other file?

Q: I can't see data in any of my files?
Q: What are the "Raw" files used for (CustomerRaw, CompanyRaw)?
Q: I get a Syntax Error message or Lexical Element error and can't find the problem?
Q: When the list of available tables comes up, is there a way to filter out certain confidential databases so they cannot be selected?
Q: How can I find out more information about how this driver and ODBC work?
Q: When I log in to the driver a FLEXquarters logo screen pops up for about 2 seconds and it goes away. Is it possible to turn this off?
Q: I can't find the QuickBooks driver from Microsoft Office/Word/Excel?
Q: Why is the driver so slow?
Q: Why does Access say there are only 101 records in the file, I know there are more?
Q: I have executed a query in Access and it seems like the system is just hanging up?
Q: How does QODBC determine the data path?
Q: What is the Open Style?
Q: When I try to edit a record in Access 2000, I'm told the record has been changed by another user, but I'm the only user?
Q: What specific files can be changed by QODBC?
Q: Can you explain why I'm not seeing bill payments in the Checks table in Access? Only "hand" checks appear?
Q: I get an error attempting to edit or insert to the memo field (notes) for a Customer record?
Q: I can read a file, but I get errors when I try to edit/delete/create records?
Q: How can I save new records to the Invoice table, Purchase orders and other tables which have single or multi-line detail attached to them?
Q: How can I update inventory quantities?
Q: Do you have more detailed technical information?
Q: How can I keep up with the latest news on QODBC?
Q: How can I get the latest version of QODBC?
Q: How does QODBC Interface with QuickBooks?

Q: How do I add customers?
Q: What is the difference between the Internet version of the QODBC driver and the normal version?
Q: I have a spreadsheet that I want to pull the cash balances from 30 different companies into. Can you please tell me the most efficient way to accomplish this?
Q: I keep getting an error '[QODBC] Assignment Error' when I try to import an entire table into Excel 2000?
Q: I need an example of how to insert data into the Timetracking table?
Q: How do I do an Inner Join in QODBC?
Q: Can I get some examples of how to use QODBC via Visual Basic?
Q: Do you have any examples of how to use QODBC with Microsoft .NET?
Q: Is it possible to declare and use a local variable with a query? What is the QODBC syntax?
Q: My customer has a very large database and multiple users need reports frequently and they take a while to run. Will your product help?
Q: If I simply want to export the results of a financial report to Excel or Access, do I have to figure out how to calculate the report again or is there a simpler way?
Q: How are dates formatted in SQL queries when using the QuickBooks generated time stamps?
Q: How do I narrow a report to a date range to make it run faster with less data?
Q: How can I get a list of tables available via the ODBC driver? In SQL Server, I use "SELECT * FROM sysobjects WHERE..."
Q: How can I get a customer statement via QODBC?
Q: I ran a query which was invoiceline.refnumber < 5 and it returned 1 10 11 12 13 etc up to 49 - why?
Q: Where can I ask questions about accounting issues and problems using QuickBooks software?
Q: How do I import QuickBooks data into MS SQL Server?
Q: Can I use your QODBC Driver with the Java ODBC Bridge product from Sun?
Q: Although I know the difference between the read only and read write versions of QODBC, what would be the purpose of writing to QuickBooks?
Q: If multiple users are using QODBC to access QuickBooks at the same time, does the driver take care of record locking in QuickBooks?
Q: How can I apply check payments or credits to bills?
Q: How can I receive payments or credits against customer invoices?

Q: How can I tell if the driver has been properly installed?

After you have applied for an evaluation registration code from our web site, enter the information in the QODBC Setup Screen. In your program/start/menu locate the icon for QODBC Driver for QuickBooks and click on QODBC Test VBScript. After the pop screen comes up click ok. A short time later you should see a short list of employees from the sample company file.

If at any time you get an ODBC connect error message it is typically because of the following:

  • Your version of QuickBooks has not been updated to the latest revision from Intuit. Use the Updater built in to QuickBooks.
  • If you have prior versions of QuickBooks loaded on the machine attempting to use QODBC, you will get a connectivity error. The 2002 version of QuickBooks has some conflicts with prior versions when using the SDK for connectivity.
  • If you have not applied for a temporary registration code for QODBC or if the information you entered in setup panel is not exactly as it appears in the registration email, you will get an error message.
  • You may be using the Read-Only or the Read-Write version of the driver as a system service or as a web component. Only our Web version works in this manner.
  • You may have filled in the Login name and password fields in the QODBC Setup Screen. There are only used with the QODBC Extended Security option and have nothing to do with logging in to QuickBooks.

Q: What are the limitations of the evaluation version of QODBC?

Evaluation versions are full Read-Only versions which are time limited. When you apply for an evaluation version on the QODBC.com web site, a code will be e-mailed to you which will allow you full use of the driver for 30 days. This license is the same as the full read/write QODBC driver, except that it is limited to 20 record saves per session. At the end of this period, you will need to purchase the product, because it will become disabled and will no longer run. Also the "Raw" tables are unavailable in the evaluation version.  

Q: What causes an "Invalid Username" error when trying to use QODBC?

Did you enter a user name and password in the QODBC setup screen? If so, remove it and uncheck display login screen. The login name and password in our system does not relate to the QuickBooks login name, it is related to an extended security option. Details are in our users manual.

Q: I have registered QODBC, but I have not registered QuickBooks and I get an error?

QODBC requires that QuickBooks be fully registered before it will function, so you need to complete the registration process for QuickBooks before attempting to run QODBC.

Q: I have registered QODBC; can I install it on more than 1 computer?

No. Each registered version of QODBC is directly linked to the registered copy of QuickBooks which has been installed on that machine and if the QODBC registration does not match the QuickBooks registration the driver will function as an evaluation copy.

Q: I have a previous version of QuickBooks rather than QuickBooks 2002, can I use QODBC with this version?

QuickBooks 2002 is the first version that contains an SDK (Software Development Kit) which is what we use to make data calls into the QuickBooks data, so previous versions of QuickBooks will not work with our product. Using the official SDK of QuickBooks guarantees your data will be validated and protected by the QuickBooks application.

Q: What is the difference between your ODBC driver and other tools for accessing QuickBooks data?

QODBC is built on the ODBC standard interface, a method for accessing database data which has been in Windows since Windows 95, and which is already built-in to thousands of applications and desktop productivity products. While some tools offer QuickBooks access to a few limited application, we offer all of them and you can choose how to use your accounting data.

QODBC is available as a fully functional Read and Write ODBC driver for QuickBooks files, unlike some other tools which may be only able to read data.

Our product uses the official QuickBooks SDK to make sure that all of your data is 100% validated by QuickBooks. By using the official QuickBooks SDK interface, we are able to offer our customers total compatibility with all QuickBooks 2002 file formats.

We are Premier Developer Members, part of the Intuit Developer Network, and Intuit supports our product to the point where they have included us in their upcoming marketing launch for QuickBooks.

QODBC also comes with 30 days of free technical support after the sale, a detailed user manual with many examples, web support and free email support. 

Q: I am getting a QODBC Create instance error (error = 80004002) when I use VB demo or excel to access your sample data or my local company data? 

Check the revision of your QuickBooks, most likely your version does not have the latest patches provided by Intuit. With QuickBooks running, press Ctrl + 1 (do not use the keypad 1) and this will display all of your registration and revision information. If you are currently running a version less than R4 of QuickBooks you will need to apply the latest patch from Intuit, which can be installed from the "Update QuickBooks" menu option under the File menu, and can also be found at: http://www.QuickBooks.com/support/index/ndxw_10_updates.html

For a complete list of qbXML QuickBooks SDK errors, see QBXML Error Codes.

Q: My application is not directly listed in your help files or manual, and I'm having trouble getting it to run?

Some applications have different interpretations of the ODBC standard than others. Since it is impossible to support every application that does not use the ODBC standard properly, we suggest that you use a supported middleware product.

Microsoft Access installs with an MS Access ODBC driver. Most tools support using this driver (Jet engine interface), and Access works well with our ODBC driver.

Using Microsoft Access, create a new MDB and create links to the QuickBooks data files you want to access with the unsupported tool. Then create an ODBC driver link (DSN) in the control panel which uses the MS Access driver and points to this MDB. Then open the QuickBooks data files using Access as the driver, and it will in turn call our driver.

Q: There is no data source for QuickBooks in my control panel ODBC option after I install?

You can add one manually by choosing the Add option.

Q: I installed a new version of QODBC and it is running as an evaluation version?

All versions of QODBC start out as evaluation versions until you install your registration code in the setup screen from the control panel. QODBC retains the registration code when you update the product with any downloads we may have for that particular QODBC version. Registration codes for the QODBC 2002 product will not work with the installation of 2003 driver, therefore the 2003 version will revert to an evaluation copy unless upgraded.

Q: I can't install the QODBC driver; it conflicts with some other file?

Make sure *all* applications are closed when you run the installation program. This includes the Microsoft Office Shell, any desktop managers, and any mail programs, reminder programs, anti virus software. Make sure there is nothing in your startup folder. If you are in Windows 95/NT, bring up the close program screen (CTRL-ALT-DEL) and close all open programs except Explorer. Our install adds standard components to your Windows environment, and they cannot be in use when the install runs.

If you are still experiencing problems with the installer you may need to download the MS Installer program for the needed components from the download page of our web site or from Microsoft.com.

Q: I can't see data in any of my files?

First, check that the driver itself is properly installed. First click on the icon for "Sample Data". This should load and run QuickBooks 2002, and open the sample database, then minimize QuickBooks.

Start up our VBDEMO application from the menu and choose Connections/Add New Connection. An ODBC dialog will start up offering you various data source names. Click the tab for Machine Data Sources, Double click on QuickBooks Data. Press the Tables button, you should see the list of table names from the sample service based data file in the grid. Now click Query and you should see the results of all records in the Customer data file. If you see this data, your driver is properly installed and you should check the DSN of your Company File path to make sure it is correct. Using the "." (period) by itself in the DSN will use the currently open QuickBooks company file.

Q: What are the "Raw" files used for (CustomerRaw, CompanyRaw)?

The tables in our driver have been filtered, for convenience and ease of use for our customers. The Raw files include all of the data from the named table, with no filtering or modification. We modify the primary tables in a number of ways to make them more useful, some of these include:

1. We format data into SQL field types more easily used in programming languages for math and comparison operations.

2. We show only active records. Inactive records can be seen in the raw version of the table.

3. We show only "primary" records. A primary record is the first record of a set of records under the same code. For example, a customer can have 5 addresses on his account. The first address, as well as all of the other customer information is in the primary record. The other 4 addresses are in secondary records.

The Raw tables allow us to create a simple and easy to use driver, while allowing developers and more technical types access to the raw data found in the QuickBooks files, should they require it.

Q: I get a Syntax Error message or Lexical Element error and can't find the problem?

Look at the SQL statement and check for the following:

  • Values to be compared or placed in a file must be surrounded by single quotes (') unless they are numeric, then they should not.
  • Check our list of supported functions in the manual to verify that the SQL statement complies with the drivers currently supported operators and functions.

Q: When the list of available tables comes up, is there a way to filter out certain confidential databases so they cannot be selected?

Yes, using our Security functions, which prevent users from accessing certain tables. They will be able to see them in the list however, but will be unable to open them. We do this because programs like MS Access remember the list of files, and give errors if files appear to be missing.

Q: How can I find out more information about how this driver and ODBC work?

There are many good books available on ODBC programming and SQL, as well as specific references on using ODBC with various languages and tools.

If you want to see how the driver works, or you need to provide a log for our technical support department, go to the QODBC Setup Screen icon and double click it. In the entry labeled Trace File enter an ASCII file name like c:\mytrace.log, then click OK. All calls you make to our ODBC driver will now be logged to this file until you clear this filename. Note: Microsoft ODBC has a trace facility too, but this has no driver specific information about QuickBooks and will not help us find your problem.

Q: When I log in to the driver a FLEXquarters logo screen pops up for about 2 seconds and it goes away. Is it possible to turn this off?

This license screen is required so that the user can see the registration name. It only appears once per windows session. The best solution is to add a small VB program to the startup folder in windows so that the registration screen only appears when starting windows, and not when running your program.

Q: I can't find the QuickBooks driver from Microsoft Office/Word/Excel?

The default installation of Microsoft Office (typical or standard installation) does not include the helper application Microsoft Query. This component is required to access any ODBC database from within the Office package.

Re-run the Office install program and choose Custom Installation, then choose Data Access tools, this will install Microsoft Query and related tools for accessing external data. It will not re-install the entire package or lose any documents or settings.

Under Excel, an additional step is required to go to the Add-ins menu and check the box for ODBC to allow this to work.

Microsoft Query is run by the application (Word, Excel) and allows you to choose the data you want to return to the document, and then Query will not be required again until you want to modify the data query.

Remember that QODBC is an ODBC database to Office, and it will not show up next to dBase and Paradox, it is categorized as either SQL Databases or ODBC Data depending on your version of Office.

Microsoft states that standard (non Office) versions of Word and Excel should work with ODBC drivers by using the ODBC Driver Kit, which can be downloaded from their web site at: http://www.microsoft.com.

Q: Why is the driver so slow?

QODBC is a very fast ODBC driver. The QuickBooks interface SDK, which uses XML, is not the speediest file system available, but it does the job. The QODBC driver works with all front-end applications which can access ODBC, some of which are faster than others.

This question is almost never asked by our users using C as a front end language for ODBC, nor by report writer users with Crystal, or DBMS users with Delphi. These users will see immediately how fast the driver is because their applications are optimized for ODBC speed.

It is almost always asked by users of Microsoft Access, whose first operation is to open the largest file they have in their QuickBooks system in an Access Grid control and read it all into memory. They notice that the grid comes up quickly, but if they page down to the next screen-full of records, it takes a while. Why Access is slow compared to other systems can be easily explained.

Access is overloaded with multi-user functionality, meaning that features like updating all stations when a record changes on-line and other features are easily accomplished by the native Access file format.

When Access uses ODBC however, it attempts to do the same thing. For instance, when you open a multi-user grid on an ODBC data file, it must read the entire file into memory before continuing. This continues in the background, and when you press page-down, Access waits until the file has been completely read before beginning another query of the entire file, starting with the last record of the previous screen.

Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason.

Access only has this problem in Grid controls, which is unfortunately the first thing people use with our driver. However, if you create a query and send it as input to a report, you will see how fast our driver can run under Access.

When programming with SQL (the transport language of ODBC drivers) it is a good idea to think of an SQL table as being a "cloud" of data with potentially unlimited records. You should therefore design queries and views of only the fields and records required for the current transaction or report, and not attempt to query all records in a given table, which may overload the system.

Microsoft Access is optimized to work with its built-in data manager only (MDB files), and any Access programmer who has used ODBC will tell you about the limitations Access has in this area. Other packages work very well with ODBC data drivers.

If you would like to see what Access is doing that makes it appear to "lock up" while running a query, you may like to turn on our debug control panel. Details can be found later in this document.

Keep in mind that QODBC is not a database tool, but rather a translation tool. QuickBooks is a non-normalized flat file system which has no indexes available to us and will not perform like SQL Server or dBase files. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions.

Try and keep your result set as small as possible to get a feel for the system, carefully design and test any multi-file joins, and keep the number of returned fields to a minimum for maximum performance.

Our main goal is to make it easier to access QuickBooks data in a standardised database-like fashion, but queries must be optimized to perform as fast as possible.

Also, try and use ranges of dates on TimeCreated and TimeModified as much as possible to narrow down the data to the smallest possible segment. For example, make something similar to this the first part of your WHERE clause:

Invoice.TimeCreated >= {ts'2003-09-01 17:01:09'} AND
Invoice.TimeCreated <= {ts'2003-09-02 17:01:09'}

We also suggest you use reports whenever possible for maximum performance, which also removes the need to learn all about the internal structure of QuickBooks data.

Q: Why does Access say there are only 101 records in the file, I know there are more?

Access shows 101 records in a grid by default if no index has been specified. Choose an indexed field and sort (a-z) and it will show the full number of records in the file when it has finished reading the file into memory.

Q: I have executed a query in Access and it seems like the system is just hanging up?

We have added a debug panel for anyone that suspects a problem with Access or would like to know what the driver is currently doing. Click on the QODBC Setup Screen icon and turn on the debug panel by checking the associated box.

This will turn the feature on and it will show interactively the current table name and record, and allow you to exit a runaway query.

Q: How does QODBC determine the data path?

Entering the full data path of the .QBW file in the QODBC Setup Screen is the best way to guarantee that the driver will open the correct file. If you only use one company file, you can enter a "." (period) in the path field and the current data files will be used.

Q: What is the Open Style?

There are multiple ways of opening a QuickBooks file, and this setting allows you to determine how QODBC should open each company file. If you select "Follow Company File", we will simply use whatever mode is currently set on the file. Selecting "MultiUser Mode" will set the file to Multi-user status, where multiple users can update the file simultaneously. If you require exclusive write access to the file, select "Single User Mode."

Q: If multiple users are using QODBC to access QuickBooks at the same time, does the driver take care of record locking in QuickBooks?

The QODBC Driver uses the built-in multi-user features of every version of QuickBooks to handle concurrency.  Simply switch on the Multi-user mode in QuickBooks using the File menu and no special programming is required.

Q: When I try to edit a record in Access 2000, I'm told the record has been changed by another user, but I'm the only user?

When using MS Access 2000, you need to change the default behavior of Access 2000 under:

Options (Pulldown)
Advanced (Tab)
Default record locking
From "No Locks" to "Edited Record".

Once that has been done, you will only be prompted if the record you have edited has been changed by another user or form, and if that is the case, Access will refresh the record and allow you to re-edit the changes you wish to make as expected.

Q: What specific files can be changed by QODBC?

The 2.0 release offers the following functionality:

Add and query access to the following list items:

  • Account
  • Customer
  • Employee
  • OtherName
  • Vendor
  • StandardTerms
  • DateDrivenTerms
  • Class
  • SalesRep
  • CustomerType
  • VendorType
  • JobType
  • CustomerMsg
  • PaymentMethod
  • ShipMethod
  • SalesTaxCode
  • ToDo
  • ItemService
  • ItemNonInventory
  • ItemOtherCharge
  • ItemInventory
  • ItemSubtotal
  • ItemDiscount
  • ItemPayment
  • ItemSalesTax
  • ItemSalesTaxGroup
  • ItemGroup

Query only access is available for:

  • PayrollItemWage
  • BillExpenseLine, BillItemLine, BillLinkedTxn

The following special purpose queries allow multiple list item types as follows:

  • Entity - People (customers, employees, other names & vendors)
  • Terms - standard or date driven terms
  • Item - All discount, group, inventory, non-inventory, other charge, payment, sales tax, sales tax group, service and subtotal items

 Add and query functionality is available for the following transactions:

  • Bill
  • Charge
  • ChargeCredit
  • Check
  • CreditCardCharge
  • CreditCardCredit
  • CreditMemo
  • Estimate
  • Invoice
  • JournalEntry
  • PurchaseOrder
  • ReceivePayment
  • SalesReceipt
  • TimeTracking
  • VendorCredit

For more details on the above tables and their related fields please view the QuickBooks Table Schema.

Q: Can you explain why I'm not seeing bill payments in the Checks table in Access? Only "hand" checks appear?

The checks table only contains checks that are NOT used to pay bills. To get a complete check register, you have to use a combination of the check table and the billpaymentlinkedtxn (where checks that pay posted bills exist).  Eventually we will combine them to get a complete check register.

Q: I get an error attempting to edit or insert to the memo field (notes) for a Customer record?

This issue is not fixable by us, and will have to be addressed by Intuit in their SDK interface. The Notes field in the Customer Table is not editable and not available for insert through the SDK. You might send the Intuit development team a wish list request for this, as our driver can only do what the Intuit SDK allows us to do.

Q: I can read a file, but I get errors when I try to edit/delete/create records?

First make sure your user rights are setup and not restricted to read only in QuickBooks or your network software.

You can delete records from QuickBooks, but you must be in single-user mode using the QuickBooks company file. Also, QuickBooks must allow you to delete records - see our
Schema document for details on each file to see if it is possible to delete records from it.

The current release of our driver does not yet have the ability to void records.

In addition, certain tables have child records that must be saved at the same time as the parent or header record. The child tables are listed below following the name of the parent table:

  • Bill: BillExpenseLine, BillItemLine, BillLinkedTxn
  • Invoice: InvoiceLine, InvoiceLinkedTxn
  • Check: CheckExpenseLine, CheckItemLine
  • CreditCardCharge: CreditCardChargeExpenseLine, CreditCardChargeItemLine
  • CreditCardCredit: CreditCardCreditExpenseLine, CreditCardCreditItemLine
  • CreditMemo: CreditMemoLinkedTxn, CreditMemoLine
  • Host: HostSupportedVersions
  • Estimate: EstimateLine
  • ItemGroup: ItemGroupLine
  • ItemSalesTaxGroup: ItemSalesTaxGroupLine
  • JournalEntry: JournalEntryDebitLine, JournalEntryCreditLine
  • PurchaseOrder: PurchaseOrderLine
  • ReceivePayment: ReceivePaymentLine
  • SalesReceipt: SalesReceiptLine
  • VendorCredit: VendorCreditExpenseLine, VendorCreditItemLine, VendorCreditPaymentLine

Imediately below are some examples of various types of updates. The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it.

Q: How can I save new records to the Invoice table, Purchase orders and other tables which have single or multi-line detail attached to them?

The answer varies on your situation. Below are some examples of various types of updates. The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it.

If you need to create a one line item invoice for example, you can use a format similar to this:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID") VALUES
('470001-1071525403', '1', '250000-933272656', 'Building permit 1',
1.00000, 1.00, '20000-999022286')

The above transaction inserts all required data in the InvoiceLine table to create a complete record in the InvoiceLine table, as well as the Invoice header table, and saves the completed invoice record immediately. This is the simplest form of invoice creation.

Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we are creating a 3 line invoice using 3 SQL INSERT commands in sequence. The key to this process is the field named "FQSaveToCache". This field is not paret of the table, but is used as a flag to the QODBC driver. In the sequence below, you should note that the value of "FQSaveToCache" is set to 1 (true) for the first two line item insert statements, and then it is set to 0 (false) for the final statement.

A true setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 (false), the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.

QODBC maintains a connection for each application using the driver, and this cache is specific for each connection, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no set limit to the number of lines that can be cached for a single transaction, other than what QuickBooks would limit you to.

Since the data on the INSERT statement is being cached and not written to QuickBooks, some application tools (most notably Microsoft Access) will re-check that the data was saved properly to the target database by running a SELECT statement following a successful INSERT statement. In this example, this re-check will fail.

To get around this, use a pass-thru query (Append Query) and ignore any errors in the processing of the transaction, except for the last one. After the final line has been saved and the record is inserted into QuickBooks, then you can do your own validation by SELECTing the results of the Invoice number you just created in the Invoice and InvoiceLines tables to see that it was inserted correctly.

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1',
1.00000, 1.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 2',
2.00000, 2.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 3',
3.00000, 3.00, '20000-999022286', 0)

For multi-line invoices, some may find it easier to not include the header information with each line item, but rather to have seperate smaller commands for the Invoice Lines and a final INSERT for the Invoice header. This will also work as in the example below.

Here we create an invoice with 3 lines by using 3 InvoiceLine INSERT commands (with the caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000,
1.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000,
2.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000,
3.00, '20000-999022286', 1)

INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID",
"TxnDate", "RefNumber", "BillAddressAddr1", "BillAddressAddr2",
"BillAddressCity", "BillAddressState", "BillAddressPostalCode",
"BillAddressCountry", "IsPending", "TermsRefListID", "DueDate", "ShipDate",
"ItemSalesTaxRefListID", "Memo", "IsToBePrinted",
"CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403',
'40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '10000-933272658',
{d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test', 0,
'10000-999022286')

The same process above is used for Purchase Orders:

This example creates a one line Purchase Order. Note the FQSaveToCache field is not specified (or can be set to false)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID") VALUES
('10000-933272655', '1', '250000-933272656', 'See Attached 1', 1.0, 1.0,
1.11, '580000-1071526281')

This example creates one PurchaseOrder with three lines. Note the FQSaveToCache field, set to True except on the last line.

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See
Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See
Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
"PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
"PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
"PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
"FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656', 'See
Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 0)

This example creates one Purchase Order with three lines. Note the FQSaveToCache field, set to True. Saving the Purchase Order header saves the lines with it.

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 1)

INSERT INTO "PurchaseOrder" ("VendorRefListID", "RefNumber", "Memo",
"IsToBePrinted") VALUES ('10000-933272655', '1', 'Memo Test', 0)

This example creates one Journal Entry with 2 credit lines and 2 debit lines. Note the FQSaveToCache field, set to True except on the last line. Also, when saving Journal Entries, QuickBooks will reject transaction which do not balance the Credit and Debit sides.

INSERT INTO "JournalEntryCreditLine" ("RefNumber",
"JournalCreditLineAccountRefListID", "JournalCreditLineAmount",
"JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270541',
1.11, 'Test Memo 1', 1)

INSERT INTO "JournalEntryCreditLine" ("RefNumber",
"JournalCreditLineAccountRefListID", "JournalCreditLineAmount",
"JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270541',
2.22, 'Test Memo 2', 1)

INSERT INTO "JournalEntryDebitLine" ("RefNumber",
"JournalDebitLineAccountRefListID", "JournalDebitLineAmount",
"JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270541',
1.11, 'Test Memo 1', 1)

INSERT INTO "JournalEntryDebitLine" ("RefNumber",
"JournalDebitLineAccountRefListID", "JournalDebitLineAmount",
"JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270541',
2.22, 'Test Memo 2', 0)

If you prefer to use VBScript instead of SQL commands, the following example may be helpful to you.

Save the text below between the <<<VB>>> markers and save as the filename "InvoiceAdd.vbs", then yoiu can click on the script while QuickBooks is running and it will work. Similar code will be used inside Visual Basic language compilers.

<<<VB>>>
'This creates one invoice with three lines.
'Note the FQSaveToCache field, set to True except on the last line.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim oConnection
Dim oRecordset
Dim sLastVendor
Dim dTotalApplied
Dim dAmountDue

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open "DSN=Quickbooks Data;"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT * FROM InvoiceLine WHERE TxnId = 'X'" ,
oConnection, adOpenStatic, adLockOptimistic

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 1"
oRecordset.Fields("InvoiceLineRate").Value = 1
oRecordset.Fields("InvoiceLineAmount").Value = 1
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "20000-999022286"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 2"
oRecordset.Fields("InvoiceLineRate").Value = 2
oRecordset.Fields("InvoiceLineAmount").Value = 2
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "20000-999022286"
oRecordset.Fields("FQSaveToCache").Value = True
oRecordset.Update()

oRecordset.AddNew()
oRecordset.Fields("RefNumber").Value = "1"
oRecordset.Fields("CustomerRefListID").Value = "470001-1071525403"
oRecordset.Fields("InvoiceLineItemRefListID").Value = "250000-933272656"
oRecordset.Fields("InvoiceLineDesc").Value = "Building permit 3"
oRecordset.Fields("InvoiceLineRate").Value = 3
oRecordset.Fields("InvoiceLineAmount").Value = 3
oRecordset.Fields("InvoiceLineSalesTaxCodeRefListID").Value = "20000-999022286"
oRecordset.Fields("FQSaveToCache").Value = False
oRecordset.Update()

oRecordset.Close
oConnection.Close
<<<VB>>>

Q: How can I update inventory quantities?

The reason you cannot update Qty on Hand is that there are financial implications involved. The Inventory Adjustment and Line tables would theoretically handle this type of transaction; but in order to find the dollar adjustment you would have also have to figure out the present average cost of the item in stock. As you can see a far more complicated procedure than meets the eye.

We would expect that in time Intuit could come up with a way to do this - because the average cost IS available in the ItemInventory Table. I would also surmise that this would have to be done in single user mode - because you would have to lock anyone else from selling or purchasing that item at the time you make the adjustment; otherwise the average cost would be affected.

Q: Do you have more detailed technical information?

QODBC uses qbXML (The QuickBooks SDK) to access data in QuickBooks files. Click the link below for a document on this interface for further technical details on the process of communication with QuickBooks: Developers qbXML 2002 FAQ

Q: How can I keep up with the latest news on QODBC?

Subscribe to our announcement mailing list via e-mail at http://www.qodbc.com/support.htm  

Q: How can I get the latest version of QODBC?

To download the latest version, go to the Internet page: http://www.qodbc.com/download.htm

 

Q: How do I add customers?

If you need to create a customer, you can use a format similar to this:

INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode")
values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

For jobs, simply add a : and the job name to the Name field, check this via a query of customers from your data files or the samples.

Q: Although I know the difference between the read only and read write versions of QODBC, what would
be the purpose of writing to QuickBooks?

Our driver has different uses for different types of users. Many who come to our site think in terms of importing and exporting to QuickBooks, but our tool is for using live data in applications like Excel, Crystal Reports, Web Sites and Access.

Most end-users would prefer the read-only version because they want to create intelligent spreadsheets which update themselves from the latest numbers in QuickBooks each time you open them, with no more cutting, pasting or importing data. They can also create live Word mail merges and customized reports in Crystal or any other application they normally use.

Developers can use our read-write edition to build alternate systems for entering data, custom forms, time sheets, batch loaders and so forth.

Web developers have used our tools to create web forms where customer can see all of the latest financial reports and enter remote invoices, customer payments and time sheets from any web browser, including the new wireless web enabled cell phones.

QODBC is a universal tool, and hopefully this overview has given you some ideas as to what each of the versions of the product can be used for.


For jobs, simply add a : and the job name to the Name field, check this via a query of customers from your data files or the samples.

Q: What is the difference between the Internet version of the QODBC driver and the normal version?

Our web version of QODBC will allow you to use any web tool you wish to use (IIS, ASP, Cold Fusion) to access data live from QuickBooks without conversion.

The web version is a separate product from the normal desktop editions of QODBC. The reason for this is twofold:

  • Firstly, as a licensing issue, we must allow for use by potentially hundreds of login's to the machine remotely using the web.

  • Secondly, QuickBooks does not run as a system service (background task) which is what IIS Web Server Software runs as, so we have developed special software to allow us to communicate with QuickBooks running on the desktop.

You can upgrade to the Web Server Edition from the Read/Write version at any time for the difference in price.

Q: I have a spreadsheet that I want to pull the cash balances from 30 different companies into. Can you please tell me the most efficient way to accomplish this?

You need to do the following to get this to work in an automated fashion. First, following the instructions in our manual, setup the security in all 30 company files to allow QODBC to connect automatically to the QuickBooks data. Then setup a DSN for each of the 30 files specifying the full path name to the QBW in each DSN.

Using your Excel macro writing skills at this point you would create a script that would go one by one thru each of the data files, connecting to its DSN, running the query, putting the values in the spreadsheet, and closing the DSN. I would also suggest a 5-10 second pause between each close and open connection as well. in this case, make sure QuickBooks is NOT running on the desktop.

The reason it can't simply be programmed in to the spreadsheet to update all values at once is because QuickBooks only allows one QuickBooks company file to be open at a time. The QuickBooks SDK we talk to is basically the program as you run it on the desktop, somewhat automated. The general rule is that things you can't do in QuickBooks, you can't do in the QuickBooks SDK, which QODBC uses to get data.

The scripting language in Excel is called VBScript (or VBA) and it is quite easy to work with, and you could simply take the recorded macro for your first connection, and duplicate it for the remaining 29 files. An example of the VBScript code required to do this can be found in our install folder called QODBC Test VBScript. There are many books on using VBScript to automate Excel functions.

If you have no people in your organization who can help you write this up, we have consultants who specialize in this area and could help you write it.

Q: I keep getting an error '[QODBC] Assignment Error' when I try to import an entire table into Excel 2000?

Check to see if the table you are importing has any long fields and exclude them from the import. Some tables in QuickBooks have fields as long as 4000 characters and they cannot fit in a single spreadsheet cell.

Q: I need an example of how to insert data into the Timetracking table?

The following INSERT command works on the sample database included with QODBC. Modify the data elements to match your system.

INSERT INTO "timetracking" ("entityreflistid","entityreffullname",
"durationminutes","txndate","CustomerRefFullName","ItemServiceRefFullName",
"Payrollitemwagereffullname") VALUES ( '370000-933272659', 'Dan T. Miller',
480, {d'2003-12-01'}, 'Pretell Real Estate:75 Sunset Rd.', 'Removal','Salary' )

Q: How do I do an Inner Join in QODBC?

Inner Joins and Left Joins can be done with the following syntax (For a right join, reverse the table order and use a left join):

SELECT * FROM {OJ Invoice INNER JOIN Customer ON
(Invoice.CustomerRefListID = Customer.ListID)}

Other hints: If you can include a starting value or range of values (> and <) for TxnDate or TimeModified as the first option in your WHERE statement you can improve your performance many times over. Also there is a pretty big hit for using ORDER BY, if you can live without it you can improve performance. It is also faster to specify only the fields you require instead of a wildcard.

Q: Can I get some examples of how to use QODBC via Visual Basic?

Click here:
http://www.qodbc.com/docs/support/vbexamples.zip to download some VBS files that show several examples of queries and updates. They can be pasted into a Visual Basic 6 program and used. You can strongly type the dimmed variables and change the CreateObjects to news if you reference ado. The VBSs will run, as is, pasted in.

Also click here:
http://www.qodbc.com/docs/support/vbdemosrc.zip for the source to VBDemo32. It came mostly from Microsoft. It uses the Sheridan grid so you won't be able to use run it unless you have that but you can look at how to do calls ODBC directly. By far the fastest executing programs can be written with it. It will just be time consuming to get a library built to use it.

Q: Do you have any examples of how to use QODBC with Microsoft .NET?

Yes, click the link below, uncompress the zip archive, then open and run the project:
http://qodbc.com/docs/support/QODBCDemoforDotNet.zip

Q: Is it possible to declare and use a local variable with a query? What is the QODBC syntax?

No, there is not a TSQL type language for ODBC. ODBC has an API interface that you can program against using cursors and such but is is much simpler to use access methods like ADO.

Q: My customer has a very large database and multiple users need reports frequently and they take a while to run. Will your product help?

The QODBC driver allows you to run built-in QuickBooks reports and display the results in programs like Crystal Reports and MS Excel. Although the speed of reports may not actually differ in our product from inside QuickBooks, it will not lock up QuickBooks while they are running. Our product can run multiple reports on your machine while you are performing other tasks in the QuickBooks application.

In fact you could completely automate the running of reports using a product like Crystal Reports overnight and have all of the reports ready in PDF, Word or printed form to be reviewed the next day.

In addition, QODBC also supports porting all of the QuickBooks data and reports into SQL Server using Data Transformation Services where the reports could be run on the copy of the data. A QODBC customer has been nice enough to make a document on how he used QODBC via MS SQL Server Data Transformation Services to import all of the tables into SQL Server. To view the document, click here:
http://qodbc.com/docs/support/QBtoSQLServer.doc


See Data Schema REPORT DETAILS for all the sp_reports (stored procedure reports) you will be able to get including information about the columns and parameters for each report.

Q: If I simply want to export the results of a financial report to Excel or Access, do I have to figure out how to calculate the report again or is there a simpler way?

Many of the reports built-in to QuickBooks can be accessed directly via the QODBC Driver. This means that reports that you are already familiar with in QuickBooks can be run and the results can be sent to MS Excel or Access with live accounting data.

An example is this query:
sp_report CustomerBalanceDetail show Text, Blank, TxnType, Date, RefNumber,
Account, Amount, RunningBalance parameters DateMacro = 'All'

In place of a SQL command like SELECT or INSERT, we use a stored procedure pass-thru command, the results of which produce a customer outstanding balance report. This data can be loaded into any application you desire and shown in the format you require as frequently as you like. Numerous reports are available in this fashion, and we think this is the easiest way to get started with our driver because the data is already in a format you can recognize, as opposed to raw data. See
Data Schema REPORT DETAILS for all the sp_reports (stored procedure reports) you will be able to get including information about the columns and parameters for each report.

Q: How are dates formatted in SQL queries when using the QuickBooks generated time stamps?

SELECT * FROM Customer WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}

Q: How do I narrow a report to a date range to make it run faster with less data?

Always use >=, <= and = (greater than or equal to, less than or equal to, or equal) when using date fields in the where claose of the select statements. If you use > or < it will not jump in.

Examples:
SELECT * FROM Invoice WHERE TimeModified = {ts'2002-09-25 11:54:25.000'}
SELECT * FROM Invoice Where TxnDateMacro = 'LastMonth'
SELECT * FROM Invoice Where TxnDate = {d'2003-12-23'}
SELECT * FROM Invoice WHERE TimeModified >= {ts'2002-09-25 00:00:00.000'}
AND TimeModified <= {ts'2002-09-26 00:00:00.000'}

Q: How can I get a list of tables available via the ODBC driver? In SQL Server, I use "SELECT * FROM sysobjects WHERE..."

There is not an equivalent ODBC command for this, but the following ADO code can be used:

Const adSchemaTables = 20
Dim oConnection
Dim oSchema
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"
Set oSchema = oConnection.OpenSchema(adSchemaTables)
Do While (not oSchema.EOF)
             MsgBox oSchema.Fields("Table_Name").Name & ": " &
oSchema.Fields("Table_Name").Value
             oSchema.MoveNext
Loop
oSchema.Close
oConnection.Close

Q: How can I get a customer statement via QODBC?

A customer statement is basically a GeneralDetailReport (TxnListByCustomer) or a CustomDetailReport in both cases filtering by the customer whose statement you want. See the
Data Schema REPORT DETAILS documentation for further information.

Q: I ran a query which was invoiceline.refnumber < 5 and it returned 1 10 11 12 13 etc up to 49 - why?

QuickBooks defines some fields which you would expect to be numeric type as text fields, messing with the ability to compare properly. In this case you need to define the filed as a specific numeric field by using a convert function like this one:

SELECT * FROM InvoiceLine where {fn Convert(invoiceline.refnumber,
SQL_INTEGER)} < 5

Q: Where can I ask questions about accounting issues and problems using QuickBooks software?

We recommend the following e-mail discussion list, with many helpful QuickBooks experts willing to help novice users:
http://groups.yahoo.com/group/QuickBooks/

Q: How do I import QuickBooks data into MS SQL Server?

A QODBC customer has been nice enough to make a document on how he used QODBC via MS SQL Server Data Transformation Services to import all of the tables into SQL Server. To view the document, click here: http://qodbc.com/docs/support/QBtoSQLServer.doc

Q: Can I use your QODBC Driver with the Java ODBC Bridge product from Sun?

Yes, here is some example code from one of our happy customers:

// QuickBooks variables
Connection con =null;
Statement stmt = null;
ResultSet rs = null;

// parameters for QuickBooks database
static final String url = "jdbc:odbc:quickbooks";
// quickbooks is a System DSN that is the name of the QODBC driver
// On WindowsXP it can be set up at Control Panel > Administrative Tools >
// Data Sources (ODBC)

static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

public static void main(String[] args) throws Exception {
InsertCustomers t = new InsertCustomers();
}

public InsertCustomers() throws Exception {
try {
Class.forName(driver);
con = DriverManager.getConnection(url);
stmt = con.createStatement();
System.out.println("Querying QB customer table");
rs = stmt.executeQuery("SELECT * FROM customer");
// rest of code deleted

Q: How can I apply check payments or credits to bills?

Yes, here is some example code and the setup data required:

PayeeEntityRefListID = 'C0000-933272656' (Required)
BankAccountRefListID = '20000-933270541' (Required)
IsToBePrinted = 1 (Either this one or RefNumber is required)
AppliedToTxnTxnID = '2F78-1071505657','976-933373192','A2D-933373874', (Find from Bill Table)
AppliedToTxnPaymentAmount = 200.75 (Necessary)
AppliedToTxnSetCreditCreditTxnID = '5522-1197768491' (Find from VendorCredit Table)
AppliedToTxnSetCreditAppliedAmount = 10.00
TxnDate = 2007-12-15


INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, {d'2007-12-15'}, 0)

INSERT INTO BillPaymentCheckLine PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate,FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, '5522-1197768491', 10.00, {d'2007-12-15'}, 0)
 

Q: How can I receive payments or credits against customer invoices?

Yes, here is some example code and the setup data required:

CustomerRefListID = '790000-1071511999' (Required)
DepositToAccountRefListID = '80000-933270541' (Undeposited Funds)
TotalAmount = 129.30 (Necessary)
AppliedToTxnTxnID = '4909-1071512260' (Find from Invoice Table)
AppliedToTxnPaymentAmount = 129.30 (Necessary, otherwise the payment in TotalAmount will be unapplied)
AppliedToTxnSetCreditCreditTxnID = '5540-1197772280' (Find from CreditMemo Table)
AppliedToTxnSetCreditAppliedAmount = 10.03
TxnDate = 2007-12-15
 

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, FQSaveToCache) Values ('790000-1071511999', '80000-933270541', 129.30, '4909-1071512260', 129.30, {d'2007-12-15'} ,0)

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate, FQSaveToCache) Values ('790000-1071511999', '80000-933270541', 129.30, '4909-1071512260', 129.30, '5540-1197772280', 10.03, {d'2007-12-15'}, 0)