Adding a Connection to QuickBooks

To add a connection to QuickBooks:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the QuickBooks icon is not available, click the Add More icon to download and install the QuickBooks connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

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: 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 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. 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).
• Authentication: Specifies the type of authentication to perform when the user connects. The Desktop 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 Desktop Gateway receives a connection request, it authenticates the user to Windows using the credentials supplied in the request.

• Software: The QuickBooks product you are connecting to.
• 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.
• 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, so that QuickBooks data cannot be modified.

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

When a user is added, the Desktop Gateway prompts you to authorize the application with QuickBooks, if necessary.

Importing Users

The Import button prompts you to select an .ini file containing one or more user profiles.

Imported .ini files must conform to the following syntax:

[MyUserName]
Config1 = Value1
Config2 = Value2
...

The following configurations are available:

• Password: Corresponds to the "Password" option in the "Add User" form. Required when using basic authentication.
• AuthMode: Corresponds to the "Authentication" option in the "Add User" form. Options include:
  • 0 (corresponds to "Basic Authentication")
  • 1 (corresponds to "Windows Authentication")
• Software: Corresponds to the "Software" option in the "Add User" form. Options include:
  • QB Desktop
  • QB Point of Sale 10
  • QB Point of Sale 11
  • QB Point of Sale 12
  • QB Point of Sale 18
• CompanyFile: Corresponds to the "Company File" option in the "Add User" form.
• AppName: Corresponds to the "Application Name" option in the "Add User" form.
• ReadOnly: Corresponds to the "Data Access" option in the "Add User" form. Options include:
  • 0 (corresponds to "Full")
  • 1 (corresponds to "Read-only")

Here is an example configuration:

[TestUser]
Password = SamplePassword
AuthMode = 0
Software = QB Desktop
CompanyFile = C:\Users\test\Documents\samplefile.qbw
AppName = Remote Connector
ReadOnly = 0

The Status tab provides a log of the activity happening with the Desktop 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 Desktop Gateway to write logs to a file and select the log rotation interval.

The Advanced tab enables granular control over the Desktop Gateway's server.

The Desktop 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 opened. The Desktop 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 Desktop 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 Desktop 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 Desktop 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 Desktop Gateway closes the connection.

Enabling TLS/SSL

Enable TLS (1.x) to encrypt communication between your application and the Desktop 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 Desktop Gateway has a command-line interface that makes it easy to deploy on machines such as Web servers. To facilitate deployment to these environments, the Desktop 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 Desktop 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.

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 Desktop Gateway even when Open and Close are not used. This is helpful in situations when multiple applications may be simultaneously accessing the Desktop 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 Desktop 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 Desktop 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 supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

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

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

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

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 QuickBooks data as an easy-to-use SQL database. There are three parts to the data model: tables, views, and stored procedures.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples of what you might have access to in your QuickBooks account.

Common tables include:

Table	Description
Accounts	Manages accounts, allowing users to create, update, delete, and query account data.
Customers	Handles customers, supporting creation, updates, deletion, and queries for managing customer data effectively.
Vendors	Manages vendors, supporting creation, updates, deletion, and queries for supplier relationship management.
Invoices	Handles invoices, supporting creation, updates, deletion, and queries for billing and receivables tracking.
Bills	Handles the management of bills, including creating, updating, deleting, and querying data for expense tracking.
ReceivePayments	Manages receive payment transactions, supporting creation, updates, deletion, and queries for customer payments.
Items	Manages items, including creation, updates, deletion, and queries for inventory and service tracking.
JournalEntries	Handles journal entries, supporting creation, updates, deletion, and queries for accounting adjustments.
Employees	Manages employees, allowing creation, updates, deletion, and queries for employee records and payroll data.
PurchaseOrders	Handles purchase orders, supporting creation, updates, deletion, and queries for supplier management.
SalesReceipts	Handles sales receipts, supporting creation, updates, deletion, and queries for tracking point-of-sale transactions.
CreditMemos	Manages credit memos, supporting creation, updates, deletion, and queries for issuing and tracking customer credits.
Estimates	Handles estimates, including creation, updates, deletion, and queries for tracking potential sales.
TimeTracking	Manages time tracking events, supporting creation, updates, deletion, and queries for employee or job time entries.
Deposits	Manages deposits, allowing users to create, update, delete, and query financial deposits.
Transfers	Manages transfers, supporting creation, updates, and queries for tracking movement of funds between accounts.
CreditCardCharges	Manages credit card charges, allowing users to create, update, delete, and query financial transactions.
CreditCardCredits	Manages credit card credits, allowing users to create, update, delete, and query credit transactions for accurate accounting.
SalesOrders	Handles sales orders, supporting creation, updates, deletion, and queries for tracking customer orders.
PaymentMethods	Handles payment methods, supporting creation, updates, deletion, and queries for payment categorization.

Note: During bulk insert operations, the Sync App only supports multiple values for aggregate columns. Other columns allow single values.

API limitations and requirements are documented in each table.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including exposing QuickBooks reports as tables, merging accounts, and voiding transactions.

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

QuickBooks Connector for CData Sync Tables

