Skip to Content

QuickBooks Point of Sale Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

QuickBooks POS Version Support

The Jitterbit Connector for QuickBooks POS supports QuickBooks Point Of Sale Basic and Professional Editions. QuickBooks Point of Sale Multi-Store edition is not supported.

Establish a Connection

Connect to QuickBooks

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

To work with your data in practice mode, set QBPOSPractice. Additionally, set QBPOSVersion.

Connect to a Local Company File

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

  1. Open QuickBooks POS as an administrator and open the company file you want to connect to.

  2. Connect to QuickBooks POS. A dialog will appear in QuickBooks POS prompting you to authorize the connector. After granting access to the connector, you can now execute commands to QuickBooks POS.

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

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

Connection Troubleshooting

If you receive a connection error (such as "Internal error 160002") you may need to switch QuickBooks POS to multiuser mode. This is done by selecting the "Switch Company File to Multi User Mode" option in the File Menu. You should then be able to connect to the company file.

If a CompanyFile is not specified in the connection string, QuickBooks POS may present an "Enter Company Name" window the first time you connect. In this window, you must specify the company file and the computer name where the company file is located.

Use the CData QuickBooks Desktop Gateway

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

  1. If you have not already done so, download the QuickBooks Desktop Gateway from here and install it.

  2. Open the company file you want to connect to in QuickBooks POS using an administrator account in single-user mode.

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

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

  4. When you first connect, a dialog appears in QuickBooks POS prompting you to authorize the application. After authorizing, you can execute commands to QuickBooks POS.

    Specify the URL of the Desktop Gateway and the User and Password. By default, the Gateway connects to the currently open company file.

  5. If you want to access QuickBooks POS when QuickBooks POS is not running, save the company file information for the user. The Desktop Gateway automatically opens QuickBooks POS in the background with the company file for that user.

Note

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

How do I Connect to QuickBooks POS over SSL/TLS?

You can enable SSL/TLS on the Advanced tab.

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

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

CData QuickBooks Desktop Gateway

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

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

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

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

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

Users

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

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

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

  • User: Sets the username. This is required.
  • Password: Sets the password for the user. This is required when using Basic authentication (default).
  • Company File: Specifies the company file with which the application will communicate. By default this is the company file that is currently open in QuickBooks POS. Set this to the name of the company file (Note: Specifying the entire file path will result in a "Cannot connect to database" error from QuickBooks POS). A company file must be specified in order to access the company file when QuickBooks POS is closed.
  • Authentication: Specifies the type of authentication to perform when the user connects. The Desktop Gateway supports the following authentication methods:

    Basic Authentication (default): Authenticates the user with a username and password.
    Windows Authentication: Authenticates the user as a Windows user. In this case the Password field is not applicable. When the Desktop Gateway receives a connection request, it will authenticate the user to Windows using the credentials supplied in the request.
  • Application Name: Optionally sets the name of the application as seen by QuickBooks POS. Authentication to QuickBooks POS is handled based on the provided application name.

  • Data Access: Specifies the allowed access for the user.

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

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

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

Status

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

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

Advanced

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

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

Log Options

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

    Daily (default): Uses a new log file every day. Files are written with the format "yyyy_MM_dd.txt". For example, "2013_09_23.txt".
    Weekly: Uses a new log file every week. Files are written with the format "yyyy_ww.txt". For example, "2013_34.txt", where 34 means this is the 34th week of 2013.
    Monthly: Uses a new log file every month. Files are written with the format "yyyy_mm.txt". For example, "2013_09.txt".
  • Log Mode: Sets the verbosity of the log output. In most situations, Info (the default) is sufficient. The Verbose option is helpful for debugging purposes.

IP Options

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

Enable Persistent Connections

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

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

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

Enable TLS/SSL

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

The following options are used to configure TLS/SSL:

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

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

Command-Line Interface

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

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

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

RemoteConnectorService.exe /Service <Command>

The following commands are available:

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

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

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

The following command-line options are available:

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

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

Registry Keys

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

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

Advanced Features

This section details a selection of advanced features of the QuickBooks POS connector.

User Defined Views

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

SSL Configuration

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

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

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

User Defined Views

The Jitterbit Connector for QuickBooks POS allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Customers WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Insert Parent and Child Records

Use Case

When inserting records, often there is a need to fill in details about child records that have a dependency on a parent.

For instance, when dealing with a CRM system, Invoices often cannot be entered without at least one line item. Since invoice line items can have several fields, this presents a unique challenge when offering the data as relational tables.

When reading the data, it is easy enough to model an Invoice and an InvoiceLineItem table with a foreign key connecting the two. However, during inserts, the CRM system requires both the Invoice and the InvoiceLineItems to be created in a single submission.

To solve this sort of problem, our tools offer child collection columns on the parent. These columns can be used to submit insert statements that include details of both the parent and the child records.

For example, let's say that the Invoice table contains a single column called InvoiceLineItems. During the insert, we can pass the details of the records that must be inserted to the InvoiceLineItems table into Invoice record's InvoiceLineItems column.

The following subsection describes how this might be done.

Methods for Inserting Parent/Child Records

The connector facilitates two methods for inserting parent/child records: temporary table insertion and XML aggregate insertion.

