Create a connection to QuickBooks by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the QuickBooks icon is not available, click the Add More icon to download and install the QuickBooks connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to QuickBooks

The Sync App makes requests to QuickBooks through the QuickBooks Gateway. The QuickBooks Gateway runs on the same machine as QuickBooks and accepts connections through a lightweight, embedded Web server. The server supports SSL/TLS, enabling users to connect securely from remote machines. The first time you connect, you will need to authorize the Sync App with QuickBooks. For more information, refer to our Using the QuickBooks Gateway guide.

Follow the steps below to authorize with QuickBooks and connect to a company file when both QuickBooks and the Sync App are running on your local machine.

1. Open QuickBooks as an administrator and open the company file you want to connect to.
2. Connect to QuickBooks. A dialog will appear in QuickBooks prompting you to authorize the Sync App. After granting access to the Sync App, you can now execute commands to QuickBooks.

3. If you want to connect to the company file when QuickBooks is closed, set the CompanyFile connection option when you execute commands. QuickBooks will open automatically in the background with the file specified.

   Note that if QuickBooks is open through the application UI, only that CompanyFile can be used.

The CData QuickBooks Desktop Gateway can be used to read and write to QuickBooks in situations where direct COM access to QuickBooks is not available (e.g., ASP.NET, Java, or QuickBooks on a remote machine). Follow the procedure below to connect to QuickBooks for the first time through the Desktop Gateway:

1. If you have not already done so, download the CData QuickBooks Desktop Gateway from here and install it.
2. Open the company file you want to connect to in QuickBooks using an administrator account in single-user mode.

3. Open the CData QuickBooks Desktop Gateway from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.

   Note: The CData QuickBooks Desktop Gateway does not use the User and Password properties to access QuickBooks; the User and Password properties authenticate the user. Authentication to QuickBooks is handled by the ApplicationName property.

4. When you first connect, a dialog appears in QuickBooks prompting you to authorize the application. After authorizing, you can execute commands to QuickBooks. Specify the URL of the Desktop Gateway and the User and Password. By default, the Gateway connects to the currently open company file.
5. If you want to access QuickBooks when QuickBooks is not running, save the company file information for the user. The Desktop Gateway automatically opens QuickBooks in the background with the company file for that user.

NOTE: that if the QuickBooks UI is open, you can only connect to that company file. Additionally, the user permissions you specify for the Desktop Gateway must match the user permissions you used for QuickBooks. The Desktop Gateway installs as a service in the current user account.

How do I Connect to QuickBooks over SSL/TLS?

You can enable SSL/TLS on the Advanced tab.

You will also need to send your public key certificate to the Sync App. You can do so by setting the SSLServerCert property.

The following sections show how to resolve common connection errors and detail functionality that may be needed in more advanced integrations. Contact the support team for help tracing the source of an error or circumventing a performance issue.

Troubleshooting

Automatic Login Access Denied or Access Denied

Try the following to resolve this error:

• If this error occurs the first time that you connect to a company file, ensure that QuickBooks is open as an administrator in single-user mode. Be sure to open the company file that you want to allow access to.
• The application may have permission to access the company file but not to log in automatically. To allow automatic login, open the company file preferences in QuickBooks by clicking Edit -> Preferences -> Integrated Applications -> Company Preferences and then select the QuickBooks Gateway application. Then, try connecting again.
• In the company file preferences, ensure that the following option is NOT checked: "Don't allow any applications to access this company file".
• Set DelayAfterClose.

Could Not Start QuickBooks

This is the most common error and the solution depends on your specific QuickBooks installation. Follow the steps below to identify several typical causes for this error.

1. Check the log for the QuickBooks request processor, QBSDKLOG.txt. The file is not necessarily in the same directory as QuickBooks. The location of the file depends on the version of QuickBooks and the operating system you are using. The qbsdklog.txt may be in the following locations:

   C:\Program Files\Common Files\Intuit\QuickBooks
   C:\ProgramData\Intuit\QuickBooks
   C:\Documents and Settings\All Users\Intuit\QuickBooks

   Modify the QBSDK.INI file by appending the following value to the end:
   

   [LOG]
   LEVEL=verbose
   UnlimitedSize=Y

   This ensures that the log is not cleared and that the error message in the QBSDKLOG.txt file is actually captured.

2. Check if the error message in this file matches one of those below:

   	Is your company file on a mapped drive? If so, the QBSDKLOG will contain entries similar to the ones below: 20140623.102658 E 5136 RequestProcessor Unknown QBInstanceFinder error. File Name: "S:\Quickbooks\MyCompanyFile.qbw" hr = 80070057 20140623.102658 E 5136 RequestProcessor Could not find or create an instance of QuickBooks using InstanceFinder hr = 80040403 The solution is to use the network path instead of a mapped drive.
   	Does the QBSDKLOG contain many error messages like the one below? 20140612.145409 I 272 RequestProcessor Previous instance of QB is going down..! Wait and retry to get a new instance. This error can occur if a request is received to start a new QuickBooks instance while the previous QuickBooks instance is closing. Repeated connections to QuickBooks in short succession can cause abnormal behavior. You can set DelayAfterClose to wait before a new connection is opened. You can also reuse the same connection for multiple requests by enabling persistent connections on the Advanced tab in the QuickBooks Gateway and then setting the QBXMLVersion property in the connection string.
   	In addition to enabling persistent connections, you can use the following QuickBooks Gateway settings to have more control over starting and stopping the QuickBooks process. When enabled, the QuickBooks Gateway will take steps to resolve connection issues if the conditions below are met. In some scenarios, the QuickBooks Gateway may forcibly end the QuickBooks process. USE THESE SETTINGS WITH CAUTION: CloseAndRetryConnect: Boolean with a default of 'false'. If set to true, the QuickBooks Gateway will attempt to close the QuickBooks process if an error is encountered when the QuickBooks Gateway attempts to launch a new instance of QuickBooks. All other settings are only applicable when this setting is set to true. CloseAndRetryCount: Integer with a default of 3. The number of times to try to close and relaunch QuickBooks. Our developers found that 3 worked in all cases we encountered. CloseAndRetryTimeout: Integer with a default of 30. The number of seconds to wait for the current QuickBooks process to close before trying to connect again. CloseAndRetryErrorList: Comma-separated list of error codes with a default of '0x80040408,0x80040402'. Specifies a list of error codes that will cause the QuickBooks Gateway to try and forcibly end the QuickBooks process. A value of '*' means all errors. It is not recommended that you change this property unless you have a very specific reason to do so. You can set each of these properties as registry keys in HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector. In most situations, only CloseAndRetryConnect needs to be set the True. Restart the QuickBooks Gateway for the changes to take effect.

Fine Tuning Data Access

You can use the following properties to gain more granular control over how the Sync App surfaces functionality in the QuickBooks API:

• CustomFieldMode: By default, custom fields are displayed and modified as XML values. However, you can also use JSON to work with custom fields.
• IncludeLineItems: By default, line items are returned only when you query a line item table such as InvoiceLineItems. This saves performance when querying base transaction tables such as Invoices. Set this property to true to retrieve line item data when you query the base transaction table.
• IncludeLinkedTxns: By default, the Sync App does not retrieve linked transactions when you query a base table, similar to IncludeLineItems.

The CData QuickBooks Desktop Gateway is a simple application that facilitates connections to company files from your application. The Desktop Gateway accepts connections via a lightweight embedded Web server that runs on the machine where QuickBooks is installed. The server supports SSL/TLS, enabling users to connect securely from remote machines.

The first time you connect to QuickBooks, you must authorize your application. Complementing the per-application authentication of QuickBooks, the Desktop Gateway has per-user authentication. Before connecting to QuickBooks for the first time, configure at least one Desktop Gateway user.

You can configure users through the UI on the Users tab. You can then follow the procedure in "Getting Started" to connect an application to QuickBooks. After connecting, you can monitor QuickBooks connections on the Status tab.

It is recommended to configure the Desktop Gateway in the UI, but you can also run the Desktop Gateway from the command line. This can simplify deploying the Desktop Gateway in scenarios where normally there is not a user logged in, such as running a Web server. See the Advanced page to configure the Desktop Gateway when you are not using the UI.

The Desktop Gateway automatically manages the connection to QuickBooks, but you can configure almost every aspect of how users connect to QuickBooks through the Desktop Gateway. The following pages outline the capabilities of the Desktop Gateway and how to get started.

The Users tab provides an interface to add, edit, and delete users. At least one user must be added before communicating with QuickBooks.

This tab displays a list of existing users along with information about the user's configuration.

When adding or editing a user, the following options are available:

• User: Sets the username. This is required.
• Password: Sets the password for the user. This is required when using Basic authentication (default).
• Company File: Specifies the company file with which the application will communicate. By default this is the company file that is currently open in QuickBooks. This can also be set to the absolute path to a company file (.qbw file). A company file must be specified in order to access the company file when QuickBooks is closed.
• Authentication: Specifies the type of authentication to perform when the user connects. The QuickBooks Gateway supports the following authentication methods:

  Basic Authentication (default):	Authenticates the user with a username and password.
  Windows Authentication:	Authenticates the user as a Windows user. In this case the Password field is not applicable. When the QuickBooks Gateway receives a connection request, it will authenticate the user to Windows using the credentials supplied in the request.

• Application Name: Optionally sets the name of the application as seen by QuickBooks. Authentication to QuickBooks is handled based on the provided application name.
• Data Access: Specifies the allowed access for the user.

  Full:	Allows read and write access for the user.
  Read-only:	Restricts the user to read-only operations. QuickBooks data cannot be modified.

The Test Connection button provides a quick way to verify the application can connect with QuickBooks.

When a user is added the QuickBooks Gateway will prompt you to authorize the application with QuickBooks if necessary.

The Status tab provides a log of the activity happening with the QuickBooks Gateway. Logs can be cleared or copied by right-clicking in the Recent Activity window.

You can adjust the detail of the logs to include information useful when troubleshooting: Select the granularity in the Log Mode menu on the Advanced tab. On the Advanced tab, you can also configure the QuickBooks Gateway to write logs to a file and select the log rotation interval.

The Advanced tab allows granular control over the QuickBooks Gateway's server. The QuickBooks Gateway contains an embedded Web server that runs as a Windows service and listens for HTTP requests. Each request contains the XML data to be communicated to QuickBooks as well as configuration settings specifying how the connection is to be opened. The QuickBooks Gateway then communicates with QuickBooks via COM and returns the QuickBooks response (or an error message) in the HTTP reply.