Name	Description
Accounts	Manages QuickBooks Accounts, allowing users to create, update, delete, and query account data. Ensure the QBXML Version is set to 6.0 or higher for updates.
BillExpenseItems	Manages QuickBooks Bill Expense Line Items, supporting creation, updates, deletions, and queries for detailed expense tracking.
BillLineItems	Handles creation, updates, deletion, and querying of individual line items associated with QuickBooks Bills.
BillPaymentChecks	Manages Bill Payment Checks in QuickBooks, including creating, updating, deleting, and querying checks. Requires QBXML Version 6.0 or higher for updates.
BillPaymentChecksAppliedTo	Tracks the bill transactions to which Bill Payments are applied, with support for creating, updating, deleting, and querying aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
BillPaymentCreditCards	Manages QuickBooks Bill Payments made via credit cards, including creation, updates, deletion, and queries.
BillPaymentCreditCardsAppliedTo	Tracks the application of credit card payments to specific Bill transactions, supporting creation, updates, deletion, and queries. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
Bills	Handles the management of QuickBooks Bills, including creating, updating, deleting, and querying data for expense tracking.
BuildAssemblies	Manages Build Assembly transactions in QuickBooks, allowing users to insert, update, delete, and query assemblies for inventory management.
BuildAssemblyLineItems	Supports creation and querying of individual line items within QuickBooks Build Assembly transactions.
CheckExpenseItems	Handles QuickBooks Check Expense Line Items, allowing users to create, update, delete, and query detailed expense records.
CheckLineItems	Supports management of individual line items within QuickBooks Checks, including creation, updates, deletion, and queries.
Checks	Manages QuickBooks Checks, allowing users to create, update, delete, and query check transactions for financial tracking.
Class	Facilitates the management of QuickBooks Classes, including creating, updating, deleting, and querying classifications. Updates require QBXML Version 8.0 or higher.
CreditCardChargeExpenseItems	Handles QuickBooks Credit Card Charge Expense Line Items, supporting creation, updates, deletion, and detailed queries.
CreditCardChargeLineItems	Manages individual line items associated with QuickBooks Credit Card Charges, including creation, updates, deletion, and queries.
CreditCardCharges	Manages QuickBooks Credit Card Charges, allowing users to create, update, delete, and query financial transactions.
CreditCardCreditExpenseItems	Supports management of expense items associated with QuickBooks Credit Card Credits, including creation, updates, deletion, and queries.
CreditCardCreditLineItems	Handles individual line items within QuickBooks Credit Card Credits, supporting creation, updates, deletion, and queries.
CreditCardCredits	Manages QuickBooks Credit Card Credits, allowing users to create, update, delete, and query credit transactions for accurate accounting.
CreditCardRefunds	Facilitates the creation and querying of QuickBooks AR Credit Card Refund transactions for managing customer refunds.
CreditMemoLineItems	Manages individual line items within QuickBooks Credit Memos, allowing creation, updates, deletion, and detailed queries.
CreditMemos	Manages QuickBooks Credit Memos, supporting creation, updates, deletion, and queries for issuing and tracking customer credits.
Currency	Handles QuickBooks currency management, including creation, updates, deletion, and querying of supported currencies. Requires QBXML Version 8.0 or higher, with multi-currency enabled.
CustomerContacts	Manages QuickBooks Customer Contacts, allowing creation, updates, deletion, and queries for customer relationship management. Requires QBXML Version 12.0 or higher and is only available in QuickBooks editions 2016 and above.
CustomerMessages	Supports creating, deleting, and querying Customer Messages within QuickBooks to streamline communication.
CustomerNotes	Manages QuickBooks Customer Notes, allowing creation, updates, and queries for tracking customer-specific information. Requires QBXML Version 12.0 or higher.
Customers	Handles QuickBooks Customers, supporting creation, updates, deletion, and queries for managing customer data effectively.
CustomerShippingAddresses	Manages multiple shipping addresses for QuickBooks Customers, including creation, updates, deletion, and queries. Requires QBXML Version 12.0 or higher and is supported in QuickBooks 2013 and higher.
CustomerTypes	Facilitates the management of QuickBooks Customer Types, supporting creation, updates, deletion, and queries to classify customers.
DateDrivenTerms	Supports the creation, deletion, and querying of QuickBooks Date-Driven Terms for payment and invoice schedules.
DepositLineItems	Handles individual line items within QuickBooks Deposits, supporting creation, updates, deletion, and queries. Requires QBXML Version 7.0 or higher.
Deposits	Manages QuickBooks Deposits, allowing users to create, update, delete, and query financial deposits. Requires QBXML Version 7.0 or higher for updates.
EmployeeEarnings	Facilitates the management of QuickBooks Employee Earnings, supporting creation, updates, deletion, and queries for payroll tracking.
Employees	Manages QuickBooks Employees, allowing creation, updates, deletion, and queries for employee records and payroll data.
EstimateLineItems	Manages individual line items within QuickBooks Estimates, supporting creation, updates, deletion, and queries for sales planning.
Estimates	Handles QuickBooks Estimates, including creation, updates, deletion, and queries for tracking potential sales.
InventoryAdjustmentLineItems	Manages individual line items within QuickBooks Inventory Adjustments, supporting creation and queries for stock control.
InventoryAdjustments	Handles QuickBooks Inventory Adjustments, allowing creation, queries, and deletion to reconcile stock discrepancies.
InventorySites	Manages QuickBooks Inventory Sites, including creation, updates, deletion, and queries. Available only in QuickBooks Enterprise with Advanced Inventory and QBXML Version 10.0 or higher.
InvoiceLineItems	Manages individual line items within QuickBooks Invoices, supporting creation, updates, deletion, and queries for billing details.
Invoices	Handles QuickBooks Invoices, supporting creation, updates, deletion, and queries for billing and receivables tracking.
ItemLineItems	Manages individual line items in QuickBooks Items, including creation, updates, deletion, and queries for detailed inventory tracking.
ItemReceiptExpenseItems	Handles QuickBooks Item Receipt Expense Line Items, supporting creation, updates, deletion, and queries for purchase expense tracking.
ItemReceiptLineItems	Manages individual line items within QuickBooks Item Receipts, supporting creation, updates, deletion, and queries for purchase tracking.
ItemReceipts	Handles QuickBooks Item Receipts, supporting creation, updates, deletion, and queries for tracking goods received.
Items	Manages QuickBooks Items, including creation, updates, deletion, and queries for inventory and service tracking.
JobTypes	Manages QuickBooks Job Types, supporting creation and queries to classify customer jobs for better project tracking.
JournalEntries	Handles QuickBooks Journal Entries, supporting creation, updates, deletion, and queries for accounting adjustments. Note: Journal Entry Lines cannot be modified in existing entries.
JournalEntryLines	Manages individual lines within QuickBooks Journal Entries, supporting creation, updates, deletion, and queries for detailed accounting entries.
OtherNames	Manages QuickBooks 'Other Name' entities, allowing creation, updates, deletion, and queries for non-customer or vendor names.
PaymentMethods	Handles QuickBooks Payment Methods, supporting creation, updates, deletion, and queries for payment categorization.
PayrollNonWageItems	Queries non-wage payroll items in QuickBooks, providing insight into additional payroll expenses or deductions.
PayrollWageItems	Manages QuickBooks Wage Payroll Items, supporting creation and queries to streamline wage-based payroll processing.
PriceLevelPerItem	Manages Per-Item Price Levels in QuickBooks, allowing creation and queries of individual item pricing for specific customers. Available in QuickBooks Premier and Enterprise with QBXML Version 4.0 or higher. In addition to adding Per-Item Price Levels, you can also add Price Levels from this table. To delete Price Levels, you must use the PriceLevels table.
PriceLevels	Handles QuickBooks Price Levels, supporting creation, deletion, and queries for fixed-percentage pricing adjustments to a wide range of customers or items. You can add and delete Price Levels from this table, but you can only add fixed-percentage Price Levels. To add more specific per-item price levels, use the PriceLevelPerItem table. This table requires QBXML Version 4.0 or higher.
PurchaseOrderLineItems	Manages individual line items in QuickBooks Purchase Orders, supporting creation, updates, deletion, and queries for procurement tracking.
PurchaseOrders	Handles QuickBooks Purchase Orders, supporting creation, updates, deletion, and queries for supplier management.
ReceivePayments	Manages QuickBooks Receive Payment transactions, supporting creation, updates, deletion, and queries for customer payments. Requires QBXML Version 6.0 or higher.
ReceivePaymentsAppliedTo	Tracks individual transactions to which a QuickBooks Receive Payment is applied, supporting creation, updates, and queries. In a Receive Payment, each AppliedTo aggregate represents the transaction to which this part of the payment is being applied. Requires QBXML Version 6.0 or higher.
SalesOrderLineItems	Manages individual line items in QuickBooks Sales Orders, supporting creation, updates, deletion, and queries for order management.
SalesOrders	Handles QuickBooks Sales Orders, supporting creation, updates, deletion, and queries for tracking customer orders.
SalesReceiptLineItems	Manages individual line items in QuickBooks Sales Receipts, supporting creation, updates, deletion, and queries for detailed sales tracking.
SalesReceipts	Handles QuickBooks Sales Receipts, supporting creation, updates, deletion, and queries for tracking point-of-sale transactions.
SalesReps	Manages QuickBooks Sales Representatives, allowing creation, updates, deletion, and queries for tracking sales activities.
SalesTaxCodes	Handles QuickBooks Sales Tax Codes, supporting creation, updates, deletion, and queries for tax categorization.
SalesTaxItems	Manages QuickBooks Sales Tax Items, supporting creation, updates, deletion, and queries for tax calculations on invoices and sales.
ShippingMethods	Manages QuickBooks Shipping Methods, supporting creation, updates, deletion, and queries for shipping-related information.
StandardTerms	Handles QuickBooks Standard Terms, allowing creation, updates, deletion, and queries for payment terms configuration.
StatementCharges	Manages QuickBooks Statement Charges, supporting creation, updates, deletion, and queries for customer billing statements.
TimeTracking	Manages QuickBooks Time Tracking events, supporting creation, updates, deletion, and queries for employee or job time entries.
ToDo	Handles QuickBooks To-Do entries, supporting creation, updates, deletion, and queries for task management.
TransferInventory	Manages QuickBooks Inventory Transfers, supporting queries and deletion for inventory movement tracking. Available in QuickBooks Enterprise with Advanced Inventory.
TransferInventoryLineItems	Handles individual line items within QuickBooks Inventory Transfers, supporting creation and queries for detailed inventory tracking.
Transfers	Manages QuickBooks Transfers, supporting creation, updates, and queries for tracking movement of funds between accounts. Requires QBXML Version 12.0 or higher.
UnitOfMeasure	Handles QuickBooks Units of Measure, supporting creation, updates, deletion, and queries. Requires QBXML Version 7.0 or higher.
UnitOfMeasureDefaultUnits	Manages default units within QuickBooks Units of Measure, supporting creation and queries for streamlined measurement configurations. Requires QBXML Version 7.0 or higher.
UnitOfMeasureRelatedUnits	Handles related units in QuickBooks Units of Measure, supporting creation and queries for complex measurement systems. Requires QBXML Version 7.0 or higher.
VehicleMileage	Manages QuickBooks Vehicle Mileage, supporting creation, updates, deletion, and queries for mileage tracking. Requires QBXML Version 6.0 or higher.
VendorCreditExpenseItems	Handles QuickBooks Vendor Credit Expense Items, supporting creation, updates, deletion, and queries for tracking expenses linked to vendor credits.
VendorCreditLineItems	Manages individual line items in QuickBooks Vendor Credits, supporting creation, updates, deletion, and queries for detailed vendor credit tracking.
VendorCredits	Handles QuickBooks Vendor Credits, supporting creation, updates, deletion, and queries for managing vendor refunds or adjustments.
Vendors	Manages QuickBooks Vendors, supporting creation, updates, deletion, and queries for supplier relationship management.
VendorTypes	Handles QuickBooks Vendor Types, allowing creation, updates, deletion, and queries for categorizing vendors.
WorkersCompCodeLines	Queries QuickBooks Workers Compensation Code details by line item, providing insight into specific payroll classifications.
WorkersCompCodes	Queries QuickBooks Workers Compensation Codes, providing an overview of payroll classifications. Requires QBXML Version 7.0 or higher.