Temporary (#TEMP) tables

The simplest way to enter data would be to use a #TEMP table, or temporary table, which the connector will store in memory.

Reference the #TEMP table with the following syntax:

TableName#TEMP

#TEMP tables are stored in memory for the duration of a connection.

Therefore, in order to use them, you cannot close the connection between submitting inserts to them, and they cannot be used in environments where a different connection may be used for each query.

Within that single connection, the table remains in memory until the bulk insert is successful, at which point the temporary table will be wiped from memory.

For example:

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

Once the InvoiceLineItems table is populated, the #TEMP table may be referenced during an insert into the Invoice table:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

Under the hood, the connector will read in values from the #TEMP table.

Notice that the ReferenceNumber was used to identify what Invoice the lines are tied to. This is because the #TEMP table may be populated and used with a bulk insert, where there are separate lines for each invoice. This enables the #TEMP tables to be used with a bulk insert. For example:

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

In this case, we are inserting two different Invoices. The ReferenceNumber is how we determine which Lines go with which Invoice.

Note

The tables and columns presented here are an example of how the connector works in general. The specific table and column names may be different in the connector.

Direct XML Insertion

Direct XML can be used as an alternative to #TEMP tables. Since #TEMP tables are not used to construct them, it does not matter if you use the same connection or close the connection after insert.

For example:

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

OR

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

Note that the ReferenceNumber is not present in these examples because the XML, by its nature, is passed against the parent record in full per insert. Since the complete XML must be constructed and submitted for each row, there is no need to provide something to tie the child back to the parent.

Now insert the values:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

OR

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Note

The connector also supports the use of XML/JSON aggregates.

Example for QuickBooks POS

For a working example of how temp tables can be used for bulk insert in QuickBooks POS, please see the following:

// Insert into Invoices table
INSERT INTO InvoiceLineItems#TEMP (ItemName, ItemQuantity) VALUES ('Repairs','1')
INSERT INTO InvoiceLineItems#TEMP (ItemName, ItemQuantity) VALUES ('Removal','2')

INSERT INTO Invoices (CustomerName, Memo, ItemAggregate) VALUES ('Abercrombie, Kristy', 'NUnit Memo', 'InvoiceLineItems#TEMP')


// Insert into InvoiceLineItems table
INSERT INTO InvoiceLineItems#TEMP (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) VALUES ('Abercrombie, Kristy', '2011-01-01', 'UPS', '2011-01-02', 'NUnit Memo', 'We appreciate your prompt payment.', '2011-01-03', 'Some other data', 'Repairs', '1', '3.50')
INSERT INTO InvoiceLineItems#TEMP (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) VALUES ('Abercrombie, Kristy', '2011-01-01', 'UPS', '2011-01-02', 'NUnit Memo', 'We appreciate your prompt payment.', '2011-01-03', 'Some other data', 'Removal', '2', '3.50')

INSERT INTO InvoiceLineItems (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) SELECT CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate InvoiceLineItems#TEMP


//Insert into JournalEntry
INSERT Into JournalEntries#TEMP (ReferenceNumber, LineAggregate) VALUES   ('Setup 13', '<JournalEntryLines> <Row><LineType>Credit</LineType><LineAccount>Retained Earnings</LineAccount><LineAmount>100</LineAmount></Row> <Row><LineType>Credit</LineType><LineAccount>Note Payable - Bank of Anycity</LineAccount><LineAmount>20</LineAmount></Row> <Row><LineType>Debit</LineType><LineAccount>Checking</LineAccount><LineAmount>120</LineAmount></Row> </JournalEntryLines>');
INSERT Into JournalEntries#TEMP (ReferenceNumber, LineAggregate) VALUES   ('Setup 14','<JournalEntryLines> <Row><LineType>Credit</LineType><LineAccount>Retained Earnings</LineAccount><LineAmount>100</LineAmount></Row> <Row><LineType>Credit</LineType><LineAccount>Note Payable - Bank of Anycity</LineAccount><LineAmount>20</LineAmount></Row> <Row><LineType>Debit</LineType><LineAccount>Checking</LineAccount><LineAmount>120</LineAmount></Row> </JournalEntryLines>' );
INSERT INTO JournalEntries (ReferenceNumber,LineAggregate) SELECT ReferenceNumber,LineAggregate from JournalEntries#TEMP;



//Insert into TimeTracking table
INSERT INTO timetracking#TEMP (BillableStatus, Duration, Date, EmployeeId,CustomerId,CustomerName, ServiceItemName, ServiceItemId) Values ('Billable','4:10','2002-09-09',  '370000-933272659', '800013DE-1702639725', 'Testing02','Blueprint changess','340000-1071522351');
INSERT INTO timetracking#TEMP (BillableStatus, Duration, Date, EmployeeId,CustomerId,CustomerName, ServiceItemName, ServiceItemId) Values ('Empty','1:10','2002-09-02',  '370000-933272659', '800013DE-1702639725', 'Testing02','Blueprint changess','340000-1071522351');
INSERT INTO timetracking (BillableStatus, Duration, Date, EmployeeId,CustomerId,CustomerName, ServiceItemName, ServiceItemId) SELECT BillableStatus, Duration, Date, EmployeeId,CustomerId,CustomerName, ServiceItemName, ServiceItemId from timetracking#TEMP;

//Insert into SalesReceipts
INSERT INTO SalesReceiptLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Theodor', 'Repairs', 1);
INSERT INTO SalesReceipts#TEMP (CustomerName, ItemAggregate) VALUES ('name1', SalesReceiptLineItems#TEMP);
INSERT INTO SalesReceipts#TEMP (CustomerName, ItemAggregate) VALUES ('name2',SalesReceiptLineItems#TEMP);
INSERT INTO SalesReceipts (CustomerName, ItemAggregate) SELECT CustomerName, ItemAggregate from SalesReceipts#TEMP;

//Insert into Bills
INSERT INTO BillLineItems#TEMP (ReferenceNumber, ItemName, ItemQuantity) VALUES (1, 'Repairs','1');
INSERT INTO BillLineItems#TEMP (ReferenceNumber, ItemName, ItemQuantity) VALUES (2, 'Removal','2');
INSERT INTO Bills#TEMP (ReferenceNumber, VendorName, Date, ItemAggregate) VALUES (1, 'Cal Telephone', '1/1/2021', 'BillLineItems#TEMP');
INSERT INTO Bills#TEMP (ReferenceNumber, VendorName, Date, ItemAggregate) VALUES (2, 'Cal Telephone', '1/2/2021', 'BillLineItems#TEMP');
INSERT INTO Bills (ReferenceNumber, VendorName, Date, ItemAggregate) select ReferenceNumber, VendorName, Date, ItemAggregate from Bills#TEMP");

SSL Configuration

Customize the SSL Configuration

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

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

Data Model

The Data Model has two parts: Tables and Views.

Tables

Tables allow access to the data from the data source. The Jitterbit Connector for QuickBooks POS models the data from the data source in tables so that it can be easily queried and updated.

Views

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

Tables

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

Jitterbit Connector for QuickBooks POS Tables

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

Customers

Create, update, delete, and query QuickBooks POS customers.

Table Specific Information

The Customers table allows you to select, insert, update, and delete Customers within QuickBooks POS.

Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Customers WHERE FirstName LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014' AND AccountBalance > 100.00
Insert

To create a new Customer record, the LastName field is required.

Shipping addresses are specified via an XML aggregate within the ShipAddressesAggregate column. The columns that may be used in these aggregates are defined in the CustomerShipAddresses table as # columns. Note that ShipAddressAddressName is required when adding a shipping address.

The following example demonstrates how to insert a new Customer with two shipping addresses:

INSERT INTO Customers (FirstName, LastName, ShipAddressesAggregate)
VALUES ('Kristy', 'Abercrombie', '<CustomerShipAddresses>
<Row><ShipAddressAddressName>Home</ShipAddressAddressName><ShipAddressStreet>123 Main Street</ShipAddressStreet><ShipAddressCity>Chapel Hill</ShipAddressCity><ShipAddressState>NC</ShipAddressState><ShipAddressPostalCode>12345</ShipAddressPostalCode></Row>
<Row><ShipAddressAddressName>Office</ShipAddressAddressName><ShipAddressStreet>456 Downtown Drive</ShipAddressStreet><ShipAddressCity>Raleigh</ShipAddressCity><ShipAddressState>NC</ShipAddressState><ShipAddressPostalCode>98765</ShipAddressPostalCode></Row>
</CustomerShipAddresses>')
Update

Any field that is not read-only can be updated.

When updating a Customer record, shipping addresses can be added or modified via the ShipAddressesAggregate column (just as in an insert). In such cases, the ShipAddressAddressName column is required to identify the shipping address being added or updated.

In the case of updating a shipping address, pre-existing values will remain unchanged unless explicitly specified within the ShipAddressesAggregate. Therefore, to remove a value, the column value in the aggregate must be set to "" (empty string).

UPDATE Customers SET ShipAddressesAggregate = '<CustomerShipAddresses>
<Row><ShipAddressAddressName>Office</ShipAddressAddressName><ShipAddressCompany>MyCompany</ShipAddressCompany></Row>
</CustomerShipAddresses>'
WHERE ListId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
CustomerID String False Range The user-specified ID for the customer.
FullName String True The full name of the customer.
Salutation String False Range A salutation, such as Mr., Mrs., etc.
FirstName String False Range The first name of the customer as stated in the address info.
LastName String False Range The last name of the customer as stated in the address info.
CompanyName String False Range The name of the company of the customer.
Phone String False Range The primary telephone number for the customer.
Phone2 String False Range An alternate telephone or fax number for the customer.
Phone3 String False Range An alternate telephone or fax number for the customer.
Email String False Range The email address of the customer.
IsOkToEMail Boolean False Single Whether or not the customer can be notified via email.
CustomerType String False Range A customer type within QuickBooks POS.
Notes String False Range Notes on this customer.
BillAddress_Street String False Street address of the billing address of the customer.
BillAddress_Street2 String False The second line of the street address in the billing address of the customer.
BillAddress_City String False City name for the billing address of the customer.
BillAddress_State String False State name for the billing address of the customer.
BillAddress_PostalCode String False Postal code for the billing address of the customer.
BillAddress_Country String False Country for the billing address of the customer.
IsNoShipToBilling Boolean False Single Whether or not the billing address can be used as a shipping address.
DefaultShipAddress String False The default shipping address of the customer.
ShipAddressesAggregate String False An aggregate of the shipping address data, which can be used for adding a customer and their shipping address locations.
IsAcceptingChecks Boolean False Single Indicates whether checks are being accepted from this customer. The default is True.
IsUsingChargeAccount Boolean False Single Indicates whether the customer is using a charge account.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
TaxCategory String False Range The sales tax category.
CustomerDiscPercent Double False Range The suggested customer discount percentage for all items listed on a sales receipt. Note that you cannot assign both a discount percentage and a price level to a customer.
CustomerDiscType String False Single The customer discount type. Possible values are None, PriceLevel, and Percentage.
PriceLevelNumber String False Single The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
LastSale Datetime True Range The time of the last purchase made by this customer.
AmountPastDue Decimal True Range The amount past due for a transaction.
IsUsingWithQB Boolean False Single Whether or not the customer is using QuickBooks POS with QuickBooks.
AccountBalance Decimal True Range The balance of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
AccountLimit Decimal True Range The limit of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
IsRewardsMember Boolean False Single Indicates whether the customer is a rewards member.
RewardAggregate String True An aggregate of the reward data for the customer.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the customer was created.
TimeModified Datetime True Range When the customer was last modified.

CustomerShipAddresses

Create, update, delete, and query QuickBooks POS Customer Shipping Addresses.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM CustomerShipAddresses WHERE FirstName LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new Customer record or to add a shipping address to an existing Customer record. To create a new Customer record, the LastName field is required. To add a shipping address to an existing record, the ListId column of the Customer record that the address is to be added to and the ShipAddressAddressName are required.

Create a new customer:

INSERT INTO CustomerShipAddresses (FirstName, LastName, ShipAddressAddressName, ShipAddressStreet, ShipAddressCity, ShipAddressState, ShipAddressPostalCode)
VALUES ('Kristy', 'Abercrombie', 'Home', '123 Main Street', 'Chapel Hill', 'NC', '12345')

Add a shipping address to an existing customer:

INSERT INTO CustomerShipAddresses (ListId, ShipAddressAddressName, ShipAddressStreet, ShipAddressCity, ShipAddressState, ShipAddressPostalCode)
VALUES ('-1234567890123456789','Office', '456 Downtown Drive', 'Raleigh', 'NC', '98765')
Update

Any field that is not read-only can be updated.

To perform an update, ListId and ShipAddressAddressName are required fields.

UPDATE CustomerShipAddresses SET ShipAddressCompanyName = 'MyCompany' WHERE ListId = '-1234567890123456789' AND ShipAddressAddressName = 'Office'
Delete

Delete is used to remove a shipping address from the specified Customer record (it will not delete the Customer record itself). To perform a delete, ListId and ShipAddressAddressName are required fields.

DELETE FROM CustomerShipAddresses WHERE ListId = '-1234567890123456789' AND ShipAddressAddressName = 'Home'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
CustomerID String False Range The user-specified ID for the customer.
FullName String True The full name of the customer.
Salutation String False Range A salutation, such as Mr., Mrs., etc.
FirstName String False Range The first name of the customer as stated in the address info.
LastName String False Range The last name of the customer as stated in the address info.
CompanyName String False Range The name of the company of the customer.
Phone String False Range The primary telephone number for the customer.
Phone2 String False Range An alternate telephone or fax number for the customer.
Phone3 String False Range An alternate telephone or fax number for the customer.
Email String False Range The email address for communicating with the customer.
IsOkToEMail Boolean False Single Whether or not the customer can be notified via email.
CustomerType String False Range A customer type within QuickBooks POS.
Notes String False Range Notes on this customer.
DefaultShipAddress String False The default shipping address of the customer.
BillAddress_Street String False Street address of the billing address of the customer.
BillAddress_Street2 String False The second line of the street address of the billing address of the customer.
BillAddress_City String False City name for the billing address of the customer.
BillAddress_State String False State name for the billing address of the customer.
BillAddress_PostalCode String False Postal code for the billing address of the customer.
BillAddress_Country String False Country for the billing address of the customer.
IsNoShipToBilling Boolean False Single Whether or not the billing address can be used as a shipping address.
IsAcceptingChecks Boolean False Single Indicates whether checks are being accepted from this customer. The default is True.
IsUsingChargeAccount Boolean False Single Indicates whether the customer is using a charge account.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
TaxCategory String False Range The sales tax category.
CustomerDiscPercent Double False Range The suggested customer discount percentage for all items listed on a sales receipt. Note that you cannot assign both a discount percentage and a price level to a customer.
CustomerDiscType String False Single The customer discount type.
PriceLevelNumber String False Single The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
LastSale Datetime True Range The time of the last purchase made by this customer.
AmountPastDue Decimal True Range The amount past due for a transaction.
IsUsingWithQB Boolean False Single Whether or not the customer is using QuickBooks POS with QuickBooks.
AccountBalance Decimal True Range The balance of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
AccountLimit Decimal True Range The limit of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the customer was created.
TimeModified Datetime True Range When the customer was last modified.
ShipAddressAddressName# [KEY] String False The name used to identify this shipping address of the customer.
ShipAddressCompanyName# String False The company name of this shipping address of the customer.
ShipAddressFullName# String False The full name of the recipient in this shipping address of the customer.
ShipAddressStreet# String False Street address of this shipping address of the customer.
ShipAddressStreet2# String False The second line of the street address of this shipping address of the customer.
ShipAddressCity# String False City name for this shipping address of the customer.
ShipAddressState# String False State name for this shipping address of the customer.
ShipAddressPostalCode# String False Postal code for this shipping address of the customer.
ShipAddressCountry# String False Country for this shipping address of the customer.

Departments

Create, update, delete, and query QuickBooks POS Departments.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Departments WHERE DepartmentName LIKE '%WEAR%'
Insert

To create a new department record, the DepartmentName field is required.

INSERT INTO Departments (DepartmentName, DepartmentCode, DefaultMarginPercent)
VALUES ('Sports Wear', 'SPW', '7.25')
Update

Any field that is not read-only can be updated.

UPDATE Departments SET DefaultMarkupPercent = '20.50' WHERE ListId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
DepartmentName String False Range The name of the department.
DepartmentCode String False Range A unique, 1-3 character code identifying the department. The department code can be used when defining an item in inventory. The first character usually indicates the broadest classification.
DefaultMarginPercent Double False Range The default margin percentage. If you do not wish QBPOS to calculate prices for new items as they are added to inventory, leave this field and the DefaultMarkupPercent field at 0.
DefaultMarkupPercent Double False The default markup percentage.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
TaxCode String False Single The tax code of the department.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the department was created.
TimeModified Datetime True Range When the department was last modified.

Employees

Create, update, and query QuickBooks POS Employees.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Employees WHERE LoginName LIKE '%test%'
Insert

To create a new employee record, the LoginName field is required.

INSERT INTO Employees (LoginName, FirstName, LastName)
VALUES ('japple', 'John', 'Apple')
Update

Any field that is not read-only can be updated.

UPDATE Employees SET Phone = '555-123-9876' WHERE ListId = '-1234567890123456789'
Delete

Deleting an employee requires System Administrator privileges, which are only available by logging in directly to QuickBooks POS.

Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
LoginName String False Range The name the employee will use to log in to QBPOS. Because QBPOS requires that all employee login names be unique, it will append a number to the end of any login name that is the same as an existing one.
FirstName String False Range The first name of the employee.
LastName String False Range The last name of the employee.
Street String False Range Street address of the employee.
Street2 String False Range The second line of the street address of the employee.
City String False Range City name for the billing address of the employee.
State String False Range State name for the billing address of the employee.
PostalCode String False Range Postal code for the billing address of the employee.
Country String False Range Country for the billing address of the employee.
Phone String False Range The primary telephone number for the employee.
Phone2 String False Range An alternate telephone or fax number for the employee.
Phone3 String False Range An alternate telephone or fax number for the employee.
Email String False Range The email address for communicating with the employee.
Notes String False Range Notes on this employee.
IsTrackingHours Boolean False Single Indicates whether time is tracked for this employee.
CommissionPercent Double False Range If sales commissions are paid to this employee, this number specifies the percentage of that commission. You can enter numbers in the range from 0.00-99.99.
SecurityGroup String False Range The security group assigned to the employee.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the employee was created.
TimeModified Datetime True Range When the employee was last modified.

InventoryCostAdjustmentItems

Create and query QuickBooks POS Inventory Cost Adjustment Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM InventoryCostAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to add an item to an existing InventoryCostAdjustment record. To add an item to an existing record, the TxnId column of the InventoryCostAdjustment record that the item is to be added to, ItemListId, and ItemNewCost are required.

INSERT INTO InventoryCostAdjustmentItems (TxnId, ItemListId, ItemNewCost)
VALUES ('-1234567890123456789', '-1000000000000000004', '2.00')
Columns
Name Type ReadOnly Filter Type Description
TxnID String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
Comments String False Range Contains a description of the transaction.
CostDifference Decimal True Difference in cost for the items. The value displayed is equal to NewCost minus OldCost.
HistoryDocStatus String True Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo was an original memo that had to be corrected), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InventoryAdjustmentNumber Double True Range The adjustment number for an adjustment is assigned by QBPOS when an adjustment is successfully added to QBPOS.
InventoryAdjustmentSource String False Single The action or functionality that created the adjustment. Possible values are Manual (manually created memo), Physical (physical inventory), and Assembly (assembly item).
ItemsCount String True Range The number of line items in the insert that added the transaction.
NewCost Decimal True Range The cost of the items after the cost adjustment.
OldCost Decimal True Range The cost of the items before the cost adjustment.
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
Reason String False Range Enter the reason for the adjustment or select from the following predefined reasons: Cycl Cnt (cycle count), Vend Chg (vendor change), Prc Line (price line), Seas End (end of season), Annual, Shrink, Stolen, Damaged, and New Item.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemNewCost# Decimal False The new cost for each inventory item being adjusted.
ItemOldCost# Decimal True Inventory cost for this item before updating this memo.
ItemCostDifference# Decimal True Difference in cost for the item. The value displayed is equal to ItemNewCost minus ItemOldCost.
ItemNumberOfBaseUnits# Double True The base unit, used to track and report the inventory quantity.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.

InventoryCostAdjustments

Create, update, and query QuickBooks POS Inventory Cost Adjustments.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM InventoryCostAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new InventoryCostAdjustment record, the Reason field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the InventoryCostAdjustmentItems table as # columns. Note that ItemListId and ItemNewCost are required when adding an item.

The following example will insert a new InventoryCostAdjustment with two items:

INSERT INTO InventoryCostAdjustments (Reason, ItemsAggregate)
VALUES ('Discontinued', '<InventoryCostAdjustmentItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemNewCost>4.99</ItemNewCost></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemNewCost>11.97</ItemNewCost></Row>
</InventoryCostAdjustmentItems>')
Update

Any field that is not read-only can be updated.

When updating an InventoryCostAdjustment record, item costs can be adjusted via the ItemsAggregate column, just as in an insert. In such cases, the ItemListId column is required to identify the item being adjusted, and ItemNewCost is required to specify the new cost.

Note items cannot be removed from an InventoryCostAdjustment record, but rather the cost can only be adjusted, so a record of adjustments for an item is always kept.

UPDATE InventoryCostAdjustments SET ItemsAggregate = '<InventoryCostAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewCost>19.99</ItemNewCost></Row>
</InventoryCostAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
Comments String False Range Contains a description of the transaction.
CostDifference Decimal True Difference in cost for the items. The value displayed is equal to NewCost minus OldCost.
HistoryDocStatus String True Single The current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of an earlier memo), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InventoryAdjustmentNumber Double True Range The adjustment number for an adjustment is automatically assigned by QBPOS when an adjustment is successfully added to QBPOS.
InventoryAdjustmentSource String False Single The action or functionality that created the adjustment. Possible values are Manual (manually created memo), Physical (physical inventory), and Assembly (assembly item).
ItemsCount String True Range The number of line items in the insert that added the transaction.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding a transaction and its line items.
NewCost Decimal True Range The cost of the items after the cost adjustment.
OldCost Decimal True Range The cost of the items before the cost adjustment.
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
Reason String False Range Enter the reason for the adjustment or select from the following predefined reasons: Cycl Cnt (cycle count), Vend Chg (vendor change), Prc Line (price line), Seas End (end of season), Annual, Shrink, Stolen, Damaged, and New Item.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.

InventoryQtyAdjustmentItems

Create and query QuickBooks POS Inventory Quantity Adjustment Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM InventoryQtyAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to add an item to an existing InventoryQtyAdjustment record. To add an item to an existing record, the TxnId column of the InventoryQtyAdjustment record that the item is to be added to, ItemListId, and ItemNewQuantity are required.

INSERT INTO InventoryQtyAdjustmentItems (TxnId, ItemListId, ItemNewQuantity)
VALUES ('-1234567890123456789', '-1000000000000000004', '10')
Columns
Name Type ReadOnly Filter Type Description
TxnID String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
Comments String False Range Contains a description of the transaction.
CostDifference Decimal True Difference in cost for the items. The value displayed is equal to the new cost minus the old cost.
HistoryDocStatus String True Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of the original memo), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InventoryAdjustmentNumber Double True Range The adjustment number, assigned by QBPOS when an adjustment is successfully added to QBPOS.
InventoryAdjustmentSource String False Single The action or functionality that created the adjustment. Possible values are Manual (manually created memo), Physical (physical inventory), and Assembly (Assembly item).
ItemsCount String True Range The number of line items in the insert that added the transaction.
NewQuantity Double True Range The new quantity of the items being adjusted.
OldQuantity Double True Range The quantity of the items prior to the adjustment.
QtyDifference Double True Difference in quantity for the items. The value displayed is equal to NewQuantity minus OldQuantity
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
Reason String False Range Enter the reason for the adjustment or select from the following predefined reasons: Cycl Cnt (cycle count), Vend Chg (vendor change), Prc Line (price line), Seas End (end of season), Annual, Shrink, Stolen, Damaged, and New Item.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemNewQuantity# Double False The new quantity for each inventory item being adjusted.
ItemOldQuantity# Double True Inventory quantity for this item before updating this memo.
ItemQtyDifference# Double True Difference in quantity for the item. The value displayed is equal to ItemNewQuantity minus ItemOldQuantity.
ItemNumberOfBaseUnits# Double True The item serial number, used for purposes of warranty tracking or meeting legal requirements. Once recorded, former documents can be quickly located by searching by serial number.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.