This chapter details how to control each of these aspects of connecting to QuickBooks through the UI, command-line interface, and the registry. The following sections detail the options available on the Advanced tab.

Logging Options

• Write Logs to a Folder: Enables or disables writing log files to the specified folder in addition to writing logs to the Status tab.
• Folder: Specifies the folder where log files are written.
• Log Rotation: Determines how logs are organized on disk. Creates one file for each day, week, or month, depending on the following values:

  Daily (default):	Uses a new log file every day. Files are written with the format "yyyy_MM_dd.txt". For example, "2013_09_23.txt".
  Weekly:	Uses a new log file every week. Files are written with the format "yyyy_ww.txt". For example, "2013_34.txt", where 34 means this is the 34th week of 2013.
  Monthly:	Uses a new log file every month. Files are written with the format "yyyy_mm.txt". For example, "2013_09.txt".

• Log Mode: Sets the verbosity of the log output. In most situations, Info (the default) is sufficient. The Verbose option is helpful for debugging purposes.

IP Options

• Port: The port on which the server listens.
• Allowed Clients: A comma-separated list of host names or IP addresses that can access the server. The wildcard character '*' is supported. If unspecified (default) any client can connect.

Enabling Persistent Connections

All communications to QuickBooks company files must first go through QuickBooks. If QuickBooks is closed, this means that for each attempt to connect to the company file, QuickBooks needs to be launched and then closed again. By default the QuickBooks Gateway queues requests for data and performs the necessary authentication for each request. The following options can be used to override this behavior and keep the connection to the company file alive after the query finishes executing, so further requests will respond more quickly.

Warning: If a user attempts to manually open QuickBooks while a persistent connection is opened, QuickBooks will return an error stating that the company file is already in use.

• Enable Persistent Connection: This is disabled by default: Normally your code controls when the connection to QuickBooks is opened and closed by calling the Open and Close methods; however, when this setting is enabled, the QuickBooks Gateway establishes a persistent connection to QuickBooks even when Open and Close are not used. This allows multiple applications to share the connection and simultaneously access the QuickBooks Gateway.
• Idle Timeout: Sets the idle timeout for the persistent connection in seconds. This is only applicable to the persistent connection. If there is no activity within this time window the QuickBooks Gateway closes the connection.

Enabling TLS/SSL

Enable TLS (1.x) to encrypt communication between your application and the QuickBooks Gateway. TLS/SSL uses digital certificates to protect the confidentiality, integrity, and authenticity of your data: You can generate these certificates on the Advanced tab. Once you have enabled TLS, you will need to send your public key certificate to any connecting applications.

The following options are used to configure TLS/SSL:

• Enable TLS: Enables or disables TLS (1.x) communication.
• Select Certificate: Loads an existing certificate.
• Generate Certificate: Creates a new certificate.

Loading a certificate displays information about the certificate; the properties of the certificate cannot be set directly. Note: Enabling TLS disables plaintext connections.

In addition to the UI, the QuickBooks Gateway has a command-line interface that makes it easy to deploy on machines where a user is not always logged in, for example, a Web server. To facilitate deployment to these environments, the QuickBooks Gateway contains two executables:

RemoteConnector.exe	Provides the user interface and allows configuration of the application.
RemoteConnectorService.exe	Processes requests and performs all interaction with QuickBooks.

The syntax for managing the QuickBooks Gateway Windows service from the command line is as follows:

RemoteConnectorService.exe /Service <Command>

The following commands are available:

Install	Installs the Windows service.
Delete	Deletes the Windows service.
Start	Starts the Windows service.
Stop	Stops the Windows service.
State	Reports the current state of the Windows service (started or stopped).
Auto	Changes the Windows service startup type to Automatic.
Manual	Changes the Windows service startup type to Manual.
Disabled	Changes the Windows service startup type to Disabled.

You can start the MySQL daemon from the command line, as shown below:

java -jar cdata.jdbc.quickbooks.jar [ options ]

The following command-line options are available:

	Option	Description
	-h, --help	Display help for available options and exit.
	-f, --config-file	The configuration file for the daemon.
	-u, --user	The user allowed to connect. Use a configuration file to configure multiple users. If a user is specified on the command line, then only that user is given access.
	-p, --password	The password for the user specified with the user option. If both user and password are set on the command line, the users section in the config file is ignored.
	-d, --database	The database that clients will use to connect. If multiple databases are specified in the config file, connections are allowed to only the database specified on the command line.
	-c, --connection	The connection string used to connect to the data source being surfaced. If no connection string is specified on the command line, the connection string is read from the config file.
	-P, --port	The port number to use to listen for TCP/IP connections. The default port is 3306.
	-m, --max-connections	The maximum number of allowed TCP/IP connections. The default value is 25 connections.
	--session-timeout	The session timeout time in seconds. The default timeout is 20 seconds.
	-t, --protocol	The protocol used for remoting. The default value is MySQL.
	-g, --logfile	The full path of the log file.
	-F, --logrotationscheme	The interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt).
	-v, --verbosity	The verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
	--test	The database to test the connection with. If this property is not specified, the default database is used.
	--ssl-cert	The path to the SSL certificate.
	--ssl-subject	The subject of the SSL certificate.
	--ssl-password	The password of the SSL certificate.
	-n, --nodeid	Displays the NodeId of this machine.
	-l, --license	Installs the license on this machine. This option will prompt you for the type of license and other details.

Options specified on the command line take precedence over options specified in the config file. You can pass in command-line options to specify a restricted subset of the options allowed in the Configuration File.

All configuration data is read from the registry at "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector". Each user will have a separate subkey with user-specific settings. For instance "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector\User1".

Application-Level Settings