Manages QuickBooks Accounts, allowing users to create, update, delete, and query account data. Ensure the QBXML Version is set to 6.0 or higher for updates.

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 assigned to the account in QuickBooks. This value is required and serves as a primary key.
Name	String	False		31	The name of the account. This must have a value when inserting a new account and is used for identification in QuickBooks.
FullName	String	True		1000	The complete hierarchical name of the account, including all parent accounts, formatted as Parent:AccountName.
Type	String	False		100	The type of the account, such as Expense, Income, or Bank. Defines the account's role in financial transactions. The allowed values are AccountsPayable, AccountsReceivable, Bank, CostOfGoodsSold, CreditCard, Equity, Expense, FixedAsset, Income, LongTermLiability, NonPosting, OtherAsset, OtherCurrentAsset, OtherCurrentLiability, OtherExpense, OtherIncome.
SpecialType	String	True		100	Specifies the special account type in QuickBooks, if applicable, such as Accounts Receivable or Accounts Payable. 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 account number, typically used for bank accounts or other numeric identifiers.
Balance	Double	True			The total balance of the account, including amounts from all associated subaccounts.
AccountBalance	Double	True			The balance of the account itself, excluding balances from any subaccounts.
BankAccount	String	False			The bank account number or an identifying note for this account, used for reference in banking transactions.
Description	String	False		200	A detailed textual description of the account, used to provide additional context or notes.
IsActive	Boolean	False			Indicates whether the account is currently active and available for use in QuickBooks.
ParentName	String	False			References the name of the parent account. If specified, this account is treated as a subaccount of the parent.
ParentId	String	False	Accounts.ID	255	References the unique identifier of the parent account. If specified, this account is treated as a subaccount or job under the parent.
Sublevel	Integer	True			Indicates the hierarchical level of the account. The value represents the number of ancestor accounts.
CurrencyName	String	False		64	The name of the currency used for the account. Requires a minimum QBXML version of 8.0.
CurrencyId	String	False	Currency.ID	255	The unique identifier of the currency assigned to the account. Requires a minimum QBXML version of 8.0.
CashFlowClassification	String	True			Indicates how the account is classified for cash flow reporting. Possible values include None, Operating, Investing, Financing, or NotApplicable.
TaxLineName	String	True		256	The name of the tax form line associated with this account, if any. Check CompanyInfo for details on the tax form used in the company file.
TaxLineId	String	False		255	The unique identifier of the tax form line associated with this account, if any. Requires QBXML version 7.0 or later to modify.
TimeModified	Datetime	True			The timestamp of the last modification made to the account in QuickBooks.
TimeCreated	Datetime	True			The timestamp of when the account was initially created in QuickBooks.

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 initial balance of the account, typically set during account creation. Defaults to 0 if not specified.
OpeningDate	String	The date associated with the opening balance of the account. Used only when creating new accounts in QuickBooks.

Manages QuickBooks Bill Expense Line Items, supporting creation, updates, deletions, and queries for detailed expense tracking.

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	A unique identifier for the expense line item in the format BillId|ExpenseLineId, combining the bill and expense line information.
BillId	String	False	Bills.ID	255	The unique identifier for the bill associated with this transaction.
VendorName	String	False		1000	The name of the vendor for this transaction. Either VendorName or VendorId must be provided when inserting.
VendorId	String	False	Vendors.ID	255	The unique identifier for the vendor associated with this transaction. Either VendorName or VendorId must be provided when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for tracking and identification.
Date	Date	False			The date of the transaction. If set in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
TxnNumber	Integer	True			A manually assigned transaction number that identifies this transaction separately from the Quickbooks generated ID.
DueDate	Date	False			The date when payment for this bill is due.
Terms	String	False		100	The payment terms associated with this bill, such as Net30 or COD.
TermsId	String	False		255	The unique identifier for the payment terms associated with this bill.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this bill.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this bill.
Amount	Decimal	True			The total amount of the transaction, calculated by QuickBooks based on the line items or expense line items.
Memo	String	False		5000	A memo providing additional details about the transaction.
IsPaid	Boolean	True			Indicates whether this bill has been paid. Returns true if paid, otherwise false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. This field is available only in international editions of QuickBooks.
ExpenseLineId	String	True		255	The unique identifier for the specific expense line item.
ExpenseLineNumber	String	True		255	The line number associated with this expense within the transaction.
ExpenseAccount	String	False		1000	The name of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The unique identifier for the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAmount	Decimal	False			The total monetary amount for this specific expense line.
ExpenseBillableStatus	String	False		13	The billing status of the expense line, such as Billable or Non-Billable. The allowed values are Empty, Billable, NotBillable, HasBeenBilled.
ExpenseCustomer	String	False		1000	The name of the customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The unique identifier for the customer associated with this expense line.
ExpenseClass	String	False		1000	The class name associated with this expense, used for categorization.
ExpenseClassId	String	False	Class.ID	255	The unique identifier for the class associated with this expense.
ExpenseMemo	String	False		5000	A memo providing details about this specific expense line.
ExpenseTaxCode	String	False		3	The tax code associated with this expense line, indicating whether it is taxable or non-taxable.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this expense line.
ExpenseCustomFields	String	True			Custom fields defined for this specific expense line item.
ExchangeRate	Double	False			The exchange rate applied to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to the bill.
TimeCreated	Datetime	True			The timestamp of when the bill was created.
OpenAmount	Decimal	True			The current outstanding balance for the vendor associated with this bill.

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	The identifier of a transaction, such as a purchase order, to link this bill to. This must be an open transaction, and linking requires a minimum QBXML Version 4.0.

Handles creation, updates, deletion, and querying of individual line items associated with 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.

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	A unique identifier for the line item in the format BillId|ItemLineId, combining the bill and line item information.
BillId	String	False	Bills.ID	255	The unique identifier for the bill associated with this transaction.
VendorName	String	False		1000	The name of the vendor for this transaction. Either VendorName or VendorId must be provided when inserting.
VendorId	String	False	Vendors.ID	255	The unique identifier for the vendor associated with this transaction. Either VendorName or VendorId must be provided when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for tracking and identification.
Date	Date	False			The date of the transaction. If set in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
TxnNumber	Integer	True			A manually assigned transaction number that identifies this transaction separately from the Quickbooks generated ID.
DueDate	Date	False			The date when payment for this bill is due.
Terms	String	False		100	The payment terms associated with this bill, such as Net30 or COD.
TermsId	String	False		255	The unique identifier for the payment terms associated with this bill.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this bill.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this bill.
Amount	Decimal	True			The total amount of the transaction, calculated by QuickBooks based on the line items.
Memo	String	False		5000	A memo providing additional details about the transaction.
IsPaid	Boolean	True			Indicates whether this bill has been paid. Returns true if paid, otherwise false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. This field is available only in international editions of QuickBooks.
ItemLineId	String	True		255	The unique identifier for the specific line item.
ItemLineNumber	String	True		255	The line number of this item within the transaction.
ItemName	String	False			The name of the item listed in this transaction.
ItemId	String	False	Items.ID	255	The unique identifier for the item listed in this transaction.
ItemGroup	String	False		100	The name of the item group this line item belongs to, if applicable.
ItemGroupId	String	False	Items.ID	255	The unique identifier for the item group this line item belongs to, if applicable.
ItemDescription	String	False		5000	A detailed description of the item listed in this transaction.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	The unit of measure for the item as defined in the company's settings. Requires QBXML version 7.0 or above.
ItemCost	Double	False			The cost per unit of the item listed in this transaction.
ItemAmount	Decimal	False			The total amount for this item line, calculated as quantity multiplied by cost.
ItemBillableStatus	String	False		13	The billing status of the item, such as Billable or Non-Billable. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer associated with this item line.
ItemCustomerId	String	False	Customers.ID	255	The unique identifier for the customer associated with this item line.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier for the inventory site where this item is stored. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The name of the inventory site where this item is stored. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The unique identifier for the location within the inventory site for this item. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site for this item. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemSerialNumber	String	False		5000	The serial number of the item, if applicable. Requires QBXML version 11.0 and the Advanced Inventory add-on.
ItemLotNumber	String	False		40	The lot number of the item, if applicable. Requires QBXML version 11.0 and the Advanced Inventory add-on.
ItemClass	String	False		1000	The name of the class associated with this item, used for categorization.
ItemClassId	String	False	Class.ID	255	The unique identifier for the class associated with this item.
ItemTaxCode	String	False		3	The tax code indicating whether the item is taxable or non-taxable.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this item.
ItemCustomFields	String	False			Custom fields defined for this specific line item.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number of this item. Requires QBXML version 16.0 or above.
CurrencyName	String	False		64	The name of the currency used for this bill.
CurrencyId	String	False	Currency.ID	1000	The unique identifier for the currency used for this bill.
ExchangeRate	Decimal	False			The exchange rate used to convert the currency of this bill to the company's home currency.
TimeModified	Datetime	True			The timestamp of the last modification made to the bill.
TimeCreated	Datetime	True			The timestamp of when the bill was created.
OpenAmount	Decimal	True			The current outstanding balance for the vendor associated with this bill.

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	The unique identifier of a transaction, such as a purchase order, to link this bill to. This must be an open transaction and requires QBXML version 4.0.
ItemOverrideAccount	String	The name of the account used to override the default account for the item. Available only during inserts and updates.
ItemOverrideAccountId	String	The unique identifier of the account used to override the default account for the item. Available only during inserts and updates.

