QuickBooks Online Query Data Model

Connection String Options

  1. Auto Cache
  2. Cache Connection
  3. Cache Driver
  4. Cache Location
  5. Cache Metadata
  6. Cache Query Result
  7. Cache Tolerance
  8. Callback URL
  9. Company Id
  10. Connect On Open
  11. Country Code
  12. Firewall Password
  13. Firewall Port
  14. Firewall Server
  15. Firewall Type
  16. Firewall User
  17. Hide Line Totals
  18. Initiate OAuth
  19. Location
  20. Logfile
  21. Max Log File Size
  22. OAuth Access Token
  23. OAuth Access Token Secret
  24. OAuth Client Id
  25. OAuth Client Secret
  26. OAuth Refresh Token
  27. OAuth Settings Location
  28. Offline
  29. Other
  30. Pagesize
  31. Pool Idle Timeout
  32. Pool Max Size
  33. Pool Wait Time
  34. Proxy Auth Scheme
  35. Proxy Auto Detect
  36. Proxy Password
  37. Proxy Port
  38. Proxy Server
  39. Proxy SSL Type
  40. Proxy User
  41. Pseudo Columns
  42. Readonly
  43. RTK
  44. SSL Server Cert
  45. Support Enhanced SQL
  46. Tables
  47. Timeout
  48. Use Connection Pooling
  49. Use Sandbox
  50. Verbosity
  51. Views

Auto Cache

Data Type

bool

Default Value

false

Remarks

When AutoCache is set, the driver automatically maintains a cache of your table's data in the database of your choice. By default, the driver incrementally updates the cache, retrieving only changes since the last SELECT query was run if the length of time since the last run has exceeded the CacheTolerance. After the cache is updated, the query is executed against the cached data. With CacheQueryResult set, the driver updates the cache when you execute a SELECT query and returns the live results from the QuickBooks Online data.

 

Explicitly Caching SELECT Results

 

CacheQueryResult is a way to query QuickBooks Online in real time while maintaining a cache for offline use. Set CacheQueryResult to update the cache whenever you execute a SELECT statement. When you execute a SELECT statement with AutoCache and CacheQueryResult set, the driver executes the query to the remote data and caches the results; rows that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it. Data manipulation commands are executed to the remote data as well.

To query the cached data, set the Offline property. If you need to query the cached data in an online connection, you can append #CACHE to the table name. For example:

SELECT * FROM [Customers#CACHE]

 

 

Setting the Caching Database

 

When AutoCache is set, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

 

 

See Also

 

 

  • CacheTolerance: Control the tolerance for stale data in the cache.
  • CacheQueryResult: Insert or update each row returned into the corresponding table in the cache.
  • Caching: Best Practices: This section provides more examples of using AutoCache with Offline, as well as information on determining a caching strategy.

    When this property is set, the driver builds a temporary, in-memory dataset that caches the results of the queries you execute. Result sets for subsequent queries are extracted from this dataset, if possible.

    This property is useful in BI, analytics, and other tools that generate queries for you. In these tools, explicit cache queries with the #CACHE syntax may not be an option.

  • CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the QuickBooks Online metadata. Metadata then needs to be retrieved only once instead of every connection.
  • REPLICATE Statements: When you execute a SELECT statement with AutoCache set, the driver internally calls the REPLICATE statement. The REPLICATE statement enables incremental updates instead of first dropping the cached table.
  • CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.

 

 

 

Cache Connection

Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properies are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

 

Derby and Java DB

 

The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.

Alternatively, you can configure the connection to Derby manually using CacheProvider and CacheConnection. Below is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]
For example, to cache to an in-memory database, use the following:
jdbc:derby:memory

 

 

SQLite

 

To cache to SQLite, you can use the SQLite JDBC driver. Below is the syntax of the JDBC URL:

jdbc:sqlite:dataSource
  • Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

 

 

MySQL

 

The installation includes the CData JDBC Driver for MySQL. Below is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache
Below are typical connection properties:

 

 

  • Server: The IP address or domain name of the server you want to connect to.
  • Port: The port that the server is running on.
  • User: The username provided for authentication to the database.
  • Password: The password provided for authentication to the database.
  • Database: The name of the database.
SQL Server

 

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]
For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true
Below are typical SQL Server connection properties:
  • Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
  • Port: The port SQL Server is running on.
  • Database: The name of the SQL Server database.
  • Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.

    To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.

  • User: The username provided for authentication with SQL Server. Only needed if you are not using integrated security.
  • Password: The password provided for authentication with SQL Server. Only needed if you are not using integrated security.
Oracle

 

Below is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]
For example:
jdbc:oracle:thin:scott/tiger@myhost:1521:orcl
Below are typical connection properties:
  • Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).

  • Password: The password provided for authentication with the Oracle database.
  • User Id: The user Id provided for authentication with the Oracle database.
PostgreSQL

 

Below is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]
For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgres
Below are typical connection properties:
  • Host: The address of the server hosting the PostgreSQL database.
  • Port: The port used to connect to the server hosting the PostgreSQL database.
  • Database: The name of the database.
  • Username: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
  • Password: The password provided for authentication with the PostgreSQL database.

 

 

Cache Driver

Data Type

string

Default Value

""

Remarks

You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you would like to use to cache data.

Note that you must also add the CacheDriver JAR to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

 

 

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR is shipped in the JDK. You can find the JAR, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath.

jdbc:quickbooksonline:CacheLocation='c:/Temp/cachedir';InitiateOAuth=GETANDREFRESH;
To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:quickbooksonline:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';InitiateOAuth=GETANDREFRESH;
SQLite

 

Below is a JDBC URL for the SQLite JDBC driver:

jdbc:quickbooksonline:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';InitiateOAuth=GETANDREFRESH;
MySQL

 

Below is a JDBC URL for the included CData JDBC Driver for MySQL:

jdbc:quickbooksonline:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';InitiateOAuth=GETANDREFRESH;
The CData JDBC Driver for MySQL is located in the lib subfolder of the CData JDBC Driver for QuickBooks Online 2017 installation directory.
SQL Server

 

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:quickbooksonline:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';InitiateOAuth=GETANDREFRESH;
Oracle

 

Below is a JDBC URL for the Oracle Thin Client:

jdbc:quickbooksonline:Cache Driver=oracle.jdbc.driver.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';InitiateOAuth=GETANDREFRESH;
PostgreSQL

 

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:quickbooksonline:CacheDriver=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';InitiateOAuth=GETANDREFRESH;

 

 

Cache Location

Data Type

string

Default Value

""

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR to the classpath. The JAR, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

CacheLocation defaults to the directory specified by the Location setting.

See Also

 

 

  • AutoCache: Set this to implicitly create and maintain a cache for later offline use.
  • CacheMetadata: Set this to persist the QuickBooks Online catalog in CacheLocation.

 

 

Cache Metadata

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the QuickBooks Online catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

 

The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

 

 

  • When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metdata will change often.
  • When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use REPLICATE Statements or CACHE Statements to cache explicitly.

 

 

Cache Query Result

Data Type

bool

Default Value

false

Remarks

When CacheQueryResult and AutoCache are set, the rows returned from a SELECT query are cached in the cache database. The driver handles caching in a streaming fashion with each row being processed into the cache database from the original result set as you read the row from the returned ResultSet object. This ensures that the live data is not queried twice. Note that any rows you do not read from the returned ResultSet will not be updated in the cache.

 

Cache Tolerance

Data Type

string

Default Value

"600"

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver will check with the data source for newer records after the tolerance interval has expired. Otherwise it will return the data directly from the cache.

 

Callback URL

Data Type

string

Default Value

""

Remarks

This value is the URL that QuickBooks Online redirects to during the authentication process.

 

Company Id

Data Type

string

Default Value

""

Remarks

The unique identifier of a given company in QuickBooks Online. This will be determined automatically during authentication and returned from the GetOAuthAccessToken stored procedure. Do not set a value for CompanyId when using InitiateOAuth.

You can also obtain the CompanyId by pressing Ctrl+Alt+? from any screen in QuickBooks Online.

 

Connect On Open

Data Type

bool

Default Value

false

Remarks

When set to 'true', a connection will be made to QuickBooks Online when the connection is opened. This property enables the 'Test Connection' feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to QuickBooks Online and nothing from this initial connection is maintained.

Setting this property to 'false' may provide performance improvements (depending upon the number of times a connection is opened).

 

Country Code

Data Type

string

Default Value

"US"

Remarks

The country code for the edition of QuickBooks Online being used. For instance: US, IN, UK, AU, and CA.

 

Firewall Password

Data Type

string

Default Value

""

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

 

Firewall Port

Data Type

string

Default Value

""

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

 

Firewall Server

Data Type

string

Default Value

""

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

 

Firewall Type

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

 

   
Type Default Port Description
TUNNEL 80 When this is set, the driver opens a connection to QuickBooks Online and traffic flows back and forth through the proxy.
SOCKS4 1080 When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5 1080 When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

 

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

 

 

Firewall User

Data Type

string

Default Value

""

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication type specified in FirewallType.

 

Hide Line Totals

Data Type

bool

Default Value

false

Remarks

In most LineItem tables, QuickBooks Online will return at least one Subtotal line for the line items. This is technically not a line item. Set this property to True in order to hide these values when they are returned.

 

Initiate OAuth

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the driver. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the driver will only handle refreshing the OAuthAccessToken. The user will never be prompted by the driver to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

 

 

Location

Data Type

string

Default Value

""

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

The schema files used in your application must be deployed with other assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.

 

Logfile

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, take a look at Verbosity.

 

Max Log File Size

Data Type

string

Default Value

""

Remarks

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. There is no limit by default. Values lower than 100kB will use 100kB as the value instead.

 

OAuth Access Token

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your username and password. The access token protects your credentials by keeping them on the server.

 

OAuth Access Token Secret

Data Type

string

Default Value

""

Remarks

The OAuthAccessTokenSecret property is used to connect and authenticate using OAuth. The OAuthAccessTokenSecret is retrieved from the OAuth server as part of the authentication process. It is used with the OAuthAccessToken and can be used for multiple requests until it times out.

 

OAuth Client Id

Data Type

string

Default Value

""

Remarks

OAuth requires you to register your application. As part of the registration, you will receive a client Id, sometimes also called a consumer key, and a client secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.

 

OAuth Client Secret

Data Type

string

Default Value

""

Remarks

OAuth requires you to register your application. As part of the registration you will receive a client Id and a client secret, sometimes also called a consumer secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.

 

OAuth Refresh Token

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

 

OAuth Settings Location

Data Type

string

Default Value

"%APPDATA%\\CData\\QuickBooksOnline Data Provider\\OAuthSettings.txt"

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to a settings file to avoid requiring the user to manually enter OAuth connection properties. The default OAuthSettingsLocation is a settings file located in the %AppData%\CData folder.

 

Offline

Data Type

bool

Default Value

false

Remarks

When Offline is set to TRUE, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

 

Other

Data Type

string

Default Value

""

Remarks

The Other property is a semicolon-separated list of name-value pairs used in connection parameters specific to a data source.

Caching Configuration

 

   
CachePartial=True Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.

 

Integration and Formatting

 

   
ConvertDateTimeToGMT Whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.
ClientCulture This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'.
Culture This setting can be used to specify culture settings that determine how the driver interprets certain data types that are passed into the driver. For example, setting Culture='de-DE' will output German formats even on an American machine.

 

 

Pagesize

Data Type

string

Default Value

"500"

Remarks

The Pagesize property affects the maximum number of results to return per page from QuickBooks Online when executing a query. A higher value will return more results per page, but may also cause a timeout exception. 500 is the maximum number of results that may be returned per page for the Online Edition.

 

Pool Idle Timeout

Data Type

string

Default Value

""

Remarks

The allowed idle time from when the connection is free to when the connection is released and returned to the pool. The default is 60 seconds.

 

Pool Max Size

Data Type

string

Default Value

"100"

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

 

Pool Wait Time

Data Type

string

Default Value

""

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

 

Proxy Auth Scheme

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

  • BASIC: The driver performs HTTP BASIC authentication.
  • DIGEST: The driver performs HTTP DIGEST authentication.
  • NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
  • PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

 

 

Proxy Auto Detect

Data Type

bool

Default Value

true

Remarks

By default, the driver uses the system HTTP proxy. Set this to false if you want to connect to another proxy.

To connect to an HTTP proxy, see ProxyServer.

For other proxies, such as SOCKS or tunneling, see FirewallType.

 

Proxy Password

Data Type

string

Default Value

""

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

 

Proxy Port

Data Type

string

Default Value

"80"

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

 

Proxy Server

Data Type

string

Default Value

""

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

 

Proxy SSL Type

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

 

   
AUTO Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS The connection is always SSL enabled.
NEVER The connection is not SSL enabled.
TUNNEL The connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

 

 

Proxy User

Data Type

string

Default Value

""

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the username of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a username in one of the following formats:

user@domain
domain\user

 

 

Pseudo Columns

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; i.e., "*=*".

 

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

 

RTK

Data Type

string

Default Value

""

Remarks

The RTK property may be used to license a build. Please see the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

 

SSL Server Cert

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine will be rejected.

This property can take the forms:

 

   
Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

 

If not specified, any certificate trusted by the machine will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).

 

Support Enhanced SQL

Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL is set to true, the driver offloads as much of the SELECT statement processing as possible to QuickBooks Online and then processes the rest of the query in memory. In this way the driver can execute unsupported predicates, joins, and aggregation.

When SupportEnhancedSQL is set to false, the driver limits SQL execution to what is supported by the QuickBooks Online API.

Execution of Predicates

 

The driver determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets.

Execution of Joins

 

The driver uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once.

Execution of Aggregates

 

The driver retrieves all rows necessary to process the aggregation in memory.

 

Tables

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.

 

Timeout

Data Type

string

Default Value

"60"

Remarks

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.

If Timeout expires and the operation is not yet complete, the driver throws an exception.

If the Connector does not finish and respond within the chosen length of time, the driver will generate an exception instead of hanging.

 

Use Connection Pooling

Data Type

string

Default Value

"false"

Remarks

Enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

 

Use Sandbox

Data Type

bool

Default Value

false

Remarks

A boolean indicating if you are using a Sandbox account.

 

Verbosity

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:

 

   
1 Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the HTTP requests.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

 

Views

Data Type

string

Default Value

""

Remarks

Listing the Views from some databases can be expensive. Providing a list of Views in the connection string improves the performance of the driver.




 

Tables

  1. Accounts
  2. AttachableRefs
  3. Attachables
  4. BillLineItems
  5. BillPaymentLineItems
  6. BillPayments
  7. Bills
  8. Class
  9. CompanyCurrency
  10. CreditMemoLineItems
  11. CreditMemos
  12. Customers
  13. Departments
  14. DepositLineItems
  15. Deposits
  16. Employees
  17. EstimateLineItems
  18. EstimateLinkedTransactions
  19. Estimates
  20. InvoiceLineItems
  21. InvoiceLinkedTransactions
  22. Invoices
  23. Items
  24. JournalEntries
  25. JournalEntryLineItems
  26. PaymentLineItems
  27. PaymentMethods
  28. Payments
  29. PurchaseLineItems
  30. PurchaseOrderLineItems
  31. PurchaseOrders
  32. Purchases
  33. RefundReceiptLineItems
  34. RefundReceipts
  35. SalesReceiptLineItems
  36. SalesReceipts
  37. TaxAgency
  38. Terms
  39. TimeActivities
  40. Transfers
  41. VendorCreditLineItems
  42. VendorCredits
  43. Vendors

Accounts

Create, update, delete, and query QuickBooks Accounts.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the account.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true true

The name of the account. To create an account, you must provide a unique account name. Default accounts are created for new companies.

SubAccount Boolean False   true true

This value is true if the account is a subaccount, and false or null if it is a top-level account.

ParentRef String False

Accounts.Id

true true

The Id of the parent account.

ParentRef_Name String True

Accounts.Name

false false

The name of the parent account.

FullyQualifiedName String True   false false

The fully qualified name of the account. The fully qualified name consists the topmost parent element followed by each subelement and separated by colons. For example: Parent:Account1:SubAccount1:SubAccount2.

Description String False   true true

The description for the account. This may contain notes on the types of journal entries that should be posted to this account.

Active Boolean False   true false

This field sets whether or not the account is active. Inactive accounts cannot be posted to and are excluded from search results, although references to them are preserved.

Classification String False   true false

The account classification.

The allowed values are Asset, Equity, Expense, Liability, Revenue.

AccountType String False   true false

The account type or subclassification that determines how the account is used. This field is indexed by the account classification.

The allowed values are Bank, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsReceivable, Equity, Expense, OtherExpense, CostOfGoodsSold, AccountsPayable, CreditCard, LongTermLiability, OtherCurrentLiability, Income, OtherIncome.

AccountSubType String False   false false

The account subtype. This field is indexed by the AccountType field.