Name	Type	Description
LocalAuth	String	A randomly generated administrator password that is used for authorization between the user interface and the Windows service. This is only used when authorizing a user configured for Windows authentication to QuickBooks from the user interface. This may be removed or changed if desired.
AllowedClients	String	A comma-separated list of host names or IP addresses that can access the server. The wildcard character '*' is supported (default). If unspecified any client can connect.
AuthFlags	DWORD	Specifies the versions of QuickBooks to which the application can connect. The value is a binary OR of the values below, represented in hex. The default value is "0xF" (all editions are supported). "" or 0 (Do not send any auth flags) 0x01 (Simple Start) 0x02 (Pro) 0x04 (Premier) 0x08 (Enterprise)
CloseAndRetryConnect	DWORD	Specifies whether connection retry logic is enabled. When set to 1 (True), if an error is encountered while opening a connection to QuickBooks the application will attempt to stop the QuickBooks process and reconnect. The CloseAndRetryTimeout, CloseAndRetryCount, and CloseAndRetryErrorList settings are applicable when this setting is 1 (True).
CloseAndRetryTimeout	DWORD	Sets the time in seconds that the application will wait for the connection to QuickBooks to be established. The default value is 30 (seconds). If the timeout is reached, the QuickBooks process will be closed and the connection will be retried. Note that this setting should be adjusted with caution. If the timeout is set too low the QuickBooks process may not have time to open normally before reaching the timeout. This setting is only applicable when CloseAndRetryConnect is True.
CloseAndRetryCount	DWORD	Sets the number of times to retry the connection. If an error is encountered while opening a connection to QuickBooks, the application will stop the QuickBooks process and retry until this limit is reached. The default value is 3. This setting is only applicable when CloseAndRetryConnect is True.
CloseAndRetryErrorList	String	Specifies a comma-separated list of QuickBooks error codes on which to retry a connection. If QuickBooks returns an error code listed in this property, the QuickBooks process will be stopped and the connection will be retried. If the error is not in this list the application will return the error as normal. The default value is "0x80040402,0x80040408". Specify the value "*" to indicate all errors. This setting is only applicable when CloseAndRetryConnect is True.
QBInstanceFile	String	Specifies the full path to the QBINSTANCEFINDER file in the QuickBooks installation. For instance: "C:\ProgramData\Intuit\QuickBooks\QBINSTANCEFINDER17.INI". This setting is only applicable when CloseAndRetryConnect is set to True. If the connection retry logic stops the QuickBooks process the specified QBINSTANCEFINDER file will be cleared of any previous entries. QuickBooks uses the QBINSTANCEFINDER file to keep track of open instances, however, in some situations it may not be properly reset after stopping the process. When specified this setting allows the application to properly reset the file after stopping the process.
LocalHost	String	Sets the host name or user-assigned IP interface through which connections are initiated or accepted. In most cases this does not need to be set, as the application will use the default interface on the machine. If you have multiple interfaces, you can specify the interface to use here. For instance, "192.168.1.102".
LogEnabled	DWORD	Enables or disables logging to a file. Logs are always written to the console. The default is 0 (False).
LogDir	String	Sets the path to a folder on disk where log files will be written. This is only applicable if LogEnabled is set to True.
LogFormat	DWORD	Sets how often new log files are created. Possible values are the following: 0 (Daily - default) 1 (Weekly) 2 (Monthly)
LogLevel	DWORD	Sets the logging level. Possible values are the following: 0 (Off) 1 (Error) 2 (Warning 3 (Info - default) 4 (Verbose)
LogPort	DWORD	Sets a separate port for logging. Log messages are sent over UDP from RemoteConnectorService.exe to the UI. By default this is the same value as the port defined in the Port option. Set this option to avoid using the same port as another UDP service running on the same machine.
Port	DWORD	Sets the port where the server listens for incoming connections. The default value is 8166.
PersistentEnabled	DWORD	Enables or disables persistent connections to QuickBooks. The default is 0 (False), meaning that your code controls when the connection to QuickBooks is opened and closed by calling the Open and Close methods. However, when this setting is enabled, a persistent connection to QuickBooks is established by the QuickBooks Gateway even when Open and Close are not used. This is helpful in situations when multiple applications may be simultaneously accessing the QuickBooks Gateway, because it allows them to share the connection.
PersistentIdleTimeout	DWORD	Sets the idle timeout for the persistent connection in seconds. If there is no activity within this time window, the connection to QuickBooks will be closed. This is only applicable when PersistentEnabled is True.
PromptForRegPermissions	DWORD	Specifies whether to prompt to modify registry permissions when access is not allowed. This is only applicable when saving settings from the UI.
RunAsService	DWORD	Run the application as a service or with the standard run-time permissions. The default value is 1 (True).
SSLCertPassword	String	Sets the password of the SSL certificate.
SSLCertStore	String	Sets the location of the SSL certificate. This may be a path to a file or the name of a Windows certificate store: "MY", "ROOT", "CA", or "SPC".
SSLCertSubject	String	Sets the subject of the SSL certificate.
SSLCertType	String	Sets the type of SSL certificate to use. A certificate must be specified when SSL is enabled. The PFX option signifies a .pfx file on disk. The User option signifies the user's Windows certificate store. The Machine option signifies the Windows certificate store of the machine.
SSLEnabled	DWORD	Sets whether TLS/SSL connections are allowed. The default value is 0 (False). Enabling TLS/SSL disables plaintext connections.
Timeout	DWORD	Sets the operational timeout for connected clients. The default value is 60.
UseInteractiveLogon	DWORD	Sets whether interactive or network logon will authorize users when AuthMode is set to 1 (Windows). In most cases this does not need to be set. This should be set to 1 (True) if your domain controller is Samba. The default value is 0 (False).

User-Level Settings

AppName	String	Sets the name of the application that will be used to provide authentication to QuickBooks when a connection is made. If this value is not set, the QuickBooks Gateway uses the value provided by the client.
CompanyFile	String	Sets the path to a QuickBooks company file (.qbw). If this is not set, the currently open company file is used. When QuickBooks is not running, this option must be set.
Password	String	Sets the password of the user. This is required when AuthMode is set to 0 (Basic Authentication). The QuickBooks Gateway application will always store the SHA-256 hash of the password for security. However, this may also be manually set to a plaintext password to allow backward compatibility.
AuthMode	DWORD	Sets the type of authentication to perform when the user connects. From the client side the process of connecting is exactly the same no matter which option you choose. Possible values are the following: 0 (Basic Authentication - default) 1 (Windows Authentication)
Authorized	DWORD	Specifies whether the AppName has been authorized for the CompanyFile. If 1 (True) the AppName has been authorized with the CompanyFile. This is an indicator used by the application when changing settings in the UI.
ConnectionMode	String	Sets the connection mode for the user. The default is DontCare. In most cases you do not need to set this value. If this is not set, the application will connect in whatever mode QuickBooks is already open in. Possible values are the following: DontCare (default) Single Multi
ReadOnly	DWORD	Specifies whether the user has read-only (1) or full access (0).

This section details a selection of advanced features of the QuickBooks Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to QuickBooks and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App models entities in the QuickBooks API as tables, views, and stored procedures. There are three parts to the Data Model: Tables, Views, and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The CData Sync App models the data in Tables so that it can be easily queried and updated.

NOTE: In case of Bulk insert operation, the Sync App supports multiple values only for the aggregate columns whereas for the other columns only single value is supported.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

The Sync App models the data in QuickBooks into a list of tables that can be queried using standard SQL statements.

Generally, querying QuickBooks tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

QuickBooks Connector for CData Sync Tables

Name	Description
Accounts	Create, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher.
BillExpenseItems	Create, update, delete, and query QuickBooks Bill Expense Line Items.
BillLineItems	Create, update, delete, and query QuickBooks Bill Line Items.
BillPaymentChecks	Create, update, delete, and query QuickBooks Bill Payment Checks. QBXMLVersion must be set to 6.0 or higher to update a BillPaymentCheck.
BillPaymentChecksAppliedTo	Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
BillPaymentCreditCards	Create, update, delete, and query QuickBooks Bill Payments.
BillPaymentCreditCardsAppliedTo	Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
Bills	Create, update, delete, and query QuickBooks Bills.
BuildAssemblies	Insert, Update, delete, and query QuickBooks Build Assembly transactions.
BuildAssemblyLineItems	Create and query QuickBooks Build Assembly transactions.
CheckExpenseItems	Create, update, delete, and query QuickBooks Check Expense Line Items.
CheckLineItems	Create, update, delete, and query QuickBooks Check Line Items.
Checks	Create, update, delete, and query QuickBooks Checks.
Class	Create, update, delete, and query QuickBooks Classes. QuickBooks requires QBXML version 8.0 or higher for updates to a Class.
CreditCardChargeExpenseItems	Create, update, delete, and query QuickBooks Credit Card Charge Expense Line Items.
CreditCardChargeLineItems	Create, update, delete, and query QuickBooks Credit Card Charge Line Items.
CreditCardCharges	Create, update, delete, and query QuickBooks Credit Card Charges.
CreditCardCreditExpenseItems	Create, update, delete, and query QuickBooks Credit Card Credit Expense Line Items.
CreditCardCreditLineItems	Create, update, delete, and query QuickBooks Credit Card Credit Line Items.
CreditCardCredits	Create, update, delete, and query QuickBooks Credit Card Credits.
CreditCardRefunds	Query and Insert QuickBooks AR Credit Card Refund transactions.
CreditMemoLineItems	Create, update, delete, and query QuickBooks Credit Memo Line Items.
CreditMemos	Create, update, delete, and query QuickBooks Credit Memos.
Currency	Create, update, delete, and query QuickBooks Currencies. This table requires QBXML version 8.0 or higher, and you will need to enable multiple Currencies in your QuickBooks company file to use it.
CustomerContacts	Create, update, delete, and query QuickBooks Customer Contacts. This table requires QBXML version 12.0 or higher, and is only available in QuickBooks editions 2016 and above.
CustomerMessages	Create, delete, and query Customer Messages.
CustomerNotes	Create, update, and query QuickBooks Customer Notes. This table requires QBXML version 12.0 or higher.
Customers	Create, update, delete, and query QuickBooks Customers.
CustomerShippingAddresses	Create, update, delete, and query QuickBooks Customer Shipping Addresses. Multiple Customer Shipping Addresses are supported in only QuickBooks 2013 and higher. Additionally, this table requires QBXML version 12.0 or higher.
CustomerTypes	Create, update, delete, and query QuickBooks Customer Types.
DateDrivenTerms	Create, delete, and query QuickBooks Date Driven Terms.
DepositLineItems	Create, update, delete, and query QuickBooks Deposit Line Items. QBXMLVersion must be set to 7.0 or higher to update a deposit.
Deposits	Create, update, delete, and query QuickBooks Deposits. QBXMLVersion must be set to 7.0 or higher to update a deposit.
EmployeeEarnings	Create, update, delete, and query QuickBooks Employee Earnings.
Employees	Create, update, delete, and query QuickBooks Employees.
EstimateLineItems	Create, update, delete, and query QuickBooks Estimate Line Items.
Estimates	Create, update, delete, and query QuickBooks Estimates.
InventoryAdjustmentLineItems	Create and query QuickBooks Inventory Adjustment Line Items.
InventoryAdjustments	Create, query, and delete QuickBooks Inventory Adjustments.
InventorySites	Create, update, delete, and query QuickBooks Inventory Sites. Inventory Sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
InvoiceLineItems	Create, update, delete, and query QuickBooks Invoice Line Items.
Invoices	Create, update, delete, and query QuickBooks Invoices.
ItemLineItems	Create, update, delete, and query QuickBooks Item Line Items.
ItemReceiptExpenseItems	Create, update, delete, and query QuickBooks Item Receipt Expense Line Items.
ItemReceiptLineItems	Create, update, delete, and query QuickBooks Item Receipt Line Items.
ItemReceipts	Create, update, delete, and query QuickBooks Item Receipts.
Items	Create, update, delete, and query QuickBooks Items.
JobTypes	Create and query QuickBooks JobTypes.
JournalEntries	Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
JournalEntryLines	Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
OtherNames	Create, update, delete, and query QuickBooks Other Name entities.
PaymentMethods	Create, update, delete, and query QuickBooks Payment Methods.
PayrollNonWageItems	Query QuickBooks Non-Wage Payroll Items.
PayrollWageItems	Create and query QuickBooks Wage Payroll Items.
PriceLevelPerItem	Create and query QuickBooks Price Levels Per Item. Only QuickBooks Premier and Enterprise support Per-Item Price Levels. Note that while Price Levels can be added from this table, you may only add Per-Item Price Levels from this table. Price Levels may be deleted from the PriceLevels table. This table requires QBXML version 4.0 or later.
PriceLevels	Create, delete, and query QuickBooks Price Levels. Note that while Price Levels can be added and deleted from this table, you may add only fixed-percentage Price Levels from this table. Per-Item Price Levels may be added via the PriceLevelPerItem table. This table requires QBXML version 4.0 or higher.
PurchaseOrderLineItems	Create, update, delete, and query QuickBooks Purchase Order Line Items.
PurchaseOrders	Create, update, delete, and query QuickBooks Purchase Orders.
ReceivePayments	Create, update, delete, and query QuickBooks Receive Payment transactions. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
ReceivePaymentsAppliedTo	Create, update, and query QuickBooks Receive Payment AppliedTo aggregates. In a Receive Payment, each AppliedTo aggregate represents the transaction to which this part of the payment is being applied. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
SalesOrderLineItems	Create, update, delete, and query QuickBooks Sales Order Line Items.
SalesOrders	Create, update, delete, and query QuickBooks Sales Orders.
SalesReceiptLineItems	Create, update, delete, and query QuickBooks Sales Receipt Line Items.
SalesReceipts	Create, update, delete, and query QuickBooks Sales Receipts.
SalesReps	Create, update, delete, and query QuickBooks Sales Rep entities.
SalesTaxCodes	Create, update, delete, and query QuickBooks Sales Tax Codes.
SalesTaxItems	Create, update, delete, and query QuickBooks Sales Tax Items.
ShippingMethods	Create, update, delete, and query QuickBooks Shipping Methods.
StandardTerms	Create, update, delete, and query QuickBooks Standard Terms.
StatementCharges	Create, update, delete, and query QuickBooks Statement Charges.
TimeTracking	Create, update, delete, and query QuickBooks Time Tracking events.
ToDo	Create, update, delete, and query QuickBooks To Do entries.
TransferInventory	Query and delete QuickBooks Transfer Inventory transactions. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
TransferInventoryLineItems	Create and query QuickBooks Transfer Inventory Line Items. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
Transfers	Create, update, and query QuickBooks transfers. Requires QBXML version 12.0 or higher.
UnitOfMeasure	Create, update, delete, and query QuickBooks units of measure. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureDefaultUnits	Create and query QuickBooks unit-of-measure default units. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureRelatedUnits	Create and query QuickBooks unit-of-measure related units. QuickBooks requires QBXML version 7.0 or higher to use this table.
VehicleMileage	Create, update, delete, and query QuickBooks Vehicle Mileage entities. QuickBooks requires QBXML version 6.0 or higher to use this table.
VendorCreditExpenseItems	Create, update, delete, and query QuickBooks Vendor Credit Expense Line Items.
VendorCreditLineItems	Create, update, delete, and query QuickBooks Vendor Credit Line Items.
VendorCredits	Create, update, delete, and query QuickBooks Vendor Credits.
Vendors	Create, update, delete, and query QuickBooks Vendors.
VendorTypes	Create, update, delete, and query QuickBooks Vendor Types.
WorkersCompCodeLines	Query QuickBooks Workers Comp Code entries by line. Requires QBXML Version 7.0 or higher.
WorkersCompCodes	Query QuickBooks Workers Comp Code entries. Requires QBXML Version 7.0 or higher.

Create, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher.

Table Specific Information

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Accounts are Id, Name, Type, IsActive, and TimeModified. TimeModified may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Accounts WHERE Name LIKE '%Bank%' AND TimeModified > '1/1/2011' AND TimeModified < '2/1/2011'

Insert

To add an Account, specify the Name and Type fields.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier of the account.
Name	String	False		31	The name of the account. This is required to have a value when inserting.
FullName	String	True		1000	The full name of the account, including any ancestors (parents) in the format Parent:AccountName.
Type	String	False		100	The type of account. The allowed values are ACCOUNTSPAYABLE, ACCOUNTSRECEIVABLE, BANK, COSTOFGOODSSOLD, CREDITCARD, EQUITY, EXPENSE, FIXEDASSET, INCOME, LONGTERMLIABILITY, OTHERASSET, OTHERCURRENTASSET, OTHERCURRENTLIABILITY, OTHEREXPENSE, OTHERINCOME, NONPOSTING.
SpecialType	String	True		100	The special account type in QuickBooks if applicable. The allowed values are AccountsPayable, AccountsReceivable, CondenseItemAdjustmentExpenses, CostOfGoodsSold, DirectDepositLiabilities, Estimates, ExchangeGainLoss, InventoryAssets, ItemReceiptAccount, OpeningBalanceEquity, PayrollExpenses, PayrollLiabilities, PettyCash, PurchaseOrders, ReconciliationDifferences, RetainedEarnings, SalesOrders, SalesTaxPayable, UncategorizedExpenses, UncategorizedIncome, UndepositedFunds.
Number	String	False			The bank number of the account.
Balance	Double	True			The total balance of the account, including subaccounts.
AccountBalance	Double	True			The balance of this account only. This balance does not include subaccounts.
BankAccount	String	False			The bank account number for the account (or an identifying note).
Description	String	False		200	A textual description of the account.
IsActive	Boolean	False			This property indicates whether the object is currently enabled for use by QuickBooks.
ParentName	String	False			This is a reference to a parent account. If set to a nonempty string, then this account is a subaccount of its parent.
ParentId	String	False	Accounts.ID	255	This is a reference to a parent account. If set to a nonempty string, then this account is a subaccount or job of its parent.
Sublevel	Integer	True			The number of ancestors the account has.
CurrencyName	String	False		64	The name of the currency for the account. A minimum QBXML version of 8.0 is required for this feature.
CurrencyId	String	False	Currency.ID	255	The unique Id of the currency for the account. A minimum QBXML version of 8.0 is required for this feature.
CashFlowClassification	String	True			Indicates how the account is classified for cash flow reporting.' value='None, Operating, Investing, Financing, NotApplicable.
TaxLineName	String	True		256	The name of the line on the tax form this account is associated with, if any. Check the CompanyInfo to see which tax form is associated with the company file.
TaxLineId	String	False		255	The Id of the line on the tax form this account is associated with, if any. Check the CompanyInfo to see which tax form is associated with the company file. Requires QBXML Version 7.0 to modify.
TimeModified	Datetime	True			When the account was last modified.
TimeCreated	Datetime	True			When the account was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
OpeningBalance	String	The opening balance of the account (by default 0). Note that this property is only used when adding new accounts to QuickBooks.
OpeningDate	String	The opening balance date of the account. Note that this property is only used when adding new accounts to QuickBooks.

Create, update, delete, and query QuickBooks Bill Expense Line Items.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple expense Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Expense Line Items:

INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Utilities:Telephone', 52.25)
INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Professional Fees:Accounting', 235.87)
INSERT INTO BillExpenseItems (VendorName, Date, ExpenseAccount, ExpenseAmount) SELECT VendorName, Date, ExpenseAccount, ExpenseAmount FROM BillExpenseItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format BillId|ExpenseLineId.
BillId	String	False	Bills.ID	255	The bill identifier.
VendorName	String	False		1000	Vendor for this transaction. Either VenderName or VendorId must have a value when inserting.
VendorId	String	False	Vendors.ID	255	Vendor Id for this transaction. Either VenderName or VendorId must have a value when inserting.
ReferenceNumber	String	False		21	Reference number for the transaction.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
DueDate	Date	False			Date when payment is due.
Terms	String	False		100	Reference to terms of payment.
TermsId	String	False		255	Reference Id for the terms of payment.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference Id for the accounts-payable account.
Amount	Decimal	True			Amount of the transaction. This is calculated by QuickBooks based on the line items or expense line items.
Memo	String	False		5000	Memo for the transaction.
IsPaid	Boolean	True			Indicates whether this bill has been paid.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ExpenseLineId	String	True		255	The expense line item identifier.
ExpenseLineNumber	String	True		255	The expense line number.
ExpenseAccount	String	False		1000	The account name for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The account Id for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAmount	Decimal	False			The total amount of this expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ExpenseCustomer	String	False		1000	The customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The customer associated with this expense line.
ExpenseClass	String	False		1000	The class name of this expense.
ExpenseClassId	String	False	Class.ID	255	The class Id of this expense.
ExpenseMemo	String	False		5000	A memo for this expense line.
ExpenseTaxCode	String	False		3	Sales tax information for this item (taxable or non-taxable).
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or non-taxable).
ExpenseCustomFields	String	True			The custom fields for this expense item.
ExchangeRate	Double	False			Exchange Rate is the market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the 'home' currency.
TimeModified	Datetime	True			When the Bill was last modified.
TimeCreated	Datetime	True			When the Bill was created.
OpenAmount	Decimal	True			The current balance for the Vendor

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
LinkToTxnId	String	A transaction to link the bill to. This transaction must be a purchase order. You will get a run-time error if the transaction specified is already closed or fully received. This is only available on insert and requires a minimum QBXML Version 4.0.