Manages Bill Payment Checks in QuickBooks, including creating, updating, deleting, and querying checks. Requires QBXML Version 6.0 or higher for updates.

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 bill payment transaction in QuickBooks.
PayeeName	String	False		1000	The name of the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required when inserting.
PayeeId	String	False	Vendors.ID	255	The unique identifier for the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for identification and tracking.
TxnNumber	Int	True			A manually assigned transaction number for identifying this payment separately from the Quickbooks generated ID.
Date	Date	False			The date of the transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
Amount	Decimal	True			The total amount of the bill payment. This value is calculated by QuickBooks based on the associated line items or expense line items.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this transaction.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this transaction.
BankAccountName	String	False		1000	The name of the bank account from which funds are drawn for this bill payment. Applicable only for the check payment method.
BankAccountId	String	False	Accounts.ID	255	The unique identifier for the bank account from which funds are drawn for this bill payment. Applicable only for the check payment method.
IsToBePrinted	Boolean	False			Indicates whether the transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box in the user interface is checked. The default value is false.
Memo	String	False		5000	A memo or note associated with the transaction, visible on internal reports.
AppliedToAggregate	String	False		5000	An aggregate of data showing which bills the payment is applied to. Used for adding a bill payment check and its associated data.
ExchangeRate	Double	False			The currency exchange rate used to convert this payment's currency to the company's home currency.
AmountInHomeCurrency	Double	True			The total amount of the payment converted to the company's home currency.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
TimeModified	Datetime	True			The timestamp of the last modification made to this bill payment.
TimeCreated	Datetime	True			The timestamp of when this bill payment was originally 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	Represents all applied-to-specific columns that can be used during insertion to specify details about which bills the payment applies to.

Tracks the bill transactions to which Bill Payments are applied, with support for creating, updating, deleting, and querying 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	A unique identifier for the applied-to entry, formatted as BillPaymentId|AppliedToId.
BillPaymentId	String	False	BillPaymentChecks.ID	255	The unique identifier for the bill payment transaction associated with this applied-to entry.
PayeeName	String	False		1000	The name of the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required and must match the vendor associated with the bill being paid when inserting.
PayeeId	String	False	Vendors.ID	255	The unique identifier for the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required and must match the vendor associated with the bill being paid when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for identification and tracking.
TxnNumber	Int	True			A manually assigned transaction number for identifying this payment separately from the Quickbooks generated ID.
Date	Date	False			The date of the transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this transaction.
AccountsPayableId	String	False		255	The unique identifier for the accounts payable account associated with this transaction.
BankAccountId	String	False		255	The unique identifier for the bank account from which funds are drawn for this bill payment. Applicable only for the check payment method.
BankAccountName	String	False		1000	The name of the bank account from which funds are drawn for this bill payment. Applicable only for the check payment method.
IsToBePrinted	Boolean	False			Indicates whether the transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box in the user interface is checked. The default value is false.
Memo	String	False		5000	A memo or note associated with the transaction, visible on internal reports.
ExchangeRate	Double	False			The currency exchange rate used to convert this payment's currency to the company's home currency.
AmountInHomeCurrency	Double	True			The total amount of the payment converted to the company's home currency.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
AppliedToRefId	String	False		255	The unique identifier for the bill to which this payment is being applied. Found in the Bills table.
AppliedToAmount	Decimal	False			The amount of the payment being applied to the bill.
AppliedToBalanceRemaining	Decimal	True			The remaining balance of the bill after this payment is applied.
AppliedToCreditAmount	Decimal	False			The amount of credit being applied to the bill.
AppliedToCreditMemoId	String	False		255	The unique identifier for the credit memo being applied to the bill.
AppliedToDiscountAccountId	String	False		255	The unique identifier for the discount account being applied to the bill.
AppliedToDiscountAccountName	String	False		1000	The name of the discount account being applied to the bill.
AppliedToDiscountAmount	Decimal	False			The discount amount being applied to the bill.
AppliedToPaymentAmount	Decimal	False			The specific payment amount being applied to the bill.
AppliedToReferenceNumber	String	True		21	The reference number associated with the bill being paid.
AppliedToTxnDate	Date	True			The date of the transaction being applied.
AppliedToTxnType	String	True		100	The type of transaction being applied, such as Bill or CreditMemo.
TimeModified	Datetime	True			The timestamp of the last modification made to the bill payment.
TimeCreated	Datetime	True			The timestamp of when the bill payment was originally 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 amount of credit applied to the bill during this transaction.

Manages QuickBooks Bill Payments made via credit cards, including creation, updates, deletion, and queries.

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 bill payment transaction.
PayeeName	String	False		1000	The name of the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required.
PayeeId	String	False	Vendors.ID	255	The unique identifier for the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required.
ReferenceNumber	String	False		21	The reference number for the transaction, used for identification and tracking.
TxnNumber	Int	True			A manually assigned transaction number for identifying this payment separately from the Quickbooks generated ID.
Date	Date	False			The date of the transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
Amount	Decimal	True			The total amount of the bill payment. This value is calculated by QuickBooks based on the associated line items or expense line items.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this transaction.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this transaction.
CreditCardName	String	False		1000	The name of the credit card account this payment is charged to. Applicable only for credit card payment methods.
CreditCardId	String	False		255	The unique identifier for the credit card account this payment is charged to. Applicable only for credit card payment methods.
Memo	String	False		5000	A memo or note associated with the transaction, visible on internal reports.
AppliedToAggregate	String	False		5000	An aggregate of data showing which bills the payment is applied to. Used for adding a bill payment credit card and its associated data.
ExchangeRate	Double	False			The currency exchange rate used to convert this payment's currency to the company's home currency.
AmountInHomeCurrency	Double	True			The total amount of the payment converted to the company's home currency.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
TimeModified	Datetime	True			The timestamp of the last modification made to this bill payment.
TimeCreated	Datetime	True			The timestamp of when this bill payment was originally 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	Represents all applied-to-specific columns that can be used during insertion to specify details about which bills the payment applies to.

