The CData Sync App provides a straightforward way to continuously pipeline your QuickBooks POS data to any database, data lake, or data warehouse, making it easily available for Analytics, Reporting, AI, and Machine Learning.
The QuickBooks POS connector can be used from the CData Sync application to pull data from QuickBooks POS and move it to any of the supported destinations.
Create a connection to QuickBooks POS by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the QuickBooks POS icon is not available, click the Add More icon to download and install the QuickBooks POS connector from the CData site.
Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.
The Sync App 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 Sync App 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.
Follow the steps below to authorize with QuickBooks POS and connect to a company file when both QuickBooks POS and the Sync App are running on your local machine.
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.
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.
The CData 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:
Open the CData QuickBooks Desktop Gateway from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.
Note: The CData QuickBooks Desktop Gateway does not use the User and Password properties to access QuickBooks POS; the User and Password properties authenticate the user. Authentication to QuickBooks POS is handled by the ApplicationName property.
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.
You can enable SSL/TLS on the Advanced tab.
You will also need to send your public key certificate to the Sync App. You can do so by setting the SSLServerCert property.
The CData QuickBooks Desktop Gateway is a simple application that facilitates connections to company files from your application. The Desktop Gateway accepts connections via a lightweight embedded Web server that runs on the machine where QuickBooks 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 "Getting 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.
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:
| Basic Authentication (default): | Authenticates the user with a username and password. |
| Windows Authentication: | Authenticates the user as a Windows user. In this case the Password field is not applicable. When the QuickBooks Gateway receives a connection request, it will authenticate the user to Windows using the credentials supplied in the request. |
| 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 QuickBooks Gateway will prompt you to authorize the application with QuickBooks POS if necessary.
The Status tab provides a log of the activity happening with the QuickBooks Gateway. Logs can be cleared or copied by right-clicking in the Recent Activity window.
You can adjust the detail of the logs to include information useful when troubleshooting: Select the granularity in the Log Mode menu on the Advanced tab. On the Advanced tab, you can also configure the QuickBooks Gateway to write logs to a file and select the log rotation interval.
The Advanced tab allows granular control over the QuickBooks Gateway's server. The QuickBooks Gateway contains an embedded Web server that runs as a Windows service and listens for HTTP requests. Each request contains the XML data to be communicated to QuickBooks POS as well as configuration settings specifying how the connection is to be opened. The QuickBooks 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.
| 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". |
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 QuickBooks Gateway queues requests for data and performs the necessary authentication for each request. The following options can be used to override this behavior and keep the connection to the company file alive after the query finishes executing, so further requests will respond more quickly.
Warning: If a user attempts to manually open QuickBooks POS while a persistent connection is opened, QuickBooks POS will return an error stating that the company file is already in use.
The following options are used to configure TLS/SSL:
In addition to the UI, the QuickBooks Gateway has a command-line interface that makes it easy to deploy on machines where a user is not always logged in, for example, a Web server. To facilitate deployment to these environments, the QuickBooks Gateway contains two executables:
| RemoteConnector.exe | Provides the user interface and allows configuration of the application. |
| RemoteConnectorService.exe | Processes requests and performs all interaction with QuickBooks POS. |
The syntax for managing the QuickBooks Gateway Windows service from the command line is as follows:
RemoteConnectorService.exe /Service <Command>
The following commands are available:
| Install | Installs the Windows service. |
| Delete | Deletes the Windows service. |
| Start | Starts the Windows service. |
| Stop | Stops the Windows service. |
| State | Reports the current state of the Windows service (started or stopped). |
| Auto | Changes the Windows service startup type to Automatic. |
| Manual | Changes the Windows service startup type to Manual. |
| Disabled | Changes the Windows service startup type to Disabled. |
You can start the MySQL daemon from the command line, as shown below:
java -jar cdata.jdbc.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. |
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".
| 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).
|
| 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:
|
| LogLevel | DWORD | Sets the logging level. Possible values are the following:
|
| 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 QuickBooks Gateway even when Open and Close are not used. This is helpful in situations when multiple applications may be simultaneously accessing the QuickBooks Gateway, because it allows them to share the connection. |
| PersistentIdleTimeout | DWORD | Sets the idle timeout for the persistent connection in seconds. If there is no activity within this time window, the connection to QuickBooks 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). |
| 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 QuickBooks 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 QuickBooks Gateway application will always store the SHA-256 hash of the password for security. However, this may also be manually set to a plaintext password to allow backward compatibility. |
| AuthMode | DWORD | Sets the type of authentication to perform when the user connects. From the client side the process of connecting is exactly the same no matter which option you choose. Possible values are the following:
|
| 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:
|
| ReadOnly | DWORD | Specifies whether the user has read-only (1) or full access (0). |
This section details a selection of advanced features of the QuickBooks POS Sync App.
The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
The Sync App 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).
See Query Processing for more information.
See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.
By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
The Data Model has two parts: Tables and Views.
The Sync App models the data in QuickBooks POS into a list of tables that can be queried using standard SQL statements.
Generally, querying QuickBooks POS tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
| Name | Description |
| Accounts | Create, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher. |
| BillExpenseItems | Create, update, delete, and query QuickBooks Bill Expense Line Items. |
| BillLineItems | Create, update, delete, and query QuickBooks Bill Line Items. |
| BillPaymentChecks | Create, update, delete, and query QuickBooks Bill Payment Checks. QBXMLVersion must be set to 6.0 or higher to update a BillPaymentCheck. |
| BillPaymentChecksAppliedTo | Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied. |
| BillPaymentCreditCards | Create, update, delete, and query QuickBooks Bill Payments. |
| BillPaymentCreditCardsAppliedTo | Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied. |
| Bills | Create, update, delete, and query QuickBooks Bills. |
| BuildAssemblies | Insert, Update, delete, and query QuickBooks Build Assembly transactions. |
| BuildAssemblyLineItems | Create and query QuickBooks Build Assembly transactions. |
| CheckExpenseItems | Create, update, delete, and query QuickBooks Check Expense Line Items. |
| CheckLineItems | Create, update, delete, and query QuickBooks Check Line Items. |
| Checks | Create, update, delete, and query QuickBooks Checks. |
| Class | Create, update, delete, and query QuickBooks Classes. QuickBooks requires QBXML version 8.0 or higher for updates to a Class. |
| CreditCardChargeExpenseItems | Create, update, delete, and query QuickBooks Credit Card Charge Expense Line Items. |
| CreditCardChargeLineItems | Create, update, delete, and query QuickBooks Credit Card Charge Line Items. |
| CreditCardCharges | Create, update, delete, and query QuickBooks Credit Card Charges. |
| CreditCardCreditExpenseItems | Create, update, delete, and query QuickBooks Credit Card Credit Expense Line Items. |
| CreditCardCreditLineItems | Create, update, delete, and query QuickBooks Credit Card Credit Line Items. |
| CreditCardCredits | Create, update, delete, and query QuickBooks Credit Card Credits. |
| CreditCardRefunds | Query and Insert QuickBooks AR Credit Card Refund transactions. |
| CreditMemoLineItems | Create, update, delete, and query QuickBooks Credit Memo Line Items. |
| CreditMemos | Create, update, delete, and query QuickBooks Credit Memos. |
| Currency | Create, update, delete, and query QuickBooks Currencies. This table requires QBXML version 8.0 or higher, and you will need to enable multiple Currencies in your QuickBooks company file to use it. |
| CustomerContacts | Create, update, delete, and query QuickBooks Customer Contacts. This table requires QBXML version 12.0 or higher, and is only available in QuickBooks editions 2016 and above. |
| CustomerMessages | Create, delete, and query Customer Messages. |
| CustomerNotes | Create, update, and query QuickBooks Customer Notes. This table requires QBXML version 12.0 or higher. |
| Customers | Create, update, delete, and query QuickBooks Customers. |
| CustomerShippingAddresses | Create, update, delete, and query QuickBooks Customer Shipping Addresses. Multiple Customer Shipping Addresses are supported in only QuickBooks 2013 and higher. Additionally, this table requires QBXML version 12.0 or higher. |
| CustomerTypes | Create, update, delete, and query QuickBooks Customer Types. |
| DateDrivenTerms | Create, delete, and query QuickBooks Date Driven Terms. |
| DepositLineItems | Create, update, delete, and query QuickBooks Deposit Line Items. QBXMLVersion must be set to 7.0 or higher to update a deposit. |
| Deposits | Create, update, delete, and query QuickBooks Deposits. QBXMLVersion must be set to 7.0 or higher to update a deposit. |
| EmployeeEarnings | Create, update, delete, and query QuickBooks Employee Earnings. |
| Employees | Create, update, delete, and query QuickBooks Employees. |
| EstimateLineItems | Create, update, delete, and query QuickBooks Estimate Line Items. |
| Estimates | Create, update, delete, and query QuickBooks Estimates. |
| InventoryAdjustmentLineItems | Create and query QuickBooks Inventory Adjustment Line Items. |
| InventoryAdjustments | Create, query, and delete QuickBooks Inventory Adjustments. |
| InventorySites | Create, update, delete, and query QuickBooks Inventory Sites. Inventory Sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. |
| InvoiceLineItems | Create, update, delete, and query QuickBooks Invoice Line Items. |
| Invoices | Create, update, delete, and query QuickBooks Invoices. |
| ItemLineItems | Create, update, delete, and query QuickBooks Item Line Items. |
| ItemReceiptExpenseItems | Create, update, delete, and query QuickBooks Item Receipt Expense Line Items. |
| ItemReceiptLineItems | Create, update, delete, and query QuickBooks Item Receipt Line Items. |
| ItemReceipts | Create, update, delete, and query QuickBooks Item Receipts. |
| Items | Create, update, delete, and query QuickBooks Items. |
| JobTypes | Create and query QuickBooks JobTypes. |
| JournalEntries | Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry. |
| JournalEntryLines | Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry. |
| OtherNames | Create, update, delete, and query QuickBooks Other Name entities. |
| PaymentMethods | Create, update, delete, and query QuickBooks Payment Methods. |
| PayrollNonWageItems | Query QuickBooks Non-Wage Payroll Items. |
| PayrollWageItems | Create and query QuickBooks Wage Payroll Items. |
| PriceLevelPerItem | Create and query QuickBooks Price Levels Per Item. Only QuickBooks Premier and Enterprise support Per-Item Price Levels. Note that while Price Levels can be added from this table, you may only add Per-Item Price Levels from this table. Price Levels may be deleted from the PriceLevels table. This table requires QBXML version 4.0 or later. |
| PriceLevels | Create, delete, and query QuickBooks Price Levels. Note that while Price Levels can be added and deleted from this table, you may add only fixed-percentage Price Levels from this table. Per-Item Price Levels may be added via the PriceLevelPerItem table. This table requires QBXML version 4.0 or higher. |
| PurchaseOrderLineItems | Create, update, delete, and query QuickBooks Purchase Order Line Items. |
| PurchaseOrders | Create, update, delete, and query QuickBooks Purchase Orders. |
| ReceivePayments | Create, update, delete, and query QuickBooks Receive Payment transactions. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment. |
| ReceivePaymentsAppliedTo | Create, update, and query QuickBooks Receive Payment AppliedTo aggregates. In a Receive Payment, each AppliedTo aggregate represents the transaction to which this part of the payment is being applied. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment. |
| SalesOrderLineItems | Create, update, delete, and query QuickBooks Sales Order Line Items. |
| SalesOrders | Create, update, delete, and query QuickBooks Sales Orders. |
| SalesReceiptLineItems | Create, update, delete, and query QuickBooks Sales Receipt Line Items. |
| SalesReceipts | Create, update, delete, and query QuickBooks Sales Receipts. |
| SalesReps | Create, update, delete, and query QuickBooks Sales Rep entities. |
| SalesTaxCodes | Create, update, delete, and query QuickBooks Sales Tax Codes. |
| SalesTaxItems | Create, update, delete, and query QuickBooks Sales Tax Items. |
| ShippingMethods | Create, update, delete, and query QuickBooks Shipping Methods. |
| StandardTerms | Create, update, delete, and query QuickBooks Standard Terms. |
| StatementCharges | Create, update, delete, and query QuickBooks Statement Charges. |
| TimeTracking | Create, update, delete, and query QuickBooks Time Tracking events. |
| ToDo | Create, update, delete, and query QuickBooks To Do entries. |
| TransferInventory | Query and delete QuickBooks Transfer Inventory transactions. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. |
| TransferInventoryLineItems | Create and query QuickBooks Transfer Inventory Line Items. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. |
| Transfers | Create, update, and query QuickBooks transfers. Requires QBXML version 12.0 or higher. |
| UnitOfMeasure | Create, update, delete, and query QuickBooks units of measure. QuickBooks requires QBXML version 7.0 or higher to use this table. |
| UnitOfMeasureDefaultUnits | Create and query QuickBooks unit-of-measure default units. QuickBooks requires QBXML version 7.0 or higher to use this table. |
| UnitOfMeasureRelatedUnits | Create and query QuickBooks unit-of-measure related units. QuickBooks requires QBXML version 7.0 or higher to use this table. |
| VehicleMileage | Create, update, delete, and query QuickBooks Vehicle Mileage entities. QuickBooks requires QBXML version 6.0 or higher to use this table. |
| VendorCreditExpenseItems | Create, update, delete, and query QuickBooks Vendor Credit Expense Line Items. |
| VendorCreditLineItems | Create, update, delete, and query QuickBooks Vendor Credit Line Items. |
| VendorCredits | Create, update, delete, and query QuickBooks Vendor Credits. |
| Vendors | Create, update, delete, and query QuickBooks Vendors. |
| VendorTypes | Create, update, delete, and query QuickBooks Vendor Types. |
| WorkersCompCodeLines | Query QuickBooks Workers Comp Code entries by line. Requires QBXML Version 7.0 or higher. |
| WorkersCompCodes | Query QuickBooks Workers Comp Code entries. Requires QBXML Version 7.0 or higher. |
Create, update, delete, and query QuickBooks POS customers.
The Customers table allows you to select, insert, update, and delete Customers within QuickBooks POS.
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.
| 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
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>')
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'
| 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. |
| 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. |
Create, update, delete, and query QuickBooks POS Customer Shipping Addresses.
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.
| 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 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')
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 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'
| 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. |
| 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. |
Create, update, delete, and query QuickBooks POS Departments.
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.
| 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%'
To create a new department record, the DepartmentName field is required.
INSERT INTO Departments (DepartmentName, DepartmentCode, DefaultMarginPercent)
VALUES ('Sports Wear', 'SPW', '7.25')
Any field that is not read-only can be updated.
UPDATE Departments SET DefaultMarkupPercent='20.50' WHERE ListId='-1234567890123456789'
| 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. |
Create, update, and query QuickBooks POS Employees.
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.
| 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%'
To create a new employee record, the LoginName field is required.
INSERT INTO Employees (LoginName, FirstName, LastName)
VALUES ('japple', 'John', 'Apple')
Any field that is not read-only can be updated.
UPDATE Employees SET Phone='555-123-9876' WHERE ListId='-1234567890123456789'
| 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. |
| 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. |
Create and query QuickBooks POS Inventory Cost Adjustment Items.
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.
| 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 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')
| 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. |
Create, update, and query QuickBooks POS Inventory Cost Adjustments.
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.
| 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'
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>')
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'
| 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. |
Create and query QuickBooks POS Inventory Quantity Adjustment Items.
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.
| 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 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')
| 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. |
Create, update, and query QuickBooks POS Inventory Quantity Adjustments.
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.
| 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'
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>')
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'
| 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. |
Create, update, and query QuickBooks POS Item Pictures.
| 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. |
Create, update, delete, and query QuickBooks POS Items.
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.
| 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
To create a new item record, the DepartmentListId field is required.
INSERT INTO Items (DepartmentListId, Desc1, Cost)
VALUES ('-1234567890123456789', 'T-Shirt', '9.99')
Any field that is not read-only can be updated.
UPDATE Items SET Size='L' WHERE ListId='-1000000000000000001'
| 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. |
Create, update, delete, and query QuickBooks POS Price Adjustment Items.
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.
| 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 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')
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 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'
| 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. |
Create, update, and query QuickBooks POS Price Adjustments.
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.
| 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'
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>')
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'
| 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. |
Create, update, delete, and query QuickBooks POS Price Discount Items.
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.
| 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 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')
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 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'
| 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. |
Create, update, and query QuickBooks POS Price Discounts.
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.
| 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'
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>')
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'
| 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. |
Create, update, delete, and query QuickBooks POS Purchase Order Items.
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.
| 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 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)
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 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'
| 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. |
Create, update, delete, and query QuickBooks POS Purchase Orders.
The PurchaseOrders table allows you to SELECT, INSERT, UPDATE, and DELETE Purchase Orders within QuickBooks POS.
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.
| 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'
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>')
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'
| 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. |
Create, update, delete, and query QuickBooks POS Sales Order Items.
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.
| 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 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)
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 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'
| 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. |
Create, update, delete, and query QuickBooks POS Sales Orders.
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.
| 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'
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>')
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'
| 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. |
Create, update, and query QuickBooks POS Sale Order Items.
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.
| 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 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)
| Name | Type | ReadOnly | Filter Type | Description |
| ID | String | True |
Index line identifier, auto generated by CData. | |
| 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. |
Create, update, and query QuickBooks POS Sales Receipts.
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.
| 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'
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>')
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".
| 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. |
Create, update, and query QuickBooks POS employee time entries.
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.
| 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%'
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')
Any field that is not read-only can be updated.
UPDATE TimeEntries SET ClockOutTime='2014-08-04T19:15:20-04:00' WHERE ListId='-1234567890123456789'
| 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. |
Create, update, delete and query QuickBooks POS vendors.
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.
| 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%'
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')
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'
| 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. |
| 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. |
Create and query QuickBooks POS Voucher Items.
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.
| 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 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)
| 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. |
Create, update, delete, and query QuickBooks POS Vouchers.
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.
| 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'
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>')
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".
| 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 are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
| 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. |
| 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. |
| 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. |
Query the company information from QuickBooks POS.
The CompanyInfo table returns the current QBPOS company used by the application.
SELECT * FROM CompanyInfo
| 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. |
Query QuickBooks POS Custom Fields.
| 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. |
Query QuickBooks POS Customer Rewards.
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.
| 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
| 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. |
| 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. |
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 Sync App. 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>
The connection string 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 in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a 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. |
| Property | Description |
| URL | The URL for the CData QuickBooks Desktop Gateway. For example, http://localhost:2080. |
| User | A username for the CData QuickBooks Desktop Gateway connection (if required by the Desktop Gateway). |
| Password | A password for the CData QuickBooks Desktop Gateway connection. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
| Property | Description |
| LogModules | Core modules to be included in the log file. |
| 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. |
| 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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| 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. |
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
| 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. |
The name of the CompanyFile to open.
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 CData QuickBooks Desktop Gateway user.
The QuickBooks Point Of Sale software version.
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.
Specifies whether to use practice mode within QuickBooks Point Of Sale.
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.
The name of the developer's application.
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.
The version of QBPOSXML used in the outgoing message.
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 |
This section provides a complete list of the RemoteConnector properties you can configure in the connection string for this provider.
| Property | Description |
| URL | The URL for the CData QuickBooks Desktop Gateway. For example, http://localhost:2080. |
| User | A username for the CData QuickBooks Desktop Gateway connection (if required by the Desktop Gateway). |
| Password | A password for the CData QuickBooks Desktop Gateway connection. |
The URL for the CData QuickBooks Desktop Gateway. For example, http://localhost:2080.
If the URL is specified, the Sync App 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 Sync App's request to QuickBooks POS and return the response.
A username for the CData QuickBooks Desktop Gateway connection (if required by the Desktop Gateway).
The Desktop Gateway must require user authentication before this property is used.
A password for the CData QuickBooks Desktop Gateway connection.
The Desktop Gateway must require user authentication before this property is used.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
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 |
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the Sync App opens a connection to QuickBooks POS and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
| AUTO | Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
| ALWAYS | The connection is always SSL enabled. |
| NEVER | The connection is not SSL enabled. |
| TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Core modules to be included in the log file. |
Core modules to be included in the log file.
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| 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. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
The path to a directory which contains the schema files for the Sync App (.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%\\CData\\QuickBooksPOS Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.
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.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.
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.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| 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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| 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. |
A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed.
If QuickBooks POS is generating internal errors (in particular with automatic login), setting this delay to a positive value may solve the problem.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
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.
| 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. |
The maximum number of results to return per page from QuickBooks POS.
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.
This property indicates whether or not to include pseudo columns as columns to the table.
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, "*=*".
The value in seconds until the timeout error is thrown, canceling the operation.
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 Sync App throws an exception.
A filepath pointing to the JSON configuration file containing your custom views.
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App 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 Sync App.
This User Defined View configuration file is formatted as follows:
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"