Create, update, delete, and query QuickBooks Bill Line Items.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Line Items:

INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Repairs', 1)
INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Removal', 2)
INSERT INTO BillLineItems (VendorName, Date, ItemName, ItemQuantity) SELECT VendorName, Date, ItemName, ItemQuantity FROM BillLineItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format BillId|ItemLineId.
BillId	String	False	Bills.ID	255	The bill identifier.
VendorName	String	False		1000	Vendor for this transaction. Either VenderName or VendorId must have a value when inserting.
VendorId	String	False	Vendors.ID	255	Vendor Id for this transaction. Either VenderName or VendorId must have a value when inserting.
ReferenceNumber	String	False		21	Reference number for the transaction.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
DueDate	Date	False			Date when payment is due.
Terms	String	False		100	Reference to terms of payment.
TermsId	String	False		255	Reference Id for the terms of payment.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference Id for the accounts-payable account.
Amount	Decimal	True			Amount of the transaction. This is calculated by QuickBooks based on the line items or expense line items.
Memo	String	False		5000	Memo for the transaction.
IsPaid	Boolean	True			Indicates whether this bill has been paid.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ItemLineId	String	True		255	The line item identifier.
ItemLineNumber	String	True		255	The line item number.
ItemName	String	False			The item name.
ItemId	String	False	Items.ID	255	The item name.
ItemGroup	String	False		100	Item group name. Reference to a group of line items this item is part of.
ItemGroupId	String	False	Items.ID	255	Item group name. Reference to a group of line items this item is part of.
ItemDescription	String	False		5000	A description of the item.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	In a transaction line item, the name of the unit of measure is selected from within the item's available units. If the company file is enabled only for single unit of measure per item, this must be the base unit. Only available in QBXML Version 7.0 and above.
ItemCost	Double	False			The unit cost for the item.
ItemAmount	Decimal	False			Total amount for the item.
ItemBillableStatus	String	False		13	Billing status of the item. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer who ordered the item.
ItemCustomerId	String	False	Customers.ID	255	The Id of the customer who ordered the item.
ItemInventorySiteId	String	False	InventorySites.ID	31	The inventory site Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The inventory site name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The inventory site location Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The inventory site location name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemSerialNumber	String	False		5000	The serial number of this item. This requires QBXML version at least 11.0 and the Advanced Inventory add-on.
ItemLotNumber	String	False		40	The lot number of this item. This requires QBXML version at least 11.0 and the Advanced Inventory add-on.
ItemClass	String	False		1000	The name for the class of the item.
ItemClassId	String	False	Class.ID	255	The Id for the class of the item.
ItemTaxCode	String	False		3	Sales tax information for this item (taxable or non-taxable).
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or non-taxable).
ItemCustomFields	String	False			The custom fields for this lineitem.
CurrencyName	String	False		64	Name of the currency code used for this bill.
CurrencyId	String	False	Currency.ID	1000	Id of the currency code used for this bill.
ExchangeRate	Decimal	False			Exchange Rate is the market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the 'home' currency.
TimeModified	Datetime	True			When the Bill was last modified.
TimeCreated	Datetime	True			When the Bill was created.
OpenAmount	Decimal	True			The current balance for the Vendor

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
LinkToTxnId	String	A transaction to link the bill to. This transaction must be a purchase order. You will get a run-time error if the transaction specified is already closed or fully received. This is only available on insert and requires a minimum QBXML Version 4.0.
ItemOverrideAccount	String	The Account Name used to override the default Account for the Item. This is only available during inserts and updates.
ItemOverrideAccountId	String	The Account Id used to override the default Account for the Item. This is only available during inserts and updates.