Tracks the application of credit card payments to specific Bill transactions, supporting creation, updates, deletion, and queries. 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	A unique identifier for the applied-to entry, formatted as BillPaymentId|AppliedToId, combining the payment and application details.
BillPaymentId	String	False	BillPaymentCreditCards.ID	255	The unique identifier for the bill payment transaction associated with this applied-to entry.
PayeeName	String	False		1000	The name of the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required and must match the vendor associated with the bill being paid when inserting.
PayeeId	String	False	Vendors.ID	255	The unique identifier for the entity (such as a vendor) to whom the payment is being made. Either PayeeId or PayeeName is required and must match the vendor associated with the bill being paid when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for identification and tracking.
TxnNumber	Int	True			A manually assigned transaction number for identifying this payment separately from the Quickbooks generated ID.
Date	Date	False			The date of the transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this transaction.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this transaction.
CreditCardName	String	False		1000	The name of the credit card account this payment is charged to. Applicable only for credit card payment methods.
CreditCardId	String	False		255	The unique identifier for the credit card account this payment is charged to. Applicable only for credit card payment methods.
IsToBePrinted	Boolean	False			Indicates whether the transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box in the user interface is checked. The default value is false.
Memo	String	False		5000	A memo or note associated with the transaction, visible on internal reports.
ExchangeRate	Double	False			The currency exchange rate used to convert this payment's currency to the company's home currency.
AmountInHomeCurrency	Double	True			The total amount of the payment converted to the company's home currency.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
AppliedToRefId	String	True		255	The unique identifier for the bill to which this payment is being applied. Found in the Bills table.
AppliedToAmount	Decimal	True			The specific amount of the payment being applied to the bill.
AppliedToBalanceRemaining	Double	True			The remaining balance of the bill after this payment is applied.
AppliedToCreditMemoId	String	False	CreditMemos.ID	255	The unique identifier for the credit memo being applied to the bill.
AppliedToDiscountAccountName	String	False		1000	The name of the discount account being applied to the bill.
AppliedToDiscountAccountId	String	False	Accounts.ID	255	The unique identifier for the discount account being applied to the bill.
AppliedToDiscountAmount	Decimal	False			The discount amount being applied to the bill.
AppliedToPaymentAmount	Decimal	False			The specific payment amount being applied to the bill.
AppliedToReferenceNumber	String	True		21	The reference number of the bill or transaction being paid.
AppliedToTxnDate	Date	True			The date of the transaction being applied.
AppliedToTxnType	String	True		100	The type of transaction being applied, such as Bill or CreditMemo.
TimeModified	Datetime	True			The timestamp of the last modification made to the bill payment.
TimeCreated	Datetime	True			The timestamp of when the bill payment was originally 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 specific amount of credit applied to the bill during this transaction.

Handles the management of QuickBooks Bills, including creating, updating, deleting, and querying data for expense tracking.

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 in QuickBooks.
VendorName	String	False		1000	The name of the vendor associated with this bill. Either VendorName or VendorId must be provided when inserting.
VendorId	String	False	Vendors.ID	255	The unique identifier for the vendor associated with this bill. Either VendorName or VendorId must be provided when inserting.
ReferenceNumber	String	False		21	A reference number for the bill transaction, used for identification and tracking.
Date	Date	False			The date of the transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
Amount	Decimal	True			The total amount of the bill. This value is calculated by QuickBooks based on the line items or expense line items.
TxnNumber	Integer	True			A manually assigned transaction number to identify this bill separately from the Quickbooks generated ID.
DueDate	Date	False			The date when payment for this bill is due.
Terms	String	False		100	The payment terms associated with this bill, such as Net30 or COD.
TermsId	String	False		255	The unique identifier for the payment terms associated with this bill.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this bill.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this bill.
Memo	String	False		5000	A memo or note providing additional details about this bill.
IsPaid	Boolean	True			Indicates whether this bill has been paid. Returns true if paid, otherwise false.
ExchangeRate	Double	False			The exchange rate used to convert this bill's currency to the home currency of the QuickBooks company file.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. This field is available only in international editions of QuickBooks.
ItemCount	Integer	True			The total number of line items associated with this bill.
ItemAggregate	String	False		5000	An aggregate of the line item data, used to add a bill and its line item data in a single operation.
ExpenseItemCount	Integer	True			The total number of expense line items associated with this bill.
ExpenseItemAggregate	String	False		5000	An aggregate of the expense line item data, used to add a bill and its expense line item data in a single operation.
TransactionCount	Integer	True			The total number of related transactions linked to this bill.
TransactionAggregate	String	True		5000	An aggregate of the linked transaction data associated with this bill.
CurrencyName	String	False		64	The name of the currency used for this bill.
CurrencyId	String	False	Currency.ID	1000	The unique identifier for the currency used for this bill.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
TimeModified	Datetime	True			The timestamp of the last modification made to this bill.
TimeCreated	Datetime	True			The timestamp of when this bill was originally created.
OpenAmount	Decimal	True			The current outstanding balance for the vendor associated with this bill.

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	Represents all line-item-specific columns that can be used during insertion to specify line item details for this bill.
Expense*	String	Represents all expense-item-specific columns that can be used during insertion to specify expense item details for this bill.
LinkToTxnId	String	The unique identifier of a transaction, such as a purchase order, to link this bill to. This transaction must be open, and linking requires a minimum QBXML Version 4.0.

Manages Build Assembly transactions in QuickBooks, allowing users to insert, update, delete, and query assemblies for inventory management.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier for the build assembly transaction in QuickBooks.
TxnNumber	Integer	True			A manually assigned transaction number for identifying this BuildAssembly separately from the Quickbooks generated ID.
ItemInventoryAssemblyRef_ListID	String	False	Items.ID	255	The unique identifier of the inventory assembly being built. Either this field or ItemInventoryAssemblyRef_FullName must be provided when inserting a BuildAssembly.
ItemInventoryAssemblyRef_FullName	String	False		1000	The name of the inventory assembly being built. Either this field or ItemInventoryAssemblyRef_ListID must be provided when inserting a BuildAssembly.
InventorySiteRef_ListID	String	False	InventorySites.ID	255	The unique identifier of the inventory site where the assembly is being built.
InventorySiteRef_FullName	String	False		31	The name of the inventory site where the assembly is being built.
SerialNumber	String	False		5000	The serial number of the asset being built. Cannot be used if LotNumber is specified.
LotNumber	String	False		40	The lot number of the asset being built. Cannot be used if SerialNumber is specified.
TxnDate	Date	False			The date of the transaction for the build assembly.
RefNumber	String	False		21	A reference number for the transaction, used for identification. This number does not have to be unique.
Memo	String	False		5000	A memo providing additional details about the build assembly transaction.
IsPending	Boolean	True			Indicates whether the transaction is pending. If true, the build assembly has not yet been completed.
QuantityToBuild	Double	False			The number of assemblies to be built. The transaction fails if this number exceeds the available on-hand inventory.
QuantityCanBuild	Double	True			The maximum number of assemblies that can be built with the current parts on hand.
QuantityOnHand	Double	True			The total quantity of these items currently in inventory. Adjustments must be made using inventory adjustments.
QuantityOnSalesOrder	Double	True			The number of these items that have been sold but not yet delivered to customers (as recorded in sales orders).
MarkPendingIfRequired	Boolean	False			Indicates whether the assembly should be marked as pending if there are insufficient parts to complete it. Requires QBXML Version 7.0 and is not returned in QuickBooks responses.
ExternalGUID	String	True			A user-defined globally unique identifier (GUID) for this transaction. Requires QBXML Version 9.0.
BuildAssemblyLineAggregate	String	True		5000	An aggregate of line item data for adding a build assembly and its associated line items in a single operation.
TimeCreated	Datetime	True			The timestamp of when this build assembly transaction was originally created.
TimeModified	Datetime	True			The timestamp of the last modification made to this build assembly transaction.
EditSequence	String	True			A unique identifier used for version control of this build assembly transaction, ensuring data integrity during updates.
ExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number associated with this item. Requires QBXML Version 16.0 or higher.

Supports creation and querying of individual line items within QuickBooks Build Assembly transactions.