InventoryQtyAdjustments

Create, update, and query QuickBooks POS Inventory Quantity Adjustments.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM InventoryCostAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new InventoryQtyAdjustment record, the Reason field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the InventoryQtyAdjustmentItems table as # columns. Note that ItemListId and ItemNewQuantity are required when adding an item.

The following example will insert a new InventoryQtyAdjustment with two items:

INSERT INTO InventoryQtyAdjustments (Reason, ItemsAggregate)
VALUES ('New Shipment', '<InventoryQtyAdjustmentItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemNewQuantity>10</ItemNewQuantity></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemNewQuantity>20</ItemNewQuantity></Row>
</InventoryQtyAdjustmentItems>')
Update

Any field that is not read-only can be updated.

When updating an InventoryQtyAdjustment record, item quantities can be adjusted via the ItemsAggregate column, just as in an insert. In such cases, the ItemListId column is required to identify the item being adjusted and ItemNewQuantity is required to specify the new quantity.

Note items cannot be removed from an InventoryQtyAdjustment record, but rather the cost can only be adjusted, so a record of adjustments for an item is always kept.

UPDATE InventoryQtyAdjustments SET ItemsAggregate = '<InventoryQtyAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewQuantity>30</ItemNewQuantity></Row>
</InventoryQtyAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
Comments String False Range Contains a description of the transaction.
CostDifference Decimal True Difference in cost for the items. The value displayed is equal to the new cost minus the old cost.
HistoryDocStatus String True Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of the original memo), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InventoryAdjustmentNumber Double True Range The adjustment number, generated by QuickBooks POS.
InventoryAdjustmentSource String False Single The action or functionality that created the adjustment. Possible values are Manual (manually created memo), Physical (physical inventory), and Assembly (assembly item).
ItemsCount String True Range The number of line items in the request that added the transaction.
ItemsAggregate String False An aggregate of the line item data, which can be used to add a transaction and its line items.
NewQuantity Double True Range The new quantity of the items being adjusted.
OldQuantity Double True Range The quantity of the items prior to the adjustment.
QtyDifference Double True Difference in quantity for the items. The value displayed is equal to NewQuantity minus OldQuantity.
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
Reason String False Range Enter the reason for the adjustment or select from the following predefined reasons: Cycl Cnt (cycle count), Vend Chg (vendor change), Prc Line (price line), Seas End (end of season), Annual, Shrink, Stolen, Damaged, and New Item.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) or Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.

ItemPictures

Create, update, and query QuickBooks POS Item Pictures.

Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
PictureName# String False The name of the item picture.
EncodedPicture# String False The Base64 encoded item picture.

Items

Create, update, delete, and query QuickBooks POS Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Items WHERE QuantityOnHand < 5
Insert

To create a new item record, the DepartmentListId field is required.

INSERT INTO Items (DepartmentListId, Desc1, Cost)
VALUES ('-1234567890123456789', 'T-Shirt', '9.99')
Update

Any field that is not read-only can be updated.

UPDATE Items SET Size = 'L' WHERE ListId = '-1000000000000000001'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
ALU String False Range Alternate lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
Attribute String False Range A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
COGSAccount String False Range The Cost of Goods Sold (COGS) Account for the item. This field is used if you are using QBPOS with QuickBooks.
Cost Decimal False Range The averaged cost of the current on-hand quantity of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos.
DepartmentListID String False Single The ID of the department the item is associated with.
DepartmentCode String True Range A unique, 1-3 character code identifying the department. The department code can be used when defining an item in inventory. The first character usually indicates the broadest classification.
Desc1 String False Range The principal item description field. This description is printed on receipts.
Desc2 String False Range Secondary description field, usually used for any additional information to describe this particular item, such as the catalog number of a vendor. Also can be used for additional information at the point of sale.
IncomeAccount String False Range The Income Account for the item. This field is used if you are using QBPOS with QuickBooks.
IsBelowReorder Boolean True Single Indicates whether the item is below the reorder point.
IsEligibleForCommission Boolean False Single Indicates whether the item is eligible for a commission.
IsPrintingTags Boolean False Single Indicates whether the Print Tags checkbox is selected for this item, that is, whether this item is included in the list of items for which price tags are to be printed.
IsUnorderable Boolean False Single Indicates whether the item is orderable. Items flagged as Unorderable do not show up on the reorder reminder list when their on-hand number falls below the reorder point.
HasPictures Boolean True Single Indicates whether the item has pictures to identify it.
IsEligibleForRewards Boolean False Single Indicates whether the item is eligible for rewards.
IsWebItem Boolean False Single Indicates whether the item is sold online.
ItemNumber Double True Range Unique number assigned to the item when it is added to QBPOS.
ItemType String False Single The type of item being added. Possible values are Inventory, NonInventory, Service, Assembly, Group, and SpecialOrder.
LastReceived Datetime True Range Date this item was last received into inventory.
MarginPercent Double False The profit, expressed as a percentage of the price. If defined in a department record, this value is used to calculate the prices of new items as they are added to inventory. Note that modifying the margin in inventory causes QBPOS to recalculate your prices and the Markup Percent.
MarkupPercent Double False The profit, expressed as a percentage over the cost. Works in the same manner as Margin Percent. Making an entry in this field causes QBPOS to recalculate your item prices and margin.
MSRP Double False Range Manufacturer's suggested retail price. Reference price that is printed on price tags if defined in inventory.
OnHandStore01 Double False Range The quantity of the item on hand in Store 1 inventory.
OnHandStore02 Double False Range The quantity of the item on hand in Store 2 inventory.
ReorderPointStore01 Double False Range The quantity at which an item should be reordered for Store 1.
ReorderPointStore02 Double False Range The quantity at which an item should be reordered for Store 2.
OrderByUnit String False Range The default unit of measure when you order this item from vendors. If you are using multiple units of measure instead of a single unit of measure, you can use one unit of measure to order by (OrderByUnit) and another to sell by (SellByUnit). You can override this by specifying a different unit of measure when you insert a purchase order.
OrderCost Decimal False Range The current cost paid to the vendor for the item.
Price1 Decimal False Range The baseline or everyday retail price of the item.
Price2 Decimal False Range Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences.
Price3 Decimal False Range Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences.
Price4 Decimal False Range Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences.
Price5 Decimal False Range Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences.
QuantityOnCustomerOrder String True Range The quantity, in terms of the base unit of measure, of the inventory item that is currently under order by customers and therefore not available.
QuantityOnHand String True Range The number of units of an item currently in stock. This number is updated by receiving vouchers and sales receipts.
QuantityOnOrder String True Range The number of units of the item that are currently on order. In multistore configurations, this number is the on-order number for all of the stores.
QuantityOnPendingOrder String True Range The number of units of the item that are currently pending on order.
ReorderPoint Double False Range The quantity at which an item should be reordered.
SellByUnit String False Range The default unit of measure when you sell the item. You can override this by specifying a different unit of measure when you insert a sales receipt.
SerialFlag String False Single The serial flag indicates whether the user is prompted to enter a serial number when the item is listed on documents. Enter Prompt to be prompted to enter a serial number; enter Optional (default), and the user will not be prompted but can still enter a serial number on the document.
Size String False Range The size of an item. This field can also be used to describe a second item attribute other than size. 1-8 characters are allowed.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
TaxCode String False Single The tax code that is actually applied at transaction time is the tax code of the tax category that is currently specified as the default tax category in the sales tax preferences.
UnitOfMeasure String False Range If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item. Inventory quantities are tracked and reported in terms of this base unit.
UnitOfMeasure1 String False Aggregate value of the first unit of measure.
UnitOfMeasure2 String False Aggregate value of the second unit of measure.
UnitOfMeasure3 String False Aggregate value of the third unit of measure.
UPC String False Range The UPC/EAN/ISBN of the item. This field must be 13 characters.
VendorCode String True Range The code assigned to the vendor specified for the item.
VendorListID String False Single A reference to the vendor.
VendorInfo2 String False Info for the second vendor.
VendorInfo3 String False Info for the third vendor.
VendorInfo4 String False Info for the fourth vendor.
VendorInfo5 String False Info for the fifth vendor.
WebDesc String False Range The description of the item for use online.
WebPrice Decimal False Range The price of the item when sold online.
Manufacturer String False Range The manufacturer of the item.
Weight Double False Range The weight of the item.
WebSKU String True Range The SKU of the item for online use.
Keywords String False Range Keywords to identify the item.
WebCategories String False Categories to identify the item online.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the item was created.
TimeModified Datetime True Range When the item was last modified.