Create, update, delete, and query QuickBooks Bill Payment Checks. QBXMLVersion must be set to 6.0 or higher to update a BillPaymentCheck.

Table Specific Information

BillPaymentChecks may be inserted, queried, or updated via the BillPaymentChecks or BillPaymentChecksAppliedTo tables. BillPaymentChecks may be deleted by using the BillPaymentChecks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentChecks are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentChecks WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a BillPaymentCheck, specify a Payee and BankAccount. The Payee must match the Vendor associated with the Bill you are adding a payment for. The AppliedToAggregate column may be used to specify an XML aggregate of AppliedTo data. The columns that may be used in these aggregates are defined in the BillPaymentChecksAppliedTo table and it starts with AppliedTo. For example, the following will insert a new BillPaymentCheck with two AppliedTo entries:

INSERT INTO BillPaymentChecks (PayeeName, BankAccountName, AppliedToAggregate) 
VALUES ('Vu Contracting', 'Checking', 
'<BillPaymentChecksAppliedTo>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>20.00</AppliedToAmount></Row>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>51.25</AppliedToAmount></Row>
</BillPaymentChecksAppliedTo>')

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier for the transaction.
PayeeName	String	False		1000	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required.
PayeeId	String	False	Vendors.ID	255	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Int	True			The transaction number.
Date	Date	False			The date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
Amount	Decimal	True			Amount of the transaction. This is calculated by QuickBooks based on the line items or expense line items.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference to the accounts-payable account Id.
BankAccountName	String	False		1000	Refers to the Account funds are being drawn from for this bill payment. This property is only applicable to the check payment method.
BankAccountId	String	False	Accounts.ID	255	Refers to the Account funds are being drawn from for this bill payment. This property is only applicable to the check payment ethod.
IsToBePrinted	Boolean	False			Indicates whether or not the transaction is to be printed. If set to true, the 'To Be Printed' box in the QuickBooks user interface will be checked. The default value is false.
Memo	String	False		5000	A memo to appear on internal reports.
AppliedToAggregate	String	False		5000	An aggregate of the applied-to data which can be used for adding a bill payment check and its applied-to data.
ExchangeRate	Double	False			Currency exchange rate for this Bill Payment.
AmountInHomeCurrency	Double	True			Amount in units of the home currency.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
TimeModified	Datetime	True			When the bill payment was last modified.
TimeCreated	Datetime	True			When the bill payment was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AppliedTo*	String	All applied-to-specific columns may be used in insertions.

Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.

Table Specific Information

BillPaymentChecks may be inserted, queried, or updated via the BillPaymentChecks or BillPaymentChecksAppliedTo tables. BillPaymentChecks may be deleted by using the BillPaymentChecks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentChecks are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentChecksAppliedTo WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a BillPaymentCheck entry, specify the Payee and BankAccount fields. The Payee must match the Vendor associated with the Bill you are adding a payment for. All AppliedTo columns can be used to explicitly identify the Bills being paid. For example, the following will insert a new BillPaymentCheck with two AppliedTo entries:

INSERT INTO BillPaymentChecksAppliedTo#TEMP (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'Checking', '178C1-1450221347', 20.00)
INSERT INTO BillPaymentChecksAppliedTo#TEMP (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'Checking', '881-933371709', 51.25)
INSERT INTO BillPaymentChecksAppliedTo (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) SELECT PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount FROM BillPaymentChecksAppliedTo#TEMP

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format BillPaymentId|AppliedToId.
BillPaymentId	String	False	BillPaymentChecks.ID	255	The Id of the bill payment transaction.
PayeeName	String	False		1000	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required. This must match the Vendor associated with the Bill being paid when inserting.
PayeeId	String	False	Vendors.ID	255	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required. This must match the Vendor associated with the Bill being paid when inserting.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Int	True			The transaction number.
Date	Date	False			The date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False		255	Reference to the accounts-payable account Id.
BankAccountId	String	False		255	Refers to the account funds are being drawn from for this bill payment. This property is only applicable to the check payment method.
BankAccountName	String	False		1000	Refers to the account funds are being drawn from for this bill payment. This property is only applicable to the check payment method.
IsToBePrinted	Boolean	False			Indicates whether or not the transaction is to be printed. If set to true, the 'To Be Printed' box in the QuickBooks user interface will be checked. The default value is false.
Memo	String	False		5000	A memo to appear on internal reports.
ExchangeRate	Double	False			Currency exchange rate for this Bill Payment.
AmountInHomeCurrency	Double	True			Amount in units of the home currency.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
AppliedToRefId	String	False		255	The applied-to reference identifier. This is a reference to a bill Id, which can be found in the Bills table.
AppliedToAmount	Decimal	False			The amount to be applied.
AppliedToBalanceRemaining	Decimal	True			The balance remaining to be applied.
AppliedToCreditAmount	Decimal	False			The amount of the credit to be applied.
AppliedToCreditMemoId	String	False		255	The Id of the credit memo to be applied.
AppliedToDiscountAccountId	String	False		255	The discount account Id to be applied.
AppliedToDiscountAccountName	String	False		1000	The discount account name to be applied.
AppliedToDiscountAmount	Decimal	False			The discount amount to be applied.
AppliedToPaymentAmount	Decimal	False			The payment amount to be applied.
AppliedToReferenceNumber	String	True		21	The ref number to be applied.
AppliedToTxnDate	Date	True			The transaction date to be applied.
AppliedToTxnType	String	True		100	The transaction type that was applied.
TimeModified	Datetime	True			When the bill payment was last modified.
TimeCreated	Datetime	True			When the bill payment was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AppliedToCreditAppliedAmount	String	The credit applied amount to be applied.

Create, update, delete, and query QuickBooks Bill Payments.

Table Specific Information

BillPaymentCreditCards may be inserted, queried, or updated via the BillPaymentCreditCards or BillPaymentCreditCardsAppliedTo tables. BillPaymentCreditCards may be deleted by using the BillPaymentCreditCards table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentCreditCards are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentCreditCards WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a BillPaymentCreditCard, specify a Payee and CreditCard. The Payee must match the Vendor associated with the Bill you are adding a payment for. The AppliedToAggregate column may be used to specify an XML aggregate of AppliedTo data. The columns that may be used in these aggregates are defined in the BillPaymentCreditCardsAppliedTo table and it starts with AppliedTo. For example, the following will insert a new BillPaymentCreditCard with two AppliedTo entries:

INSERT INTO BillPaymentCreditCard (PayeeName, CreditCardName, AppliedToAggregate) 
VALUES ('Vu Contracting', 'CalOil Credit Card', 
'<BillPaymentCreditCardsAppliedTo>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>20.00</AppliedToAmount></Row>
<Row><AppliedToRefId>881-933371709</AppliedToRefId><AppliedToAmount>51.25</AppliedToAmount></Row>
</BillPaymentCreditCardsAppliedTo>')

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier for the transaction.
PayeeName	String	False		1000	A reference to the the entity merchandise was purchased from. Either PayeeId or PayeeName is required.
PayeeId	String	False	Vendors.ID	255	A reference to the the entity merchandise was purchased from. Either PayeeId or PayeeName is required.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Int	True			The transaction number.
Date	Date	False			The date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
Amount	Decimal	True			Amount of the transaction. This is calculated by QuickBooks based on the line items or expense line items.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference to the accounts-payable account Id.
CreditCardName	String	False		1000	Refers to the credit card account this payment is being charged to. This property is only applicable to the credit card payment Method.
CreditCardId	String	False		255	Refers to the credit card account this payment is being charged to. This property is only applicable to the credit card payment Method.
Memo	String	False		5000	A memo to appear on internal reports.
AppliedToAggregate	String	False		5000	An aggregate of the applied-to data which can be used for adding a bill payment credit card and its applied-to data.
ExchangeRate	Double	False			Currency exchange rate for this Bill Payment.
AmountInHomeCurrency	Double	True			Amount in units of the home currency.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
TimeModified	Datetime	True			When the bill payment was last modified.
TimeCreated	Datetime	True			When the bill payment was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AppliedTo*	String	All applied-to-specific columns may be used in insertions.

Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.

Table Specific Information

BillPaymentCreditCards may be inserted, queried, or updated via the BillPaymentCreditCards or BillPaymentCreditCardsAppliedTo tables. BillPaymentCreditCards may be deleted by using the BillPaymentCreditCards table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentCreditCards are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentCreditCardsAppliedTo WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

You can also use BillPaymentCreditCards to insert a BillPaymentCreditCard.

To add a BillPaymentCreditCard, specify a Payee and CreditCard. The Payee must match the Vendor associated with the Bill you are adding a payment for. All AppliedTo columns can be used to explicitly identify the Bills being paid. For example, the following will insert a new BillPaymentCreditCard with two AppliedTo entries:

INSERT INTO BillPaymentCreditCardsAppliedTo#TEMP (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'CalOil Credit Card', '178C1-1450221347', 20.00)
INSERT INTO BillPaymentCreditCardsAppliedTo#TEMP (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'CalOil Credit Card', '881-933371709', 51.25)
INSERT INTO BillPaymentCreditCardsAppliedTo (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) SELECT PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount FROM BillPaymentCreditCardsAppliedTo#TEMP

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format BillPaymentId|AppliedToId.
BillPaymentId	String	False	BillPaymentCreditCards.ID	255	The Id of the bill payment transaction.
PayeeName	String	False		1000	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required. This must match the vendor associated with the bill being paid when inserting.
PayeeId	String	False	Vendors.ID	255	A reference to the entity merchandise was purchased from. Either PayeeId or PayeeName is required. This must match the vendor associated with the bill being paid when inserting.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Int	True			The transaction number.
Date	Date	False			The date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference to the accounts-payable account Id.
CreditCardName	String	False		1000	Refers to the credit card account this payment is being charged to. This property is only applicable to the credit card payment method.
CreditCardId	String	False		255	Refers to the credit card account this payment is being charged to. This property is only applicable to the credit card payment method.
IsToBePrinted	Boolean	False			Indicates whether or not the transaction is to be printed. If set to true, the 'To Be Printed' box in the QuickBooks user interface will be checked. The default value is false.
Memo	String	False		5000	A memo to appear on internal reports.
ExchangeRate	Double	False			Currency exchange rate for this Bill Payment.
AmountInHomeCurrency	Double	True			Amount in units of the home currency.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
AppliedToRefId	String	True		255	The applied-to reference identifier. This is a reference to a bill Id, which can be found in the bills table.
AppliedToAmount	Decimal	True			The amount to be applied.
AppliedToBalanceRemaining	Double	True			The balance remaining to be applied.
AppliedToCreditMemoId	String	False	CreditMemos.ID	255	The Id of the credit memo to be applied.
AppliedToDiscountAccountName	String	False		1000	The discount account name to be applied.
AppliedToDiscountAccountId	String	False	Accounts.ID	255	The discount account Id to be applied.
AppliedToDiscountAmount	Decimal	False			The discount amount to be applied.
AppliedToPaymentAmount	Decimal	False			The payment amount to be applied.
AppliedToReferenceNumber	String	True		21	The ref number to be applied.
AppliedToTxnDate	Date	True			The transaction date to be applied.
AppliedToTxnType	String	True		100	The transaction type that was applied.
TimeModified	Datetime	True			When the bill payment was last modified.
TimeCreated	Datetime	True			When the bill payment was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AppliedToCreditAppliedAmount	String	The credit applied amount to be applied.

Create, update, delete, and query QuickBooks Bills.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

You can also use BillLineItems and BillExpenseItems to insert a bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the BillLineItems and BillExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new Bill with two Line Items:

INSERT INTO Bills (VendorName, Date, ItemAggregate) 
VALUES ('Cal Telephone', '1/1/2011', 
'<BillLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</BillLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier for the bill.
VendorName	String	False		1000	Vendor for this transaction. Either VenderName or VendorId must have a value when inserting.
VendorId	String	False	Vendors.ID	255	Vendor Id for this transaction. Either VenderName or VendorId must have a value when inserting.
ReferenceNumber	String	False		21	Reference number for the transaction.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
Amount	Decimal	True			Amount of the transaction. This is calculated by QuickBooks based on the Line Items or Expense Line Items.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
DueDate	Date	False			Date when payment is due.
Terms	String	False		100	Reference to terms of payment.
TermsId	String	False		255	Reference Id for the terms of payment.
AccountsPayable	String	False		1000	Reference to the accounts-payable account.
AccountsPayableId	String	False	Accounts.ID	255	Reference Id for the accounts-payable account.
Memo	String	False		5000	Memo for the transaction.
IsPaid	Boolean	True			Indicates whether this Bill has been paid.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ItemCount	Integer	True			The count of line items.
ItemAggregate	String	False		5000	An aggregate of the line item data which can be used for adding a bill and its line item data.
ExpenseItemCount	Integer	True			The count of expense line items.
ExpenseItemAggregate	String	False		5000	An aggregate of the expense item data which can be used for adding a bill and its expense item data.
TransactionCount	Integer	True			The count of related transactions to the bill.
TransactionAggregate	String	True		5000	An aggregate of the linked transaction data.
CurrencyName	String	False		64	Name of the currency code used for this bill.
CurrencyId	String	False	Currency.ID	1000	Id of the currency code used for this bill.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
TimeModified	Datetime	True			When the bill was last modified.
TimeCreated	Datetime	True			When the bill was created.
OpenAmount	Decimal	True			The current balance for the Vendor

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Item*	String	All line-item-specific columns may be used in insertions.
Expense*	String	All expense-item-specific columns may be used in insertions.
LinkToTxnId	String	A transaction to link the bill to. This transaction must be a purchase order. You will get a run-time error if the transaction specified is already closed or fully received. This is only available on insert and requires a minimum QBXML Version 4.0.

Insert, Update, delete, and query QuickBooks Build Assembly transactions.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier.
TxnNumber	Integer	True			An identifying number for this transaction.
ItemInventoryAssemblyRef_ListID	String	False	Items.ID	255	A reference to the Id of an inventory assembly. Either ItemInventoryAssemblyRef_ListID or ItemInventoryAssemblyRef_FullName is required when inserting a BuildAssembly.
ItemInventoryAssemblyRef_FullName	String	False		1000	A reference to the name of an inventory assembly. Either ItemInventoryAssemblyRef_ListID or ItemInventoryAssemblyRef_FullName is required when inserting a BuildAssembly.
InventorySiteRef_ListID	String	False	InventorySites.ID	255	A reference to the Id of an inventory site.
InventorySiteRef_FullName	String	False		31	A reference to the name of an inventory site.
SerialNumber	String	False		5000	The serial number of the asset. This cannot be used with LotNumber.
LotNumber	String	False		40	The lot number of the asset. This cannot be used with SerialNumber.
TxnDate	Date	False			The date of the transaction.
RefNumber	String	False		21	A reference number identifying the transaction. This does not have to be unique.
Memo	String	False		5000	A memo about the transaction.
IsPending	Boolean	True			If IsPending is set to true, the transaction in question has not been completed.
QuantityToBuild	Double	False			Specifies the number of assemblies to be built. The transaction will fail if the number specified here exceeds the number of on-hand items.
QuantityCanBuild	Double	True			Indicates the number of this assembly that can be built from the parts on hand.
QuantityOnHand	Double	True			The number of these items in the inventory. To change the QuantityOnHand, you would need to add an inventory adjustment.
QuantityOnSalesOrder	Double	True			The number of these items that have been sold (as recorded in sales orders) but not delivered to customers.
MarkPendingIfRequired	Boolean	False			Allows the build assembly to be marked pending if there are insufficient quantities to complete the build assembly. This field requires QBXML Version 7.0 and is not returned in the response from QuickBooks.
ExternalGUID	String	True			A user-defined GUID value. Requires QBXML Version 9.0.
BuildAssemblyLineAggregate	String	True		5000	An aggregate of the line item data which can be used for adding a transfer inventory and its line item data.
TimeCreated	Datetime	True			The datetime the transaction was made.
TimeModified	Datetime	True			The last datetime the transaction was modified.
EditSequence	String	True			An identifier used for versioning for this copy of the object.

Create and query QuickBooks Build Assembly transactions.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier.
BuildAssemblyId	String	False	BuildAssemblies.ID	255	The unique Id of the build assembly.
TxnNumber	Integer	True			An identifying number for this transaction.
ItemInventoryAssemblyRef_ListID	String	False	Items.ID	255	A reference to the Id of an inventory assembly. Either ItemInventoryAssemblyRef_ListID or ItemInventoryAssemblyRef_FullName is required when inserting a BuildAssembly.
ItemInventoryAssemblyRef_FullName	String	False		1000	A reference to the name of an inventory assembly. Either ItemInventoryAssemblyRef_ListID or ItemInventoryAssemblyRef_FullName is required when inserting a BuildAssembly.
InventorySiteRef_ListID	String	False	InventorySites.ID	255	A reference to the Id of an inventory site. Requires QBXML Version 10.0.
InventorySiteRef_FullName	String	False		31	A reference to the name of an inventory site. Requires QBXML Version 10.0.
SerialNumber	String	False		5000	The serial number of the asset. This cannot be used with LotNumber.
LotNumber	String	False		40	The lot number of the asset. This cannot be used with SerialNumber.
TxnDate	Date	False			The date of the transaction.
RefNumber	String	False		21	A reference number identifying the transaction. This does not have to be unique.
Memo	String	False		5000	A memo about the transaction.
IsPending	Boolean	True			If IsPending is set to true, the transaction in question has not been completed.
QuantityToBuild	Double	False			Specifies the number of assemblies to be built. The transaction will fail if the number specified here exceeds the number of on-hand items.
QuantityCanBuild	Double	True			Indicates the number of this assembly that can be built from the parts on hand.
QuantityOnHand	Double	True			The number of these items in the inventory. To change the QuantityOnHand, you would need to add an inventory adjustment.
QuantityOnSalesOrder	Double	True			The number of these items that have been sold (as recorded in sales orders) but not delivered to customers.
MarkPendingIfRequired	Boolean	False			Allows the build assembly to be marked pending if there are insufficient quantities to complete the build assembly. This field requires QBXML Version 7.0 and is not returned in the response from QuickBooks.
ExternalGUID	String	True			A user-defined GUID value. Requires QBXML Version 9.0.
ComponentItemLineRet_ItemRef_ListID	String	True	Items.ID	255	Reference to the Id of an item.
ComponentItemLineRet_ItemRef_FullName	String	True		255	Reference to the full name of an item.
ComponentItemLineRet_InventorySiteRef_ListID	String	True	InventorySites.ID	255	Reference to the Id of an inventory site. Requires QBXML Version 10.0.
ComponentItemLineRet_InventorySiteRef_FullName	String	True		31	Reference to the full name of an inventory site. Requires QBXML Version 10.0.
ComponentItemLineRet_Desc	String	True		5000	Description for the line item.
ComponentItemLineRet_QuantityOnHand	Double	True			The number of these items in the inventory.
ComponentItemLineRet_QuantityNeeded	Double	True			The number of these items used in the assembly.
TimeCreated	Datetime	True			The datetime the transaction was made.
TimeModified	Datetime	True			The last datetime the transaction was modified.
EditSequence	String	True			An identifier used for versioning for this copy of the object.

Create, update, delete, and query QuickBooks Check Expense Line Items.

Table Specific Information

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CheckExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM CheckExpenseItems WHERE Date >= '2020-01-07' AND Date < '2020-01-10' 

SELECT * FROM CheckExpenseItems WHERE [Date] = '2020-01-07' 

Insert

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new Check transaction. For example, the following will insert a new Check with two Expense Line Items:

INSERT INTO CheckExpenseItems#TEMP (Account, Date, ExpenseAccount, ExpenseAmount) VALUES ('Checking', '1/1/2011', 'Utilities:Telephone', 52.25,)
INSERT INTO CheckExpenseItems#TEMP (Account, Date, ExpenseAccount, ExpenseAmount) VALUES ('Checking', '1/1/2011', 'Professional Fees:Accounting', 235.87)
INSERT INTO CheckExpenseItems (Account, Date, ExpenseAccount, ExpenseAmount) SELECT Account, Date, ExpenseAccount, ExpenseAmount FROM CheckExpenseItems#TEMP 

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CheckId|ExpenseLineId.
CheckId	String	False	Checks.ID	255	The item identifier for the check. This is obtained from the Checks table.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
Account	String	False		1000	The name of the account funds are being drawn from.
AccountId	String	False	Accounts.ID	255	The Id of the account funds are being drawn from.
Payee	String	False		1000	The name of the payee for the check.
PayeeId	String	False	Vendors.ID	255	The Id of the payee for the check.
Date	Date	False			Date of transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
Amount	Decimal	True			Amount of the transaction.
Memo	String	False		5000	A memo regarding this transaction.
Address	String	True			Full address returned by QuickBooks.
Line1	String	False		500	First line of the address.
Line2	String	False		500	Second line of the address.
Line3	String	False		500	Third line of the address.
Line4	String	False		500	Fourth line of the address.
Line5	String	False		41	Fifth line of the address.
City	String	False		255	City name for the address of the check.
State	String	False		255	State name for the address of the check.
PostalCode	String	False		30	Postal code for the address of the check.
Country	String	False		255	Country for the address of the check.
Note	String	False		41	Note for the address of the check.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
ExpenseLineId	String	True		255	The line item identifier.
ExpenseLineNumber	String	True		255	The expense line number.
ExpenseAccount	String	False		1000	The account name for this expense line.
ExpenseAccountId	String	False	Accounts.ID	255	The account Id for this expense line.
ExpenseAmount	Decimal	False			The total amount of this expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED. The default value is EMPTY.
ExpenseCustomer	String	False		1000	The customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The customer associated with this expense line.
ExpenseClass	String	False		1000	The class name of this expense.
ExpenseClassId	String	False	Class.ID	255	The class Id of this expense.
ExpenseTaxCode	String	False		3	Sales tax information for this item. Available in only international editions of QuickBooks.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax Id information for this item. Available in only international editions of QuickBooks.
ExpenseMemo	String	False		5000	A memo for this expense line.
ExpenseCustomFields	String	True			The custom fields for this expense item.
IsToBePrinted	Boolean	False			Whether this transaction is to be printed. The default value is false.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
CurrencyId	String	False	Currency.ID	255	The unique Id of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the check was last modified.
TimeCreated	Datetime	True			When the check was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ApplyCheckToTxnId	String	Identifies the transaction to be paid by this check. This can be used in updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. This can be used in updates and inserts.

Create, update, delete, and query QuickBooks Check Line Items.

Table Specific Information

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CheckLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM CheckLineItems WHERE Date >= '2020-01-07' AND Date < '2020-01-10' 

SELECT * FROM CheckLineItems WHERE [Date] = '2020-01-07' 

Insert

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new Check transaction. For example, the following will insert a new Check with two Line Items:

INSERT INTO CheckLineItems#TEMP (Account, Date, ItemName, ItemQuantity) VALUES ('Checking', '1/1/2011', 'Repairs', 1)
INSERT INTO CheckLineItems#TEMP (Account, Date, ItemName, ItemQuantity) VALUES ('Checking', '1/1/2011', 'Removal', 2)
INSERT INTO CheckLineItems (Account, Date, ItemName, ItemQuantity) VALUES Account, Date, ItemName, ItemQuantity FROM CheckLineItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CheckId|ItemLineId.
CheckId	String	False	Checks.ID	255	The item identifier for the check. This is obtained from the checks table.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
Account	String	False		1000	The name of the account funds are being drawn from.
AccountId	String	False	Accounts.ID	255	The Id of the account funds are being drawn from.
Payee	String	False		1000	The name of the payee for the check.
PayeeId	String	False	Vendors.ID	255	The id of the payee for the check.
Date	Date	False			Date of transaction.
Amount	Decimal	True			Amount of the transaction.
Memo	String	False		5000	A memo regarding this transaction.
Address	String	True			Full address returned by QuickBooks.
Line1	String	False		500	First line of the address.
Line2	String	False		500	Second line of the address.
Line3	String	False		500	Third line of the address.
Line4	String	False		500	Fourth line of the address.
Line5	String	False		41	Fifth line of the address.
City	String	False		255	City name for the address of the check.
State	String	False		255	State name for the address of the check.
PostalCode	String	False		30	Postal code for the address of the check.
Country	String	False		255	Country for the address of the check.
Note	String	False		41	Note for the address of the check.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
ItemLineId	String	True		255	The line item identifier.
ItemLineNumber	String	True		255	The line item number.
ItemName	String	False			The item name.
ItemId	String	False	Items.ID		The item Id.
ItemGroup	String	False		100	Item group name. Reference to a group of line items this item is part of.
ItemGroupId	String	False	Items.ID	255	Item group Id. Reference to a group of line items this item is part of.
ItemDescription	String	False		5000	A description of the item.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	In a transaction line item, the name of the unit of measure is selected from within the item's available units. If the company file is enabled for only a single unit of measure per item, this must be the base unit. Only available in QBXML Version 7.0 and above.
ItemCost	Double	False			The unit cost for the item.
ItemAmount	Decimal	False			Total amount for the item.
ItemBillableStatus	String	False		13	Billing status of the item. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED. The default value is EMPTY.
ItemCustomer	String	False		1000	The name of the customer who ordered the item.
ItemCustomerId	String	False	Customers.ID	255	The Id of the customer who ordered the item.
ItemClass	String	False		1000	The name for the class of the item.
ItemClassId	String	False	Class.ID	255	The Id for the class of the item.
ItemInventorySiteId	String	False	InventorySites.ID	31	The inventory site Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The inventory site name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The inventory site location Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The inventory site location name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemTaxCode	String	False		3	Sales tax information for this item. Available in only international editions of QuickBooks.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax Id information for this item. Available in only international editions of QuickBooks.
ItemCustomFields	String	False			The custom fields for this lineitem.
IsToBePrinted	Boolean	False			Whether this transaction is to be printed. The default value is false.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
CurrencyId	String	False	Currency.ID	255	The unique Id of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the check was last modified.
TimeCreated	Datetime	True			When the check was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ApplyCheckToTxnId	String	Identifies the transaction to be paid by this check. This can be used in updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. This can be used in updates and inserts.

Create, update, delete, and query QuickBooks Checks.

Table Specific Information

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Checks WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM Checks WHERE Date >= '2020-01-07' AND Date < '2020-01-10' 

SELECT * FROM Checks WHERE [Date] = '2020-01-07' 

Insert

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the CheckLineItems and CheckExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new Check with two Line Items:

INSERT INTO Checks (Account, Date, ItemAggregate) VALUES ('Checking', '1/1/2011', 
'<CheckLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CheckLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier.
ReferenceNumber	String	False		21	The transaction reference number.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.
Account	String	False		1000	The name of the account funds are being drawn from.
AccountId	String	False	Accounts.ID	255	The id of the account funds are being drawn from.
Payee	String	False		1000	The name of the payee for the Check.
PayeeId	String	False	Vendors.ID	255	The Id of the payee for the Check.
Date	Date	False			Date of transaction.
Amount	Decimal	True			Amount of the transaction.
Memo	String	False		5000	A memo regarding this transaction.
Address	String	True			Full address returned by QuickBooks.
Line1	String	False		500	First line of the address.
Line2	String	False		500	Second line of the address.
Line3	String	False		500	Third line of the address.
Line4	String	False		500	Fourth line of the address.
Line5	String	False		41	Fifth line of the address.
City	String	False		255	City name for the address of the check.
State	String	False		255	State name for the address of the check.
PostalCode	String	False		30	Postal code for the address of the check.
Country	String	False		255	Country for the address of the check.
Note	String	False		41	Note for the address of the check.
ItemCount	Integer	True			The count of line items.
ItemAggregate	String	False		5000	An aggregate of the line item data which can be used for adding a check and its line item data.
ExpenseItemCount	Integer	True			The count of expense line items.
ExpenseItemAggregate	String	False		5000	An aggregate of the expense item data which can be used for adding a check and its expense item data.
IsToBePrinted	Boolean	False			Whether this transaction is to be printed. The default value is false.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
CurrencyId	String	False	Currency.ID	255	The unique Id of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
TimeModified	Datetime	True			When the check was last modified.
TimeCreated	Datetime	True			When the check was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Item*	String	All line-item-specific columns may be used in insertions.
Expense*	String	All expense-item-specific columns may be used in insertions.
ApplyCheckToTxnId	String	Identifies the transaction to be paid by this check. This can be used in updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. This can be used in updates and inserts.

Create, update, delete, and query QuickBooks Classes. QuickBooks requires QBXML version 8.0 or higher for updates to a Class.

Table Specific Information

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for the Class table are Id, Name, and IsActive. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax.

Insert

To insert a Class, specify the Name field.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier of the class.
Name	String	False		100	The name of the class.
FullName	String	True		1000	The full name of the class in the form ParentName|ClassName.
IsActive	Boolean	False			Boolean determining if the class is active.
ParentRef_FullName	String	False			Full name of the parent for the class. You may specify only ParentRef_FullName or ParentRef_ListId on INSERT/UPDATE statements and not both.
ParentRef_ListId	String	False	Class.ID	255	Id for the parent of the class. You may specify only ParentRef_FullName or ParentRef_ListId on INSERT/UPDATE statements and not both.
Sublevel	Integer	True			How many parents the class has.
EditSequence	String	True		16	A string indicating the revision of the class.
TimeCreated	Datetime	True			The time the class was created.
TimeModified	Datetime	True			The last time the class was modified.

Create, update, delete, and query QuickBooks Credit Card Charge Expense Line Items.

Table Specific Information

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardChargeExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new CreditCardCharge transaction. For example, the following will insert a new CreditCardCharge with two Expense Line Items:

INSERT INTO CreditCardChargeExpenseItems#TEMP (AccountName, ExpenseAccount ExpenseAmount) VALUES ('CalOil Credit Card', 'Job Expenses:Job Materials', 52.25)
INSERT INTO CreditCardChargeExpenseItems#TEMP (AccountName, ExpenseAccount ExpenseAmount) VALUES ('CalOil Credit Card', 'Automobile:Fuel', 235.87)
INSERT INTO CreditCardChargeExpenseItems (AccountName, ExpenseAccount, ExpenseAmount) SELECT AccountName, ExpenseAccount, ExpenseAmount FROM CreditCardChargeExpenseItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CCChargeId|ItemLineId.
CCChargeId	String	False	CreditCardCharges.ID	255	The item identifier.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
ReferenceNumber	String	False		21	Reference number for the transaction.
AccountName	String	False		1000	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
AccountId	String	False	Accounts.ID	255	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
Memo	String	False		5000	Memo to appear on internal reports only.
PayeeName	String	False		1000	Name of the payee for the transaction.
PayeeId	String	False	Vendors.ID	255	Id of the payee for the transaction.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ExpenseLineId	String	True		255	The expense line item identifier.
ExpenseLineNumber	String	True		255	The expense line number.
ExpenseAccount	String	False		1000	The account name for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The account Id for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAmount	Decimal	False			The total amount of this expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ExpenseCustomer	String	False		1000	The customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The customer associated with this expense line.
ExpenseClass	String	False		1000	The class name of this expense.
ExpenseClassId	String	False	Class.ID	255	The class Id of this expense.
ExpenseMemo	String	False		5000	A memo for this expense line.
ExpenseTaxCode	String	False		3	Sales tax information for this item (taxable or nontaxable).
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or nontaxable).
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the credit card charge was last modified.
TimeCreated	Datetime	True			When the credit card charge was created.

Create, update, delete, and query QuickBooks Credit Card Charge Line Items.

Table Specific Information

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardChargeLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new CreditCardCharge transaction. For example, the following will insert a new CreditCardCharge with two Line Items:

INSERT INTO CreditCardChargeLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Repairs', 1)
INSERT INTO CreditCardChargeLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Removal', 2)
INSERT INTO CreditCardChargeLineItems (AccountName, ItemName, ItemQuantity) SELECT AccountName, ItemName, ItemQuantity FROM CreditCardChargeLineItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CCChargeId|ItemLineId.
CCChargeId	String	False	CreditCardCharges.ID	255	The item identifier.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
ReferenceNumber	String	False		21	Reference number for the transaction.
AccountName	String	False		1000	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
AccountId	String	False	Accounts.ID	255	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
Memo	String	False		5000	Memo to appear on internal reports only.
PayeeName	String	False		1000	Name of the payee for the transaction.
PayeeId	String	False	Vendors.ID	255	Id of the payee for the transaction.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ItemLineId	String	True		255	The line item identifier.
ItemLineNumber	String	True		255	The line item number.
ItemName	String	False			The item name.
ItemId	String	False	Items.ID	255	The item identifier.
ItemGroup	String	False		100	Item group name. Reference to a group of line items this item is part of.
ItemGroupId	String	False	Items.ID	255	Item group name. Reference to a group of line items this item is part of.
ItemDescription	String	False		5000	A description of the item.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		100	In a transaction line item, the name of the unit of measure is selected from within the available units for the item. If the company file is enabled for only a single unit of measure per item, this must be the base unit. Only available in QBXML Version 7.0 and above.
ItemCost	Double	False			The unit cost for an item.
ItemAmount	Decimal	False			Total amount for this item.
ItemBillableStatus	String	False		13	Billing status of the item. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer who ordered the item.
ItemCustomerId	String	False	Customers.ID	255	The Id of the customer who ordered the item.
ItemClass	String	False		1000	The name for the class of the item.
ItemClassId	String	False	Class.ID	255	The Id for the class of the item.
ItemTaxCode	String	False		3	Sales tax information for this item (taxable or nontaxable).
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or nontaxable).
ItemInventorySiteId	String	False	InventorySites.ID	31	The inventory site Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The inventory site name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The inventory site location Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The inventory site location name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the transaction was last modified.
TimeCreated	Datetime	True			When the transaction was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ItemOverrideAccount	String	The Account Name used to override the default Account for the Item. This is only available during inserts and updates.
ItemOverrideAccountId	String	The Account Id used to override the default Account for the Item. This is only available during inserts and updates.

Create, update, delete, and query QuickBooks Credit Card Charges.

Table Specific Information

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCharges WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the CreditCardChargeLineItems and CreditCardChargeExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new CreditCardCharge with two Line Items:

INSERT INTO CreditCardCharges (AccountName, ItemAggregate) 
VALUES ('CalOil Credit Card', 
'<CreditCardChargeLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CreditCardChargeLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier of the transaction.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
ReferenceNumber	String	False		11	Reference number for the transaction.
AccountName	String	False		1000	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
AccountId	String	False	Accounts.ID	255	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
Memo	String	False		5000	Memo to appear on internal reports only.
PayeeName	String	False		1000	Name of the payee for the transaction.
PayeeId	String	False	Vendors.ID	255	Id of the payee for the transaction.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ItemCount	Integer	True			The count of line items.
ItemAggregate	String	False		5000	An aggregate of the line item data which can be used for adding a bill and its line item data.
ExpenseItemCount	Integer	True			The count of expense line items.
ExpenseItemAggregate	String	False		5000	An aggregate of the expense item data which can be used for adding a bill and its expense item data.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
TimeModified	Datetime	True			When the credit card charge was last modified.
TimeCreated	Datetime	True			When the credit card charge was created.
TxnNumber	Integer	True			The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Item*	String	All line-item-specific columns may be used in insertions.
Expense*	String	All expense-item-specific columns may be used in insertions.

Create, update, delete, and query QuickBooks Credit Card Credit Expense Line Items.

Table Specific Information

CreditCardCredits may be inserted, queried, or updated via the CreditCardCredits, CreditCardCreditExpenseItems, or CreditCardCreditLineItems tables. CreditCardCredits may be deleted by using the CreditCardCredits table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCredits are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCreditExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a CreditCardCredit, specify an Account and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new CreditCardCredit transaction. For example, the following will insert a new CreditCardCredit with two Expense Line Items:

INSERT INTO CreditCardCreditExpenseItems#TEMP (AccountName, ExpenseAccount, ExpenseAmount) VALUES ('CalOil Credit Card', 'Job Expenses:Job Materials', 52.25)
INSERT INTO CreditCardCreditExpenseItems#TEMP (AccountName, ExpenseAccount, ExpenseAmount) VALUES ('CalOil Credit Card', 'Automobile:Fuel', 235.87)
INSERT INTO CreditCardCreditExpenseItems (AccountName, ExpenseAccount, ExpenseAmount) SELECT AccountName, ExpenseAccount, ExpenseAmount FROM CreditCardCreditExpenseItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CCCreditId|ItemLineId.
CCCreditId	String	False	CreditCardCredits.ID	255	The item identifier.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
ReferenceNumber	String	False		21	Reference number for the transaction.
AccountName	String	False		1000	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
AccountId	String	False	Accounts.ID	255	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
Memo	String	False		5000	Memo to appear on internal reports only.
PayeeName	String	False		1000	Name of the payee for the transaction.
PayeeId	String	False	Vendors.ID	255	Id of the payee for the transaction.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ExpenseLineId	String	True		255	The expense line item identifier.
ExpenseLineNumber	String	True		255	The expense line number.
ExpenseAccount	String	False		1000	The account name for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The account Id for this expense line. ExpenseAccount or ExpenseAccountId must have a value when inserting.
ExpenseAmount	Decimal	False			The total amount of this expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ExpenseCustomer	String	False		1000	The customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The customer associated with this expense line.
ExpenseClass	String	False		1000	The class name of this expense.
ExpenseClassId	String	False	Class.ID	255	The class Id of this expense.
ExpenseMemo	String	False		5000	A memo for this expense line.
ExpenseTaxCode	String	False		3	Sales tax information for this item (taxable or nontaxable).
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or nontaxable).
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the credit card credit was last modified.
TimeCreated	Datetime	True			When the credit card credit was created.

Create, update, delete, and query QuickBooks Credit Card Credit Line Items.

Table Specific Information

CreditCardCredits may be inserted, queried, or updated via the CreditCardCredits, CreditCardCreditExpenseItems, or CreditCardCreditLineItems tables. CreditCardCredits may be deleted by using the CreditCardCredits table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCredits are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCreditLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a CreditCardCredit, specify an Account and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new CreditCardCredit transaction. For example, the following will insert a new CreditCardCredit with two Line Items:

INSERT INTO CreditCardCreditLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Repairs', 1)
INSERT INTO CreditCardCreditLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Removal', 2)
INSERT INTO CreditCardCreditLineItems (AccountName, ItemName, ItemQuantity) SELECT AccountName, ItemName ItemQuantity FROM CreditCardCreditLineItems#TEMP

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier in the format CCCreditId|ItemLineId.
CCCreditId	String	False	CreditCardCredits.ID	255	The item identifier.
Date	Date	False			Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value.
ReferenceNumber	String	False		21	Reference number for the transaction.
AccountName	String	False		1000	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
AccountId	String	False	Accounts.ID	255	A reference to the credit card account. Either AccountId or AccountName must have a value when inserting.
Memo	String	False		5000	Memo to appear on internal reports only.
PayeeName	String	False		1000	Name of the payee for the transaction.
PayeeId	String	False	Vendors.ID	255	Id of the payee for the transaction.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
ItemLineId	String	True		255	The line item identifier.
ItemLineNumber	String	True		255	The line item number.
ItemName	String	False			The item name.
ItemId	String	False	Items.ID	255	The item identifier.
ItemGroup	String	False		100	Item group name. Reference to a group of line items this item is part of.
ItemGroupId	String	False	Items.ID	255	Item group name. Reference to a group of line items this item is part of.
ItemDescription	String	False		5000	A description of the item.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	In a transaction line item, the name of the unit of measure is selected from within the available units for the item. If the company file is enabled only for single unit of measure per item, this must be the base unit. Only available in QBXML Version 7.0 and above.
ItemCost	Double	False			The unit cost for an item.
ItemAmount	Decimal	False			Total amount for this item.
ItemBillableStatus	String	False		13	Billing status of the item. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer who ordered the item.
ItemCustomerId	String	False	Customers.ID	255	The Id of the customer who ordered the item.
ItemClass	String	False		1000	The name for the class of the item.
ItemClassId	String	False	Class.ID	1000	The Id for the class of the item.
ItemTaxCode	String	False		3	Sales tax information for this item (taxable or nontaxable).
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	Sales tax information for this item (taxable or nontaxable).
ItemInventorySiteId	String	False	InventorySites.ID	31	The inventory site Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The inventory site name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The inventory site location Id of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The inventory site location name of this item. This requires QBXML version at least 10.0 and the Advanced Inventory add-on.
ExchangeRate	Double	False			The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency.
TimeModified	Datetime	True			When the bill was last modified.
TimeCreated	Datetime	True			When the bill was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ItemOverrideAccount	String	The Account Name used to override the default Account for the Item. This is only available during inserts and updates.
ItemOverrideAccountId	String	The Account Id used to override the default Account for the Item. This is only available during inserts and updates.