Columns

Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier for this build assembly line item.
BuildAssemblyId	String	False	BuildAssemblies.ID	255	The unique identifier of the build assembly transaction associated with this line item.
TxnNumber	Integer	True			A manually assigned transaction number for identifying this build assembly transaction separately from the Quickbooks generated ID.
ItemInventoryAssemblyRef_ListID	String	False	Items.ID	255	The unique identifier of the inventory assembly being built. Either this field or ItemInventoryAssemblyRef_FullName must be provided when inserting a BuildAssembly.
ItemInventoryAssemblyRef_FullName	String	False		1000	The name of the inventory assembly being built. Either this field or ItemInventoryAssemblyRef_ListID must be provided when inserting a BuildAssembly.
InventorySiteRef_ListID	String	False	InventorySites.ID	255	The unique identifier of the inventory site associated with this line item. Requires QBXML Version 10.0.
InventorySiteRef_FullName	String	False		31	The name of the inventory site associated with this line item. Requires QBXML Version 10.0.
SerialNumber	String	False		5000	The serial number of the asset associated with this line item. Cannot be used if LotNumber is specified.
LotNumber	String	False		40	The lot number of the asset associated with this line item. Cannot be used if SerialNumber is specified.
TxnDate	Date	False			The date of the transaction for the build assembly.
RefNumber	String	False		21	A reference number for the transaction, used for identification. This number does not have to be unique.
Memo	String	False		5000	A memo providing additional details about this build assembly transaction.
IsPending	Boolean	True			Indicates whether this transaction is pending. If true, the build assembly has not yet been completed.
QuantityToBuild	Double	False			The number of assemblies to be built. The transaction fails if this quantity exceeds the number of on-hand items.
QuantityCanBuild	Double	True			The maximum number of assemblies that can be built with the current parts on hand.
QuantityOnHand	Double	True			The total quantity of these items currently in inventory. Adjustments must be made using inventory adjustments.
QuantityOnSalesOrder	Double	True			The number of these items recorded in sales orders but not yet delivered to customers.
MarkPendingIfRequired	Boolean	False			Specifies whether the build assembly should be marked as pending if there are insufficient parts to complete it. Requires QBXML Version 7.0 and is not returned in QuickBooks responses.
ExternalGUID	String	True			A user-defined globally unique identifier (GUID) for this transaction. Requires QBXML Version 9.0.
ComponentItemLineRet_ItemRef_ListID	String	True	Items.ID	255	The unique identifier of the item used in this assembly line.
ComponentItemLineRet_ItemRef_FullName	String	True		255	The full name of the item used in this assembly line.
ComponentItemLineRet_InventorySiteRef_ListID	String	True	InventorySites.ID	255	The unique identifier of the inventory site where the component item is stored. Requires QBXML Version 10.0.
ComponentItemLineRet_InventorySiteRef_FullName	String	True		31	The full name of the inventory site where the component item is stored. Requires QBXML Version 10.0.
ComponentItemLineRet_Desc	String	True		5000	A description of the line item used in this assembly.
ComponentItemLineRet_QuantityOnHand	Double	True			The total quantity of the component item currently in inventory.
ComponentItemLineRet_QuantityNeeded	Double	True			The quantity of the component item required to build the specified number of assemblies.
TimeCreated	Datetime	True			The timestamp of when this build assembly transaction was originally created.
TimeModified	Datetime	True			The timestamp of the last modification made to this build assembly transaction.
EditSequence	String	True			A unique identifier used for version control of this build assembly transaction, ensuring data integrity during updates.
ExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number associated with this line item. Requires QBXML Version 16.0 or higher.

Handles QuickBooks Check Expense Line Items, allowing users to create, update, delete, and query detailed expense records.

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 for this expense line, formatted as CheckId|ExpenseLineId, combining the check and line item details.
CheckId	String	False	Checks.ID	255	The unique identifier for the check associated with this expense. Obtained from the Checks table.
ReferenceNumber	String	False		21	The reference number for the check transaction, used for identification and tracking.
TxnNumber	Integer	True			A manually assigned transaction number for identifying this check separately from the Quickbooks generated ID.
Account	String	False		1000	The name of the account from which the funds for this check are being drawn.
AccountId	String	False	Accounts.ID	255	The unique identifier of the account from which the funds for this check are being drawn.
Payee	String	False		1000	The name of the payee associated with this check.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this check.
Date	Date	False			The date of the check transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
Amount	Decimal	True			The total amount of the check transaction.
Memo	String	False		5000	A memo providing additional details about this check transaction.
Address	String	True			The full address associated with this check, as returned by QuickBooks.
Line1	String	False		500	The first line of the address associated with this check.
Line2	String	False		500	The second line of the address associated with this check.
Line3	String	False		500	The third line of the address associated with this check.
Line4	String	False		500	The fourth line of the address associated with this check.
Line5	String	False		41	The fifth line of the address associated with this check.
City	String	False		255	The city associated with the address of the check.
State	String	False		255	The state associated with the address of the check.
PostalCode	String	False		30	The postal code associated with the address of the check.
Country	String	False		255	The country associated with the address of the check.
Note	String	False		41	A note associated with the address of the check.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
ExpenseLineId	String	True		255	The unique identifier for the specific expense line item.
ExpenseLineNumber	String	True		255	The line number of this expense within the check transaction.
ExpenseAccount	String	False		1000	The name of the account associated with this expense line.
ExpenseAccountId	String	False	Accounts.ID	255	The unique identifier of the account associated with this expense line.
ExpenseAmount	Decimal	False			The total amount of this specific expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line, such as Billable or Non-Billable. The allowed values are Empty, Billable, NotBillable, HasBeenBilled. The default value is Empty.
ExpenseCustomer	String	False		1000	The name of the customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this expense line.
ExpenseClass	String	False		1000	The name of the class associated with this expense line, used for categorization.
ExpenseClassId	String	False	Class.ID	255	The unique identifier of the class associated with this expense line.
ExpenseTaxCode	String	False		3	The tax code indicating whether the expense line is taxable or non-taxable. Available only in international editions of QuickBooks.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this expense line. Available only in international editions of QuickBooks.
ExpenseMemo	String	False		5000	A memo providing additional details about this expense line.
ExpenseCustomFields	String	True			Custom fields defined for this specific expense line.
IsToBePrinted	Boolean	False			Indicates whether this check transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box is checked. The default value is false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency used for this check. Requires QBXML Version 8.0 or higher.
CurrencyId	String	False	Currency.ID	255	The unique identifier of the currency used for this check. Requires QBXML Version 8.0 or higher.
ExchangeRate	Double	False			The exchange rate used to convert this check's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this check transaction.
TimeCreated	Datetime	True			The timestamp of when this check transaction was originally 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	The unique identifier of the transaction (such as a bill) to be paid by this check. Can be used during updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. Can be used during updates and inserts.

Supports management of individual line items within QuickBooks Checks, including creation, updates, deletion, and queries.

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 for this line item, formatted as CheckId|ItemLineId, combining the check and line item details.
CheckId	String	False	Checks.ID	255	The unique identifier for the check associated with this line item, obtained from the Checks table.
ReferenceNumber	String	False		21	The reference number for the check transaction, used for identification and tracking.
TxnNumber	Integer	True			A manually assigned transaction number to identify this check separately from the Quickbooks generated ID.
Account	String	False		1000	The name of the account from which the funds for this check are being drawn.
AccountId	String	False	Accounts.ID	255	The unique identifier of the account from which the funds for this check are being drawn.
Payee	String	False		1000	The name of the payee associated with this check.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this check.
Date	Date	False			The date of the check transaction.
Amount	Decimal	True			The total amount of the check transaction.
Memo	String	False		5000	A memo providing additional details about this check transaction.
Address	String	True			The full address associated with this check, as returned by QuickBooks.
Line1	String	False		500	The first line of the address associated with this check.
Line2	String	False		500	The second line of the address associated with this check.
Line3	String	False		500	The third line of the address associated with this check.
Line4	String	False		500	The fourth line of the address associated with this check.
Line5	String	False		41	The fifth line of the address associated with this check.
City	String	False		255	The city associated with the address of the check.
State	String	False		255	The state associated with the address of the check.
PostalCode	String	False		30	The postal code associated with the address of the check.
Country	String	False		255	The country associated with the address of the check.
Note	String	False		41	A note associated with the address of the check.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
ItemLineId	String	True		255	The unique identifier for the specific line item.
ItemLineNumber	String	True		255	The line number of this item within the check transaction.
ItemName	String	False			The name of the item listed in this transaction.
ItemId	String	False	Items.ID		The unique identifier for the item listed in this transaction.
ItemGroup	String	False		100	The name of the item group this line item belongs to, if applicable.
ItemGroupId	String	False	Items.ID	255	The unique identifier for the item group this line item belongs to, if applicable.
ItemDescription	String	False		5000	A detailed description of the item listed in this transaction.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	The unit of measure for the item as defined in the company's settings. Requires QBXML Version 7.0 or higher.
ItemCost	Double	False			The cost per unit of the item listed in this transaction.
ItemAmount	Decimal	False			The total amount for this item line, calculated as quantity multiplied by cost.
ItemBillableStatus	String	False		13	The billing status of the item, such as Billable or Non-Billable. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED. The default value is EMPTY.
ItemCustomer	String	False		1000	The name of the customer associated with this item line.
ItemCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this item line.
ItemClass	String	False		1000	The name of the class associated with this item, used for categorization.
ItemClassId	String	False	Class.ID	255	The unique identifier of the class associated with this item.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The name of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The unique identifier of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number associated with this item. Requires QBXML Version 16.0 or higher.
ItemTaxCode	String	False		3	The tax code indicating whether the item is taxable or non-taxable. Available only in international editions of QuickBooks.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this item. Available only in international editions of QuickBooks.
ItemCustomFields	String	False			Custom fields defined for this specific line item.
IsToBePrinted	Boolean	False			Indicates whether this check transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box is checked. The default value is false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency used for this check. Requires QBXML Version 8.0 or higher.
CurrencyId	String	False	Currency.ID	255	The unique identifier of the currency used for this check. Requires QBXML Version 8.0 or higher.
ExchangeRate	Double	False			The exchange rate used to convert this check's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this check transaction.
TimeCreated	Datetime	True			The timestamp of when this check transaction was originally 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	The unique identifier of the transaction (such as a bill) to be paid by this check. Can be used during updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. Can be used during updates and inserts.