The allowed values are CashOnHand, Checking, MoneyMarket, RentsHeldInTrust, Savings, TrustAccounts, AllowanceForBadDebts, DevelopmentCosts, EmployeeCashAdvances, OtherCurrentAssets, Inventory, Investment_MortgageRealEstateLoans, Investment_Other, Investment_TaxExemptSecurities, Investment_USGovernmentObligations, LoansToOfficers, LoansToOthers, LoansToStockholders, PrepaidExpenses, Retainage, UndepositedFunds, AccumulatedDepletion, AccumulatedDepreciation, DepletableAssets, FurnitureAndFixtures, Land, NLeaseholdImprovements, OtherFixedAssets, AccumulatedAmortization, Buildings, IntangibleAssets, MachineryAndEquipment, Vehicles, LeaseBuyout, OtherLongTermAssets, SecurityDeposits, AccumulatedAmortizationOfOtherAssets, Goodwill, Licenses, OrganizationalCosts, AccountsReceivable, OpeningBalanceEquity, PartnersEquity, RetainedEarnings, AccumulatedAdjustment, OwnersEquity, PaidInCapitalOrSurplus, PreferredStock, CommonStock, TreasuryStock, AdvertisingPromotional, BadDebts, BankCharges, CharitableContributions, Entertainment, EntertainmentMeals, EquipmentRentalGlobalTaxExpenseInsurance, InterestPaid, LegalProfessionalFees, OfficeGeneralAdministrativeExpenses, OtherMiscellaneousServiceCost, PromotionalMeals, RentOrLeaseOfBuildings, RepairMaintenance, ShippingFreightDelivery, SuppliesMaterials, Travel, TravelMeals, Utilities, Auto, CostOfLabor, DuesSubscriptions, PayrollExpenses, TaxesPaid, Depreciation, ExchangeGainOrLoss, OtherMiscellaneousExpense, PenaltiesSettlements, Amortization, EquipmentRentalCos, OtherCostsOfServiceCos, ShippingFreightDeliveryCos, SuppliesMaterialsCogs, CostOfLaborCos, AccountsPayable, CreditCard, NotesPayable, OtherLongTermLiabilities, ShareholderNotesPayable, LineOfCredit, LoanPayable, GlobalTaxPayable, GlobalTaxSuspense, OtherCurrentLiabilities, PayrollClearing, PayrollTaxPayable, PrepaidExpensesPayable, RentsInTrustLiability, TrustAccountsLiabilities, FederalIncomeTaxPayable, InsurancePayable, SalesTaxPayable, StateLocalIncomeTaxPayable, NonProfitIncome, OtherPrimaryIncome, SalesOfProductIncome, ServiceFeeIncome, DiscountsRefundsGiven, DividendIncome, InterestEarned, OtherInvestmentIncome, OtherMiscellaneousIncome, TaxExemptInterest.

AcctNum String False   false false

A user-defined account number that identifies the account within the chart of accounts and notes the information that should be posted to the account.

CurrentBalance Double True   true true

The current balance. This field is available for only balance sheet accounts.

CurrentBalanceWithSubAccounts Double False   false false

The cumulative current balance amount for the account and all its subaccounts.

CurrencyRef String False   false false

The Id of the currency used by the account.

CurrencyRef_Name String True   false false

A name that identifies the currency used by the account.

TaxCodeRef String False

TaxCodes.Id

false false

The ID of the associated Tax Code. This ID can be used with the TaxCodes view to find more information about the associated tax. Only available in International versions of QuickBooks Online.

 

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
rows@next String

 

 

 

 

AttachableRefs

Attachables

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
EntityRefId [KEY] String True   true true

The ID of the Entity Ref of the attachable.

AttachableId [KEY] String True

Attachables.ID

true true

The Id of the attachable.

SyncToken String False   false false

The version number of the entity. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

FileName String False   true true

FileName of the attachment.

Size Integer False   true true

Size of the attachment.

ContentType String False   true true

ContentType of the attachment.

Category String False   true true

Category of the attachment.

Latitude Double False   true true

Latitude from where the attachment was requested.

Longitude Double False   true true

Longitude from where the attachment was requested.

Note String False   true true

Note either related to the attachment or as a standalone note.

PlaceName String False   true true

PlaceName from where the attachment was requested.

Tag String False   true true

Tag name for the requested attachment.

AttachableRef_EntityRef# String False   true false

Value

AttachableRef_EntityRef_type# String False   true false

Type

AttachableRef_EntityRef_name# String False   false false

Type name

AttachableRef_IncludeOnSend# Boolean False   true false

On sending email to customer (most likely for txns), this field indicates whether or not the attachment is to be sent together with the email.

AttachableRef_LineInfo# String False   true true

If the entity is a transaction, user can also specify a transaction detail line to reference.

FileAccessUri String False   true true

FullPath FileAccess URI of the attachment.

TempDownloadUri String False   true true

TempDownload URI which can be directly downloaded by clients.

 

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
rows@next String

 

 

 

 

Attachables

Attachables

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the attachable.

SyncToken String False   false false

The version number of the entity. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

FileName String False   true true

FileName of the attachment.

Size Integer False   true true

Size of the attachment.

ContentType String False   true true

ContentType of the attachment.

Category String False   true true

Category of the attachment.

Latitude Double False   true true

Latitude from where the attachment was requested.

Longitude Double False   true true

Longitude from where the attachment was requested.

Note String False   true true

Note either related to the attachment or as a standalone note.

PlaceName String False   true true

PlaceName from where the attachment was requested.

Tag String False   true true

Tag name for the requested attachment.

AttachableRefAggregate String False   false false

Aggregate that specifies the transaction object to which this attachable file is to be linked

FileAccessUri String False   true true

FullPath FileAccess URI of the attachment.

TempDownloadUri String False   true true

TempDownload URI which can be directly downloaded by clients.

 

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
rows@next String

 

 

 

 

BillLineItems

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

 

Table Specific Information

 

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

Insert

 

To add a Bill, specify the Vendor, APAccountRef (the identifier of the Accounts Payable account), TxnDate (the transaction date), and at least one Line Item. All columns denoted with a # are Line Item columns and 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 (VendorRef, APAccountRef, TxnDate, Line_DetailType#1, Line_AccountBasedExpenseLineDetail_AccountRef#1, Line_Amount#1, Line_DetailType#2, Line_AccountBasedExpenseLineDetail_AccountRef#2, Line_Amount#2) VALUES ('21', '66', '1/1/2011', 'AccountBasedExpenseLineDetail', '7', 4.04, 'AccountBasedExpenseLineDetail', '8', 6.06)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the bill.

BillId [KEY] String False

Bills.ID

true true

The Id of the bill.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date of the transaction. This field is often the date when the transaction was entered into the system, but it is not required to be. If the date is not supplied, the current date on the server is used. For posting transactions, this is the posting date that affects financial statements.

PrivateNote String False   false false

A private note about the transaction. By default, this note will not appear on the transaction records.

Line_Id# String False   false false

The Id of the line item.

Line_Amount# Double False   false false

The total amount of the charges and discounts for the line item. This includes charges and allowances but excludes the tax amount.

Line_Description# String False   false false

The line description.

Line_DetailType# String False   false false

The detail type of the line item.

Line_AccountBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The customer Id for the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The customer name for the expense.

Line_AccountBasedExpenseLineDetail_AccountRef# String False

Accounts.Id

false false

The Id of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_AccountRef_Name# String True

Accounts.Name

false false

The name of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales tax code for the expense.

Line_AccountBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBilled.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Value# Double False   false false

Markup value.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Percent# Double False   false false

Markup amount expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

Line_AccountBasedExpenseLineDetail_MarkupInfo_PriceLevelRef# String False   false false

The Id of the entity for the price level for the markup.

Line_ItemBasedExpenseLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item associated with the expense.

Line_ItemBasedExpenseLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item associated with the expense.

Line_ItemBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class for the line item.

Line_ItemBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the line item.

Line_ItemBasedExpenseLineDetail_UnitPrice# Double False   false false

The unit price of the subject item as referenced.

Line_ItemBasedExpenseLineDetail_Qty# Double False   false false

Number of items for the line.

Line_ItemBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The customer Id for the expense.

Line_ItemBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The customer name for the expense.

Line_ItemBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales tax code for the expense.

Line_ItemBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBill.

Line_ItemBasedExpenseLineDetail_MarkupInfo_Value# Double False   false false

Markup value.

Line_ItemBasedExpenseLineDetail_MarkupInfo_Percent# Double False   false false

Markup amount expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef# String False   false false

The Id of the entity for the price level for the markup.

Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef_Name# String True   false false

A name that identifies the price level for the markup.

VendorRef String False

Vendors.Id

true true

The Id of the vendor.

VendorRef_Name String True

Vendors.DisplayName

false false

The name of the vendor.

APAccountRef String False

Accounts.Id

true true

The Id of the accounts-payable account.

APAccountRef_Name String True

Accounts.Name

false false

The name of the accounts-payable account.

TotalAmt Double False   true true

The total amount of the bill, determined by taking the sum of all the line items of the bill.

SalesTermRef String False

Terms.Id

true true

The Id of the sales term.

SalesTermRef_Name String True

Terms.Name

false false

A name that identifies the sales term.

DueDate Date False   true true

The due date of the bill. This date excludes early payment discount incentives and late payment penalties.

Balance Double True   true false

The unpaid amount of the bill. When paid in full this value is 0.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

A name that identifies the department.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

The tax calculated for the transaction. This value excludes any tax line items that have been manually inserted into the transaction.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of the tax line items.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

A name that identifies the currency of the transaction.

ExchangeRate Double False   false false

The number of home currency units required to equal one foreign currency unit. This column is available only if the company file is using the multicurrency feature and is available in only the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply taxes. This column is available in only the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

BillPaymentLineItems

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

 

Table Specific Information

 

BillPayments may be inserted or queried via the BillPayments or BillPaymentLineItems tables. BillPayments may be deleted by using the BillPayments table.

Insert

 

To add a BillPayment, specify the Vendor, PayType, TxnDate (transaction date), and TotalAmt columns and at least one Line Item, as well as either CreditCardPayment or CheckPayment. You must enter an amount for each Line Item up to but not exceeding the total amount remaining to be paid for each Bill. All columns denoted with a # are Line Item columns and can be used to create a new BillPayment transaction that has multiple Line Items. For example, the following query will insert a new BillPayment with two Line Items:

INSERT INTO BillPaymentLineItems (VendorRef, PayType, TxnDate, CheckPayment_BankAccountRef, CheckPayment_PrintStatus, Line_Amount#1, Line_LinkedTxn_TxnId#1, Line_LinkedTxn_TxnType#1, Line_Amount#2, Line_LinkedTxn_TxnId#2, Line_LinkedTxn_TxnType#2, TotalAmt) VALUES ('21', 'Check', '1/1/2011', '41', 'NotSet', 0.01, '313', 'Bill', 0.02, '312', 'Bill', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the bill payment.

BillPaymentId [KEY] String False

BillPayments.Id

true true

The Id of the bill payment.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The document number for this transaction. When creating a line item, if this field is not provided QuickBooks business logic will assign the document number using the next-in-sequence algorithm.

TxnDate Date False   true true

The date of the transaction. This field is often the date when the transaction was entered into the system, but it is not required to be. For posting transactions, this is the posting date that affects financial statements

PrivateNote String False   false false

A private note about the transaction. By default, this field does not appear on the transaction records.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department.

Line_Amount# Double False   false false

The amount of the line item.

Line_LinkedTxn_TxnId# String False

Bills.Id

false false

The Id of the transaction linked to the line item.

Line_LinkedTxn_TxnType# String False   false false

The type of the transaction linked to the line item.

VendorRef String False

Vendors.Id

true true

The Id of the vendor for this transaction.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor for this transaction.

APAccountRef String False

Accounts.Id

true true

The Id of the accounts-payable account that the vendor credit is credited to. If the company has a single accounts-payable account, this account is implied. This must be a Liability account and the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The name of the accounts-payable account the vendor credit is credited to. If the company has a single accounts-payable account, this account is implied. This must be a Liability-type account and the subtype must be of type Payables.

PayType String False   false false

The payment type.

The allowed values are Check, CreditCard.

CheckPayment_BankAccountRef String False

Accounts.Id

true true

The Id of the bank account, which issued the check.

CheckPayment_BankAccountRef_Name String True

Accounts.Name

false false

A name that identifies the bank account.

CheckPayment_PrintStatus String False   false false

The print status of the check payment. This field is only applicable for checks and ignored for credit card charges and refunds.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NeedToPrint.

CreditCardPayment_CCAccountRef String False

Accounts.Id

true true

The Id of the credit card account.

CreditCardPayment_CCAccountRef_Name String True

Accounts.Name

false false

The name of the credit card account.

TotalAmt Double False   true true

The total amount of the bill, determined by the sum of all line items in the bill payment.

ProcessBillPayment Boolean False   false false

This field indicates that the payment should be processed by the merchant account service. This field is available for companies with credit card processing enabled in QuickBooks Online.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The exchange rate for the currency. This field is available only if the company file uses the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available in only the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

BillPayments

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

 

Table Specific Information

 

BillPayments may be inserted or queried via the BillPayments or BillPaymentLineItems tables. They may be deleted by using the BillPayments table.

Insert

 

To add a BillPayment, specify the Vendor, PayType, TxnDate, and TotalAmt columns, as well as at least one Line Item and either CreditCardPayment or CheckPayment. You must enter an amount for each Line Item up to but not exceeding the total amount remaining to be paid for each Bill. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the BillLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new BillPayment with two Line Items:

INSERT INTO BillPayments (VendorRef, PayType, TxnDate, CheckPayment_BankAccountRef, CheckPayment_PrintStatus, TotalAmt, LineAggregate) 
VALUES ('21', 'Check', '1/1/2011', '41', 'NotSet', 0.03 '
<Line><Amount>Repairs</Amount><LinkedTxn><TxnId>312</TxnId><TxnType>Bill</TxnType></LinkedTxn></Line>
<Line><Amount>Removal</Amount><LinkedTxn><TxnId>313</TxnId><TxnType>Bill</TxnType></LinkedTxn></LinkedTxn></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the bill payment.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The document number for the transaction. If it is not provided in a create operation, QuickBooks business logic will assign the document number using the next-in-sequence algorithm.

TxnDate Date False   true true

The date of the transaction. This field is often the date when the transaction was entered into the system, but it is not required to be. This field is used in the financial statements for posting transactions.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

DepartmentRef String False

Departments.Id

false false

The Id of the department entity, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department.

LineAggregate String False   false false

An XML aggregate containing the line items associated with the bill payment.

VendorRef String False

Vendors.Id

true true

The Id of the vendor.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor.

APAccountRef String False

Accounts.Id

true true

The Id of the accounts-payable account the vendor credit is credited to. If a single accounts-payable account is used, this account is implied. This account must be a Liability account and the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The name of the accounts-payable account the vendor credit is credited to. If the company uses a single accounts-payable account, this account is implied. This account must be a Liability account and the subtype must be of type Payables.

PayType String False   false false

The payment type.

The allowed values are Check, CreditCard.

CheckPayment_BankAccountRef String False

Accounts.Id

false false

The Id of the bank account.

CheckPayment_BankAccountRef_Name String True

Accounts.Name

false false

The name of the bank account.

CheckPayment_PrintStatus String False   false false

The print status of the check payment. This field is only applicable for checks. It is ignored for credit card charges or refunds.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NeedToPrint.

CreditCardPayment_CCAccountRef String False

Accounts.Id

true true

The Id of the credit card account.

CreditCardPayment_CCAccountRef_Name String True

Accounts.Name

false false

A name that identifies the credit card account.

TotalAmt Double False   true true

This field sets the total amount of the bill. This includes the total of all the payments from the line items of the bill payment.

ProcessBillPayment Boolean False   false false

This field sets whether the payment is processed by the merchant account service. This field is valid for QuickBooks Online companies with credit card processing.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is only valid if the company file uses the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available in only the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

Bills

Create, update, delete, and query QuickBooks Bills.

 

Table Specific Information

 

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

Insert

 

To add a Bill, specify the Vendor, APAccountRef, and TxnDate columns and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the BillLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new Bill with two Line Items:

INSERT INTO Bills (VendorRef, APAccountRef, TxnDate, LineAggregate) 
VALUES ('21', '66', '1/1/2011', '
<Line><Amount>4.04</Amount><DetailType>AccountBasedExpenseLineDetail</DetailType><AccountBasedExpenseLineDetail><AccountRef>7</AccountRef></AccountBasedExpenseLineDetail></Line>
<Line><Amount>6.06</Amount><DetailType>AccountBasedExpenseLineDetail</DetailType><AccountBasedExpenseLineDetail><AccountRef>8</AccountRef></AccountBasedExpenseLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the bill.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date of the transaction. This field is often the date when the transaction was entered into the system, but it is not required to be. For posting transactions, this is the posting date that affects financial statements.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

LineAggregate String False   false false

An XML aggregate of the line items of the bill.

VendorRef String False

Vendors.Id

true true

The Id of the vendor.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor associated with the transaction.

APAccountRef String False

Accounts.Id

true true

The Id of the accounts-payable account.

APAccountRef_Name String True

Accounts.Name

false false

The name of the accounts-payable account.

TotalAmt Double False   true true

The total amount due, determined by taking the sum of the line items.

SalesTermRef String False

Terms.Id

true true

The Id of the sales terms entity of the bill.

SalesTermRef_Name String True

Terms.Name

false false

A name that identifies the sales terms entity of the bill.

DueDate Date False   true true

The due date for the bill, excluding early payment discount incentives and late payment penalties.

Balance Double True   true false

The unpaid amount of the bill. When paid in full this value is 0.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is available only if the multicurrency feature has been set for the company file. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for only the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply the tax. This field is valid in only the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

Class

Create, update, delete, and query QuickBooks Classes.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the class.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true true

The name of the class.

SubClass Boolean True   true true

This field indicates whether the class is a subclass or not.

ParentRef String False

Class.Id

false false

The Id of the parent record.

ParentRef_Name String True

Class.Name

false false

The Id of the parent record.

Active Boolean False   true true

This field sets whether or not the class is active. Inactive classes may be hidden from display and excluded from financial transactions.

FullyQualifiedName String True   true true

The fully qualified name of the class. The fully qualified name consists the topmost parent element followed by each subelement and separated by colons. For example: Parent:Account1:SubAccount1:SubAccount.

 

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
rows@next String

 

 

 

 

CompanyCurrency

Create, update, delete, and query QuickBooks CompanyCurrency. Applicable only for those companies that enable multicurrency, a companycurrency object defines a currency that is active in the QuickBooks Online company. One or more companycurrency objects are active based on the company's multicurrency business requirements and correspond to the list displayed by the Currency Center in the QuickBooks Online UI

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the currency object.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Code String False   true true

A three letter string representing the ISO 4217 code for the currency.

Name String False   false true

The fullName of the Currrency object.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

Active Boolean False   true false

This field sets whether or not the currency is active. Inactive accounts cannot be posted to and are excluded from search results, although references to them are preserved.

 

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
rows@next String

 

 

 

 

CreditMemoLineItems

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

 

Table Specific Information

 

CreditMemos may be inserted, updated, or queried via the CreditMemos or CreditMemoLineItems tables. CreditMemos may be deleted by using the CreditMemos table.

Insert

 

To add a CreditMemo, specify a Customer and at least one Line Item. All columns denoted with a # are Line Item columns and can be used to insert multiple Line Items when creating a new CreditMemo transaction. For example, the following will insert a new CreditMemo with two Line Items:

INSERT INTO CreditMemoLineItems (CustomerRef, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('4', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the credit memo.

CreditMemoId [KEY] String False

CreditMemos.Id

true true

The Id of the credit memo.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom fields for the credit memo.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when the transaction occurred.

PrivateNote String False   false false

A private note about the transaction. By default, this field will not appear on transaction records.

LinkedTxnAggregate String False   false false

An XML aggregate of linked transactions on the credit memo.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

The description of the line item that appears in the printed record.

Line_Amount# Double False   false false

The total amount of the charges or discounts for the given line. This includes the charges and allowances but excludes the tax amount.

Line_DetailType# String False   false false

The detail type of the line item. Different detail types indicate different types of line items.

Line_SalesItemLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item. When a line item lacks an ItemRef it is treated as documentation and the Line_Amount attribute is ignored.

Line_SalesItemLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item. When a line item lacks an ItemRef it is treated as documentation and the Line_Amount attribute is ignored.

Line_SalesItemLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class entity of the line item.

Line_SalesItemLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class entity of the line item.

Line_SalesItemLineDetail_UnitPrice# Double False   false false

The unit price of the item.

Line_SalesItemLineDetail_Qty# Double False   false false

The number of items in the line item.

Line_SalesItemLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The tax code for this item.

Line_SalesItemLineDetail_ServiceDate# Date False   false false

The date when the service was performed.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true true

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

A name that identifies the customer.

CustomerMemo String False   false false

A message to the customer, visible on the transaction.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

The first line of the address.

BillAddr_Line2 String False   false false

The second line of the address.

BillAddr_Line3 String False   false false

The third line of the address.

BillAddr_Line4 String False   false false

The fourth line of the address.

BillAddr_Line5 String False   false false

The fifth line of the address.

BillAddr_City String False   false false

The city name.

BillAddr_Country String False   false false

The country name.

BillAddr_CountrySubDivisionCode String False   false false

The country region. The state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

The postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

The note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded address.

ShipAddr_Line1 String False   false false

The first line of the shipping address.

ShipAddr_Line2 String False   false false

The second line of the shipping address.

ShipAddr_Line3 String False   false false

The third line of the shipping address.

ShipAddr_Line4 String False   false false

The fourth line of the shipping address.

ShipAddr_Line5 String False   false false

The fifth line of the shipping address.

ShipAddr_City String False   false false

The city name

ShipAddr_Country String False   false false

The country name

ShipAddr_CountrySubDivisionCode String False   false false

The region within a country.

ShipAddr_PostalCode String False   false false

The postal code.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class.

ClassRef_Name String True

Class.Name

false false

The name of the class.

SalesTermRef String False

Terms.Id

true false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

A name that identifies the sales term associated with the transaction.

TotalAmt Double False   true true

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the credit memo.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSent, NeedToSend, EmailSent.

The default value is NotSet.

DepositToAccountRef String False

Accounts.Id

false false

The asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

The name of the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

RemainingCredit Double False   false false

The total credit amount still available to apply towards the payment.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores location of the transaction.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, this field is a required input.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether the discount is applied before or after taxes are calculated.

CurrencyRef String False   false false

The Id of the currency used for all amounts of the transaction. This field is available in only the UK, AU, IN, and CA editions.

CurrencyRef_Name String True   false false

The name of the currency used for all amounts of the transaction. This field is available in only the UK, AU, IN, and CA editions.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for only the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in only the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction in the home currency. This field includes the total of all the charges, allowances, and taxes. This field is valid in only the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

CreditMemos

Create, update, delete, and query QuickBooks Credit Memos.

 

Table Specific Information

 

CreditMemos may be inserted, queried, or updated via the CreditMemos or CreditMemoLineItems tables. CreditMemos may be deleted by using the CreditMemos table.

Insert

 

To add a CreditMemo, specify a Customer and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the CreditMemoLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new CreditMemo with two Line Items:

INSERT INTO CreditMemos (CustomerRef, LineAggregate) 
VALUES ('4', '
<Line><Amount>0.01</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>2</ItemRef></SalesItemLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>3</ItemRef></SalesItemLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   false false

The Id of the credit memo.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom fields for the credit memo.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

PrivateNote String False   false false

The private note about the transaction.

LineAggregate String False   false false

An XML aggregate of line items on the credit memo.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true true

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer or job.

CustomerMemo String False   false false

The message to the customer, visible on the transaction.

BillAddr_Id String False   false false

The Id of the entity for the address, mainly used for modifying the address. This value is assigned by the data service.

BillAddr_Line1 String False   false false

The first line of the address.

BillAddr_Line2 String False   false false

The second line of the address.

BillAddr_Line3 String False   false false

The third line of the address.

BillAddr_Line4 String False   false false

The fourth line of the address.

BillAddr_Line5 String False   false false

The fifth line of the address.

BillAddr_City String False   false false

The city name.

BillAddr_Country String False   false false

The country name.

BillAddr_CountrySubDivisionCode String False   false false

The country region. The state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

The postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

The note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for hte address.

ShipAddr_Line1 String False   false false

The first line of the shipping address.

ShipAddr_Line2 String False   false false

The second line of the shipping address.

ShipAddr_Line3 String False   false false

The third line of the shipping address.

ShipAddr_Line4 String False   false false

The fourth line of the shipping address.

ShipAddr_Line5 String False   false false

The fifth line of the shipping address.

ShipAddr_City String False   false false

The city name

ShipAddr_Country String False   false false

The country name

ShipAddr_CountrySubDivisionCode String False   false false

The region within a country.

ShipAddr_PostalCode String False   false false

The postal code.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

true false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

TotalAmt Double False   true true

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the credit memo.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSent, NeedToSend, EmailSent.

The default value is NotSet.

DepositToAccountRef String False

Accounts.Id

false false

The asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

Name of the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

RemainingCredit Double False   false false

The total credit amount still available to be applied towards the payment.

DepartmentRef String False

Departments.Id

false false

The Id of the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the location of the transaction.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before taxes are calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply the tax. This field is valid in only the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction, expressed in the home currency. This includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

Customers

Create, update, delete, and query QuickBooks Customers.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the customer.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Title String False   false false

The title of the person. This field supports all i18n locales. At least one of the following name elements is required: Title, GivenName, MiddleName, or FamilyName.

GivenName String False   true true

The given name or first name of the customer. At least one of the following elements is required: Title, GivenName, MiddleName, or FamilyName.

MiddleName String False   true true

The middle name of the customer. The customer can have zero or more middle names. At least one of the elements is required: Title, GivenName, MiddleName, or FamilyName.

FamilyName String False   true true

The family name or the last name of the customer. At least one of the following name elements is required: Title, GivenName, MiddleName, or FamilyName.

Suffix String False   true true

The suffix of the name.

FullyQualifiedName String True   true true

The fully qualified name of the customer. The fully qualified name consists the topmost parent element followed by each subelement and separated by colons. For example: Parent:Account1:SubAccount1:SubAccount2.

CompanyName String False   true true

The name of the company associated with the customer.

DisplayName String False   true true

The name of the customer to be displayed. This value must be unique.

PrintOnCheckName String False   true true

The name of the customer as printed on a check. If this is not provided, it is populated from DisplayName.

Active Boolean False   true true

This field indicates whether the customer is currently enabled for use by QuickBooks.

PrimaryPhone_FreeFormNumber String False   false false

The primary phone number.

AlternatePhone_FreeFormNumber String False   false false

The alternate phone number.

Mobile_FreeFormNumber String False   false false

The mobile phone number.

Fax_FreeFormNumber String False   false false

The fax number.

PrimaryEmailAddr_Address String False   false false

The first line of the address.

WebAddr_URI String False   false false

The website address.

DefaultTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the customer.

Taxable Boolean False   false false

This field indicates whether this customer is taxable.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This value is assigned by the data service.

BillAddr_Line1 String False   false false

The first line of the address.

BillAddr_Line2 String False   false false

The second line of the address.

BillAddr_Line3 String False   false false

The third line of the address.

BillAddr_Line4 String False   false false

The fourth line of the address.

BillAddr_Line5 String False   false false

The fifth line of the address.

BillAddr_City String False   false false

The city name.

BillAddr_Country String False   false false

The country name.

BillAddr_CountrySubDivisionCode String False   false false

The country region. The state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

The postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

A note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

Notes String False   false false

Notes on the customer.

Job Boolean False   true false

This field indicates whether the customer is a job or subcustomer. If false or null, this is a top-level customer.

BillWithParent Boolean False   false false

This field sets whether the customer is to be billed together with its parent entity. This field is valid only if this entity is a job or subcustomer.

ParentRef String False

Customers.Id

false false

The immediate parent of the subcustomer or job in the hierarchical 'Customer:Job' list. This field is required for the create operation if the customer is a subcustomer or a job.

ParentRef_Name String True

Customers.DisplayName

false false

The name of the immediate parent of the subcustomer or job in the hierarchical 'Customer:Job' list.

Level Integer False   false false

This field shows the level of the hierarchy the customer record is located in. A value of 0 specifies the top level of the hierarchy. The hierarchy is implicit when the parent is specified in an insert command.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the customer.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the customer.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method associated with the customer.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method associated with the customer.

Balance Double False   true true

The open balance amount or the amount unpaid by the customer. For the create operation, this represents the opening balance for the customer. When returned in response to the query request it represents the current open balance for that customer.

OpenBalanceDate Date False   false false

The date of the open balance. This field is used in the create operation.

BalanceWithJobs Double True   false false

The cumulative open balance amount for the customer and all its subcustomers.

PreferredDeliveryMethod String False   false false

The preferred delivery method.

The allowed values are Print, Email, None.

ResaleNum String False   false false

The resale number or additional info about the customer.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

 

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
rows@next String

 

 

 

 

Departments

Create, update, delete, and query QuickBooks Departments.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the department.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true true

The name of the department.

SubDepartment Boolean False   false false

This field specifies whether the department is a subdepartment.

ParentRef String False

Departments.Id

false false

The Id of the parent class entity.

ParentRef_Name String False

Departments.Name

false false

The name of the parent class entity.

FullyQualifiedName String True   false false

The fully qualified name of the department. The fully qualified name consists the topmost parent element followed by each subelement and separated by colons. For example: Parent:Account1:SubAccount1:SubAccount2.

Active Boolean False   false false

This field indicates whether or not the department is active. Inactive records are hidden from most display purposes and financial transactions.

 

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
rows@next String

 

 

 

 

DepositLineItems

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

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the invoice.

DepositId [KEY] String False

Deposits.Id

true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note that does not appear on the transaction records.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

The description of the line item.

Line_Amount# Double False   false false

The amount of the line item.

Line_DetailType# String False   false false

The detailType of the line item.

Line_DepositLineDetail_PaymentMethodRef# String False

PaymentMethods.Id

false false

The payment method id of the line item.

Line_DepositLineDetail_PaymentMethodRef_Name# String False

PaymentMethods.Name

false false

The payment method id of the line item.

Line_DepositLineDetail_AccountRef# String False

Accounts.Id

false false

Account where the funds are deposited to.

Line_DepositLineDetail_AccountRef_Name# String False

Accounts.Name

false false

Account where the funds are deposited to.

Line_DepositLineDetail_Entity# String False

Customers.Id

false false

Reference to a customer from which the deposit was recieved

Line_DepositLineDetail_Entity_Name# String False

Customers.DisplayName

false false

Reference to a customer from which the deposit was recieved

Line_DepositLineDetail_ClassRef# String False

Class.Id

false false

Reference to the Class associated with the transaction

Line_DepositLineDetail_ClassRef_Name# String False

Class.Name

false false

Reference to the Class associated with the transaction

Line_DepositLineDetail_CheckNum# String False   false false

The check number for the deposit

Line_DepositLineDetail_TxnType# String False   false false

The type of the payment transaction. Different types indicate different types of line items.

Line_DepositLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The Sales/Purchase tax code associated with the Line. This field is valid in the UK, AU, IN, and CA editions.

Line_DepositLineDetail_TaxApplicableOn# String False   false false

Indicates whether the tax applicable on the line is sales or purchase. This field is valid in the UK, AU, IN, and CA editions.

Line_LinkedTxn_TxnId# String True   false false

The id of the linked transaction

Line_LinkedTxn_TxnType# String True   false false

The type of the linked transaction. Different types indicate different types of line items.

Line_LinkedTxn_TxnLineId# String True   false false

The lineitem id of the linked transaction line item.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction. This field is valid in the UK, AU, IN, and CA editions.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list. This field is valid in the UK, AU, IN, and CA editions.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items. This field is valid in the UK, AU, IN, and CA editions.

DepositToAccountRef String False

Accounts.Id

false false

The Id of the asset account to be used for this deposit.

DepositToAccountRef_Name String False

Accounts.Name

false false

The name of the asset account to be used for this deposit.

ClassRef String False

Class.Id

false false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

CashBack_AccountRef String True   true false

The asset account (bank account) Id to be used for this cash back transaction

CashBack_AccountRef_Name String False   false false

The asset account (bank account) Name to be used for this cash back transaction

CashBack_Amount Double False   false false

The amount of the cash back transaction.

CashBack_Memo String True   true false

The memo associated with this cash back transaction.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which store the location of the transaction as defined using location tracking in QuickBooks Online.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

Deposits

Create, update, delete, and query QuickBooks Deposits.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note that does not appear on the transaction records.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction. This field is valid in the UK, AU, IN, and CA editions.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list. This field is valid in the UK, AU, IN, and CA editions.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items. This field is valid in the UK, AU, IN, and CA editions.

DepositToAccountRef String False

Accounts.Id

false false

The Id of the asset account to be used for this deposit.

DepositToAccountRef_Name String False

Accounts.Name

false false

The name of the asset account to be used for this deposit.

ClassRef String False

Class.Id

false false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

CashBack_AccountRef String True

Accounts.Id

true false

The asset account (bank account) Id to be used for this cash back transaction

CashBack_AccountRef_Name String False

Accounts.Name

false false

The asset account (bank account) Name to be used for this cash back transaction

CashBack_Amount Double False   false false

The amount of the cash back transaction.

CashBack_Memo String True   true false

The memo associated with this cash back transaction.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which store the location of the transaction as defined using location tracking in QuickBooks Online.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

Employees

Create, update, delete, and query QuickBooks Employees.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the employee.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Organization Boolean False   false false

This field indicates whether the entity represents an organization or a person.

Title String False   false false

The title of the employee. The employee can have zero or more titles. This field supports all i18n locales.

EmployeeNumber String False   true true

The employee number in the directory of the employer.

GivenName String False   true true

The given name or first name of the employee.

MiddleName String False   true true

The middle name of the employee. The person can have zero or more middle names.

FamilyName String False   true true

The family name or last name of the employee.

Suffix String False   false false

The suffix of the name of the employee.

DisplayName String False   true false

The name of the person or organization as displayed. This field is required.

PrintOnCheckName String False   false false

The name of the employee as it should appear on a check.

Active Boolean False   true false

This field sets whether this entity is currently enabled for use.

Mobile_FreeFormNumber String False   false false

The mobile phone number.

PrimaryEmailAddr_Address String False   false false

The primary email address.

PrimaryAddr_Id String False   false false

The Id of the entity for the address, mainly used for modifying the address. This value is assigned by the data service.

PrimaryAddr_Line1 String False   false false

First line of the address.

PrimaryAddr_Line2 String False   false false

Second line of the address.

PrimaryAddr_Line3 String False   false false

Third line of the address.

PrimaryAddr_Line4 String False   false false

Fourth line of the address.

PrimaryAddr_Line5 String False   false false

Fifth line of the address.

PrimaryAddr_City String False   false false

City name.

PrimaryAddr_Country String False   false false

Country name.

PrimaryAddr_CountrySubDivisionCode String False   false false

Region within a country. The state name for the USA or the province name for Canada.

PrimaryAddr_PostalCode String False   false false

Postal code. The zip code for the USA and Canada.

PrimaryAddr_Note String False   false false

Note for the customer.

PrimaryAddr_Lat String False   false false

The latitude coordinate of the geocoded address.

PrimaryAddr_Long String False   false false

The longitude coordinate of the geocoded address.

SSN String False   false false

The social security number (SSN) of the employee. If SSN is set, it is masked in the response with XXX-XX-XXXX. If XXX-XX-XXXX is sent in the create or update request, XXX-XX-XXXX is ignored and the old value is preserved.

BillableTime Boolean False   false false

This field indicates whether the employee is eligible for billable time.

BillRate Double False   false false

This field is available only if BillableTime is true. This field is not required, even if BillableTime is true.

HiredDate Date False   false false

The hire date of the employee.

ReleasedDate Date False   false false

The release date of the employee.

BirthDate Date False   false false

The birth date of the employee.

Gender String False   false false

The gender of the employee. To clear the gender value, set this field to Null in a full update request.

The allowed values are Male, Female.

 

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
rows@next String

 

 

 

 

EstimateLineItems

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

 

Table Specific Information

 

Estimates may be inserted, queried, or updated via the Estimates or EstimateLineItems tables. Estimates may be deleted by using the Estimates table.

Insert

 

To add an Estimate, specify a Customer and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new Estimate transaction. For example, the following will insert a new Estimate with two Line Items:

INSERT INTO EstimateLineItems (CustomerRef, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('4', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the estimate.

EstimateId [KEY] String False

Estimates.Id

true true

The Id of the estimate.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

PrivateNote String False   false false

A private note, which will not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the estimate.

Line_Id# String False   false false

The Id of the line item.

Line_LineNum# String False   false false

The line number.

Line_Description# String False   false false

The line description.

Line_Amount# Double False   false false

The line amount.

Line_DetailType# String False   false false

The line detail type. Different detail types indicate different types of line items.

Line_SalesItemLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item in the line item.

Line_SalesItemLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item in the line item.

Line_SalesItemLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class in the line item.

Line_SalesItemLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class in the line item.

Line_SalesItemLineDetail_UnitPrice# Double False   false false

The unit price of the item.

Line_SalesItemLineDetail_Qty# Double False   false false

The number of items for the line item.

Line_SalesItemLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The tax code for the item.

Line_SalesItemLineDetail_ServiceDate# Date False   false false

The service date for the item.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer, which appears in the invoice and the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

The first line of the billing address.

BillAddr_Line2 String False   false false

The second line of the billing address.

BillAddr_Line3 String False   false false

The third line of the billing address.

BillAddr_Line4 String False   false false

The fourth line of the billing address.

BillAddr_Line5 String False   false false

The fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the shipping address.

ShipAddr_Line2 String False   false false

Second line of the shipping address.

ShipAddr_Line3 String False   false false

Third line of the shipping address.

ShipAddr_Line4 String False   false false

Fourth line of the shipping address.

ShipAddr_Line5 String False   false false

Fifth line of the shipping address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

DueDate Date False   true false

The due date for the invoice, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   true true

This field indicates whether the transaction is a finance charge.

ExpirationDate Date False   false false

The date when an estimate becomes invalid.

AcceptedBy String False   false false

The name of the customer who accepted the estimate.

AcceptedDate Date False   false false

The date the estimate was accepted.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether any discounts are applied before taxes are calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply taxes. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction, expressed in the home currency. This includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

EstimateLinkedTransactions

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

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LinkedTxnId [KEY] String True   false false

The Id of the line item of the estimate.

EstimateId [KEY] String False

Estimates.Id

true true

The Id of the estimate.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

PrivateNote String False   false false

A private note, which will not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxn_TxnId# String False   false false

The Id of the line item.

LinkedTxn_TxnType# String False   false false

The line number of the line item.

LinkedTxn_TxnLineId# String False   false false

The description of the line item.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer, which appears in the invoice and the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

The first line of the billing address.

BillAddr_Line2 String False   false false

The second line of the billing address.

BillAddr_Line3 String False   false false

The third line of the billing address.

BillAddr_Line4 String False   false false

The fourth line of the billing address.

BillAddr_Line5 String False   false false

The fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the shipping address.

ShipAddr_Line2 String False   false false

Second line of the shipping address.

ShipAddr_Line3 String False   false false

Third line of the shipping address.

ShipAddr_Line4 String False   false false

Fourth line of the shipping address.

ShipAddr_Line5 String False   false false

Fifth line of the shipping address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

DueDate Date False   true false

The due date for the invoice, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   true true

This field indicates whether the transaction is a finance charge.

ExpirationDate Date False   false false

The date when an estimate becomes invalid.

AcceptedBy String False   false false

The name of the customer who accepted the estimate.

AcceptedDate Date False   false false

The date the estimate was accepted.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether any discounts are applied before taxes are calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply taxes. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction, expressed in the home currency. This includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

Estimates

Create, update, delete, and query QuickBooks Estimates.

 

Table Specific Information

 

Estimates may be inserted, queried, or updated via the Estimates or EstimateLineItems tables. Estimates may be deleted by using the Estimates table.

Insert

 

To add an Estimate, specify a Customer and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the EstimateLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new estimate with two Line Items:

INSERT INTO Estimates (CustomerRef, LineAggregate) 
VALUES ('4', '
<Line><Amount>0.01</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>2</ItemRef></SalesItemLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>3</ItemRef></SalesItemLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the Estimate.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

PrivateNote String False   false false

A private note, which will not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the estimate.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

A message to the customer, which appears in the invoice and in the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the billing address.

BillAddr_Line2 String False   false false

Second line of the billing address.

BillAddr_Line3 String False   false false

Third line of the billing address.

BillAddr_Line4 String False   false false

Fourth line of the billing address.

BillAddr_Line5 String False   false false

Fifth line of the billing address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Country region. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. The zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the shipping address.

ShipAddr_Line2 String False   false false

Second line of the shipping address.

ShipAddr_Line3 String False   false false

Third line of the shipping address.

ShipAddr_Line4 String False   false false

Fourth line of the shipping address.

ShipAddr_Line5 String False   false false

Fifth line of the shipping address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Country region. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales terms associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales terms associated with the transaction.

DueDate Date False   true false

The due date for the invoice, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   true true

This column sets whether the transaction is a finance charge.

ExpirationDate Date False   false false

The date when the estimate becomes invalid.

AcceptedBy String False   false false

The name of the customer who accepted the estimate.

AcceptedDate Date False   false false

The date the estimate was accepted.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply taxes. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

InvoiceLineItems

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

 

Table Specific Information

 

Invoices may be inserted, queried, or updated via the Invoices or InvoiceLineItems tables. Invoices may be deleted by using the Invoices table.

Insert

 

To add an Invoice, specify a Customer and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new Invoice transaction. For example, the following will insert a new Invoice with two Line Items:

INSERT INTO InvoiceLineItems (CustomerRef, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('4', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)

 

To add lines to an existing Invoice, You'll need to specify the InvoiceId in the WHERE clause for the INSERT command. For example:

INSERT INTO InvoiceLineItems (InvoiceId, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('1234', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)
Update

 

To update an existing Line Item, you'll need to specify the LineId:

UPDATE InvoiceLineItems SET Line_SalesItemLineDetail_Qty=20 WHERE LineId='5656'
Delete

 

To delete existing Line Items, you'll also need to set the LineId:

DELETE From InvoiceLineItems WHERE LineId='5656'
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the invoice.

InvoiceId [KEY] String False

Invoices.Id

true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

Am XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note for the transaction, which does not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the estimate.

Line_Id# String False   false false

The Id of the line item.

Line_LineNum# String False   false false

The line number of the line item.

Line_Description# String False   false false

The description of the line item.

Line_Amount# Double False   false false

The amount of the line item.

Line_DetailType# String False   false false

The detail type of the line item. Different detail types indicate different types of line items.

Line_SalesItemLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item in the line item.

Line_SalesItemLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item in the line item.

Line_SalesItemLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class in the line item.

Line_SalesItemLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class in the line item.

Line_SalesItemLineDetail_UnitPrice# Double False   false false

The unit price of the item.

Line_SalesItemLineDetail_Qty# Double False   false false

The number of items for the line item.

Line_SalesItemLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The tax code for the item.

Line_SalesItemLineDetail_ServiceDate# Date False   false false

The service date for the item.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer, which appears in the invoice and the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales terms associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales terms associated with the transaction.

DueDate Date False   true false

The date when the invoice is to be paid, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double True   true true

This field indicates whether the transaction is a finance charge.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

AllowIPNPayment Boolean False   false false

This field sets whether the invoice can be paid online using the Intuit Payment Network.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. Valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for only the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

InvoiceLinkedTransactions

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

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LinkedTxnId [KEY] String True   false false

The Id of the line item of the invoice.

InvoiceId [KEY] String False

Invoices.Id

true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

Am XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note for the transaction, which does not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxn_TxnId# String False   false false

The Id of the line item.

LinkedTxn_TxnType# String False   false false

The line number of the line item.

LinkedTxn_TxnLineId# String False   false false

The description of the line item.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code for the transaction.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DsiplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer, which appears in the invoice and the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales terms associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales terms associated with the transaction.

DueDate Date False   true false

The date when the invoice is to be paid, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   false false

This field indicates whether the transaction is a finance charge.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

AllowIPNPayment Boolean False   false false

This field sets whether the invoice can be paid online using the Intuit Payment Network.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. Valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for only the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

The total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

Invoices

Create, update, delete, and query QuickBooks Invoices.

 

Table Specific Information

 

Invoices may be inserted, queried, or updated via the Invoices or InvoiceLineItems tables. Invoices may be deleted by using the Invoices table.

Insert

 

To add an Invoice, specify a Customer and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the InvoiceLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new Invoice with two Line Items:

INSERT INTO Invoices (CustomerRef, LineAggregate) 
VALUES ('4', '
<Line><Amount>0.01</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>2</ItemRef></SalesItemLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>3</ItemRef></SalesItemLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note that does not appear on the transaction records.

TxnStatus String False   false false

The status of the transaction.

The allowed values are Accepted, Closed, Pending, Rejected.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the estimate.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer that appears in the invoice and in the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

DueDate Date False   true false

The date when the invoice is to be paid, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double True   true true

This field indicates whether the transaction is a finance charge.

PaymentRefNum String False   false false

The reference number for the payment.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method.

DepositToAccountRef String False

Accounts.Id

false false

The asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

Name of the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DeliveryInfo_DeliveryType String False   false false

Type of the delivery. Used to confirm that email has been sent via the send operation.

DeliveryInfo_DeliveryTime Datetime False   false false

Delivery date and time.

Deposit Double False   false false

The deposit made towards this invoice.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which store the location of the transaction as defined using location tracking in QuickBooks Online.

AllowIPNPayment Boolean False   false false

This field indicates whether the invoice can be paid online using the Intuit Payment Network.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit.This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

Total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

Items

Create, update, delete, and query QuickBooks Items.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the item.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true true

The name of the item.

FullyQualifiedName String False   false false

The fully qualified name of the item. The fully qualified name consists the topmost parent element followed by each subelement and separated by colons. For example: Parent:Account1:SubAccount1:SubAccount2.

Description String False   false false

The description for the item that describes the details of the service or product.

Active Boolean False   true false

This column sets whether the item active. Inactive items may be hidden from display and are not used in financial transactions.

ParentRef String False

Items.Id

false true

The Id of the parent entity of the item. If SubItem is true, then ParenRef is required.

ParentRef_Name String True

Items.Name

false false

A name that identifies the parent entity of the item. If SubItem is true, then ParenRef is required.

SubItem Boolean False   false false

This field indicates whether the item is a subitem. A value of false or null indicates a top-level item.

UnitPrice Double False   false true

Monetary value of the service or product, expressed in the home currency.

Type String False   true true

Classification that specifies the use of this item.

The allowed values are Inventory, NonInventory, Service, Category, Group.

Taxable Boolean False   false false

This field indicates whether the item is subject to tax.

AssetAccountRef String False

Accounts.Id

false false

The Id of the inventory asset account. If the same inventory asset account is assigned for individual items, the current balance of that account will show the current total value of the inventory. However, individual items may have different account.

AssetAccountRef_Name String True

Accounts.Name

false false

The name of the inventory asset account. If the same inventory asset account is assigned for individual items, the current balance of that account will show the current total value of the inventory. However, individual items may have different accounts.

IncomeAccountRef String False

Accounts.Id

false false

The Id of the account that records the proceeds from the sale of this item.

IncomeAccountRef_Name String True

Accounts.Name

false false

The name of the account that records the proceeds from the sale of this item.

PurchaseDesc String False   false false

The purchase description for the item.

PurchaseCost Double False   false false

The amount paid when buying or ordering the item, as expressed in the home currency.

ExpenseAccountRef String False

Accounts.Id

false false

The Id of the expense account associated with the purchase item.

ExpenseAccountRef_Name String True

Accounts.Name

false false

The name of the expense account reference associated with the purchase item.

QtyOnHand Double False   false true

The current quantity of the inventory items available for sale.

ReorderPoint Double False   false false

The minimum quantity of a product to have on hand.

InvStartDate Date False   false false

The date to start tracking inventory. Required if TrackQtyOnHand is set.

SalesTaxCodeRef String False

TaxCodes.Id

false false

The sales tax code for the item.

PurchaseTaxCodeRef String False

TaxCodes.Id

false false

The purchase tax code for the item.

TrackQtyOnHand Boolean False   false false

This field indicates whether there is quantity on hand to be tracked.

Level Integer True   false false

The level of the item. The value 0 indicates the top-level parent. Otherwise, this field specifies the depth from the top parent.

Sku String False   true false

The stock keeping unit (SKU) for this Item. This is a company-defined identifier for an item or product used in tracking inventory.

SalesTaxIncluded Boolean False   false false

This field indicates whether the sales tax is included in the item amount, and therefore not calculated for the transaction.

PurchaseTaxIncluded Boolean False   false false

This field indicates whether the purchase tax is included in the item amount, and therefore not calculated for the transaction.

ItemGroupDetail_ItemGroupLineAggregate String True   false false

One or more Item objects that comprise a bundle.

 

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
rows@next String

 

 

 

 

JournalEntries

Create, update, delete, and query QuickBooks Journal Entries.

 

Table Specific Information

 

JournalEntries may be inserted, queried, or updated via the JournalEntries or JournalEntryLineItems tables. JournalEntries may be deleted by using the JournalEntries table.

Insert

 

To add a JournalEntry, there must be at least one credit line and at least one debit line where the total of the debit and credit Lines are equal to each other. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the JournalEntryLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new JournalEntry with two Line Items:

INSERT INTO JournalEntries (LineAggregate) 
VALUES ('
<Line><Amount>0.02</Amount><DetailType>JournalEntryLineDetail</DetailType><JournalEntryLineDetail><PostingType>Debit</PostingType><Entity><Type>Customer</Type><EntityRef>24</EntityRef></Entity><AccountRef>33</AccountRef></JournalEntryLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>JournalEntryLineDetail</DetailType><JournalEntryLineDetail><PostingType>Credit</PostingType><AccountRef>14</AccountRef></JournalEntryLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the journal entry.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

Adjustment Boolean False   false false

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

LineAggregate String False   false false

An XML aggregate of the line items of a transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

The total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

TotalAmt Double False   true true

The total amount of the transaction. Calculated by QuickBooks business logic; any value you supply is over-written by QuickBooks.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is only valid if the company file uses the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available in only the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

JournalEntryLineItems

Create, update, delete, and query QuickBooks Journal Entry Line Items.

 

Table Specific Information

 

JournalEntries may be inserted, queried, or updated via the JournalEntries or JournalEntryLineItems tables. JournalEntries may be deleted by using the JournalEntries table.

Insert

 

To add a JournalEntry, there must be at least one credit line and at least one debit line where the total of the debit and credit lines are equal to each other. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new JournalEntry transaction. For example, the following will insert a new JournalEntry with two Line Items:

INSERT INTO JournalEntryLineItems (Line_DetailType#1, Line_JournalEntryLineDetail_PostingType#1, Line_JournalEntryLineDetail_Entity_Type#1, Line_JournalEntryLineDetail_Entity_EntityRef#1, Line_JournalEntryLineDetail_AccountRef#1, Line_Amount#1, Line_DetailType#2, Line_JournalEntryLineDetail_PostingType#2, Line_JournalEntryLineDetail_AccountRef#2, Line_Amount#2) VALUES ('JournalEntryLineDetail', 'Debit', 'Customer', '24', '33', 0.02, 'JournalEntryLineDetail', 'Credit', '14', 0.02)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the journal entry.

JournalEntryId [KEY] String False

JournalEntries.Id

true true

The Id of the journal entry.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

Reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

Adjustment Boolean False   false false

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

A description of the line item that appears in the printed record.

Line_Amount# Double False   false false

The amount of the line item.

Line_DetailType# String False   false false

The detail type of the line item. Different detail types indicate different types of line items.

Line_JournalEntryLineDetail_PostingType# String False   false false

This field indicates the posting type of the line item of the journal entry detail.

The allowed values are Credit, Debit.

Line_JournalEntryLineDetail_Entity_Type# String False   false false

The type of the entity to be referenced.

Line_JournalEntryLineDetail_Entity_EntityRef# String False   false false

The Id of the entity to be referenced.

Line_JournalEntryLineDetail_Entity_EntityRef_Name# String True   false false

The name of the entity to be referenced.

Line_JournalEntryLineDetail_AccountRef# String False

Accounts.Id

false false

The Id of the account associated with the journal entry line item.

Line_JournalEntryLineDetail_AccountRef_Name# String True

Accounts.Name

false false

The name of the account associated with the journal entry line item.

Line_JournalEntryLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class associated with the journal entry line item.

Line_JournalEntryLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class associated with the journal entry line.

Line_JournalEntryLineDetail_DepartmentRef# String False

Departments.Id

false false

The Id of the department associated with the journal entry line.

Line_JournalEntryLineDetail_DepartmentRef_Name# String True

Departments.Name

false false

The name of the department associated with the journal entry line.

Line_JournalEntryLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales or purchase tax code associated with the journal entry line. This field is used for non-U.S., non-Canadian companies. This field is valid for the UK and IN editions.

Line_JournalEntryLineDetail_TaxApplicableOn# String False   false false

This field indicates whether the tax applicable to the line item is sales or purchase tax. This field is valid for the UK and IN editions.

Line_JournalEntryLineDetail_TaxAmount# Double False   false false

The tax applicable for this line item. This field is valid for the UK, AU, and IN editions.

Line_JournalEntryLineDetail_BillableStatus# String False   false false

The billable status of the journal entry line. The line is to be billed to a customer if the account is an expense account and the Entity Reference specifies a Customer or a Job.

The allowed values are Billable, NotBillable, HasBeenBilled.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

TotalAmt Double False   true true

The total amount of the transaction. Calculated by QuickBooks business logic; any value you supply is over-written by QuickBooks.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is only valid if the company file uses the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available in only the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

PaymentLineItems

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

 

Table Specific Information

 

Payments may be inserted, queried, or updated via the Payments or PaymentLineItems tables. Payments may be deleted by using the Payments table.

Insert

 

To add a Payment, specify a Customer, TotalAmt, and optionally Line Items. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new payment transaction. For example, the following will insert a new Payment with two Line Items:

INSERT INTO PaymentLineItems (CustomerRef, Line_Amount#1, Line_LinkedTxn_TxnType#1, Line_LinkedTxn_TxnId#1, Line_Amount#2, Line_LinkedTxn_TxnType#2, Line_LinkedTxn_TxnId#2, TotalAmt) VALUES ('4', 0.01, 'Invoice', '116', 0.02, 'Invoice', '117', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item on the payment.

PaymentId [KEY] String False

Payments.Id

true true

The Id of the payment.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

TxnDate Date False   true false

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

The private note about the transaction. This note will not appear on the transaction records by default.

TxnStatus String False   false false

The status of the transaction. For Payment entities, this status is always set to PAID.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the line item.

Line_Amount# Double False   false false

Amount of the line item.

Line_LinkedTxn_TxnId# String False   false false

The Id of a linked transaction in the line item.

Line_LinkedTxn_TxnType# String False   false false

The type of the transaction linked to the line item.

Line_LinkedTxn_TxnLineId# String False   false false

The description of the line item.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

RemitToRef String True   false false

This field contains the Id of the entity for the party or location that the payment is to be remitted to or sent to.

RemitToRef_Name String True   false false

A name that identifies the party or location that the payment is to be remitted to or sent to.

ARAccountRef String False

Accounts.Id

false false

Reference to the Accounts Receivable (Accounts Receivable account) account associated with the transaction. It is strongly recommended to always supply this field, even though it is ignored by QuickBooks Online, because QuickBooks Online has only one accounts receivable account.

ARAccountRef_Name String True

Accounts.Name

false false

Reference to the ARAccount (Accounts Receivable account) associated with the transaction. It is strongly recommended to always supply an ARAccountRef even though it is ignored by QBO, because QBO has only one accounts receivable account.

DepositToAccountRef String False

Accounts.Id

false false

An optional asset account specification to designate the account the payment money needs to be deposited to.

DepositToAccountRef_Name String True

Accounts.Name

false false

The optional asset account specification to designate the account the payment money needs to be deposited to.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method.

The allowed values are Cash, Check, CreditCard, Other.

PaymentRefNum String False   true true

The reference number for the payment received.

CreditCardPayment_CreditChargeInfo String False   false false

Holds credit card information to request a credit card payment from a merchant account service, but not any response or authorization information from the merchant account service provider.

CreditCardPayment_Number String False   false false

The credit card account number, as printed on the card. This field must not have white space or formatting characters.

CreditCardPayment_Type String False   false false

The type of credit card.

CreditCardPayment_NameOnAcct String False   false false

The name of the account holder, as printed on the card.

CreditCardPayment_CcExpiryMonth Integer False   false false

The expiration month on the card.

CreditCardPayment_CcExpiryYear Integer False   false false

4-digit expiration year on the card.

CreditCardPayment_BillAddrStreet String False   false false

The billing address.

CreditCardPayment_PostalCode String False   false false

The postal code.

CreditCardPayment_CommercialCardCode String False   false false

The code associated with commercial cards such as purchase, corporate, or business cards. Lower transaction fee rates apply for these cards when this field is defined.

CreditCardPayment_CCTxnMode String False   false false

Credit card transaction mode used in credit card payment transactions.

The allowed values are CardNotPresent, CardPresent.

CreditCardPayment_CCTxnType String False   false false

The type of credit card transaction.

The allowed values are Authorization, Capture, Charge, Refund, VoiceAuthorization.

CreditCardPayment_PrevCCTransId String False   false false

Id of the previous payment transaction.

TotalAmt Double False   true true

This field indicates the total amount of the entity associated. This includes the total of all the payments from the payment details.

UnappliedAmt Double False   false false

This field indicates the amount that has not been applied to pay amounts owed for sales transactions.

ProcessPayment Boolean False   false false

This field indicates that the payment should be processed by merchant account service. This field is valid for QuickBooks Online companies with credit card processing.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

Currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

PaymentMethods

Create, update, delete, and query QuickBooks Payment Methods.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the payment method.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true true

The name of the payment method item.

Active Boolean False   false false

This field sets whether or not the payment method is active. Inactive payment methods may be hidden from display and may not be used on financial transactions.

Type String False   false false

Defines the type, or the way the payment was made.

 

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
rows@next String

 

 

 

 

Payments

Create, update, delete, and query QuickBooks Payments.

 

Table Specific Information

 

Payments may be inserted, queried, or updated via the Payments or PaymentLineItems tables. Payments may be deleted by using the Payments table.

Insert

 

To add a Payment, specify a Customer and at least one item Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the PaymentLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new Payment with two Line Items:

INSERT INTO Payments (CustomerRef, TotalAmt, LineAggregate) 
VALUES ('4', '0.03', '
<Line><Amount>0.01</Amount><LinkedTxn><TxnId>116</TxnId><TxnType>Invoice</TxnType></LinkedTxn></Line>
<Line><Amount>0.02</Amount><LinkedTxn><TxnId>117</TxnId><TxnType>Invoice</TxnType></LinkedTxn></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the payment.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

TxnDate Date False   true false

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

TxnStatus String False   false false

The status of the transaction. For payment entities, this status is always set to PAID.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions liked to this one.

LineAggregate String False   false false

A line item of a transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

RemitToRef String True   false false

The Id of the entity for the party or location that the payment is to be remitted to or sent to.

RemitToRef_Name String True   false false

The name of the party or location that the payment is to be remitted to or sent to.

ARAccountRef String False

Accounts.Id

false false

The Id of the Accounts Receivable account (ARAccount) associated with the transaction. It is strongly recommended to always supply this field, even though it is ignored by QuickBooks Online, because QuickBooks Online has only one accounts recievable account.

ARAccountRef_Name String True

Accounts.Name

false false

The name of the Accounts Receivable account (ARAccount) associated with the transaction. It is strongly recommended to always supply this field, even though it is ignored by QuickBooks Online, because QuickBooks Online has only one accounts recievable account.

DepositToAccountRef String False

Accounts.Id

false false

An optional asset account specification. The Id of the asset account that designates the account the payment money needs to be deposited to.

DepositToAccountRef_Name String True

Accounts.Name

false false

An optional asset account specification. The name of the account the payment money needs to be deposited to.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method.

The allowed values are Cash, Check, CreditCard, Other.

PaymentRefNum String False   true true

The reference number for the payment received.

CreditCardPayment_CreditChargeInfo String False   false false

This field holds credit card information to request a credit card payment from a merchant account service, but not any response or authorization information from the merchant account service provider.

CreditCardPayment_Number String False   false false

The credit card account number, as printed on the card. This field must not have white space or formatting characters.

CreditCardPayment_Type String False   false false

The type of credit card.

CreditCardPayment_NameOnAcct String False   false false

The account holder name as printed on the card.

CreditCardPayment_CcExpiryMonth Integer False   false false

The expiration month on the card.

CreditCardPayment_CcExpiryYear Integer False   false false

The 4-digit expiration year on the card.

CreditCardPayment_BillAddrStreet String False   false false

The street of the billing address.

CreditCardPayment_PostalCode String False   false false

The postal code.

CreditCardPayment_CommercialCardCode String False   false false

The code associated with commercial cards such as purchase, corporate, or business cards. Lower transaction fee rates apply for these cards when this field is defined.

CreditCardPayment_CCTxnMode String False   false false

The credit card transaction mode used in credit card payment transactions.

The allowed values are CardNotPresent, CardPresent.

CreditCardPayment_CCTxnType String False   false false

The type of credit card transaction.

The allowed values are Authorization, Capture, Charge, Refund, VoiceAuthorization.

CreditCardPayment_PrevCCTransId String False   false false

The Id of the previous payment transaction.

TotalAmt Double False   true true

The total amount of the entity associated. This includes the total of all the payments from the payment details.

UnappliedAmt Double False   false false

The amount that has not been applied to pay amounts owed for sales transactions.

ProcessPayment Boolean False   false false

This field indicates that the payment should be processed by merchant account service. This field is valid for companies with credit card processing enabled in QuickBooks Online.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

PurchaseLineItems

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

 

Table Specific Information

 

Purchases may be inserted, queried, or updated via the Purchases or PurchaseLineItems tables. Purchases may be deleted by using the Purchases table.

Insert

 

To add a Purchase, specify the AccountRef, PaymentType, TotalAmt, and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new Purchase transaction. For example, the following will insert a new Purchase with two Line Items:

INSERT INTO PurchaseLineItems (AccountRef, PaymentType, Line_Amount#1, Line_DetailType#1, Line_ItemBasedExpenseLineDetail_ItemRef#1, Line_Amount#2, Line_DetailType#2, Line_ItemBasedExpenseLineDetail_ItemRef#2, TotalAmt) VALUES ('41',  'Cash', 0.01, 'ItemBasedExpenseLineDetail', '2',  0.02, 'ItemBasedExpenseLineDetail', '3', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item of the purchase.

PurchaseId [KEY] String False

Purchases.Id

true true

The Id of the purchase.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default. This field maps to the Memo field on the Check and CreditCard form.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

A description of the line item that appears in the printed record.

Line_Amount# Double False   false false

The total amount of the charges or discounts for the given line. This field includes the charges and allowances, but it excludes the tax amount.

Line_DetailType# String False   false false

The type of line in the transaction.

Line_ItemBasedExpenseLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item in the line item. When a line item lacks an ItemRef it is treated as documentation and the Line.Amount attribute is ignored.

Line_ItemBasedExpenseLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item. When a line item lacks an ItemRef it is treated as documentation and the Line_Amount field is ignored.

Line_ItemBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class for the line item.

Line_ItemBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the line item.

Line_ItemBasedExpenseLineDetail_UnitPrice# Double False   false false

The unit price of the subject item as referenced by ItemRef.

Line_ItemBasedExpenseLineDetail_Qty# Double False   false false

Number of items for the line.

Line_ItemBasedExpenseLineDetail_RatePercent# Double False   false false

The amount is expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

Line_ItemBasedExpenseLineDetail_MarkupInfo_Value# Double False   false false

Markup value.

Line_ItemBasedExpenseLineDetail_MarkupInfo_Percent# Double False   false false

Markup amount expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef# String False   false false

The Id of the entity for the price level for the markup.

Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef_Name# String True   false false

A name that identifies the price level for the markup.

Line_ItemBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales tax code for this item.

Line_ItemBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The Id of the customer associated with the expense.

Line_ItemBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The name of the customer associated with the expense.

Line_ItemBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBilled.

Line_AccountBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The customer id for the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The customer name for the expense.

Line_AccountBasedExpenseLineDetail_AccountRef# String False

Accounts.Id

false false

The Id of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_AccountRef_Name# String True

Accounts.Name

false false

The name of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBilled.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Value# Double False   false false

Markup value.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Percent# Double False   false false

Markup amount expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

Line_AccountBasedExpenseLineDetail_MarkupInfo_PriceLevelRef# String False   false false

The Id of price level for the markup.

Line_AccountBasedExpenseLineDetail_MarkupInfo_PriceLevelRef_Name# String True   false false

The name of the price level for the markup.

Line_AccountBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The type of line in the transaction.

AccountRef String False

Accounts.Id

false false

The Id of the account associated with the transaction.

AccountRef_Name String True

Accounts.Name

false false

A name that identifies the account associated with the transaction. Checks should reference a bank account and CreditCard should reference a credit card account.

PaymentType String False   true true

The expense type.

The allowed values are Cash, Check, CreditCard.

EntityRef String False   false false

Specifies the party with whom an expense is associated.

EntityRef_Name String True   false false

Specifies the party with whom an expense is associated.

Credit Boolean False   false false

This field is valid only for the CreditCard payment type.

TotalAmt Double True   true true

The total amount due, determined the sum of the line items. This includes all charges, allowances, taxes, discounts, etc.

PrintStatus String False   false false

The print status of the payment. This field is applicable only for checks. It is ignored for credit card charges or refunds.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction.

Status String True   false false

The status of the purchase.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

PurchaseOrderLineItems

Create, update, delete, and query QuickBooks Purchase Order Line Items.

 

Table Specific Information

 

PurchaseOrders may be inserted, queried, or updated via the PurchaseOrders or PurchaseOrderLineItems tables. PurchaseOrders may be deleted by using the PurchaseOrders table.

Insert

 

To add a PurchaseOrder, specify the Vendor, TotalAmt, and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new PurchaseOrder transaction. For example, the following will insert a new PurchaseOrder with two Line Items:

INSERT INTO PurchaseOrderLineItems (VendorRef, Line_Amount#1, Line_DetailType#1, Line_ItemBasedExpenseLineDetail_ItemRef#1, Line_Amount#2, Line_DetailType#2, Line_ItemBasedExpenseLineDetail_ItemRef#2, TotalAmt) VALUES ('21', 0.01, 'ItemBasedExpenseLineDetail', '2', 0.02, 'ItemBasedExpenseLineDetail', '3', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false true

The Id of the line item of the purchase order.

PurchaseOrderId [KEY] String False

PurchaseOrders.Id

true false

The Id of the purchase order.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

A description of the line item that appears in the printed record.

Line_Amount# Double False   false false

The amount of the line item. It represents the discount amount, charge amount, tax amount, or subtotal amount based on the value of the DetailType field.

Line_DetailType# String False   false false

The type of line in the transaction.

Line_ItemBasedExpenseLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item of the line item. When a line lacks an ItemRef it is treated as documentation and the Line_Amount field is ignored.

Line_ItemBasedExpenseLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item of the line item. When a line lacks an ItemRef it is treated as documentation and the Line_Amount field is ignored.

Line_ItemBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class for the line item.

Line_ItemBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the line item.

Line_ItemBasedExpenseLineDetail_UnitPrice# Double False   false false

The price of the referenced item.

Line_ItemBasedExpenseLineDetail_Qty# Double False   false false

The number of items for the line.

Line_ItemBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales tax code for this item.

Line_ItemBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The Id of the customer associated with the expense.

Line_ItemBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The name of the customer associated with the expense.

Line_ItemBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBilled.

Line_AccountBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class entity of the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The customer Id for the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The customer name for the expense.

Line_AccountBasedExpenseLineDetail_AccountRef# String False

Accounts.Id

false false

The Id of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_AccountRef_Name# String True

Accounts.Name

false false

The name of the expense account of the expense.

Line_AccountBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The sales tax code for the expense.

Line_AccountBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of the expense.

The allowed values are Billable, NotBillable, HasBeenBilled.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Value# Double False   false false

Markup value.

Line_AccountBasedExpenseLineDetail_MarkupInfo_Percent# Double False   false false

Markup amount expressed as a percent of charges already entered in the current transaction. To enter a rate of 10% use 10.0, not 0.01.

VendorRef String False

Vendors.Id

false false

The Id of the vendor for this transaction.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor for this transaction.

APAccountRef String False

Accounts.Id

true true

The accounts-payable account the bill is credited to. Many/most small businesses have a single accounts-payable account, so the account can be implied. When specified, the account must be a Liability account, and further, the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The accounts-payable account to which the bill is credited. Many/most small businesses have a single accounts-payable account, so the account can be implied. When specified, the account must be a Liability account, and further, the subtype must be of type Payables.

TotalAmt Double True   true true

The total amount due, determined by the sum of the line item amounts. This includes all charges, allowances, taxes, and discounts.

VendorAddr_Id String True   false false

The Id of the entity for the vendor address, mainly used for modifying the address. This field is assigned by the data service.

VendorAddr_Line1 String True   false false

First line of the vendor address.

VendorAddr_Line2 String True   false false

Second line of the vendor address.

VendorAddr_Line3 String True   false false

Third line of the vendor address.

VendorAddr_Line4 String True   false false

Fourth line of the vendor address.

VendorAddr_Line5 String True   false false

Fifth line of the vendor address.

VendorAddr_City String True   false false

City name.

VendorAddr_Country String True   false false

Country name.

VendorAddr_CountrySubDivisionCode String True   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

VendorAddr_PostalCode String True   false false

Postal code. For example the zip code for the USA and Canada.

VendorAddr_Note String True   false false

Note for the customer.

VendorAddr_Lat String True   false false

The latitude coordinate of the geocoded vendor address.

VendorAddr_Long String True   false false

The longitude coordinate of the geocoded vendor address.

ShipAddr_Id String False   false false

The Id of the Intuit entity for the address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the shipping address.

ShipAddr_Line2 String False   false false

Second line of the shipping address.

ShipAddr_Line3 String False   false false

Third line of the shipping address.

ShipAddr_Line4 String False   false false

Fourth line of the shipping address.

ShipAddr_Line5 String False   false false

Fifth line of the shipping address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, state name for the USA, province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ShipMethodRef String False   false false

The Id of the entity for the shipping method. This entity is a reference type of all Ids that are taken as input or output.

ShipMethodRef_Name String True   false false

The name of the entity for the shipping method. This entity is a reference type of all Ids that are taken as input or output.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

TaxCodeRef String False

TaxCodes.Id

false false

The tax code for the purchase. This field is valid in only the UK, AU, IN, and CA editions.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

PurchaseOrders

Create, update, delete, and query QuickBooks Purchase Orders.

 

Table Specific Information

 

PurchaseOrders may be inserted, queried, or updated via the PurchaseOrders or PurchaseOrderLineItems tables. PurchaseOrders may be deleted by using the PurchaseOrders table.

Insert

 

To add a PurchaseOrder, specify the Vendor, TotalAmt, and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the PurchaseOrderLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new PurchaseOrder with two Line Items:

INSERT INTO PurchaseOrders (VendorRef, TotalAmt, LineAggregate) 
VALUES ('21', '0.03', '
<Line><Amount>0.01</Amount><DetailType>ItemBasedExpenseLineDetail</DetailType><ItemBasedExpenseLineDetail><ItemRef>2</ItemRef></ItemBasedExpenseLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>ItemBasedExpenseLineDetail</DetailType><ItemBasedExpenseLineDetail><ItemRef>3</ItemRef></ItemBasedExpenseLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the purchase order.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

LineAggregate String False   false false

An XML aggregate of line items in the transaction.

VendorRef String False

Vendors.Id

false false

The Id of the vendor.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor.

APAccountRef String False

Accounts.Id

true true

The Id of the accounts-payable account to which the bill is credited. This account is implied when a company has a single accounts-payable account. When specified, the account must be a Liability account and the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The name of the accounts-payable account to which the bill is credited. This account is implied when a company has a single accounts-payable account. When specified, the account must be a Liability account and the subtype must be of type Payables.

TotalAmt Double True   true true

The total amount due, determined by the sum of the line item amounts. This includes all charges, allowances, taxes, and discounts.

VendorAddr_Id String True   false false

Id of the Intuit entity for the vendor address, mainly used for modifying the address. This field is assigned by the data service.

VendorAddr_Line1 String True   false false

First line of the address.

VendorAddr_Line2 String True   false false

Second line of the address.

VendorAddr_Line3 String True   false false

Third line of the address.

VendorAddr_Line4 String True   false false

Fourth line of the address.

VendorAddr_Line5 String True   false false

Fifth line of the address.

VendorAddr_City String True   false false

City name.

VendorAddr_Country String True   false false

Country name.

VendorAddr_CountrySubDivisionCode String True   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

VendorAddr_PostalCode String True   false false

Postal code. For example, the zip code for the USA and Canada.

VendorAddr_Note String True   false false

Note for the customer.

VendorAddr_Lat String True   false false

Latitude coordinate of the geocoded vendor address.

VendorAddr_Long String True   false false

Longitude coordinate of the geocoded vendor address.

ShipAddr_Id String False   false false

Id of the Intuit entity for the shipping address, mainly used for modifying the address. Read-only; assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, state name for USA, province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, zip code for USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

Latitude coordinate of the Geocode (Geospacial Entity Object Code).

ShipAddr_Long String False   false false

Longitude coordinate of the Geocode (Geospacial Entity Object Code).

ShipMethodRef String False   false false

Reference type of all Ids that are taken as input or output.

ShipMethodRef_Name String True   false false

Reference type of all Ids that are taken as input or output.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

TaxCodeRef String False

TaxCodes.Id

false false

The tax code for the purchase. This field is valid in the UK, AU, IN, and CA editions.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

Currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method used to apply the tax. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

Purchases

Create, update, delete, and query QuickBooks Purchases.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the purchase.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

Reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default. This field maps to the Memo field on the Check and CreditCard form.

LineAggregate String False   false false

A line item of a transaction.

AccountRef String False

Accounts.Id

false false

The Id of the account associated with the purchase. Check payments should reference a bank account and credit card purchases should refer to a credit card account.

AccountRef_Name String True

Accounts.Name

false false

A name that identifies the account associated with the purchase. Checks should reference a bank account and credit card purchases should refer to a credit card account.

PaymentType String False   true true

The payment type.

The allowed values are Cash, Check, CreditCard.

EntityRef String False   false false

The Id of the entity for the party the expense is associated with.

EntityRef_Name String True   false false

A name that identifies the party the expense is associated with.

Credit Boolean False   false false

This field is valid only for the credit card payment type.

TotalAmt Double True   true true

The total amount due, determined by the sum of the line item amounts. This includes all charges, allowances, taxes, discounts, etc.

PrintStatus String False   false false

The print status of the purchase. This field is applicable only for checks and ignored for credit card charges or refunds.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction.

Status String True   false false

The status of the purchase.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

RefundReceiptLineItems

Create, update, delete, and query QuickBooks RefundReceiptLineItems.

 

Table Specific Information

 

RefundReceipts may be inserted, queried, or updated via the RefundReceipts or RefundReceiptLineItems tables. RefundReceipts may be deleted by using the RefundReceipts table.

Insert

 

To add a RefundReceipt, specify a Customer, DepositToAccount and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new RefundReceipt transaction. For example, the following will insert a new RefundReceipt with two Line Items:

INSERT INTO RefundReceiptLineItems (CustomerRef, DepositToAccountRef, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('4', '45', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)

 

To add lines to an existing RefundReceipt, You'll need to specify the RefundReceiptId in the WHERE clause for the INSERT command. For example:

INSERT INTO RefundReceiptLineItems (RefundReceiptId, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#1, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, Line_Amount#2) VALUES ('1234', 'SalesItemLineDetail', '2', 0.01, 'SalesItemLineDetail', '3', 0.02)
Update

 

To update an existing Line Item, you'll need to specify the LineId:

UPDATE RefundReceiptLineItems SET Line_SalesItemLineDetail_Qty=20 WHERE LineId='5656'
Delete

 

To delete existing Line Items, you'll also need to set the LineId:

DELETE From RefundReceiptLineItems WHERE LineId='5656'
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   true false

The Id of the line item of the invoice of the read receipt.

RefundReceiptId [KEY] String False

RefundReceipts.Id

true true

The Id of the read receipt.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note that does not appear on the transaction records.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the estimate.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

Line_Id# String False   false false

The Id of the line item.

Line_LineNum# String False   false false

The line number of the line item.

Line_Description# String False   false false

The description of the line item.

Line_Amount# Double False   false false

The amount of the line item.

Line_DetailType# String False   false false

The detail type of the line item. Different detail types indicate different types of line items.

Line_SalesItemLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item in the line item.

Line_SalesItemLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item in the line item.

Line_SalesItemLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class in the line item.

Line_SalesItemLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class in the line item.

Line_SalesItemLineDetail_UnitPrice# Double False   false false

The unit price of the item.

Line_SalesItemLineDetail_Qty# Double False   false false

The number of items for the line item.

Line_SalesItemLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The tax code for the item.

Line_SalesItemLineDetail_ServiceDate# Date False   false false

The service date for the item.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer that appears in the invoice and in the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

false false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

CheckPayment_CheckNum String True   false false

The check number printed on the check.

CheckPayment_Status String False   false false

Status of the check. Values provided by service/business logic.

CheckPayment_NameOnAccount String False   false false

Name of persons or entities holding the account, as printed on the check.

CheckPayment_AcctNum String False   false false

Checking account number, as printed on the check.

CheckPayment_BankName String True   false false

The name of the bank on which the check was drawn.

CreditCardPayment_CreditChargeInfo_Type String False   false false

The type of credit card.

CreditCardPayment_CreditChargeInfo_NameOnAcct String False   false false

The name of the account holder, as printed on the card.

CreditCardPayment_CreditChargeInfo_CcExpiryMonth Integer False   false false

The expiration month of the card.

CreditCardPayment_CreditChargeInfo_CcExpiryYear Integer False   false false

The four-digit expiration year of the card.

CreditCardPayment_CreditChargeInfo_BillAddrStreet String False   false false

The street of the billing address of the credit card holder.

CreditCardPayment_CreditChargeInfo_PostalCode String False   false false

The postal code.

CreditCardPayment_CreditChargeInfo_Amount Double False   false false

The amount processed using the credit card

CreditCardPayment_CreditChargeInfo_ProcessPayment String False   false false

Unique identifier of the payment transaction.

CreditCardPayment_CreditChargeResponse_AuthCode String False   false false

Code returned from the credit card processor to indicate that the charge will be paid by the card issuer.

CreditCardPayment_CreditChargeResponse_TxnAuthorizationTime Datetime False   false false

Timestamp indicating the time in which the card processor authorized the transaction.

CreditCardPayment_CreditChargeResponse_Status String False   false false

Indicates the status of the payment transaction. Possible values include Completed, Unknown.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

DueDate Date False   true false

The date when the invoice is to be paid, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which store the location of the transaction as defined using location tracking in QuickBooks Online.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

BillEmail_Address String False   false false

This field identifies the email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   false false

This field indicates whether the transaction is a finance charge.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method associated with the transaction.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method associated with the transaction.

PaymentRefNum String False   false false

The reference number for the payment received.

PaymentType String False   false false

The type of payment used for the transaction.

The allowed values are Cash, Check, CreditCard, Other.

DepositToAccountRef String False

Accounts.Id

false false

The asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

The name of the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

CurrencyRef String False   false false

The Id of the currency used in the transaction. This field is valid in the UK, AU, IN, and CA editions.

CurrencyRef_Name String False   false false

The name of the currency in which all amounts on the associated transaction are expressed. This field is valid in the UK, AU, IN, and CA editions.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit.This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

Total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

RefundReceipts

Create, update, delete, and query QuickBooks RefundReceipts.

 

Table Specific Information

 

RefundReceipts may be inserted, queried, or updated via the RefundReceipts or RefundReceiptLineItems tables. RefundReceipts may be deleted by using the RefundReceipts table.

Insert

 

To add a RefundReceipt, specify a Customer, DepositToAccount and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the RefundReceiptLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new RefundReceipt with two Line Items:

INSERT INTO RefundReceipts (CustomerRef, DepositToAccountRef, LineAggregate) 
VALUES ('15', '35',
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>2</ItemRef></SalesItemLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>3</ItemRef></SalesItemLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the invoice.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom field information.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

A private note that does not appear on the transaction records.

LinkedTxnAggregate String False   false false

An XML aggregate of transactions linked to the refund receipt.

LineAggregate String False   false false

An XML aggregate of line items associated with the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The tax code of the transaction.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer that appears in the invoice and in the printed invoice.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

The latitude coordinate of the geocoded billing address.

BillAddr_Long String False   false false

The longitude coordinate of the geocoded billing address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA or the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

The latitude coordinate of the geocoded shipping address.

ShipAddr_Long String False   false false

The longitude coordinate of the geocoded shipping address.

ClassRef String False

Class.Id

false false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

CheckPayment_CheckNum String True   false false

The check number printed on the check.

CheckPayment_Status String False   false false

Status of the check. Values provided by service/business logic.

CheckPayment_NameOnAccount String False   false false

Name of persons or entities holding the account, as printed on the check.

CheckPayment_AcctNum String False   false false

Checking account number, as printed on the check.

CheckPayment_BankName String True   false false

The name of the bank on which the check was drawn.

CreditCardPayment_CreditChargeInfo_Type String False   false false

The type of credit card.

CreditCardPayment_CreditChargeInfo_NameOnAcct String False   false false

The name of the account holder, as printed on the card.

CreditCardPayment_CreditChargeInfo_CcExpiryMonth Integer False   false false

The expiration month of the card.

CreditCardPayment_CreditChargeInfo_CcExpiryYear Integer False   false false

The four-digit expiration year of the card.

CreditCardPayment_CreditChargeInfo_BillAddrStreet String False   false false

The street of the billing address of the credit card holder.

CreditCardPayment_CreditChargeInfo_PostalCode String False   false false

The postal code.

CreditCardPayment_CreditChargeInfo_Amount Double False   false false

The amount processed using the credit card

CreditCardPayment_CreditChargeInfo_ProcessPayment String False   false false

Unique identifier of the payment transaction.

CreditCardPayment_CreditChargeResponse_AuthCode String False   false false

Code returned from the credit card processor to indicate that the charge will be paid by the card issuer.

CreditCardPayment_CreditChargeResponse_TxnAuthorizationTime Datetime False   false false

Timestamp indicating the time in which the card processor authorized the transaction.

CreditCardPayment_CreditChargeResponse_Status String False   false false

Indicates the status of the payment transaction. Possible values include Completed, Unknown.

SalesTermRef String False

Terms.Id

false false

The Id of the sales term associated with the transaction.

SalesTermRef_Name String True

Terms.Name

false false

The name of the sales term associated with the transaction.

DueDate Date False   true false

The date when the invoice is to be paid, not including any early payment discount incentives or late payment penalties. If the date is not supplied, the current date on the server is used.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TotalAmt Double True   true true

This field indicates the total amount of the transaction. This includes the total of all the charges, allowances, and taxes.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which store the location of the transaction as defined using location tracking in QuickBooks Online.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

BillEmail_Address String False   false false

This field identifies the email address where the invoice is sent. If the value of EmailStatus is NeedToSend, BillEmail is a required input.

Balance Double False   false false

This field indicates whether the transaction is a finance charge.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method associated with the transaction.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method associated with the transaction.

PaymentRefNum String False   false false

The reference number for the payment received.

PaymentType String False   false false

The type of payment used for the transaction.

The allowed values are Cash, Check, CreditCard, Other.

DepositToAccountRef String False

Accounts.Id

false false

The asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

The name of the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit.This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

The method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double True   false false

Total amount of the transaction in the home currency. Includes the total of all the charges, allowances, and taxes. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

SalesReceiptLineItems

Create, update, delete, and query QuickBooks Sales Receipt Line Items.

 

Table Specific Information

 

SalesReceipts may be inserted, queried, or updated via the SalesReceipts or SalesReceiptLineItems tables. SalesReceipts may be deleted by using the SalesReceipts table.

Insert

 

To add a SalesReceipt, specify the Customer, TotalAmt, and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new SalesReceipt transaction. For example, the following will insert a new SalesReceipt with two Line Items:

INSERT INTO SalesReceiptLineItems (CustomerRef, Line_Amount#1, Line_DetailType#1, Line_SalesItemLineDetail_ItemRef#1, Line_Amount#2, Line_DetailType#2, Line_SalesItemLineDetail_ItemRef#2, TotalAmt) VALUES ('4', 0.01, 'SalesItemLineDetail', '2', 0.02, 'SalesItemLineDetail', '3', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false false

The Id of the line item on the sales receipt.

SalesReceiptId [KEY] String False

SalesReceipts.Id

true true

The Id of the sales receipt.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom fields associated with the transaction.

DocNumber String False   true true

The reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

TxnStatus String False   false false

The status of the transaction. Depending on the transaction type it may have different values. For sales transactions acceptable values are defined in PaymentStatusEnum. For estimates, the values accepted are defined in QboEstimateStatusEnum.

LinkedTxnAggregate String False   false false

An XML aggregate of the transactions linked to the line item.

Line_Id# String False   false false

The Id of the line item.

Line_LineNum# String False   false false

The number of the line item.

Line_Description# String False   false false

A description of the line item that appears in the printed record.

Line_Amount# Double False   false false

Total amount of the charges or discounts for the given line. Includes the charges and allowances, but it excludes the tax amount.

Line_DetailType# String False   false false

The line detail type. Different detail types indicate different types of line items.

Line_SalesItemLineDetail_ItemRef# String False

Items.Id

false false

The Id of an item in the line item. When a line item lacks an ItemRef it is treated as documentation and the Line_Amount field is ignored.

Line_SalesItemLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item. When a line lacks an ItemRef it is treated as documentation and the Line_Amount field is ignored.

Line_SalesItemLineDetail_ClassRef# String False

Class.Id

false false

The Id of the entity for the class for the line item.

Line_SalesItemLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the line item.

Line_SalesItemLineDetail_UnitPrice# Double False   false false

The unit price of the item referenced by ItemRef.

Line_SalesItemLineDetail_Qty# Double False   false false

The number of items for the line.

Line_SalesItemLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The tax code for this item.

Line_SalesItemLineDetail_ServiceDate# Date False   false false

The date when the service is performed.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

Latitude coordinate of the geocoded address.

BillAddr_Long String False   false false

Longitude coordinate of the geocoded address.

ShipAddr_Id String False   false false

Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

Latitude coordinate of the geocoded address.

ShipAddr_Long String False   false false

Longitude coordinate of the geocoded address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

The date for delivery of goods or services.

TrackingNum String False   false false

The tracking number for the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double False   true false

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes. By default, this is recalculated based on sub items total and overridden. Calculated by QuickBooks business logic; cannot be written to QuickBooks.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the sales receipt is sent.

Balance Double True   true true

The balance reflecting any payments made against the transaction.

PaymentRefNum String False   false false

The reference number for the payment.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method entity.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method entity.

DepositToAccountRef String False

Accounts.Id

false false

The Id of the entity for the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

A name that identifies the asset account where the payment money is deposited. If you do not specify this account, QuickBooks Online uses the Undeposited Funds account.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

The currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double False   false false

Total amount of the transaction in the home currency. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

SalesReceipts

Create, update, delete, and query QuickBooks Sales Receipts.

 

Table Specific Information

 

SalesReceipts may be inserted, queried, or updated via the SalesReceipts or SalesReceiptLineItems tables. SalesReceipts may be deleted by using the SalesReceipts table.

Insert

 

To add a SalesReceipt, specify the Customer, TotalAmt, and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the SalesReceiptLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new SalesReceipt with two Line Items:

INSERT INTO SalesReceipts (CustomerRef, TotalAmt, LineAggregate) 
VALUES ('21', '0.03', '
<Line><Amount>0.01</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>2</ItemRef></SalesItemLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>SalesItemLineDetail</DetailType><SalesItemLineDetail><ItemRef>3</ItemRef></SalesItemLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the sales receipt.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

CustomFieldAggregate String False   false false

An XML aggregate of custom fields associated with the transaction.

DocNumber String False   true true

Reference number for the transaction.

TxnDate Date False   true true

The date when this transaction occurred.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction as defined using location tracking in QuickBooks Online.

PrivateNote String False   false false

The private note about the transaction. This note will not appear on the transaction records by default.

TxnStatus String False   false false

The status of the transaction. Depending on the transaction type it may have different values. For sales transactions, the acceptable values are defined in PaymentStatusEnum. For estimates, the values accepted are defined in QboEstimateStatusEnum.

LinkedTxnAggregate String False   false false

Zero or more linked transactions.

LineAggregate String False   false false

Zero or more lines for the transaction.

TxnTaxDetail_TxnTaxCodeRef String False

TaxCodes.Id

false false

The transaction tax code.

TxnTaxDetail_TotalTax String False   false false

Total tax calculated for the transaction, excluding any tax lines manually inserted into the transaction line list.

TxnTaxDetail_TaxLineAggregate String False   false false

An XML aggregate of tax line items.

CustomerRef String False

Customers.Id

true false

The Id of the customer.

CustomerRef_Name String True

Customers.DisplayName

false false

The name of the customer.

CustomerMemo String False   false false

The message to the customer.

BillAddr_Id String False   false false

The Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

The note for the customer.

BillAddr_Lat String False   false false

Latitude coordinate of the geocoded address.

BillAddr_Long String False   false false

Longitude coordinate of the geocoded address.

ShipAddr_Id String False   false false

The Id of the entity for the shipping address, mainly used for modifying the address. This field is assigned by the data service.

ShipAddr_Line1 String False   false false

First line of the address.

ShipAddr_Line2 String False   false false

Second line of the address.

ShipAddr_Line3 String False   false false

Third line of the address.

ShipAddr_Line4 String False   false false

Fourth line of the address.

ShipAddr_Line5 String False   false false

Fifth line of the address.

ShipAddr_City String False   false false

City name.

ShipAddr_Country String False   false false

Country name.

ShipAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

ShipAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

ShipAddr_Note String False   false false

Note for the customer.

ShipAddr_Lat String False   false false

Latitude coordinate of the geocoded address.

ShipAddr_Long String False   false false

Longitude coordinate of the geocoded address.

ClassRef String False

Class.Id

true false

The Id of the class associated with the transaction.

ClassRef_Name String True

Class.Name

false false

The name of the class associated with the transaction.

ShipMethodRef String False   false false

The Id of the shipping method associated with the transaction.

ShipMethodRef_Name String True   false false

The name of the shipping method associated with the transaction.

ShipDate Date False   false false

Date for delivery of goods or services.

TrackingNum String False   false false

The tracking number of the shipping provider for the delivery of the goods associated with the transaction.

TotalAmt Double False   true false

The total amount of the transaction. This includes the total of all the charges, allowances, and taxes. By default, this is recalculated and overriden based on total of the subitems. This field is calculated by QuickBooks business logic and cannot be written to QuickBooks.

PrintStatus String False   false false

The print status of the invoice.

The allowed values are NotSet, NeedToPrint, PrintComplete.

The default value is NotSet.

EmailStatus String False   false false

The email status of the invoice.

The allowed values are NotSet, NeedToSend, EmailSent.

The default value is NotSet.

BillEmail_Address String False   false false

The email address where the sales receipt is sent.

Balance Double True   true true

The balance reflecting any payments made against the transaction.

PaymentRefNum String False   false false

The reference number for the payment.

PaymentMethodRef String False

PaymentMethods.Id

false false

The Id of the payment method.

PaymentMethodRef_Name String True

PaymentMethods.Name

false false

The name of the payment method.

DepositToAccountRef String False

Accounts.Id

false false

The Id of the asset account where the payment money is deposited. If you do not specify this account, QBO uses the Undeposited Funds account.

DepositToAccountRef_Name String True

Accounts.Name

false false

A name that identifies the asset account where the payment money is deposited. If you do not specify this account, QBO uses the Undeposited Funds account.

ApplyTaxAfterDiscount Boolean False   false false

This field sets whether discounts are applied before the tax is calculated.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

Currency exchange rate. This field is valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

HomeTotalAmt Double False   false false

Total amount of the transaction in the home currency. This field is valid in the UK, AU, IN, and CA editions.

 

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
rows@next String

 

 

 

 

TaxAgency

Create and Query QuickBooks Tax Agency.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the sales tax code.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DisplayName String False   true true

The name for the tax agency.

TaxRegistrationNumber String True   true true

The Registration number for the agency

TaxTrackedOnPurchases Boolean True   true false

This field denotes whether this tax agency is used to track tax on purchases.

TaxTrackedOnSales Boolean True   true false

This field denotes whether this tax agency is used to track tax on sales.

LastFileDate Date True   false false

The last tax filing date for this tax agency. This field is automatically populated by QuickBooks business logic at tax filing time.

 

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
rows@next String

 

 

 

 

Terms

Create, update, delete, and query QuickBooks Terms.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the term.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

Name String False   true false

The name for the term.

Active Boolean False   true false

This field sets whether this entity is currently enabled for use by QuickBooks.

Type String False   false false

The type of the sales term.

DiscountPercent Double False   false false

The discount percentage available against an amount if paid within the days specified by DiscountDays.

DueDays Integer False   false false

Number of days from delivery of goods or services until the payment is due.

DiscountDays Integer False   false false

The discount applies if paid within this number of days.

DayOfMonthDue Integer False   false false

The payment must be received by this day of the month.

DueNextMonthDays Integer False   false false

The payment is due next month if it was issued the specified number of days before the value of DayOfMonthDue.

DiscountDayOfMonth Integer False   false false

The discount applies if paid before this day of month.

 

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
rows@next String

 

 

 

 

TimeActivities

Create, update, delete, and query QuickBooks Time Activities.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true false

The Id of the time activity.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

TxnDate Date False   true true

The date of the time activity.

NameOf String False   false false

The type of the person who performs the task.

The allowed values are Employee, Vendor.

EmployeeRef String False

Employees.Id

false false

The Id of the employee. Either the EmployeeId or EmployeeName is required for the create operation. EmployeeId is preferred.

EmployeeRef_Name String True

Employees.DisplayName

false false

The fully qualified name for an employee. A fully qualified name starts with the employee name followed by a colon and the employee subentry name. Up to 5 additional subemployees can be declared by appending a colon and subentry name. For example: Store54:Shane Hamby.

VendorRef String False

Vendors.Id

false false

The Id vendor associated with the transaction. A reference either the VendorId or VendorName is required for the create operation, although VendorId is preferred.

VendorRef_Name String True

Vendors.Name

false false

The fully qualified name for a vendor. A fully qualified name starts with the name followed by a colon and the subentry name. Up to five levels of subentries can be defined by appending a colon and name for the subentry. For example: Rock Construction:Building Materials.

CustomerRef String False

Customers.Id

false false

The Id of the customer receiving the goods or services. For example, if the transaction is associated with a job or project, such as John Doe:Kitchen, this Id must reference John Doe.

CustomerRef_Name String True

Customers.DisplayName

false false

The fully qualified name for a customer or organization. A fully qualified name starts with the customer name followed by a colon and the subcustomer name. Up to five additional subcustomers can be defined by appending a colon and the name of the subcustomer. For example: Rock Construction:Sammuels.

DepartmentRef String False

Departments.Id

false false

The Id of the department.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department.

ItemRef String False

Items.Id

false false

The Id of the item. This field is required in create commands.

ItemRef_Name String True

Items.Name

false false

The name of item. This field is required in create commands.

ClassRef String False

Class.Id

false false

Reference type of all Ids that are taken as input or output.

ClassRef_Name String True

Class.Name

false false

Reference type of all Ids that are taken as input or output.

BillableStatus String False   false false

Billable status of the time activity.

The allowed values are Billable, NotBillable, HasBeenBilled.

Taxable Boolean False   false false

This value is true if the time activity recorded is both billable and taxable.

HourlyRate Double False   false false

Hourly bill rate of the employee or vendor for this time activity.

Hours Integer False   false false

Hours worked.

Minutes Integer False   false false

Minutes worked. Valid values are 0 - 59.

StartTime Datetime False   false false

Time work started.

EndTime Datetime False   false false

Time work ended.

Description String False   false false

Description of work completed during the time activity.

 

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
rows@next String

 

 

 

 

Transfers

Create, update, delete, and query QuickBooks Transfers

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the payment.

TxnDate Date False   true true

The date entered by the user when this transaction occurred.

PrivateNote String False   false false

User entered, organization-private note about the transaction.

FromAccountRef String False

Accounts.Id

false false

The account Id from which the funds were transfered.

FromAccountRef_Name String False

Accounts.Name

false false

The account Name from which the funds were transfered.

ToAccountRef String False

Accounts.Id

false false

The account Id to which the funds were transfered.

ToAccountRef_Name String False

Accounts.Name

false false

The account Name to which the funds were transfered.

Amount Double False   false false

The total amount of the transaction

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

 

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
rows@next String

 

 

 

 

VendorCreditLineItems

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

 

Table Specific Information

 

VendorCredits may be inserted, queried, or updated via the VendorCredits or VendorCreditLineItems tables. VendorCredits may be deleted by using the VendorCredits table.

Insert

 

To add a VendorCredit, specify the Vendor, TotalAmt, and at least one Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new VendorCredit transaction. For example, the following will insert a new VendorCredit with two Line Items:

INSERT INTO VendorCreditLineItems (VendorRef, Line_Amount#1, Line_DetailType#1, Line_AccountBasedExpenseLineDetail_CustomerRef#1, Line_AccountBasedExpenseLineDetail_AccountRef#1, Line_Amount#2, Line_DetailType#2, Line_AccountBasedExpenseLineDetail_CustomerRef#2, Line_AccountBasedExpenseLineDetail_AccountRef#2, TotalAmt) VALUES ('21', 0.01, 'AccountBasedExpenseLineDetail', '4', '41', 0.02, 'AccountBasedExpenseLineDetail', '4', '41', 0.03)
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
LineId [KEY] String True   false true

The Id of the line item on the vendor credit.

VendorCreditId [KEY] String False

VendorCredits.Id

true false

The Id of the vendor credit.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The primary document number for this transaction. If it is not provided, QuickBooks business logic will assign the document number using the next-in-sequence algorithm.

TxnDate Date False   true true

The date of the transaction. Often, but not required to be, this value is the date the transaction was created in the system. For posting transactions, this is the posting date that affects financial statements

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

DepartmentRef String False

Departments.Id

false false

The reference number for the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The reference number for the transaction.

Line_Id# String False   false false

The Id of the line item.

Line_Description# String False   false false

The description of the line item that appears in the printed record.

Line_Amount# Double False   false false

Total amount of the charges or discounts for the given line item. Includes the charges and allowances, but it excludes the tax amount.

Line_DetailType# String False   false false

The detail type of the line item.

Line_AccountBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The Id of the customer in the expense.

Line_AccountBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The name of the customer in the expense.

Line_AccountBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class for the expense.

Line_AccountBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the expense.

Line_AccountBasedExpenseLineDetail_AccountRef# String False

Accounts.Id

false false

The Id of the account associated with the expense.

Line_AccountBasedExpenseLineDetail_AccountRef_Name# String True

Accounts.Name

false false

The name of the account associated with the expense.

Line_AccountBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of this type of line item.

Line_AccountBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The reference to the tax code used for this line.

Line_ItemBasedExpenseLineDetail_ItemRef# String False

Items.Id

false false

The Id of the item associated with the expense.

Line_ItemBasedExpenseLineDetail_ItemRef_Name# String True

Items.Name

false false

The name of the item associated with the expense.

Line_ItemBasedExpenseLineDetail_ClassRef# String False

Class.Id

false false

The Id of the class for the expense.

Line_ItemBasedExpenseLineDetail_ClassRef_Name# String True

Class.Name

false false

The name of the class for the expense.

Line_ItemBasedExpenseLineDetail_UnitPrice# Double False   false false

The unit price of the item for the expense.

Line_ItemBasedExpenseLineDetail_Qty# Double False   false false

The number of items for the line.

Line_ItemBasedExpenseLineDetail_TaxCodeRef# String False

TaxCodes.Id

false false

The reference to the tax code used for this line.

Line_ItemBasedExpenseLineDetail_CustomerRef# String False

Customers.Id

false false

The Id of the customer in the expense.

Line_ItemBasedExpenseLineDetail_CustomerRef_Name# String True

Customers.DisplayName

false false

The name of the customer in the expense.

Line_ItemBasedExpenseLineDetail_BillableStatus# String False   false false

The billable status of this type of line item.

VendorRef String False

Vendors.Id

true false

The Id of the vendor for this transaction.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor for this transaction.

APAccountRef String False

Accounts.Id

true true

The accounts-payable account to which the bill is credited. If the company uses a single accounts-payable account, this account is implied. When specified, the account must be a Liability account and the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The accounts-payable account to which the bill is credited. This account is implied if the company uses a single accounts-payable account. When specified, the account must be a Liability account and the subtype must be of type Payables.

TotalAmt Double False   true true

The total amount due, determined by the sum of the line item amounts. This includes all charges, allowances, taxes, and discounts.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

Currency exchange rate. Valid only if the company file is set up to use the multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

VendorCredits

Create, update, delete, and query QuickBooks Vendor Credits.

 

Table Specific Information

 

VendorCredits may be inserted, queried, or updated via the VendorCredits or VendorCreditLineItems tables. VendorCredits may be deleted by using the VendorCredits table.

Insert

 

To add a VendorCredit, specify a Vendor, TotalAmt, and at least one Line Item. The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the VendorCreditLineItems table as # columns. Each _ denotes hierarchy. Use <Line> at the base level. For example, the following will insert a new VendorCredit with two Line Items:

INSERT INTO VendorCredits (VendorRef, TotalAmt, LineAggregate) 
VALUES ('21', '0.03', '
<Line><Amount>0.01</Amount><DetailType>AccountBasedExpenseLineDetail</DetailType><AccountBasedExpenseLineDetail><CustomerRef>4</CustomerRef><AccountRef>41</AccountRef></AccountBasedExpenseLineDetail></Line>
<Line><Amount>0.02</Amount><DetailType>AccountBasedExpenseLineDetail</DetailType><AccountBasedExpenseLineDetail><CustomerRef>4</CustomerRef><AccountRef>41</AccountRef></AccountBasedExpenseLineDetail></Line>
')
Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the vendor credit.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime True   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime True   true true

The time the record was last updated.

DocNumber String False   true true

The primary document number for this transaction. If it is not provided, QuickBooks business logic will assign the document number using the next-in-sequence algorithm.

TxnDate Date False   true true

The date of the transaction. Often, but not required to be, this value is the date the transaction was created in the system. For posting transactions, this is the posting date that affects financial statements.

PrivateNote String False   false false

A private note about the transaction. This note will not appear on the transaction records by default.

DepartmentRef String False

Departments.Id

false false

The Id of the department, which stores the location of the transaction.

DepartmentRef_Name String True

Departments.Name

false false

The name of the department, which stores the location of the transaction.

LineAggregate String False   false false

An XML aggregate of the individual line item entries contained in the vendor credit.

VendorRef String False

Vendors.Id

true false

The Id of the vendor.

VendorRef_Name String True

Vendors.Name

false false

The name of the vendor.

APAccountRef String False

Accounts.Id

true true

The accounts-payable account the bill is credited to. If the company has a single accounts-payable account, this account is implied. When specified, the account must be a Liability account and the subtype must be of type Payables.

APAccountRef_Name String True

Accounts.Name

false false

The accounts-payable account to which the bill is credited. If the company has a single accounts-payable account, this account is implied. When specified, the account must be a Liability account and the subtype must be of type Payables.

TotalAmt Double False   true true

The total amount due, determined by the sum of the line item amounts. This includes all charges, allowances, taxes, and discounts.

CurrencyRef String False   false false

The Id of the currency used in the transaction.

CurrencyRef_Name String True   false false

The name of the currency used in the transaction.

ExchangeRate Double False   false false

Currency exchange rate. Valid only if the company file is set up to use multicurrency feature. In QuickBooks, exchange rates are always recorded as the number of home currency units it takes to equal one foreign currency unit. This field is available for the UK, AU, IN, and CA editions.

GlobalTaxCalculation String False   false false

Method in which tax is applied. This field is valid in the UK, AU, IN, and CA editions.

The allowed values are TaxExcluded, TaxInclusive, NotApplicable.

 

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
rows@next String

 

 

 

 

Vendors

Create, update, delete, and query QuickBooks Vendors.

 

Columns

 

 

 

   
Name Type ReadOnly References Filterable Sortable Description
Id [KEY] String True   true true

The Id of the vendor.

SyncToken String False   false false

The sync token. This must be specified when executing an update or delete.

MetaData_CreateTime Datetime False   true true

The time the record was created.

MetaData_LastUpdatedTime Datetime False   true true

The time the record was last updated.

Title String False   false false

Title of a person.

GivenName String False   true true

Given name or first name of a person.

MiddleName String False   true true

Middle name of the person.

FamilyName String False   true true

Family name or last name of the person.

Suffix String False   false true

Suffix of the name.

DisplayName String False   true true

The name of the vendor, as displayed.

CompanyName String False   true true

The name of the company associated with the vendor.

PrintOnCheckName String False   true true

Name of the person or organization as printed on a check

Active Boolean False   true false

This field sets whether the vendor is currently enabled for use by QuickBooks.

PrimaryPhone_FreeFormNumber String False   false false

Primary phone number.

AlternatePhone_FreeFormNumber String False   false false

Alternate phone number.

Mobile_FreeFormNumber String False   false false

The mobile phone number.

Fax_FreeFormNumber String False   false false

Fax number.

PrimaryEmailAddr_Address String False   false false

Email address of the vendor.

WebAddr_URI String False   false false

Website address.

BillAddr_Id String False   false false

Id of the entity for the billing address, mainly used for modifying the address. This field is assigned by the data service.

BillAddr_Line1 String False   false false

First line of the address.

BillAddr_Line2 String False   false false

Second line of the address.

BillAddr_Line3 String False   false false

Third line of the address.

BillAddr_Line4 String False   false false

Fourth line of the address.

BillAddr_Line5 String False   false false

Fifth line of the address.

BillAddr_City String False   false false

City name.

BillAddr_Country String False   false false

Country name.

BillAddr_CountrySubDivisionCode String False   false false

Region within a country. For example, the state name for the USA and the province name for Canada.

BillAddr_PostalCode String False   false false

Postal code. For example, the zip code for the USA and Canada.

BillAddr_Note String False   false false

Note for the customer.

BillAddr_Lat String False   false false

Latitude coordinate of the geocoded address.

BillAddr_Long String False   false false

Longitude coordinate of the geocoded address.

OtherContactInfo_Type String False   false false

The type of contact information.

OtherContactInfo_Telephone_FreeFormNumber String False   false false

Telephone number information.

TaxIdentifier String False   false false

Tax Id of the vendor.

TermRef String False

Terms.Id

false false

The Id of a term associated with the vendor.

TermRef_Name String True

Terms.Name

false false

A name that identifies a term associated with the vendor.

Balance String False   true true

The amount the company owes to this vendor. For the create operation, this represents the opening balance or the unpaid amount for the vendor. When returned in response to the query request it represents the current open balance or unpaid amount for that vendor.

AcctNum String False   false false

Name or number of the account associated with this vendor.

Vendor1099 Boolean False   false false

This field specifies that the vendor is an independent contractor who is given a 1099-MISC form at the end of the year. The 1099 vendor is paid with regular checks and taxes are not withheld on their behalf.

CurrencyRef String False   false false

The Id of the currency used in all the business transactions created for or received from that vendor.

CurrencyRef_Name String True   false false

The name of the currency used in all the business transactions created for or received from that vendor.

 

Pseudo-Columns

 

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

 

 

   
Name Type Description
rows@next String

 

 

 




 

Views

  1. BalanceSheetDetailReport
  2. BalanceSheetSummaryReport
  3. BudgetDetails
  4. Budgets
  5. CompanyInfo
  6. Entitlements
  7. Preferences
  8. TaxCodes
  9. TaxRates

BalanceSheetDetailReport

The QuickBooks report CustomerBalanceDetail.

 

Columns

 

 

 

   
Name Type References Description
Label String   A column for Label.
TransactionType String   A column for TransactionType containing String data.
Num String   A column for Num containing String data.
DueDate String   A column for Due_Date containing Date data.
Amount Double   A column for Amount containing Money data.
OpenBalance Double   A column for OpenBalance containing Money data.
Balance Double   A column for Balance containing Money data.

 

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
StartDueDate String   The startdate of which receivables are due. StartDueDate must be less than EndDueDate.
EndDueDate String   The enddate in which receivables are due. EndDueDate must be greater than StartDueDate.
ReportDate String   Start date to use for the report.
AgingMethod String   The date upon which aging is determined.
ARPaid String   The accounts receivable paid status.

The default value is Unpaid.

Customer String   Filters report contents to include information for specified customers. This is a comma separated list of one or more Customer IDs.
Department String   Filters report contents to include information for specified departments if so configured in the company file. This is a comma separated list of one or more Department IDs.
ShipVia String   Filter by the shipping method as specified in Invoice's ShipMethodRef. This is the name of the shipping method.
Term String   Filters report contents to include information for specified term or terms supplied. This is a comma separated list of one or more Term IDs.

 

 

 

BalanceSheetSummaryReport

The QuickBooks report BalanceSheet.

 

Columns

 

 

 

   
Name Type References Description
Account String   A column for Account containing Account data.
Total Double   A column for Total containing Money data.

 

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
DateMacro String   A predefined date range. Use this if you want the report to cover a standard report date range. Otherwise, use the StartDate and EndDate to cover an explicit range.
StartDate String   The startdate of the report. StartDate must be less than EndDate.
EndDate String   The enddate of the report. EndDate must be greater than StartDate.
AccountingMethod String   The accounting method used in the report.
Customer String   Filters report contents to include information for specified customers. This is a comma separated list of one or more Customer IDs.
Department String   Filters report contents to include information for specified departments if so configured in the company file. This is a comma separated list of one or more Department IDs.
Vendor String   Filters report contents to include information for specified vendors. This is a comma separated list of one or more Vendor IDs.
Class String   Filters report contents to include information for specified classes if so configured in the company file. This is a comma separated list of one or more Class IDs.
Item String   Filters report contents to include information for specified items. This is a comma separated list of one or more Item IDs.

 

 

 

BudgetDetails

Query QuickBooks BudgetDetails.

 

Columns

 

 

 

   
Name Type References Description
BudgetId [KEY] String   The Id of the customer.
DetailId [KEY] String   The Id of the customer.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
Name String   A user recogniyable name for the Account.
StartDate Datetime   The begin date for the Budget.
EndDate Datetime   The end date for the Budget.
BudgetType String   The type of budget.
BudgetEntryType String   Period that the budget covers.
Active Boolean   A boolean indicating if the budget is active.
BudgetDetail_BudgetDate# Datetime   Date of the individual BudgetDetail.
BudgetDetail_Amount# Double   Amount assigned to a BudgetDetail.
BudgetDetail_AccountRef# String

Accounts.Id

The id of the account associated with the BudgetDetail.
BudgetDetail_AccountRef_Name# String

Accounts.Name

The name of the account associated with the BudgetDetail.
BudgetDetail_CustomerRef# String

Customers.Id

The id of the customer associated with the BudgetDetail.
BudgetDetail_CustomerRef_Name# String

Customers.DisplayName

The name of the customer associated with the BudgetDetail.
BudgetDetail_ClassRef# String

Class.Id

The id of the class associated with the BudgetDetail.
BudgetDetail_ClassRef_Name# String

Class.Name

The name of the class associated with the BudgetDetail.
BudgetDetail_DepartmentRef# String

Departments.Id

The id of the department associated with the BudgetDetail.
BudgetDetail_DepartmentRef_Name# String

Departments.Name

The name of the department associated with the BudgetDetail.

 

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
rows@next String    

 

 

 

Budgets

Query QuickBooks Budgets.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the customer.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
Name String   A user recogniyable name for the Account.
StartDate Datetime   The begin date for the Budget.
EndDate Datetime   The end date for the Budget.
BudgetType String   The type of budget.
BudgetEntryType String   Period that the budget covers.
Active Boolean   A boolean indicating if the budget is active.

 

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
rows@next String    

 

 

 

CompanyInfo

Retrieve information about the QuickBooks company.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the company.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
CompanyName String   The name of the company.
LegalName String   The legal name of the company.
CompanyAddr_Id String   The Id of the Intuit entity for the company address, mainly used for modifying the address. This field is assigned by the data service.
CompanyAddr_Line1 String   The first line of the address.
CompanyAddr_Line2 String   The second line of the address.
CompanyAddr_Line3 String   The third line of the address.
CompanyAddr_Line4 String   The fourth line of the address.
CompanyAddr_Line5 String   The fifth line of the address.
CompanyAddr_City String   The city name.
CompanyAddr_Country String   The country name.
CompanyAddr_CountrySubDivisionCode String   The country region. The state name for the USA or the province name for Canada.
CompanyAddr_PostalCode String   The postal code. The zip code for the USA and Canada.
CompanyAddr_Note String   A note for the customer.
CompanyAddr_Lat String   The latitude coordinate of the geocoded address.
CompanyAddr_Long String   The longitude coordinate of the geocoded address.
CustomerCommunicationAddr_Id String   The Id of the customer communication address. This field is assigned by the data service. The customer will use this address to contact the company. This field is also used to modify the address.
CustomerCommunicationAddr_Line1 String   The first line of the address.
CustomerCommunicationAddr_Line2 String   The second line of the address.
CustomerCommunicationAddr_Line3 String   The third line of the address.
CustomerCommunicationAddr_Line4 String   The fourth line of the address.
CustomerCommunicationAddr_Line5 String   The fifth line of the address.
CustomerCommunicationAddr_City String   The city name.
CustomerCommunicationAddr_Country String   The country name.
CustomerCommunicationAddr_CountrySubDivisionCode String   The country region. The state name for the USA or the province name for Canada.
CustomerCommunicationAddr_PostalCode String   The postal code. The zip code for the USA and Canada.
CustomerCommunicationAddr_Note String   The note for the customer.
CustomerCommunicationAddr_Lat String   The latitude coordinate of the geocoded address.
CustomerCommunicationAddr_Long String   The longitude coordinate of the geocoded address.
LegalAddr_Id String   The Id of the company address used in government communications. This field is assigned by the data service and mainly used for modifying the address.
LegalAddr_Line1 String   The first line of the address.
LegalAddr_Line2 String   The second line of the address.
LegalAddr_Line3 String   The third line of the address.
LegalAddr_Line4 String   The fourth line of the address.
LegalAddr_Line5 String   The fifth line of the address.
LegalAddr_City String   The city name.
LegalAddr_Country String   The country name.
LegalAddr_CountrySubDivisionCode String   The region within a country. For example, the state name for the USA or the province name for Canada.
LegalAddr_PostalCode String   The postal code. The zip code for the USA and Canada.
LegalAddr_Note String   The note for the customer.
LegalAddr_Lat String   The latitude coordinate of the geocoded address.
LegalAddr_Long String   The longitude coordinate of the geocoded address.
Email_Address String   The default email address.
PrimaryPhone_FreeFormNumber String   The primary phone number.
EmployerId String   The employer identifier (EIN).
Country String   The country that the company belongs to. This field is used for financial calculations.
SupportedLanguages String   A comma-separated list of languages supported by the company.
WebAddr_URI String   The default company website.
CompanyStartDate Date   The date when the company file was created. This field and Metadata_CreateTime contain the same value.

 

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
rows@next String    

 

 

 

Entitlements

Retrieves QuickBooks Entitlements.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the entitlement.
Name String   The name of the entitlement.
Term String   The term associated with the entitlement.

 

 

 

Preferences

Query QuickBooks Preferences. The Preferences table contains settings for company-wide preferences, which affect all users.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the preference record.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
AccountingInfoPrefs_TrackDepartments Boolean   This setting corresponds to the Track locations preference in QuickBooks Online Company Settings under Categories.
AccountingInfoPrefs_DepartmentTerminology String   Specifies the term used by the company for department. This string is used as a label on transaction forms.
AccountingInfoPrefs_ClassTrackingPerTxnLine Boolean   These settings correspond to how classes are assigned when Track classes in QuickBooks Online Company Settings under Categories is set to On.
AccountingInfoPrefs_ClassTrackingPerTxn Boolean   These settings correspond to how classes are assigned when Track classes in QuickBooks Online Company Settings under Categories is set to On.
AccountingInfoPrefs_CustomerTerminology String   A value used for describing customers in the UI.
TaxPrefs_UsingSalesTax Boolean   This field sets whether sales tax is enabled.
TaxPrefs_TaxGroupCodeRef String   The first-used tax code on a sales form after a new sales tax center has been enabled.
CurrencyPrefs_MultiCurrencyEnabled String   This field sets whether multicurrency is enabled for this company.
CurrencyPrefs_HomeCurrency String   The currency code of the country where the business is located.
ProductAndServicesPrefs_ForSales Boolean   This field indicates whether the Product and Services for Sales preference is enabled.
ProductAndServicesPrefs_ForPurchase Boolean   This field indicates whether the Product and Services for Purchase preference is enabled.
ProductAndServicesPrefs_QuantityWithPriceAndRate Boolean   This field indicates whether quantity with price and rate is enabled.
ProductAndServicesPrefs_QuantityOnHand Boolean   This field indicates whether QuantityOnHand is enabled.
SalesFormsPrefs_AllowDiscount Boolean    
SalesFormsPrefs_AllowDeposit Boolean   Allows deposits to be specified.
SalesFormsPrefs_AllowShipping Boolean   Allows the use of Shipping Info fields.
SalesFormsPrefs_AllowServiceDate Boolean   Allows the use of Service Date fields.
SalesFormsPrefs_AllowEstimates Boolean   Allows the use of Estimate entities.
SalesFormsPrefs_CustomTxnNumbers Boolean   Sets whether custom transaction numbers can be specified.
SalesFormsPrefs_DefaultTerms Integer   Default sales terms.
SalesFormsPrefs_EstimateMessage String   Message to the customers on estimates.
SalesFormsPrefs_DefaultDiscountAccount String   Default discount account.
SalesFormsPrefs_DefaultShippingAccount String   Default shipping account.
SalesFormsPrefs_DefaultCustomerMessage String   Default customer message.
SalesFormsPrefs_IPNSupportEnabled Boolean   Enables support for IPN.
SalesFormsPrefs_ETransactionEnabledStatus String   This field enables ETransaction status.
SalesFormsPrefs_ETransactionAttachPDF Boolean   This field indicates whether PDFs may be attached to ETransactions.
SalesFormsPrefs_ETransactionPaymentEnabled Boolean   This field indicates whether payment entities can be used with ETransactions.
SalesFormsPrefs_CustomFieldAggregate String   An XML aggregate of the custom fields for sales form entities.
VendorAndPurchasesPrefs_TrackingByPreferences Boolean   Enables tracking by using settings in preferences.
VendorAndPurchasesPrefs_TrackingByCustomer Boolean   Enables tracking using customer information.
VendorAndPurchasesPrefs_BillableExpenseTracking Boolean   Enables tracking using billable expense fields.
VendorAndPurchasesPrefs_WorkWeekStartDate Date   Sets the starting day of the work week.
VendorAndPurchasesPrefs_POCustomFieldAggregate String   Defines PO custom fields.
TimeTrackingPrefs_UseServices Boolean   Enables time tracking services.
TimeTrackingPrefs_BillPreferences Boolean   Enables billing for time tracking entries.
TimeTrackingPrefs_BillCustomers Boolean   Enables time tracking services for the customer in a bill.
TimeTrackingPrefs_ShowBillRateToAll Boolean   Billing rate to all employees enabled.
TimeTrackingPrefs_WorkWeekStartDate String   The starting day of the work week.
TimeTrackingPrefs_MarkTimeEntiresBillable Boolean   This field sets whether to mark time entries as billable.
EmailMessagesPrefs_InvoiceMessage_Subject String   The subject portion of the default invoice message.
EmailMessagesPrefs_InvoiceMessage_Message String   The message portion of the default invoice message.
EmailMessagesPrefs_EstimateMessage_Subject String   The subject portion of the default estimate message.
EmailMessagesPrefs_EstimateMessage_Message String   The message portion of the default estimate message.
EmailMessagesPrefs_SalesReceiptMessage_Subject String   The subject portion of the default sales receipt message.
EmailMessagesPrefs_SalesReceiptMessage_Message String   The message portion of the default sales receipt message.
EmailMessagesPrefs_StatementMessage_Subject String   The subject portion of the default statement message.
EmailMessagesPrefs_StatementMessage_Message String   The message portion of the default statement message.
OtherPrefs_NameValueAggregate String   This field specifies an extension of the preference entity to allow name-value pairs in the custom fields at the top level.

 

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
rows@next String    

 

 

 

TaxCodes

Query QuickBooks Sales Tax Codes.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the sales tax code.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
Name String   The name for the tax sales code.
Description String   A description for the sales tax code. This field must not contain white space or formatting characters.
Active Boolean   This field sets whether the sales tax code entity is active. Inactive sales tax codes may be hidden from display and are not used on financial transactions.
Taxable Boolean   This field indicates that the sales tax code signifies a non-taxable entity if false or null (default) or a taxable entity if true.
TaxGroup Boolean   This field indicates whether the sales tax code is for a tax group.
SalesTaxRateList_TaxRateDetailAggregate String   An XML aggregate of sales tax rates.
PurchaseTaxRateList_TaxRateDetailAggregate String   An XML aggregate of purchase tax rates. This field available in only the UK, AU, CA, and IN editions.

 

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
rows@next String    

 

 

 

TaxRates

Query QuickBooks Tax Rates.

 

Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the tax rate.
SyncToken String   The sync token. This must be specified when executing an update or delete.
MetaData_CreateTime Datetime   The time the record was created.
MetaData_LastUpdatedTime Datetime   The time the record was last updated.
Name String   The name for the tax rate.
Description String   A description for the sales tax rate. This field must not contain white space or formatting characters.
Active Boolean   This field sets whether the tax rate is active. This value is false if inactive. Inactive sales tax codes may be hidden from display and are not used on financial transactions.
RateValue String   The value of the tax rate.
AgencyRef String

TaxAgency.Id

The Id of the tax agency.
AgencyRef_Name String

TaxAgency.Name

The name of the tax agency.
TaxReturnLineRef String   The Id of the tax return line.
TaxReturnLineRef_Name String   The name of the tax return line.
SpecialTaxType String   The special tax type to handle zero-rate taxes. VAT-registered businesses who receive goods, services, or acquisitions from other EU countries will need to calculate the VAT due, but not paid, on these acquisitions. The rate of VAT that is payable is the same that would have been paid if the goods had been supplied by a UK supplier.
DisplayType String   The display type of the tax rate. This field is used to configure display settings.
EffectiveTaxRate Double   The effective tax rate. This field is used to know which tax rate is applicable on any date.

 

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
rows@next String