PriceAdjustmentItems

Create, update, delete, and query QuickBooks POS Price Adjustment Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PriceAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new PriceAdjustment record or to add an item to an existing PriceAdjustment record.

To create a new PriceAdjustment record, the PriceAdjustmentName field is required.

INSERT INTO PriceAdjustmentItems (PriceAdjustmentName, ItemListId, ItemNewPrice)
VALUES ('New Adjustment', '-1000000000000000001', '10.00')

To add an item to an existing record, the TxnId column of the PriceAdjustment record that the item is to be added to and the ItemListId are required.

INSERT INTO PriceAdjustmentItems (TxnId, ItemListId, ItemNewPrice)
VALUES ('-1234567890123456789', '-1000000000000000001', '10.00')
Update

Any field that is not read-only can be updated.

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE PriceAdjustmentItems SET ItemNewPrice = '30.00' WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '1'
Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that

are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM PriceAdjustmentItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
AppliedBy String True Range The employee who applied the price adjustment.
Comments String False Range Contains a description of the transaction.
DateApplied Datetime True Range Date the price adjustment was applied.
DateRestored Datetime True Range Date the price adjustment was restored.
ItemsCount String True Range The number of line items in the insert that added the transaction.
PriceAdjustmentName String False Range The name of the price adjustment.
PriceAdjustmentStatus String True Single The status of the price adjustment.
PriceLevelNumber String False Single The suggested price level discount given to the customer.
RestoredBy String True Range The employee who restored the price adjustment.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated. Possible values are Modified, Sent, and Acknowledged.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemTxnLineID# [KEY] String True Identification number of the transaction line. (TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero.) If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemNewPrice# Decimal False The price of the item after the price adjustment.
ItemOldPrice# Decimal True The price of the item before the price adjustment.
ItemOldCost# Decimal True Inventory cost for this item before updating this memo.

PriceAdjustments

Create, update, and query QuickBooks POS Price Adjustments.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PriceAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new PriceAdjustment record, the PriceAdjustmentName field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PriceAdjustmentItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new PriceAdjustment with two items:

INSERT INTO PriceAdjustments (PriceAdjustmentName, ItemsAggregate)
VALUES ('New Adjustment', '<PriceAdjustmentItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemNewPrice>10.00</ItemNewPrice></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemNewPrice>20.00</ItemNewPrice></Row>
</PriceAdjustmentItems>')
Update

Any field that is not read-only can be updated.

When updating a PriceAdjustment record, item prices can be added or modified via the ItemsAggregate column.

To modify an existing item in a PriceAdjustment record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item to be added must be specified (just as in an insert).

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of the existing items.

Modify Items Example: Total three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.

UPDATE PriceAdjustments SET ItemsAggregate = '<PriceAdjustmentItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemNewPrice>30.00</ItemNewPrice></Row>
<Row><ItemTxnLineId>2</ItemTxnLineId></Row>
</PriceAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE PriceAdjustments SET ItemsAggregate = '<PriceAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewPrice>30.00</ItemNewPrice></Row>
</PriceAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
AppliedBy String True Range The employee who applied the price adjustment.
Comments String False Range Contains a description of the transaction.
DateApplied Datetime True Range Date the price adjustment was applied.
DateRestored Datetime True Range Date the price adjustment was restored.
ItemsCount String True Range The number of line items in the insert that added the transaction.
ItemsAggregate String False An aggregate of the line item data, which can be used to add a transaction and its line items.
PriceAdjustmentName String False Range The name of the price adjustment.
PriceAdjustmentStatus String True Single The status of the price adjustment.
PriceLevelNumber String False Single The suggested price level discount for the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
RestoredBy String True Range The employee who restored the price adjustment.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.

PriceDiscountItems

Create, update, delete, and query QuickBooks POS Price Discount Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PriceDiscountItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new PriceDiscount record or to add an item to an existing PriceDiscount record.

To create a new PriceDiscount record, the PriceDiscountName and PriceDiscountReason fields are required.

INSERT INTO PriceDiscountItems (PriceDiscountName, PriceDiscountReason, PriceDiscountXValue, ItemListId, ItemUnitOfMeasure)
VALUES ('New Discount', 'Sale', '10', '-1000000000000000001', 'lbs')

To add an item to an existing record, the TxnId column of the PriceDiscount record that the item is to be added to and the ItemListId are required.

INSERT INTO PriceDiscountItems (TxnId, ItemListId, ItemUnitOfMeasure)
VALUES ('-1234567890123456789', '-1000000000000000001', 'lbs')
Update

Any field that is not read-only can be updated.

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE PriceDiscountItems SET ItemUnitOfMeasure = 'lbs' WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '1'
Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that

are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM PriceDiscountItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
PriceDiscountName String False Range The name of the price discount.
PriceDiscountReason String False Range The status of the price discount.
PriceDiscountType String False Single The type of price discount.
Associate String False Range The employee making the adjustment.
LastAssociate String True Range The employee who made the last adjustment transaction.
ItemsCount String True Range The number of line items in the insert that added the transaction.
StartDate Datetime False Range The starting date of the discount.
StopDate Datetime False Range The ending date of the discount.
IsInactive Boolean False Single Determines whether the discount is active.
PriceDiscountPriceLevels String False The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PriceDiscountXValue Decimal False Range The discount price over the X value.
PriceDiscountYValue Decimal False Range The discount price over the Y yalue.
IsApplicableOverXValue Boolean False Single Determines whether the discount is applicable over the X value.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemTxnLineID# [KEY] String True Identification number of the transaction line. (TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero.) If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.

PriceDiscounts

Create, update, and query QuickBooks POS Price Discounts.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PriceDiscounts WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new PriceDiscount record, the PriceDiscountName and PriceDiscountReason fields are required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PriceDiscountItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new PriceDiscount with two items:

INSERT INTO PriceDiscounts (PriceDiscountName, PriceDiscountReason, PriceDiscountXValue, ItemsAggregate)
VALUES ('New Discount', 'Sale', '10', '<PriceDiscountItems>
<Row><ItemListId>-1000000000000000001</ItemListId></Row>
<Row><ItemListId>-1000000000000000002</ItemListId></Row>
</PriceDiscountItems>')
Update

Any field that is not read-only can be updated.

When updating a PriceDiscount record, items can be added or modified via the ItemsAggregate column.

To modify an existing item in a PriceDiscount record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item that is to be added must be specified, just as in an insert.

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of only the existing items.

Modify Items Example: Total two items with ItemTxnLineId values of 1 and 2. Item 1 is updated and Item 2 will be deleted.

UPDATE PriceDiscounts SET ItemsAggregate = '<PriceDiscountItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemUnitOfMeasure>lbs</ItemUnitOfMeasure></Row>
</PriceDiscountItems>'
WHERE TxnId = '-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE PriceDiscounts SET ItemsAggregate = '<PriceDiscountItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemUnitOfMeasure>lbs</ItemUnitOfMeasure></Row>
</PriceDiscountItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
PriceDiscountName String False Range The name of the price discount.
PriceDiscountReason String False Range The status of the price discount.
PriceDiscountType String False Single The type of price discount.
Associate String False Range The employee making the adjustment.
LastAssociate String True Range The employee who made the last adjustment transaction.
ItemsCount String True Range The number of line items in the insert that added the transaction.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding the transaction and its line items.
StartDate Datetime False Range The starting date of the discount.
StopDate Datetime False Range The ending date of the discount.
IsInactive Boolean False Single Determines whether the discount is active.
PriceDiscountPriceLevels String False The price levels the discount should be applied to.
PriceDiscountXValue Decimal False Range The discount price over the X value.
PriceDiscountYValue Decimal False Range The discount price over the Y value.
IsApplicableOverXValue Boolean False Single Determines whether the discount is applicable over the X value.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.

PurchaseOrderItems

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

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PurchaseOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new Purchase Order record or to add an item to an existing Purchase Order record.

To create a new Purchase Order record, the PurchaseOrderNumber and VendorListId fields are required.

INSERT INTO PurchaseOrderItems (PurchaseOrderNumber, VendorListId, ItemListId, ItemCost, ItemQty)
VALUES ('PO12345', '-9876543210987654321', '-1000000000000000001', 15.00, 10)

To add an item to an existing record, the TxnId column of the Purchase Order record that the item is to be added to and the ItemListId are required.

INSERT INTO PurchaseOrderItems (TxnId, ItemListId, ItemCost, ItemQty)
VALUES ('-1234567890123456789', '-1000000000000000001', 15.00, 10)
Update

Any field that is not read-only can be updated.

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE PurchaseOrderItems SET ItemSize = 'L' WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '1'
Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that

are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM PurchaseOrderItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
CancelDate Datetime False Range Enter the date after which delivery may be refused. If the company preferences for receiving are set to check for cancel dates, the end user is alerted when receiving items against a purchase order past this cancel date. (That user can still accept the shipment, at their discretion.) If the company preferences for receiving are not set to check the cancel date, the cancel date is ignored.
CompanyName String True Range The name of the business.
Discount Decimal False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the transaction. Entering a discount percentage causes the Discount field to be automatically calculated.
Fee Decimal False Range You can enter a fee using this field. Note that fees entered on a purchase order are not spread over the cost of individual items. When a receiving voucher references a purchase order with a fee entered, the fee can be transferred to the voucher at that point.
Instructions String False Range You can use this optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on purchase orders but is not displayed on-screen.
ItemsCount String True Range The number of line items in the insert that added the transaction.
PurchaseOrderNumber String False Range The user-defined number identifying the purchase order. This value should be unique. This number is assigned to the purchase order by QBPOS if left empty during the creation process.
PurchaseOrderStatusDesc String False Single All purchase orders have a status of either Open, Closed, or Custom. The Custom status is defined by the QBPOS user in the company preferences for purchasing.
QtyDue Double True The total document quantity remaining to be received. Updated by receiving vouchers.
QtyOrdered Double True Range The total purchase order quantity ordered, combined for all items.
QtyReceived Double True Range The total document quantity received to date.
SalesOrderNumber String True Range A unique number assigned to the sales order at creation by QBPOS. If a sales order was used to create the purchase order, the SalesOrderNumber for the purchase order is returned.
ShipToStoreNumber Double False Range The store to which the item is to be shipped.
StartShipDate Datetime False Range The estimated shipping date for the order. Included on purchase journal and merchandise on-order reports.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of the extended item costs of the transaction, before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
Terms String True Although not visible on the purchase order, any payment terms that are defined for the vendor file are carried to the purchase order.
TermsDiscount Decimal False Range The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays Double False Range The number of days after the invoice date in which payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays Double False Range Payment must be made within this number of days, counting from the invoice date.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
UnfilledPercent String True Range The percentage of the total purchase order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
VendorCode String True Range The code assigned to the vendor.
VendorListID String False Single A reference to the vendor.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemTxnLineID# [KEY] String True Identification number of the transaction line. (TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero.) If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemALU# String False Alternate lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAttribute# String False A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
ItemCost# Decimal False The average cost of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos.
ItemDesc1# String False The principal item description field. This description is printed on receipts.
ItemDesc2# String False Secondary description field, usually used for any additional information to describe this particular item, such as a vendor's catalog number. Also can be used for additional information at the point of sale.
ItemExtendedCost# Decimal False The quantity ordered multiplied by the item cost.
ItemItemNumber# Double True Unique number assigned to the item when it is added to QBPOS.
ItemNumberOfBaseUnits# Double True The base unit, used to track and report the inventory quantity.
ItemQty# Double False The quantity of the line item being ordered or transferred.
ItemQtyReceived# Double True The item quantity received to date.
ItemSize# String False The size of an item. This field can also be used to describe a second item attribute other than size if desired. This field must be 1-8 characters.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC# String False The UPC, EAN, or ISBN of the item. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.

PurchaseOrders

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

Table Specific Information

The PurchaseOrders table allows you to SELECT, INSERT, UPDATE, and DELETE Purchase Orders within QuickBooks POS.

Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM PurchaseOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new Purchase Order record, the PurchaseOrderNumber and VendorListId fields are required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PurchaseOrderItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Purchase Order with two items:

INSERT INTO PurchaseOrders (PurchaseOrderNumber, VendorListId, ItemsAggregate)
VALUES ('PO12345', '-9876543210987654321', '<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemCost>10.00</ItemCost><ItemQty>25</ItemQty></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemCost>20.00</ItemCost><ItemQty>25</ItemQty></Row>
</PurchaseOrderItems>')
Update

Any field that is not read-only can be updated.

When updating a Purchase Order record, items can be added or modified via the ItemsAggregate column.

To modify an existing item in a Purchase Order record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item to be added must be specified, just as in an insert.

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of only the existing items.

Modify Items Example: Total three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.

UPDATE PurchaseOrders SET ItemsAggregate = '<PurchaseOrderItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemSize>L</ItemSize></Row>
<Row><ItemTxnLineId>2</ItemTxnLineId></Row>
</PurchaseOrderItems>'
WHERE TxnId = '-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE PurchaseOrders SET ItemsAggregate = '<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>30.00</ItemCost><ItemQty>10</ItemQty></Row>
</PurchaseOrderItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the adjustment.
CancelDate Datetime False Range The date after which delivery may be refused. If the company preferences for receiving are set to check for cancel dates, the end user is alerted when receiving items against a purchase order past this cancel date. (The user can still accept shipments, at their discretion.) If the company preferences for receiving are not set to check the cancel date, the cancel date is ignored.
CompanyName String True Range The name of the business.
Discount Double False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the purchase order. Discounts are applied to the purchase order subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Fee Decimal False Range You can enter a fee using this field. Note that fees entered on a purchase order (such as a special handling fee) are not spread over the cost of individual items. When a receiving voucher references a purchase order with a fee entered, the fee can be transferred to the voucher at that point.
Instructions String False Range You can use this optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on purchase orders but is not displayed on-screen.
ItemsCount String True Range The number of line items in the insert that added the transaction.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding a transaction and its line items.
PurchaseOrderNumber String False Range A user-defined number identifying the purchase order. This value should be unique. This number is automatically assigned to the purchase order by QBPOS if left empty during the creation process.
PurchaseOrderStatusDesc String False Single All purchase orders have the status of either Open, Closed, or Custom. The Custom status is defined by the QBPOS user in the company preferences for purchasing.
QtyDue Double True The total document quantity remaining to be received. Updated by receiving vouchers.
QtyOrdered Double True Range The total quantity ordered, combined for all items.
QtyReceived Double True Range The total quantity received to date.
SalesOrderNumber String True Range This is a unique number automatically assigned to the sales order at creation by QBPOS. If a sales order was used to create the purchase order, the SalesOrderNumber for the purchase order is returned.
ShipToStoreNumber Double False Range The store to which the item is to be shipped.
StartShipDate Datetime False Range The estimated shipping date for the order. Included on purchase journal and merchandise on-order reports.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
Terms String True While not visible on the purchase order, any payment terms that are defined for the vendor file are carried to the purchase order.
TermsDiscount Decimal False Range The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays Double False Range The number of days after the invoice date when payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays Double False Range Payment must be made within this number of days, counting from the invoice date.
Total Decimal True Range The total amount, after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
UnfilledPercent String True Range The percentage of the total purchase order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
VendorCode String True Range The code assigned to the vendor.
VendorListID String False Single A reference to the vendor.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.

SalesOrderItems

Create, update, delete, and query QuickBooks POS Sales Order Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM SalesOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new Sales Order record or to add an item to an existing Sales Order record.

To create a new Sales Order record, the PurchaseOrderNumber and VendorListId fields are required.

INSERT INTO SalesOrderItems (CustomerListId, SalesOrderNumber, ItemListId, ItemCost, ItemQty)
VALUES ('-9876543210987654321', 'SO12345', '-1000000000000000001', 15.00, 1)

To add an item to an existing record, the TxnId column of the Sales Order record that the item is to be added to and the ItemListId are required.

INSERT INTO SalesOrderItems (TxnId, ItemListId, ItemCost, ItemQty)
VALUES ('-1234567890123456789', '-1000000000000000001', 15.00, 1)
Update

Any field that is not read-only can be updated.

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE SalesOrderItems SET ItemSize = 'L' WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '1'
Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that

are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM SalesOrderItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
CustomerListID String False Single The reference key to the customer.
Associate String False Range The employee making the adjustment.
BalanceDue Decimal True Range The balance remaining on the order.
Cashier String False Range Name of the employee taking the sales order. This value is autofilled with the logged-in employee name if logins are required.
DepositBalance Decimal True The sum of all deposits received, less any deposits already used.
Discount Decimal False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the transaction. Discounts are applied to the subtotal. Entering the Discount Percent causes the Discount field to be automatically calculated.
Instructions String False Range Optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on purchase orders, but is not displayed on-screen.
ItemsCount String True Range The number of line items in the insert that added the transaction.
PriceLevelNumber String False Single The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PromoCode String False Range A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.
Qty Double True Range The quantity of the line item being ordered or transferred.
SalesOrderNumber String False Range This is a unique number assigned by QBPOS to the sales order at creation.
SalesOrderStatusDesc String False Current status of the order. Possible values are OPEN, CLOSED, or a custom status you have defined in the company preferences. Used to track and filter the progression of orders for viewing and reporting.
SalesOrderType String False The type of the customer order document. Possible values are SalesOrder, Layaway, WorkOrder, and WebOrder.
StoreExchangeStatus String True In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
Subtotal Decimal True Range The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
TaxAmount Decimal True Range The tax amount, returned in the response to inserting a non-held transaction.
TaxCategory String False Range The tax category (tax location).
TaxPercentage String False Range The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
UnfilledPercent String True Range The percentage of the total purchase order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
BillingInformation_Salutation String True A salutation, such as Mr., Mrs., etc.
BillingInformation_FirstName String True The first name of the customer as stated in the address info.
BillingInformation_LastName String True The last name of the customer as stated in the address info.
BillingInformation_CompanyName String True The name of the company of the customer.
BillingInformation_Phone String True The primary telephone number for the customer.
BillingInformation_Phone2 String True An alternate telephone or fax number for the customer.
BillingInformation_Phone3 String True An alternate telephone or fax number for the customer.
BillingInformation_Street String True Street address of the billing address of the customer.
BillingInformation_Street2 String True The second line of the street address of the billing address of the customer.
BillingInformation_City String True City name for the billing address of the customer.
BillingInformation_State String True State name for the billing address of the customer.
BillingInformation_PostalCode String True Postal code for the billing address of the customer.
BillingInformation_Country String True Country for the billing address of the customer.
ShippingInformation_AddressName String False The name used to identify the shipping address of the customer.
ShippingInformation_CompanyName String False The company name of the shipping address of the customer.
ShippingInformation_FullName String False The recipient's full name of the shipping address of the customer.
ShippingInformation_Phone String False The primary telephone number for the shipping address of the customer.
ShippingInformation_Street String False Street address of the shipping address of the customer.
ShippingInformation_Street2 String False The second line of the street address of the shipping address of the customer.
ShippingInformation_City String False City name for the shipping address of the customer.
ShippingInformation_State String False State name for the shipping address of the customer.
ShippingInformation_PostalCode String False Postal code for the shipping address of the customer.
ShippingInformation_Country String False Country for the shipping address of the customer.
ShippingInformation_ShipBy String False The shipping method used to send merchandise to a customer.
ShippingInformation_Shipping Decimal False Indicates the amount to be paid for shipping.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
ItemTxnLineID# [KEY] String True Identification number of the transaction line. TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero. If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemALU# String False Alternate lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAssociate# String False The employee making the adjustment transaction.
ItemAttribute# String False A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
ItemCommission# Decimal False The commission amount for the item, based on the item price and the Associates commission percent, if the item is eligible for commissions.
ItemDesc1# String False The principal item description field. This description is printed on receipts.
ItemDesc2# String False Secondary description field usually used for any additional information to describe this particular item, such as the catalog number of the vendor. Also can be used for additional information at the point of sale.
ItemDiscount# Decimal False The amount of a discount applicable to the transaction. Discounts are applied to the transaction subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
ItemDiscountPercent# Double False A percentage discount applicable to the item. Discounts are applied to the transaction subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
ItemDiscountType# String False The reason for an item discount.
ItemExtendedPrice# Decimal True The extended price of a line item.
ItemExtendedTax# Decimal True Total tax per line item in the order.
ItemItemNumber# Double True Unique number assigned by QBPOS to the item when it is added.
ItemNumberOfBaseUnits# Double True The base unit, used to track and report the inventory quantity.
ItemPrice# Decimal False The purchase price or sales price of this item.
ItemPriceLevelNumber# String True The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
ItemQty# Double False The quantity of the line item being ordered or transferred.
ItemQtySold# Double True The quantity of the ordered item that is sold to the customer.
ItemSerialNumber# String False Item serial numbers can be recorded in QBPOS for purposes of warranty tracking or meeting legal requirements.
ItemSize# String False The size of an item. This field can also be used to describe a second item attribute other than size if desired. This field must be 1-8 characters.
ItemTaxAmount# String True The tax amount for the item. Either a tax amount or a tax percentage can be specified, but not both; the other value and the document total tax amounts will be recomputed to reflect the value you specify.
ItemTaxCode# String False The tax code of the item.
ItemTaxPercentage# String True The tax percentage for the item. Either a tax amount or a tax percentage can be specified, but not both; the other value and the document total tax amounts will be recomputed to reflect the value you specify.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC# String False The UPC/EAN/ISBN of the item. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.
ItemWebDesc# String True The description of the item for use online.
ItemManufacturer# String True The manufacturer of the item.
ItemWeight# Double True The weight of the item.
ItemWebSKU# String True The SKU of the item for online use.

SalesOrders

Create, update, delete, and query QuickBooks POS Sales Orders.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM SalesOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new Sales Orders record, the CustomerListId field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the SalesOrderItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Sales Order with two items:

INSERT INTO SalesOrders (CustomerListId, SalesOrderNumber, ItemsAggregate)
VALUES ('-9876543210987654321', 'SO12345', '<SalesOrderItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemPrice>15.00</ItemPrice><ItemQty>1</ItemQty></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemPrice>25.00</ItemPrice><ItemQty>1</ItemQty></Row>
</SalesOrderItems>')
Update

Any field that is not read-only can be updated.

When updating a Sales Order record, items can be added or modified via the ItemsAggregate column.

To modify an existing item in a Sales Order record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item to be added must be specified, just as in an insert.

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of only the existing items.

Modify Items Example: Total of three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.

UPDATE SalesOrders SET ItemsAggregate = '<SalesOrderItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemSize>L</ItemSize></Row>
<Row><ItemTxnLineId>2</ItemTxnLineId></Row>
</SalesOrderItems>'
WHERE TxnId = '-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE SalesOrders SET ItemsAggregate = '<SalesOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>40.00</ItemCost><ItemQty>1</ItemQty></Row>
</SalesOrderItems>'
WHERE TxnId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
CustomerListID String False Single A reference to the customer.
Associate String False Range The employee making the adjustment.
BalanceDue Decimal True Range The balance remaining on the order.
Cashier String False Range Name of the employee taking the sales order. Note that this value is autofilled with the logged-in employee name if logins are required.
DepositBalance Decimal True The sum of all deposits received, less any deposits already used.
Discount Decimal False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the transaction. Discounts are applied to the subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Instructions String False Range Optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on sales orders, but is not displayed on-screen.
ItemsCount String True Range The number of line items in the request that added the transaction to QBPOS.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding a transaction and its line items.
PriceLevelNumber String False Single The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PromoCode String False Range A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.
Qty Double True Range The quantity of the line item being ordered or transferred.
SalesOrderNumber String False Range This is a unique number assigned by QBPOS to the sales order at creation.
SalesOrderStatusDesc String False Current status of the order. Possible values are OPEN, CLOSED, or a custom status you have defined in the company preferences. Used to track and filter the progression of orders for viewing and reporting.
SalesOrderType String False The type of the customer order document. Possible values are SalesOrder, Layaway, WorkOrder, and WebOrder.
StoreExchangeStatus String True In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated. Possible values are Modified, Sent, and Acknowledged.
Subtotal Decimal True Range The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
TaxAmount Decimal True Range The tax amount, returned in the response to inserting a non-held transaction.
TaxCategory String False Range The tax category (tax location).
TaxPercentage String False Range The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
Total Decimal True Range The total amount, after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
UnfilledPercent String True Range The percentage of the total sales order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
BillingInformation_Salutation String True A salutation, such as Mr., Mrs., etc.
BillingInformation_FirstName String True The first name of the customer as stated in the address info.
BillingInformation_LastName String True The last name of the customer as stated in the address info.
BillingInformation_CompanyName String True The name of the company of the customer.
BillingInformation_Phone String True The primary telephone number for the customer.
BillingInformation_Phone2 String True An alternate telephone or fax number for the customer.
BillingInformation_Phone3 String True An alternate telephone or fax number for the customer.
BillingInformation_Street String True Street address of the billing address of the customer.
BillingInformation_Street2 String True The second line of the street address of the billing address of the customer.
BillingInformation_City String True City name for the billing address of the customer.
BillingInformation_State String True State name for the billing address of the customer.
BillingInformation_PostalCode String True Postal code for the billing address of the customer.
BillingInformation_Country String True Country for the billing address of the customer.
ShippingInformation_AddressName String False The name used to identify the shipping address of the customer.
ShippingInformation_CompanyName String False The company name of the shipping address of the customer.
ShippingInformation_FullName String False The recipient's full name of the shipping address of the customer.
ShippingInformation_Phone String False The primary telephone number for the shipping address of the customer.
ShippingInformation_Street String False Street address of the shipping address of the customer.
ShippingInformation_Street2 String False The second line of the street address of the shipping address of the customer.
ShippingInformation_City String False City name for the shipping address of the customer.
ShippingInformation_State String False State name for the shipping address of the customer.
ShippingInformation_PostalCode String False Postal code for the shipping address of the customer.
ShippingInformation_Country String False Country for the shipping address of the customer.
ShippingInformation_ShipBy String False The shipping method used to send merchandise to a customer.
ShippingInformation_Shipping Decimal False Indicates the amount to be paid for shipping.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the sales order was created.
TimeModified Datetime True Range When the sales order was last modified.

SalesReceiptItems

Create, update, and query QuickBooks POS Sale Order Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM SalesReceiptItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new Sales Receipt record or to add an item to an existing Sales Receipt record.

To create a new Sales Receipt record, the CustomerListId is required along with an Item.

INSERT INTO SalesReceiptItems (SalesReceiptType, CustomerListId, SalesReceiptNumber, CashTenderAmount, ItemListId, ItemPrice, ItemQty)
VALUES ('Sales', '-9876543210987654321', 'SR12345', 2.15, '-1000000000000000001', 2.00, 1)

To add an item to an existing record, the TxnId column of the Sales Receipt record that the item is to be added to and the ItemListId are required.

INSERT INTO SalesReceiptItems (TxnId, CashTenderAmount, ItemListId, ItemPrice, ItemQty)
VALUES ('-1234567890123456789', 2.15, '-1000000000000000001', 2.00, 1)
Columns
Name Type ReadOnly Filter Type Description
ID String True Index line identifier, auto generated by .
TxnID String True Single The unique identifier, generated by QBPOS.
CustomerListID String False Single A reference to the customer.
Associate String False Range The employee making the adjustment.
Cashier String False Range Name of the employee making the transaction. Notice that this value is auto-filled with the logged-in employee name if logins are required.
Comments String False Range A description of the transaction.
Discount Decimal False Range The amount of a discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range The percentage discount applicable to the transaction. Discounts are applied to the order subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
HistoryDocStatus String False Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this is the corrected version of the original memo), Reversing (this memo caused an older memo to be reversed), Regular (the document has been corrected).
ItemsCount String True Range The number of line items in the request that added the transaction to QBPOS.
PriceLevelNumber String False Single The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PromoCode String False Range A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
SalesOrderTxnID String False Single A reference to the associated sales order, which will be automatically updated with changes to the sales receipt.
SalesReceiptNumber String False Range A unique number assigned to the receipt by QBPOS at creation.
SalesReceiptType String False Single The receipt type. Possible values are Sales, Return, Deposit, Refund, Payout, and Payin.
ShipDate Datetime False Range The date the merchandise shipped.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
TaxAmount String True Range The tax amount, returned in the response to inserting a non-held transaction.
TaxCategory String False Range The tax category (location).
TaxPercentage String False Range The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
TenderType String False Single Identifies which payment method was used for customer payment in the transaction. Possible values are None, Cash, Check, CreditCard, DebitCard, GiftCard, Account, Gift, Deposit, and Split.
TipReceiver String False Range The employee to whom the tip is to be paid.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TrackingNumber String True Range The number provided to customers by the shipping company to help them track merchandise location and progress during shipment.
TxnDate Date False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single Indicates the current state of the transaction. Possible values are Normal (indicates the transaction is complete) and Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
BillingInformation_Salutation String True A salutation, such as Mr., Mrs., etc.
BillingInformation_FirstName String True The first name of the customer as stated in the address info.
BillingInformation_LastName String True The last name of the customer as stated in the address info.
BillingInformation_CompanyName String True The name of the company of the customer.
BillingInformation_Phone String True The primary telephone number for the customer.
BillingInformation_Phone2 String True An alternate telephone or fax number for the customer.
BillingInformation_Phone3 String True An alternate telephone or fax number for the customer.
BillingInformation_Street String True Street address of the billing address of the customer.
BillingInformation_Street2 String True The second line of the street address in the billing address for the customer.
BillingInformation_City String True City name for the billing address of the customer.
BillingInformation_State String True State name for the billing address of the customer.
BillingInformation_PostalCode String True Postal code for the billing address of the customer.
BillingInformation_Country String True Country for the billing address of the customer.
ShippingInformation_AddressName String False The name used to identify the shipping address of the customer.
ShippingInformation_CompanyName String False The company name of the shipping address of the customer.
ShippingInformation_FullName String False The full name of the recipient in the shipping address of the customer.
ShippingInformation_Phone String False The primary telephone number for the shipping address of the customer.
ShippingInformation_Street String False Street address of the shipping address of the customer.
ShippingInformation_Street2 String False The second line of the street address of the shipping address of the customer.
ShippingInformation_City String False City name for the shipping address of the customer.
ShippingInformation_State String False State name for the shipping address of the customer.
ShippingInformation_PostalCode String False Postal code for the shipping address of the customer.
ShippingInformation_Country String False Country for the shipping address of the customer.
ShippingInformation_ShipBy String False The shipping method used to send merchandise to a customer.
ShippingInformation_Shipping Decimal False Indicates the amount to be paid for shipping.
AccountTenderAmount Decimal False Amount paid by the customer.
AccountTipAmount Decimal False The amount of the gratuity paid to the employee.
CashTenderAmount Decimal False Cash amount paid by the customer.
CheckNumber String False The number of the check used as payment.
CheckTenderAmount Decimal False Check amount paid by the customer.
CreditCardName String False The name of the credit card used in the transaction, for example, Visa.
CreditCardTenderAmount Decimal False Credit card amount paid by the customer.
CreditCardTipAmount Decimal False Credit card amount of the gratuity paid to the employee.
DebitCardCashback Decimal False Debit card amount paid by the customer.
DebitCardTenderAmount Decimal False Debit card amount paid by the customer.
DepositTenderAmount Decimal False Deposit amount paid by the customer.
GiftCertificateNumber String False Number of the gift certificate used for full or partial payment.
GiftTenderAmount Decimal False Gift certificate amount paid by the customer.
GiftCardTenderAmount Decimal False Gift card amount paid by the customer.
GiftCardTipAmount Decimal False Gift card amount of the gratuity paid to the employee.
ItemListID# String False The unique identifier for the item, generated by QuickBooks POS.
ItemALU# String False Alternate Lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAssociate# String False The employee making the adjustment.
ItemAttribute# String False A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value can be 1-8 characters.
ItemCommission# Decimal False The commission amount for the item, based on the item price and the Associates commission percent, if the item is eligible for commissions.
ItemCost# Decimal False The average cost of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos.
ItemDesc1# String False The principal item description field. This description is printed on receipts.
ItemDesc2# String False Secondary description field usually used for any additional information to describe this particular item, such as the catalog number of a vendor. Also can be used for additional information at the point of sale.
ItemDiscount# Decimal False The amount of a discount applicable to the transaction. Discounts are applied to the transaction subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
ItemDiscountPercent# Double False A percentage discount applicable to the item. Discounts are applied to the transaction subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
ItemDiscountType# String False Allows you to specify the reason for an item discount. Discount types can be customized in the company preferences and used to filter reports.
ItemExtendedPrice# Decimal True The extended price of a line item.
ItemExtendedTax# Decimal True Total tax per line item.
ItemItemNumber# Double True Unique number assigned to the item when it is added to QBPOS.
ItemNumberOfBaseUnits# Double True The base unit, used to specify inventory quantity.
ItemPrice# Decimal False The purchase or sales price of this item.
ItemPriceLevelNumber# String True The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
ItemQty# Double False The quantity of the line item being ordered or transferred.
ItemSerialNumber# String False Item serial numbers can be recorded in QBPOS for purposes of warranty tracking or meeting legal requirements. Once recorded, former documents can be quickly located by searching by serial number.
ItemSize# String False The size of an item. This field can also be used to describe a second item attribute other than size if desired. This field can contain 1-8 characters.
ItemTaxAmount# String True The tax amount for the item, returned in the response to inserting a non-held transaction.
ItemTaxCode# String False The tax code of the item.
ItemTaxPercentage# String True Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC# String False The UPC/EAN/ISBN of the item. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.
ItemWebDesc# String True The description of the item for use online.
ItemManufacturer# String True The manufacturer of the item.
ItemWeight# Double True The weight of the item.
ItemWebSKU# String True The SKU of the item for online use.

SalesReceipts

Create, update, and query QuickBooks POS Sales Receipts.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM SalesReceipts WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new Sales Receipt record, the CustomerListId field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the SalesReceiptItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Sales Receipt with two items (note: 7.5% sales tax):

INSERT INTO SalesReceipts (SalesReceiptType, CustomerListId, SalesReceiptNumber, CashTenderAmount, ItemsAggregate)
VALUES ('Sales', '-9876543210987654321', 'SR12345', 43.00, '<SalesReceiptItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemPrice>15.00</ItemPrice><ItemQty>1</ItemQty></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemPrice>25.00</ItemPrice><ItemQty>1</ItemQty></Row>
</SalesReceiptItems>')
Update

Modifying a Sales Receipt is not allowed by QuickBooks POS. Instead an insert can be performed and SalesReceiptType can be set to the desired function, such as "Refund".

Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QBPOS.
CustomerListID String False Single A reference to the customer.
Associate String False Range The employee making the adjustment.
Cashier String False Range Name of the employee making the transaction. Notice that this value is auto-filled with the logged-in employee name if logins are required.
Comments String False Range A description of the transaction.
Discount Decimal False Range The amount of a discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range The percentage discount applicable to the transaction. Discounts are applied to the order subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
HistoryDocStatus String False Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this is the corrected version of the original memo), Reversing (this memo caused an older memo to be reversed), Regular (the document has been corrected).
ItemsCount String True Range The number of line items in the request that added the transaction to QBPOS.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding a transaction and its line items.
PriceLevelNumber String False Single The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PromoCode String False Range A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.
QuickBooksFlag String False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
SalesOrderTxnID String False Single A reference to the associated sales order, which will be automatically updated with changes to the sales receipt.
SalesReceiptNumber String False Range A unique number assigned to the receipt by QBPOS at creation.
SalesReceiptType String False Single The receipt type. Possible values are Sales, Return, Deposit, Refund, Payout, and Payin.
ShipDate Datetime False Range The date the merchandise shipped.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
TaxAmount String True Range The tax amount, returned in the response to inserting a non-held transaction.
TaxCategory String False Range The tax category (location).
TaxPercentage String False Range The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
TenderType String False Single Identifies which payment method was used for customer payment in the transaction. Possible values are None, Cash, Check, CreditCard, DebitCard, GiftCard, Account, Gift, Deposit, and Split.
TipReceiver String False Range The employee to whom the tip is to be paid.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TrackingNumber String True Range The number provided to customers by the shipping company to help them track merchandise location and progress during shipment.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single Indicates the current state of the transaction. Possible values are Normal (indicates the transaction is complete) and Held (the transaction has not yet been finalized).
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the transaction was created.
TimeModified Datetime True Range When the transaction was last modified.
BillingInformation_Salutation String True A salutation, such as Mr., Mrs., etc.
BillingInformation_FirstName String True The first name of the customer as stated in the address info.
BillingInformation_LastName String True The last name of the customer as stated in the address info.
BillingInformation_CompanyName String True The name of the company of the customer.
BillingInformation_Phone String True The primary telephone number for the customer.
BillingInformation_Phone2 String True An alternate telephone or fax number for the customer.
BillingInformation_Phone3 String True An alternate telephone or fax number for the customer.
BillingInformation_Street String True Street address of the billing address of the customer.
BillingInformation_Street2 String True The second line of the street address in the billing address for the customer.
BillingInformation_City String True City name for the billing address of the customer.
BillingInformation_State String True State name for the billing address of the customer.
BillingInformation_PostalCode String True Postal code for the billing address of the customer.
BillingInformation_Country String True Country for the billing address of the customer.
ShippingInformation_AddressName String False The name used to identify the shipping address of the customer.
ShippingInformation_CompanyName String False The company name of the shipping address of the customer.
ShippingInformation_FullName String False The full name of the recipient in the shipping address of the customer.
ShippingInformation_Phone String False The primary telephone number for the shipping address of the customer.
ShippingInformation_Street String False Street address of the shipping address of the customer.
ShippingInformation_Street2 String False The second line of the street address of the shipping address of the customer.
ShippingInformation_City String False City name for the shipping address of the customer.
ShippingInformation_State String False State name for the shipping address of the customer.
ShippingInformation_PostalCode String False Postal code for the shipping address of the customer.
ShippingInformation_Country String False Country for the shipping address of the customer.
ShippingInformation_ShipBy String False The shipping method used to send merchandise to a customer.
ShippingInformation_Shipping Decimal False Indicates the amount to be paid for shipping.
AccountTenderAmount Decimal False Amount paid by the customer.
AccountTipAmount Decimal False The amount of the gratuity paid to the employee.
CashTenderAmount Decimal False Cash amount paid by the customer.
CheckNumber String False The number of the check used as payment.
CheckTenderAmount Decimal False Check amount paid by the customer.
CreditCardName String False The name of the credit card used in the transaction, for example, Visa.
CreditCardTenderAmount Decimal False Credit card amount paid by the customer.
CreditCardTipAmount Decimal False Credit card amount of the gratuity paid to the employee.
DebitCardCashback Decimal False The amount of cash given back to the customer from the debit card transaction.
DebitCardTenderAmount Decimal False Debit card amount paid by the customer.
DepositTenderAmount Decimal False Deposit amount paid by the customer.
GiftCertificateNumber String False Number of the gift certificate used for full or partial payment.
GiftTenderAmount Decimal False Gift certificate amount paid by the customer.
GiftCardTenderAmount Decimal False Gift card amount paid by the customer.
GiftCardTipAmount Decimal False Gift card amount of the gratuity paid to the employee.

TimeEntries

Create, update, and query QuickBooks POS employee time entries.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM TimeEntries WHERE FirstName LIKE '%George%'
Insert

To create a new time entry record, there are no required fields; however, EmployeeListId should be specified to identify who the employee is.

INSERT INTO TimeEntries (EmployeeListId, ClockInTime)
VALUES ('-9876543210987654321', '2014-08-04T13:15:20-04:00')
Update

Any field that is not read-only can be updated.

UPDATE TimeEntries SET ClockOutTime = '2014-08-04T19:15:20-04:00' WHERE ListId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
EmployeeListID String False Single A reference to the employee.
EmployeeLoginName String True Range The name used by the employee to log into the QBPOS company.
FirstName String True Range The first name of the employee.
LastName String True Range The last name of the employee.
ClockInTime Datetime False Range The date and time when the employee clocked in.
ClockOutTime Datetime False Range The date and time when the employee clocked out.
CreatedBy String False Range The person who created this time entry.
QuickBooksFlag Boolean False Single The status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (the memo has not yet been sent to QuickBooks), COMPLETE (the memo has been sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the employee was created.
TimeModified Datetime True Range When the employee was last modified.

Vendors

Create, update, delete and query QuickBooks POS vendors.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Vendors WHERE CompanyName LIKE '%Cross%'
Insert

To create a new vendor record, the CompanyName field is required.

INSERT INTO Vendors (CompanyName, VendorCode, AccountNumber, FirstName, LastName)
VALUES ('Cross Industries', 'JCI', '12345', 'Joshua', 'Cross')
Update

Any field that is not read-only can be updated.

UPDATE Vendors SET Street = '123 Main St', City = 'Townville', State = 'AZ', PostalCode = '85201' WHERE ListId = '-1234567890123456789'
Columns
Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
CompanyName String False Range The name of the business.
VendorCode String False Range The user-defined code for the vendor.
AccountNumber String False Range Your account number with the vendor. This number is displayed on purchase orders that are created for the vendor.
Salutation String False Range A salutation, such as Mr., Mrs., etc.
FirstName String False Range The first name of the vendor.
LastName String False Range The last name of the vendor.
Street String False Range Street address of the vendor.
Street2 String False Range The second line of the street address of the vendor.
City String False Range City name for the billing address of the vendor.
State String False Range State name for the billing address of the vendor.
PostalCode String False Range Postal code for the billing address of the vendor.
Country String False Range Country for the billing address of the vendor.
Phone String False Range The primary telephone number for the vendor.
Phone2 String False Range An alternate telephone or fax number for the vendor.
Phone3 String False Range An alternate telephone or fax number for the vendor.
Email String False Range The email address for communicating with the vendor.
Notes String False Range Notes on this vendor.
IsInactive Boolean False Single Indicates whether the vendor is currently active.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
Terms String True Any payment terms that are defined for the vendor file are carried to the purchase order, although payment terms are not visible on a purchase order.
TermsDiscount Decimal False Range The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays Double False Range The number of days after the invoice date when payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays Double False Range Payment must be made within this number of days, counting from the invoice date.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the vendor was created.
TimeModified Datetime True Range When the vendor was last modified.

VoucherItems

Create and query QuickBooks POS Voucher Items.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM VoucherItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

Insert can be used to create a new Voucher record or to add an item to an existing Voucher record.

To create a new Voucher record, the VendorListId field is required.

INSERT INTO VoucherItems (VoucherType, VendorListId, InvoiceNumber, Fee, ItemListId, ItemCost, ItemQtyReceived)
VALUES ('Receiving', '-9876543210987654321', 'INV12345', 2.15, '-1000000000000000001', 20.00, 10)

To add an item to an existing record, the TxnId column of the Voucher that the item should be added to and the ItemListId are required.

INSERT INTO VoucherItems (TxnId, ItemListId, ItemPrice, ItemQtyReceived)
VALUES ('-1234567890123456789', '-1000000000000000001', 40.00, 5)
Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the voucher transaction.
Comments String False Range A description of the voucher.
CompanyName String True Range The name of the business.
Discount Decimal False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the voucher. Discounts are applied to the voucher subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Fee Decimal False Range You can enter a fee using this field. Notice that fees entered on a voucher (such as a special handling fee) are not spread over the cost of individual items. When a receiving voucher references a voucher with a fee entered, the fee can be transferred to the voucher at that point.
Freight String False Range Price charged by the shipping carrier to move the merchandise between the stores involved in the transaction.
HistoryDocStatus String True Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of the original), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InvoiceDate Datetime False Range The date shown on the invoice of the vendor.
InvoiceDueDate Datetime False Range The date the invoice is due and payable.
InvoiceNumber String False Range The invoice number for the shipment.
ItemsCount String True Range The number of line items in request that added the voucher.
PayeeCode String True Range The vendor or agent to be paid for the merchandise.
PayeeListID String False Single The refrrence to the vendor who is to be paid for the merchandise being received via this receiving voucher.
PayeeName String True Range The full vendor or customer name returned from the QBPOS company that matches the PayeeCode value.
PurchaseOrderNumber String True Range The number you want to assign to the voucher. It should be unique. This number is automatically assigned to the voucher by QBPOS if left empty during the creation process.
PurchaseOrderTxnID String False Single In a receiving voucher, this field links the voucher to a purchase order so it can receive items against that purchase order.
QuickBooksFlag Boolean False Single The value here displays the status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the information about the customer was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of extended item costs of the transaction before applying any discounts or fees.
TermsDiscount Double False Range The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays Double False Range The number of days after the invoice date in which payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays Double False Range Payment must be made within this number of days, counting from the invoice date.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TotalQty Double True Range Total quantity of items being transferred.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
VendorCode String True Range The code assigned to the vendor specified for the item.
VendorListID String False Single A reference to the vendor.
VoucherNumber String True Range A unique number assigned to the voucher by QBPOS at creation.
VoucherType String False Single The type of voucher you are creating. Possible values are Receiving and Return.
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the voucher was created.
TimeModified Datetime True Range When the voucher was last modified.
ItemListID# String False The unique identifier, generated by QuickBooks POS, for the item.
ItemALU# String False Alternate Lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAttribute# String False A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
ItemCost# Decimal False The average cost of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos.
ItemDesc1# String False The principal item description field. This description is printed on receipts.
ItemDesc2# String False Secondary description field usually used for any additional information to describe this particular item, such as a vendor's catalog number. Also can be used for additional information at the point of sale.
ItemExtendedCost# Decimal False The quantity ordered multiplied by the item cost.
ItemItemNumber# Double True Unique number assigned to the item when it is added to QBPOS.
ItemNumberOfBaseUnits# Double True The base unit, used to track and report the inventory quantity.
ItemOriginalOrderQty# Double False The quantity of the line item being ordered or transferred.
ItemQtyReceived# Double True The item quantity received to date.
ItemSerialNumber# String False Item serial numbers can be recorded in QBPOS for purposes of warranty tracking or meeting legal requirements. Once recorded, former documents can be quickly located by searching by serial number.
ItemSize# String False The size of an item. This field can also be used to describe a second item attribute other than size if desired. This value must be 1-8 characters.
ItemUnitOfMeasure# String False If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC# String False The item's UPC/EAN/ISBN identifier. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.

Vouchers

Create, update, delete, and query QuickBooks POS Vouchers.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM Vouchers WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
Insert

To create a new Voucher record, the VendorListId field is required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the VoucherItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Voucher with two items (note: 7.5% sales tax):

INSERT INTO Vouchers (VoucherType, VendorListId, InvoiceNumber, Fee, ItemsAggregate)
VALUES ('Receiving', '-9876543210987654321', 'INV12345', 3.00, '<VoucherItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemCost>10.00</ItemPrice><ItemQtyReceived>25</ItemQtyReceived></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemCost>20.00</ItemPrice><ItemQtyReceived>25</ItemQtyReceived></Row>
</VoucherItems>')
Update

Modifying a voucher is not allowed by QuickBooks POS. Instead an insert can be performed and VoucherType set to the desired function, such as "Return".

Columns
Name Type ReadOnly Filter Type Description
TxnID [KEY] String True Single The unique identifier, generated by QuickBooks POS.
Associate String False Range The employee making the voucher transaction.
Comments String False Range A description of the voucher.
CompanyName String True Range The name of the business.
Discount Decimal False Range The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent Double False Range Enter a percentage discount applicable to the voucher. Discounts are applied to the voucher subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Fee Decimal False Range You can enter a fee using this field. Notice that fees entered on a voucher (such as a special handling fee) are not spread over the cost of individual items. When a receiving voucher references a voucher with a fee entered, the fee can be transferred to the voucher at that point.
Freight String False Range Price charged by the shipping carrier to move the merchandise between the stores involved in the transaction.
HistoryDocStatus String True Single Indicates the current status of the cost memo created as a result of inserting the transaction. Possible values are Reversed (this memo is the corrected version of the original), Reversing (this memo caused an older memo to be reversed), and Regular (the document has been corrected).
InvoiceDate Datetime False Range The date shown on the invoice of the vendor.
InvoiceDueDate Datetime False Range The date the invoice is due and payable.
InvoiceNumber String False Range The invoice number for the shipment.
ItemsCount String True Range The number of line items in request that added the voucher.
ItemsAggregate String False An aggregate of the line item data, which can be used for adding a voucher and its line item data.
PayeeCode String True Range The vendor or agent to be paid for the merchandise.
PayeeListID String False Single The refrrence to the vendor who is to be paid for the merchandise being received via this receiving voucher.
PayeeName String True Range The full vendor or customer name returned from the QBPOS company that matches the PayeeCode value.
PurchaseOrderNumber String True Range The number you want to assign to the voucher. It should be unique. This number is automatically assigned to the voucher by QBPOS if left empty during the creation process.
PurchaseOrderTxnID String False Single In a receiving voucher, this field links the voucher to a purchase order so it can receive items against that purchase order.
QuickBooksFlag String False Single The value here displays the status of data export for a memo. This element is used only if QBPOS is being used with QuickBooks Financial Software. Possible values are NOT POSTED (not yet sent to QuickBooks), COMPLETE (sent to QuickBooks), and ERROR (attempting to send the memo to QuickBooks resulted in an error). No default value.
StoreExchangeStatus String True Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the information about the customer was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
StoreNumber Double False Range This is used only for multi-store versions of QBPOS. The value is between 1 and 10, since a maximum of 10 stores are currently supported.
Subtotal Decimal True Range The sum of extended item costs of the transaction before applying any discounts or fees.
TermsDiscount Decimal False Range The discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsDiscountDays Double False Range The number of days after the invoice date in which payment must be received in order to obtain the discount percentage. A discount is applied by the vendor if payment is received on or before the number of days specified in the TermsDiscountDays field for that vendor. Terms-related discounts for vendors are created or modified using the TermsDiscountDays and TermsDiscount fields in an insert or update.
TermsNetDays Double False Range Payment must be made within this number of days, counting from the invoice date.
Total Decimal True Range The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TotalQty Double True Range Total quantity of items being transferred.
TxnDate Datetime False Range The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
TxnState String False Single The current state of the transaction. Possible values are Normal (the transaction is complete) and Held (the transaction has not yet been finalized).
VendorCode String True Range The code assigned to the vendor specified for the item.
VendorListID String False Single A reference to the vendor.
VoucherNumber String True Range A unique number assigned to the voucher by QBPOS at creation.
VoucherType String False Single The type of voucher you are creating. Possible values are Receiving and Return.
Workstation Double False Range Workstation from which the transaction was made.
CustomFieldsOwnerID String False Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String False Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime True Range When the voucher was created.
TimeModified Datetime True Range When the voucher was last modified.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Jitterbit Connector for QuickBooks POS Views

Name Description
BalanceSheetDetail Balance Sheet Detail Report.
BalanceSheetStandard Balance Sheet Standard Report.
BalanceSheetSummary Balance Sheet Summary Report.
BillingRate Query QuickBooks Billing Rate. Requires QBXML Version 6.0 or higher.
BillLinkedTransactions Query QuickBooks Bill Linked Transactions.
CompanyInfo Query the company information from QuickBooks.
CreditMemoLinkedTransactions Query QuickBooks Credit Memo Linked Transactions.
CustomColumns Query QuickBooks Custom Columns.
DeletedEntities Query deleted Entities.
DeletedTransactions Query deleted Transactions.
EmployeeStateTaxesDetail Returns information of employee state taxes detail report.
EstimateLinkedTransactions Query QuickBooks Estimate Linked transactions.
Host Query the QuickBooks host process. The Host represents information about the QuickBooks process currently being executed.
InvoiceLinkedTransactions Query QuickBooks Invoice Linked Transactions.
ItemReceiptLinkedTransactions Query QuickBooks Item Receipt Linked Transactions.
ItemSites Create, update, delete, and query QuickBooks Item Sites. Item sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. This table requires a minimum of QBXML version 10.0.
PayrollItemDetail Returns information of Payroll item detail report (lists the payroll transactions on which each payroll item appears).
PayrollReviewDetail Returns information of Payroll detail review report (provides detailed information about how QuickBooks calculates tax amounts on employee paychecks and in year-to-date transactions).
PayrollTransactionDetail Returns information of Payroll transaction detail report (shows the line-item detail that appears on each payroll transaction).
PayrollTransactionsByPayee Payroll transactions by payee report (lists payroll transactions, grouping them by payee).
Preferences Query information about many of the preferences the QuickBooks user has set in the company file.
ProfitAndLossDetail Profit & Loss Prev Year Comparison Report.
ProfitAndLossStandard Profit & Loss YTD Comparison Report.
PurchaseOrderLinkedTransactions Query QuickBooks Purchase Order Linked Transactions.
ReceivePaymentToDeposit Returns information about payments that have been received and are ready to deposit.
SalesOrderLinkedTransactions Query QuickBooks Sales Order Linked Transactions.
StatementChargeLinkedTransactions Query QuickBooks Statement Charge Linked Transactions.
Templates Query QuickBooks templates.
Transactions Query QuickBooks transactions. You may search the Transactions using a number of values including Type, Entity, Account, ReferenceNumber, Item, Class, Date, and TimeModified.
VendorCreditLinkedTransactions Query QuickBooks Vendor Credit Linked Transactions.

CompanyInfo

Query the company information from QuickBooks POS.

Table Specific Information
Select

The CompanyInfo table returns the current QBPOS company used by the application.

SELECT * FROM CompanyInfo
Columns
Name Type Filter Type Description
CompanyName [KEY] String The company name.
Address_Street String Street address of the company.
Address_CityStateZIP String City, state, and postal code of the company.
Address_Misc1 String First miscellaneous field of the company address.
Address_Misc2 String Second miscellaneous field of the company address.
Address_Misc3 String Third miscellaneous field of the company address.
QuickBooksCompanyFile String The company file name specified in the company preferences.

CustomColumns

Query QuickBooks POS Custom Fields.

Columns
Name Type Filter Type Description
OwnerID String Multi The owner of a data extension.
DataExtName String The name of the data extension.
DataExtType String The field's data type.
AssignToObject String Multi The object(s) associated with the result, specified as a comma-separated list.

CustomerRewards

Query QuickBooks POS Customer Rewards.

Table Specific Information
Select

QuickBooks POS allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Property Description
Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator. To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available. To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.
SELECT * FROM CustomerRewards WHERE FirstName LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014' AND AccountBalance > 100.00
Columns
Name Type Filter Type Description
ListID [KEY] String The unique identifier, generated by QuickBooks POS.
CustomerID String Range The user-specified ID for the customer.
FullName String The full name of the customer.
Salutation String Range A salutation, such as Mr., Mrs., etc.
FirstName String Range The first name of the customer as stated in the address info.
LastName String Range The last name of the customer as stated in the address info.
CompanyName String Range The name of the company of the customer.
Phone String Range The primary telephone number for the customer.
Phone2 String Range An alternate telephone or fax number for the customer.
Phone3 String Range An alternate telephone or fax number for the customer.
Email String Range The email address of the customer.
IsOkToEMail Boolean Single Whether or not the customer can be notified via email.
CustomerType String Range A customer type within QuickBooks POS.
Notes String Range Notes on this customer.
BillAddress_Street String Street address of the billing address of the customer.
BillAddress_Street2 String The second line of the street address of the billing address of the customer.
BillAddress_City String City name for the billing address of the customer.
BillAddress_State String State name for the billing address of the customer.
BillAddress_PostalCode String Postal code for the billing address of the customer.
BillAddress_Country String Country for the billing address of the customer.
IsNoShipToBilling Boolean Single Whether or not the billing address can be used as a shipping address.
IsAcceptingChecks Boolean Single Indicates whether checks are being accepted from this customer. The default is True.
IsUsingChargeAccount Boolean Single Indicates whether the customer is using a charge account.
StoreExchangeStatus String Single In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
TaxCategory String Range The sales tax category.
CustomerDiscPercent Double Range The suggested customer discount percentage for all items listed on a sales receipt. Note that you cannot assign both a discount percentage and a price level to a customer.
CustomerDiscType String Single The customer discount type.
PriceLevelNumber String Single The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
LastSale Datetime Range The time of the last purchase made by this customer.
AmountPastDue Decimal Range The amount past due for a transaction.
IsUsingWithQB Boolean Single Whether or not the customer is using QuickBooks POS with QuickBooks.
AccountBalance Decimal Range The balance of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
AccountLimit Decimal Range The limit of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.
CustomFieldsOwnerID String Multi Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields String Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated Datetime Range When the customer was created.
TimeModified Datetime Range When the customer was last modified.
IsRewardsMember Boolean Indicates whether the customer is a rewards member.
RewardRewardAmount# String The amount of the reward earned.
RewardRewardPercent# String The percentage of the reward based on the transaction amount.
RewardEarnedDate# Datetime The date the reward was earned.
RewardMatureDate# Datetime The date the reward will mature.
RewardExpirationDate# Datetime The date the reward will expire.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for QuickBooks POS:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Customers table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Customers'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the ClearTransaction stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='ClearTransaction' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Customers table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Customers'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:quickbookspos:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Custom Fields

Some of the tables in QuickBooks POS allow you to define your own fields. These fields are represented as the Custom Fields column. You can use this column to modify all your custom fields.

Custom fields are a special case with the connector. QuickBooks POS will only return custom fields if they have a value, and will return nothing if no custom fields are set. Custom fields are represented in XML like so:

<CustomField><Name>Custom Field Name</Name><Value>Custom Field Value</Value></CustomField>

To clear a custom field, submit the custom field name without a value. For instance:

<CustomField><Name>Custom Field Name</Name><Value></Value></CustomField>

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Connection

Property Description
CompanyFile The name of the CompanyFile to open.
QBPOSVersion The QuickBooks Point Of Sale software version.
QBPOSPractice Specifies whether to use practice mode within QuickBooks Point Of Sale.
ApplicationName The name of the developer's application.
QBPOSXMLVersion The version of QBPOSXML used in the outgoing message.

RemoteConnector

Property Description
URL The URL for the QuickBooks Desktop Gateway. For example, http://localhost:2080.
User A username for the QuickBooks Desktop Gateway connection (if required by the Desktop Gateway).
Password A password for the QuickBooks Desktop Gateway connection.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
DelayAfterClose A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from QuickBooks POS.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Connection

This section provides a complete list of connection properties you can configure.

Property Description
CompanyFile The name of the CompanyFile to open.
QBPOSVersion The QuickBooks Point Of Sale software version.
QBPOSPractice Specifies whether to use practice mode within QuickBooks Point Of Sale.
ApplicationName The name of the developer's application.
QBPOSXMLVersion The version of QBPOSXML used in the outgoing message.

CompanyFile

The name of the CompanyFile to open.

Data Type

string

Default Value

""

Remarks

If QuickBooks POS is not currently open with a CompanyFile, QuickBooks POS will be automatically opened in the background with the file specified. Do not set CompanyFile when QuickBooks POS is open. If QuickBooks POS is open through the application UI, only the currently opened CompanyFile may be used. If you are connecting to a remote company file, the CompanyFile parameter will be ignored. Specify the company file when you create a QuickBooks Desktop Gateway user.

QBPOSVersion

The QuickBooks Point Of Sale software version.

Data Type

string

Default Value

12

Remarks

Connections to QuickBooks Point Of Sale are specific to the version being used. Therefore it is required to specify the correct version being used. If an incorrect value is specified, a connection to QuickBooks error will result.

All versions of QuickBooks Point Of Sale are supported.

QBPOSPractice

Specifies whether to use practice mode within QuickBooks Point Of Sale.

Data Type

bool

Default Value

false

Remarks

QuickBooks Point Of Sale provides a practice mode, which will be initiated when this property is set to 'True'. Practice mode allows you to experiment with features of QuickBooks Point Of Sale (including unlicensed features) without affecting your real data.

ApplicationName

The name of the developer's application.

Data Type

string

Default Value

""

Remarks

This name will appear when the application first connects to QuickBooks POS. QuickBooks will display this name and prompt the user to grant or refuse access to the application.

QBPOSXMLVersion

The version of QBPOSXML used in the outgoing message.

Possible Values

1.0, 1.1, 1.2, 2.0, 2.5, 3.0

Data Type

string

Default Value

3.0

Remarks

Except as noted in the documentation, a value of 1.0 will suffice for all requests and for all versions of QuickBooks POS that support integrated applications.

Each release of QuickBooks POS continues to support all earlier versions of the SDK, meaning that requests using version 1.0 of the QuickBooks POS SDK are supported by all versions of QuickBooks POS. However, new requests and fields are supported only in later versions of the SDK. As such, it is recommended that you set the QBPOSXMLVersion property to correspond to the version of QuickBooks POS you are interacting with. These values are:

QuickBooks POS 1.0 - 4.0 1.0
QuickBooks POS 5.0 1.2, 1.1, 1.0
QuickBooks POS 6.0 2.5, 2.0
QuickBooks POS 7.0 and greater 3.0

RemoteConnector

This section provides a complete list of the RemoteConnector properties you can configure.

Property Description
URL The URL for the QuickBooks Desktop Gateway. For example, http://localhost:2080.
User A username for the QuickBooks Desktop Gateway connection (if required by the Desktop Gateway).
Password A password for the QuickBooks Desktop Gateway connection.

URL

The URL for the QuickBooks Desktop Gateway. For example, http://localhost:2080.

Data Type

string

Default Value

""

Remarks

If the URL is specified, the connector will not communicate directly with QuickBooks. Instead, it will send a request to the specified Web address. QuickBooks POS and the Desktop Gateway should be installed at the remote location. If the connector is listening on the specified port, it will communicate the connector's request to QuickBooks POS and return the response.

User

A username for the QuickBooks Desktop Gateway connection (if required by the Desktop Gateway).

Data Type

string

Default Value

""

Remarks

The Desktop Gateway must require user authentication before this property is used.

Password

A password for the QuickBooks Desktop Gateway connection.

Data Type

string

Default Value

""

Remarks

The Desktop Gateway must require user authentication before this property is used.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

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 QuickBooks Gateway. Any other certificate that is not trusted by the machine is rejected.

This property can take the following 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

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\QuickBooksPOS Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\QuickBooksPOS Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

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

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
DelayAfterClose A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from QuickBooks POS.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

DelayAfterClose

A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed.

Data Type

string

Default Value

""

Remarks

If QuickBooks POS is generating internal errors (in particular with automatic login), setting this delay to a positive value may solve the problem.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines 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.

Pagesize

The maximum number of results to return per page from QuickBooks POS.

Data Type

int

Default Value

500

Remarks

The Pagesize property affects the maximum number of results to return per page from QuickBooks POS. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

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; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

30

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

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

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Customers WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json

Note that the specified path is not embedded in quotation marks.