Manages QuickBooks Checks, allowing users to create, update, delete, and query check transactions for financial tracking.

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 for the check transaction in QuickBooks.
ReferenceNumber	String	False		21	The reference number for the check transaction, used for identification and tracking.
TxnNumber	Integer	True			A manually assigned transaction number to identify this check separately from the Quickbooks generated ID.
Account	String	False		1000	The name of the account from which the funds for this check are being drawn.
AccountId	String	False	Accounts.ID	255	The unique identifier of the account from which the funds for this check are being drawn.
Payee	String	False		1000	The name of the payee associated with this check.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this check.
Date	Date	False			The date of the check transaction.
Amount	Decimal	True			The total amount of the check transaction.
Memo	String	False		5000	A memo providing additional details about this check transaction.
Address	String	True			The full address associated with this check, as returned by QuickBooks.
Line1	String	False		500	The first line of the address associated with this check.
Line2	String	False		500	The second line of the address associated with this check.
Line3	String	False		500	The third line of the address associated with this check.
Line4	String	False		500	The fourth line of the address associated with this check.
Line5	String	False		41	The fifth line of the address associated with this check.
City	String	False		255	The city associated with the address of the check.
State	String	False		255	The state associated with the address of the check.
PostalCode	String	False		30	The postal code associated with the address of the check.
Country	String	False		255	The country associated with the address of the check.
Note	String	False		41	A note associated with the address of the check.
ItemCount	Integer	True			The total number of line items associated with this check.
ItemAggregate	String	False		5000	An aggregate of line item data used to add a check and its line item data in a single operation.
ExpenseItemCount	Integer	True			The total number of expense line items associated with this check.
ExpenseItemAggregate	String	False		5000	An aggregate of expense line item data used to add a check and its expense item data in a single operation.
IsToBePrinted	Boolean	False			Indicates whether this check transaction is marked as 'To Be Printed' in QuickBooks. If true, the 'To Be Printed' box is checked. The default value is false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
CurrencyName	String	False		64	The name of the currency used for this check. Requires QBXML Version 8.0 or higher.
CurrencyId	String	False	Currency.ID	255	The unique identifier of the currency used for this check. Requires QBXML Version 8.0 or higher.
ExchangeRate	Double	False			The exchange rate used to convert this check's currency to the home currency of the QuickBooks company file.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
TimeModified	Datetime	True			The timestamp of the last modification made to this check transaction.
TimeCreated	Datetime	True			The timestamp of when this check transaction was originally 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	Represents all line-item-specific columns that can be used during insertion to specify line item details for this check.
Expense*	String	Represents all expense-item-specific columns that can be used during insertion to specify expense item details for this check.
ApplyCheckToTxnId	String	The unique identifier of the transaction (such as a bill) to be paid by this check. Can be used during updates and inserts.
ApplyCheckToTxnAmount	String	The amount of the transaction to be paid by this check. Can be used during updates and inserts.

Facilitates the management of QuickBooks Classes, including creating, updating, deleting, and querying classifications. Updates require QBXML Version 8.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 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 assigned to the class in QuickBooks.
Name	String	False		100	The name of the class, used for categorization or tracking within QuickBooks.
FullName	String	True		1000	The full hierarchical name of the class, formatted as ParentName|ClassName if it has a parent.
IsActive	Boolean	False			Indicates whether the class is active and available for use in QuickBooks.
ParentRef_FullName	String	False			The full name of the parent class, if applicable. Specify either ParentRef_FullName or ParentRef_ListId on INSERT/UPDATE operations, but not both.
ParentRef_ListId	String	False	Class.ID	255	The unique identifier of the parent class, if applicable. Specify either ParentRef_FullName or ParentRef_ListId on INSERT/UPDATE operations, but not both.
Sublevel	Integer	True			The hierarchical level of the class, indicating how many parent classes it has.
EditSequence	String	True		16	A unique string used for versioning, representing the revision of the class.
TimeCreated	Datetime	True			The timestamp of when this class was originally created in QuickBooks.
TimeModified	Datetime	True			The timestamp of the last modification made to this class in QuickBooks.

Handles QuickBooks Credit Card Charge Expense Line Items, supporting creation, updates, deletion, and detailed queries.

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 for the expense line, formatted as CCChargeId|ItemLineId, combining the credit card charge and line item details.
CCChargeId	String	False	CreditCardCharges.ID	255	The unique identifier of the credit card charge associated with this expense line.
Date	Date	False			The date of the credit card charge transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
ReferenceNumber	String	False		21	The reference number for the credit card charge transaction, used for identification and tracking.
AccountName	String	False		1000	The name of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
AccountId	String	False	Accounts.ID	255	The unique identifier of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
Memo	String	False		5000	A memo providing additional details about this transaction, visible on internal reports only.
PayeeName	String	False		1000	The name of the payee associated with this credit card charge.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this credit card charge.
IsTaxIncluded	Boolean	False			Indicates whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
ExpenseLineId	String	True		255	The unique identifier of the specific expense line item.
ExpenseLineNumber	String	True		255	The line number of this expense within the credit card charge transaction.
ExpenseAccount	String	False		1000	The name of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The unique identifier of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAmount	Decimal	False			The total amount of this specific expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line, such as Billable or Non-Billable. The allowed values are Empty, Billable, NotBillable, HasBeenBilled.
ExpenseCustomer	String	False		1000	The name of the customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this expense line.
ExpenseClass	String	False		1000	The name of the class associated with this expense line, used for categorization.
ExpenseClassId	String	False	Class.ID	255	The unique identifier of the class associated with this expense line.
ExpenseMemo	String	False		5000	A memo providing additional details about this specific expense line.
ExpenseTaxCode	String	False		3	The tax code indicating whether this expense line is taxable or nontaxable.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier of the tax code associated with this expense line.
ExchangeRate	Double	False			The exchange rate used to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this credit card charge transaction.
TimeCreated	Datetime	True			The timestamp of when this credit card charge transaction was originally created.

Manages individual line items associated with QuickBooks Credit Card Charges, including creation, updates, deletion, and queries.

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 for the line item, formatted as CCChargeId|ItemLineId, combining the credit card charge and line item details.
CCChargeId	String	False	CreditCardCharges.ID	255	The unique identifier for the credit card charge associated with this line item.
Date	Date	False			The date of the credit card charge transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
ReferenceNumber	String	False		21	The reference number for the credit card charge transaction, used for identification and tracking.
AccountName	String	False		1000	The name of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
AccountId	String	False	Accounts.ID	255	The unique identifier of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
Memo	String	False		5000	A memo providing additional details about this transaction, visible on internal reports only.
PayeeName	String	False		1000	The name of the payee associated with this credit card charge.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this credit card charge.
IsTaxIncluded	Boolean	False			Indicates whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
ItemLineId	String	True		255	The unique identifier for the specific line item.
ItemLineNumber	String	True		255	The line number of this item within the credit card charge transaction.
ItemName	String	False			The name of the item listed in this transaction.
ItemId	String	False	Items.ID	255	The unique identifier of the item listed in this transaction.
ItemGroup	String	False		100	The name of the item group this line item belongs to, if applicable.
ItemGroupId	String	False	Items.ID	255	The unique identifier of the item group this line item belongs to, if applicable.
ItemDescription	String	False		5000	A detailed description of the item listed in this transaction.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		100	The unit of measure for the item, selected from the available units. If the company file allows only a single unit of measure per item, this must be the base unit. Requires QBXML Version 7.0 or higher.
ItemCost	Double	False			The unit cost of the item listed in this transaction.
ItemAmount	Decimal	False			The total amount for this item line, calculated as quantity multiplied by cost.
ItemBillableStatus	String	False		13	The billing status of the item, such as Billable or Non-Billable. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer associated with this item line.
ItemCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this item line.
ItemClass	String	False		1000	The name of the class associated with this item, used for categorization.
ItemClassId	String	False	Class.ID	255	The unique identifier of the class associated with this item.
ItemTaxCode	String	False		3	The tax code indicating whether this item is taxable or nontaxable.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier of the tax code associated with this item.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The name of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The unique identifier of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number associated with this item. Requires QBXML Version 16.0 or higher.
ExchangeRate	Double	False			The exchange rate used to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this transaction.
TimeCreated	Datetime	True			The timestamp of when this transaction was originally 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 name of the account used to override the default account for this item. Applicable only during inserts and updates.
ItemOverrideAccountId	String	The unique identifier of the account used to override the default account for this item. Applicable only during inserts and updates.

Manages QuickBooks Credit Card Charges, allowing users to create, update, delete, and query financial transactions.

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 credit card charge transaction in QuickBooks.
Date	Date	False			The date of the credit card charge transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
ReferenceNumber	String	False		11	The reference number for the credit card charge transaction, used for identification and tracking.
AccountName	String	False		1000	The name of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
AccountId	String	False	Accounts.ID	255	The unique identifier of the credit card account associated with this charge. Either AccountName or AccountId must be provided when inserting.
Memo	String	False		5000	A memo providing additional details about this transaction, visible on internal reports only.
PayeeName	String	False		1000	The name of the payee associated with this credit card charge.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this credit card charge.
IsTaxIncluded	Boolean	False			Indicates whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
ItemCount	Integer	True			The total number of line items associated with this credit card charge.
ItemAggregate	String	False		5000	An aggregate of line item data used to add a credit card charge and its line items in a single operation.
ExpenseItemCount	Integer	True			The total number of expense line items associated with this credit card charge.
ExpenseItemAggregate	String	False		5000	An aggregate of expense item data used to add a credit card charge and its expense items in a single operation.
CustomFields	String	False			Custom fields returned from QuickBooks, formatted as XML.
TimeModified	Datetime	True			The timestamp of the last modification made to this credit card charge transaction.
TimeCreated	Datetime	True			The timestamp of when this credit card charge transaction was originally created.
TxnNumber	Integer	True			A manually assigned transaction number to identify this charge separately 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	Represents all line-item-specific columns that can be used during insertion to specify line item details for this credit card charge.
Expense*	String	Represents all expense-item-specific columns that can be used during insertion to specify expense item details for this credit card charge.

Supports management of expense items associated with QuickBooks Credit Card Credits, including creation, updates, deletion, and queries.

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 for the expense line, formatted as CCCreditId|ItemLineId, combining the credit card credit and line item details.
CCCreditId	String	False	CreditCardCredits.ID	255	The unique identifier of the credit card credit transaction associated with this expense line.
Date	Date	False			The date of the credit card credit transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
ReferenceNumber	String	False		21	The reference number for the credit card credit transaction, used for identification and tracking.
AccountName	String	False		1000	The name of the credit card account associated with this credit. Either AccountName or AccountId must be provided when inserting.
AccountId	String	False	Accounts.ID	255	The unique identifier of the credit card account associated with this credit. Either AccountName or AccountId must be provided when inserting.
Memo	String	False		5000	A memo providing additional details about this transaction, visible on internal reports only.
PayeeName	String	False		1000	The name of the payee associated with this credit card credit.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this credit card credit.
IsTaxIncluded	Boolean	False			Indicates whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
ExpenseLineId	String	True		255	The unique identifier of the specific expense line item.
ExpenseLineNumber	String	True		255	The line number of this expense within the credit card credit transaction.
ExpenseAccount	String	False		1000	The name of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The unique identifier of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAmount	Decimal	False			The total amount of this specific expense line.
ExpenseBillableStatus	String	False		13	The billing status of this expense line, such as Billable or Non-Billable. The allowed values are Empty, Billable, NotBillable, HasBeenBilled.
ExpenseCustomer	String	False		1000	The name of the customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this expense line.
ExpenseClass	String	False		1000	The name of the class associated with this expense line, used for categorization.
ExpenseClassId	String	False	Class.ID	255	The unique identifier of the class associated with this expense line.
ExpenseMemo	String	False		5000	A memo providing additional details about this specific expense line.
ExpenseTaxCode	String	False		3	The tax code indicating whether this expense line is taxable or nontaxable.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier of the tax code associated with this expense line.
ExchangeRate	Double	False			The exchange rate used to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this credit card credit transaction.
TimeCreated	Datetime	True			The timestamp of when this credit card credit transaction was originally created.

Handles individual line items within QuickBooks Credit Card Credits, supporting creation, updates, deletion, and queries.

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 for the line item, formatted as CCCreditId|ItemLineId, combining the credit card credit and line item details.
CCCreditId	String	False	CreditCardCredits.ID	255	The unique identifier of the credit card credit transaction associated with this line item.
Date	Date	False			The date of the credit card credit transaction. If specified in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
ReferenceNumber	String	False		21	The reference number for the credit card credit transaction, used for identification and tracking.
AccountName	String	False		1000	The name of the credit card account associated with this credit. Either AccountName or AccountId must be provided when inserting.
AccountId	String	False	Accounts.ID	255	The unique identifier of the credit card account associated with this credit. Either AccountName or AccountId must be provided when inserting.
Memo	String	False		5000	A memo providing additional details about this transaction, visible on internal reports only.
PayeeName	String	False		1000	The name of the payee associated with this credit card credit.
PayeeId	String	False	Vendors.ID	255	The unique identifier of the payee associated with this credit card credit.
IsTaxIncluded	Boolean	False			Indicates whether tax is included in the transaction amount. Available only in international editions of QuickBooks.
ItemLineId	String	True		255	The unique identifier for the specific line item.
ItemLineNumber	String	True		255	The line number of this item within the credit card credit transaction.
ItemName	String	False			The name of the item listed in this transaction.
ItemId	String	False	Items.ID	255	The unique identifier of the item listed in this transaction.
ItemGroup	String	False		100	The name of the item group this line item belongs to, if applicable.
ItemGroupId	String	False	Items.ID	255	The unique identifier of the item group this line item belongs to, if applicable.
ItemDescription	String	False		5000	A detailed description of the item listed in this transaction.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	The unit of measure for the item, selected from the available units. If the company file allows only a single unit of measure per item, this must be the base unit. Requires QBXML Version 7.0 or higher.
ItemCost	Double	False			The unit cost of the item listed in this transaction.
ItemAmount	Decimal	False			The total amount for this item line, calculated as quantity multiplied by cost.
ItemBillableStatus	String	False		13	The billing status of the item, such as Billable or Non-Billable. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer associated with this item line.
ItemCustomerId	String	False	Customers.ID	255	The unique identifier of the customer associated with this item line.
ItemClass	String	False		1000	The name of the class associated with this item, used for categorization.
ItemClassId	String	False	Class.ID	1000	The unique identifier of the class associated with this item.
ItemTaxCode	String	False		3	The tax code indicating whether this item is taxable or nontaxable.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier of the tax code associated with this item.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The name of the inventory site where this item is stored. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The unique identifier of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site for this item. Requires QBXML Version 10.0 and the Advanced Inventory add-on.
ExchangeRate	Double	False			The exchange rate used to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to this credit card credit transaction.
TimeCreated	Datetime	True			The timestamp of when this credit card credit transaction was originally created.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number associated with this item. Requires QBXML Version 16.0 or higher.

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 name of the account used to override the default account for this item. Applicable only during inserts and updates.
ItemOverrideAccountId	String	The unique identifier of the account used to override the default account for this item. Applicable only during inserts and updates.