CData Sync App は、QuickBooks POS データをデータベース、データレイク、またはデータウェアハウスに継続的にパイプライン化する簡単な方法を提供し、分析、レポート、AI、および機械学習で簡単に利用できるようにします。
QuickBooks コネクタはCData Sync アプリケーションから使用可能で、QuickBooks からデータを取得して、サポートされている任意の同期先に移動できます。
CData Sync App は、QuickBooks Point Of Sale Basic およびProfessional エディションをサポートします。QuickBooks Point of Sale Multi-Store エディションはサポートされません。
必須プロパティについては、設定タブを参照してください。
通常必須ではない接続プロパティについては、高度な設定タブを参照してください。
Sync App はQuickBooks Gateway 経由でQuickBooks にリクエストを作成します。QuickBooks Gateway はQuickBooks と同じマシン上で動作し、軽量の組み込みWeb サーバーを介して接続を受け入れます。サーバーはSSL/TLS をサポートし、ユーザーにリモートマシンからのセキュアな接続を可能にします。 初めて接続するときは、Sync App をQuickBooks で認証する必要があります。詳しくは、Using the QuickBooks Gateway ガイドを参照してください。
Follow the steps below to authorize with QuickBooks and connect to a company file when both QuickBooks and the Sync App are running on your local machine.
If you want to connect to the company file when QuickBooks is closed, set the CompanyFile connection option when you execute commands. QuickBooks will open automatically in the background with the file specified.
Note that if QuickBooks is open through the application UI, only that CompanyFile can be used.
The CData QuickBooks Desktop Gateway can be used to read and write to QuickBooks in situations where direct COM access to QuickBooks is not available (e.g., ASP.NET, Java, or QuickBooks on a remote machine). Follow the procedure below to connect to QuickBooks for the first time through the Desktop Gateway:
Open the CData QuickBooks Desktop Gateway from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.
Note: The CData QuickBooks Desktop Gateway does not use the User and Password properties to access QuickBooks; the User and Password properties authenticate the user. Authentication to QuickBooks is handled by the ApplicationName property.
NOTE: that if the QuickBooks UI is open, you can only connect to that company file. Additionally, the user permissions you specify for the Desktop Gateway must match the user permissions you used for QuickBooks. The Desktop Gateway installs as a service in the current user account.
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 is installed. The server supports SSL/TLS, enabling users to connect securely from remote machines.
The first time you connect to QuickBooks, you must authorize your application. Complementing the per-application authentication of QuickBooks, the Desktop Gateway has per-user authentication. Before connecting to QuickBooks for the first time, configure at least one Desktop Gateway user.
You can configure users through the UI on the ユーザー tab. You can then follow the procedure in はじめに to connect an application to QuickBooks. After connecting, you can monitor QuickBooks connections on the ステータス tab.
It is recommended to configure the Desktop Gateway in the UI, but you can also run the Desktop Gateway from the command line. This can simplify deploying the Desktop Gateway in scenarios where normally there is not a user logged in, such as running a Web server. See the Advanced page to configure the Desktop Gateway when you are not using the UI.
The Desktop Gateway automatically manages the connection to QuickBooks, but you can configure almost every aspect of how users connect to QuickBooks through the Desktop Gateway. The following pages outline the capabilities of the Desktop Gateway and how to get started.
The Users tab provides an interface to add, edit, and delete users. At least one user must be added before communicating with QuickBooks.
This tab displays a list of existing users along with information about the user's configuration.
When adding or editing a user, the following options are available:
| Basic Authentication (default): | Authenticates the user with a username and password. |
| Windows Authentication: | Authenticates the user as a Windows user. In this case, the Password field is not applicable. When the Desktop Gateway receives a connection request, it authenticates the user to Windows using the credentials supplied in the request. |
| Full: | Allows read and write access for the user. |
| Read-only: | Restricts the user to read-only operations, so that QuickBooks data cannot be modified. |
The Test Connection button provides a quick way to verify that the application can connect with QuickBooks.
When a user is added, the Desktop Gateway prompts you to authorize the application with QuickBooks, if necessary.
The Import button prompts you to select an .ini file containing one or more user profiles.
Imported .ini files must conform to the following syntax:
[MyUserName] Config1 = Value1 Config2 = Value2 ...The following configurations are available:
Here is an example configuration:
[TestUser] Password = SamplePassword AuthMode = 0 Software = QB Desktop CompanyFile = C:\Users\test\Documents\samplefile.qbw AppName = Remote Connector ReadOnly = 0
The Status tab provides a log of the activity happening with the Desktop Gateway. Logs can be cleared or copied by right-clicking in the Recent Activity window.
You can adjust the detail of the logs to include information useful when troubleshooting: Select the granularity in the Log Mode menu on the Advanced tab. On the Advanced tab, you can also configure the Desktop Gateway to write logs to a file and select the log rotation interval.
The Advanced tab enables granular control over the Desktop Gateway's server.
The Desktop Gateway contains an embedded Web server that runs as a Windows service and listens for HTTP requests. Each request contains the XML data to be communicated to QuickBooks, as well as configuration settings specifying how the connection is opened. The Desktop Gateway then communicates with QuickBooks via COM, and returns the QuickBooks response (or an error message) in the HTTP reply.
This chapter details how to control each of these aspects of connecting to QuickBooks through the UI, command-line interface, and the registry. The following sections detail the options available on the Advanced tab.
| 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 company files must first go through QuickBooks. If QuickBooks is closed, this means that for each attempt to connect to the company file, QuickBooks needs to be launched and then closed again. By default the Desktop Gateway queues requests for data and performs the necessary authentication for each request. The following options can be used to override this behavior and keep the connection to the company file alive after the query finishes executing, so further requests will respond more quickly.
Warning: If a user attempts to manually open QuickBooks while a persistent connection is opened, QuickBooks will return an error stating that the company file is already in use.
The following options are used to configure TLS/SSL:
In addition to the UI, the Desktop Gateway has a command-line interface that makes it easy to deploy on machines where a user is not always logged in, for example, a Web server. To facilitate deployment to these environments, the Desktop Gateway contains two executables:
| RemoteConnector.exe | Provides the user interface and allows configuration of the application. |
| RemoteConnectorService.exe | Processes requests and performs all interaction with QuickBooks. |
The syntax for managing the Desktop Gateway Windows service from the command line is as follows:
RemoteConnectorService.exe /Service <Command>
The following commands are available:
| Install | Installs the Windows service. |
| Delete | Deletes the Windows service. |
| Start | Starts the Windows service. |
| Stop | Stops the Windows service. |
| State | Reports the current state of the Windows service (started or stopped). |
| Auto | Changes the Windows service startup type to Automatic. |
| Manual | Changes the Windows service startup type to Manual. |
| Disabled | Changes the Windows service startup type to Disabled. |
All configuration data is read from the registry at "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector". Each user will have a separate subkey with user-specific settings. For instance "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector\User1".
| Name | Type | Description |
| LocalAuth | String | A randomly generated administrator password that is used for authorization between the user interface and the Windows service. This is only used when authorizing a user configured for Windows authentication to QuickBooks from the user interface. This may be removed or changed if desired. |
| AllowedClients | String | A comma-separated list of host names or IP addresses that can access the server. The wildcard character '*' is supported (default). If unspecified any client can connect. |
| AuthFlags | DWORD | Specifies the versions of QuickBooks to which the application can connect. The value is a binary OR of the values below, represented in hex. The default value is "0xF" (all editions are supported).
|
| CloseAndRetryConnect | DWORD | Specifies whether connection retry logic is enabled. When set to 1 (True), if an error is encountered while opening a connection to QuickBooks the application will attempt to stop the QuickBooks process and reconnect. The CloseAndRetryTimeout, CloseAndRetryCount, and CloseAndRetryErrorList settings are applicable when this setting is 1 (True). |
| CloseAndRetryTimeout | DWORD | Sets the time in seconds that the application will wait for the connection to QuickBooks to be established. The default value is 30 (seconds). If the timeout is reached, the QuickBooks process will be closed and the connection will be retried. Note that this setting should be adjusted with caution. If the timeout is set too low the QuickBooks process may not have time to open normally before reaching the timeout. This setting is only applicable when CloseAndRetryConnect is True. |
| CloseAndRetryCount | DWORD | Sets the number of times to retry the connection. If an error is encountered while opening a connection to QuickBooks, the application will stop the QuickBooks process and retry until this limit is reached. The default value is 3. This setting is only applicable when CloseAndRetryConnect is True. |
| CloseAndRetryErrorList | String | Specifies a comma-separated list of QuickBooks error codes on which to retry a connection. If QuickBooks returns an error code listed in this property, the QuickBooks process will be stopped and the connection will be retried. If the error is not in this list the application will return the error as normal. The default value is "0x80040402,0x80040408". Specify the value "*" to indicate all errors. This setting is only applicable when CloseAndRetryConnect is True. |
| QBInstanceFile | String |
Specifies the full path to the QBINSTANCEFINDER file in the QuickBooks installation. For instance: "C:\ProgramData\Intuit\QuickBooks\QBINSTANCEFINDER17.INI". This setting is only applicable when CloseAndRetryConnect is set to True. If the connection retry logic stops the QuickBooks process the specified QBINSTANCEFINDER file will be cleared of any previous entries. QuickBooks uses the QBINSTANCEFINDER file to keep track of open instances, however, in some situations it may not be properly reset after stopping the process. When specified this setting allows the application to properly reset the file after stopping the process. |
| LocalHost | String | Sets the host name or user-assigned IP interface through which connections are initiated or accepted. In most cases this does not need to be set, as the application will use the default interface on the machine. If you have multiple interfaces, you can specify the interface to use here. For instance, "192.168.1.102". |
| LogEnabled | DWORD | Enables or disables logging to a file. Logs are always written to the console. The default is 0 (False). |
| LogDir | String | Sets the path to a folder on disk where log files will be written. This is only applicable if LogEnabled is set to True. |
| LogFormat | DWORD | Sets how often new log files are created. Possible values are the following:
|
| 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. The default is 0 (False), meaning that your code controls when the connection to QuickBooks is opened and closed by calling the Open and Close methods. However, when this setting is enabled, a persistent connection to QuickBooks is established by the Desktop Gateway even when Open and Close are not used. This is helpful in situations when multiple applications may be simultaneously accessing the Desktop Gateway, because it allows them to share the connection. |
| PersistentIdleTimeout | DWORD | Sets the idle timeout for the persistent connection in seconds. If there is no activity within this time window, the connection to QuickBooks will be closed. This is only applicable when PersistentEnabled is True. |
| PromptForRegPermissions | DWORD | Specifies whether to prompt to modify registry permissions when access is not allowed. This is only applicable when saving settings from the UI. |
| RunAsService | DWORD | Run the application as a service or with the standard run-time permissions. The default value is 1 (True). |
| SSLCertPassword | String | Sets the password of the SSL certificate. |
| SSLCertStore | String | Sets the location of the SSL certificate. This may be a path to a file or the name of a Windows certificate store: "MY", "ROOT", "CA", or "SPC". |
| SSLCertSubject | String | Sets the subject of the SSL certificate. |
| SSLCertType | String | Sets the type of SSL certificate to use. A certificate must be specified when SSL is enabled. The PFX option signifies a .pfx file on disk. The User option signifies the user's Windows certificate store. The Machine option signifies the Windows certificate store of the machine. |
| SSLEnabled | DWORD | Sets whether TLS/SSL connections are allowed. The default value is 0 (False). Enabling TLS/SSL disables plaintext connections. |
| Timeout | DWORD | Sets the operational timeout for connected clients. The default value is 60. |
| UseInteractiveLogon | DWORD | Sets whether interactive or network logon will authorize users when AuthMode is set to 1 (Windows). In most cases this does not need to be set. This should be set to 1 (True) if your domain controller is Samba. The default value is 0 (False). |
| AppName | String | Sets the name of the application that will be used to provide authentication to QuickBooks when a connection is made. If this value is not set, the Desktop Gateway uses the value provided by the client. |
| CompanyFile | String | Sets the path to a QuickBooks company file (.qbw). If this is not set, the currently open company file is used. When QuickBooks is not running, this option must be set. |
| Password | String | Sets the password of the user. This is required when AuthMode is set to 0 (Basic Authentication). The Desktop Gateway application will always store the SHA-256 hash of the password for security. However, this may also be manually set to a plaintext password to allow backward compatibility. |
| AuthMode | DWORD | Sets the type of authentication to perform when the user connects. From the client side the process of connecting is exactly the same no matter which option you choose. Possible values are the following:
|
| Authorized | DWORD | Specifies whether the AppName has been authorized for the CompanyFile. If 1 (True) the AppName has been authorized with the CompanyFile. This is an indicator used by the application when changing settings in the UI. |
| ConnectionMode | String | Sets the connection mode for the user. The default is DontCare. In most cases you do not need to set this value. If this is not set, the application will connect in whatever mode QuickBooks is already open in. Possible values are the following:
|
| ReadOnly | DWORD | Specifies whether the user has read-only (1) or full access (0). |
このセクションでは、QuickBooks Sync App の高度な機能を厳選して説明します。
Sync App を使用すると、事前設定されたクエリによって内容が決定されるユーザー定義ビューと呼ばれる仮想テーブルを定義できます。 このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。 カスタムビューの作成と設定の概要については、ユーザー定義ビュー を参照してください。
SSL の設定 を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。 詳しくは、接続文字列オプションにあるSSLServerCert プロパティを参照してください。
Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシ に合致するようSync App を設定します。トンネル接続を設定することもできます。
Sync App は、QuickBooks にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。
詳しくはクエリ処理 を参照してください。
CData ログを調整するために使用可能な設定の概要については、ログ を参照してください。基本的なロギングでは、 次の2つの接続プロパティを設定するだけです。LogModules 接続プロパティを使用してログに記録する情報のサブセットを選択できる、 より洗練されたロギングをサポートする多数の機能があります。
デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。
別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。
Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。
さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。
次のプロパティを設定します。
Sync App はQuickBooks のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。
| 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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. |
ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。
クエリは、ビューに対して通常のテーブルと同様に実行することができます。
| Name | Description |
| BalanceSheetDetail | Balance Sheet Detail Report. |
| BalanceSheetStandard | Balance Sheet Standard Report. |
| BalanceSheetSummary | Balance Sheet Summary Report. |
| BillingRate | Query QuickBooks Billing Rate. Requires QBXML Version 6.0 or higher. |
| BillLinkedTransactions | Query QuickBooks Bill Linked Transactions. |
| CompanyInfo | Query the company information from QuickBooks. |
| CreditMemoLinkedTransactions | Query QuickBooks Credit Memo Linked Transactions. |
| CustomColumns | Query QuickBooks Custom Columns. |
| DeletedEntities | Query deleted Entities. |
| DeletedTransactions | Query deleted Transactions. |
| EmployeeStateTaxesDetail | Returns information of employee state taxes detail report. |
| EstimateLinkedTransactions | Query QuickBooks Estimate Linked transactions. |
| Host | Query the QuickBooks host process. The Host represents information about the QuickBooks process currently being executed. |
| InvoiceLinkedTransactions | Query QuickBooks Invoice Linked Transactions. |
| ItemReceiptLinkedTransactions | Query QuickBooks Item Receipt Linked Transactions. |
| ItemSites | Create, update, delete, and query QuickBooks Item Sites. Item sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. This table requires a minimum of QBXML version 10.0. |
| PayrollItemDetail | Returns information of Payroll item detail report (lists the payroll transactions on which each payroll item appears). |
| PayrollReviewDetail | Returns information of Payroll detail review report (provides detailed information about how QuickBooks calculates tax amounts on employee paychecks and in year-to-date transactions). |
| PayrollTransactionDetail | Returns information of Payroll transaction detail report (shows the line-item detail that appears on each payroll transaction). |
| PayrollTransactionsByPayee | Payroll transactions by payee report (lists payroll transactions, grouping them by payee). |
| Preferences | Query information about many of the preferences the QuickBooks user has set in the company file. |
| ProfitAndLossDetail | Profit & Loss Prev Year Comparison Report. |
| ProfitAndLossStandard | Profit & Loss YTD Comparison Report. |
| PurchaseOrderLinkedTransactions | Query QuickBooks Purchase Order Linked Transactions. |
| ReceivePaymentToDeposit | Returns information about payments that have been received and are ready to deposit. |
| SalesOrderLinkedTransactions | Query QuickBooks Sales Order Linked Transactions. |
| StatementChargeLinkedTransactions | Query QuickBooks Statement Charge Linked Transactions. |
| Templates | Query QuickBooks templates. |
| Transactions | Query QuickBooks transactions. You may search the Transactions using a number of values including Type, Entity, Account, ReferenceNumber, Item, Class, Date, and TimeModified. |
| VendorCreditLinkedTransactions | Query QuickBooks Vendor Credit Linked Transactions. |
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 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 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 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>
| プロパティ | 説明 |
| 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. |
| プロパティ | 説明 |
| 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. |
| プロパティ | 説明 |
| SSLServerCert | TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。 |
| プロパティ | 説明 |
| FirewallType | プロキシベースのファイアウォールで使われるプロトコル。 |
| FirewallServer | プロキシベースのファイアウォールの名前もしくはIP アドレス。 |
| FirewallPort | プロキシベースのファイアウォールのTCP ポート。 |
| FirewallUser | プロキシベースのファイアウォールに認証するために使うユーザー名。 |
| FirewallPassword | プロキシベースのファイアウォールへの認証に使われるパスワード。 |
| プロパティ | 説明 |
| ProxyAutoDetect | これは、システムプロキシ設定を使用するかどうかを示します。 |
| ProxyServer | HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。 |
| ProxyPort | ProxyServer プロキシが起動しているTCP ポート。 |
| ProxyAuthScheme | ProxyServer プロキシへの認証で使われる認証タイプ。 |
| ProxyUser | ProxyServer プロキシへの認証に使われるユーザー名。 |
| ProxyPassword | ProxyServer プロキシへの認証に使われるパスワード。 |
| ProxySSLType | ProxyServer プロキシへの接続時に使用するSSL タイプ。 |
| ProxyExceptions | ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。 |
| プロパティ | 説明 |
| LogModules | ログファイルに含めるコアモジュール。 |
| プロパティ | 説明 |
| Location | テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。 |
| BrowsableSchemas | このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。 |
| Tables | このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。 |
| Views | 使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。 |
| プロパティ | 説明 |
| DelayAfterClose | A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed. |
| MaxRows | クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。 |
| Other | これらの隠しプロパティは特定のユースケースでのみ使用されます。 |
| Pagesize | QuickBooks から返されるページあたりの結果の最大数。 |
| PseudoColumns | このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。 |
| Timeout | タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。 |
| UserDefinedViews | カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。 |
このセクションでは、本プロバイダーの接続文字列で設定可能なConnection プロパティの全リストを提供します。
| プロパティ | 説明 |
| 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 is not currently open with a CompanyFile, QuickBooks will be automatically opened in the background with the file specified. Do not set CompanyFile when QuickBooks is open. If QuickBooks 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 |
このセクションでは、本プロバイダーの接続文字列で設定可能なRemoteConnector プロパティの全リストを提供します。
| プロパティ | 説明 |
| 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.
このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。
| プロパティ | 説明 |
| SSLServerCert | TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。 |
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 |
このセクションでは、本プロバイダーの接続文字列で設定可能なFirewall プロパティの全リストを提供します。
| プロパティ | 説明 |
| FirewallType | プロキシベースのファイアウォールで使われるプロトコル。 |
| FirewallServer | プロキシベースのファイアウォールの名前もしくはIP アドレス。 |
| FirewallPort | プロキシベースのファイアウォールのTCP ポート。 |
| FirewallUser | プロキシベースのファイアウォールに認証するために使うユーザー名。 |
| FirewallPassword | プロキシベースのファイアウォールへの認証に使われるパスワード。 |
プロキシベースのファイアウォールで使われるプロトコル。
このプロパティは、Sync App がFirewallServer プロキシ経由でトンネルトラフィックを使うためのプロトコルを指定します。デフォルトでは、Sync App はシステムプロキシに接続します。この動作を無効化し次のプロキシタイプのどれかで接続するには、ProxyAutoDetect をfalse に設定します。
| タイプ | デフォルトポート | 説明 |
| TUNNEL | 80 | これが設定されている場合、Sync App はQuickBooks への接続を開き、プロキシを経由して通信が行われます。 |
| SOCKS4 | 1080 | これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 4 プロキシ経由で送信し、接続リクエストが許容されるかどうかを決定します。 |
| SOCKS5 | 1080 | これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 5 プロキシ経由で送信します。プロキシに認証が必要な場合には、FirewallUser およびFirewallPassword をプロキシが認識する認証情報に設定します。 |
HTTP プロキシへの接続には、ProxyServer およびProxyPort ポートを使ってください。HTTP プロキシへの認証には、ProxyAuthScheme、ProxyUser、およびProxyPassword を使ってください。
プロキシベースのファイアウォールの名前もしくはIP アドレス。
ファイアウォールトラバーサルを許容するために設定するIP アドレス、DNS 名、もしくはプロキシホスト名を指定するプロパティです。プロトコルはFirewallType で指定されます。このプロパティとFirewallServer を使って、SOCKS 経由での接続、もしくはトンネリングが可能です。HTTP プロキシへの接続には、ProxyServer を使用します。
Sync App はデフォルトでシステムプロキシを使うので注意してください。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定してください。
プロキシベースのファイアウォールのTCP ポート。
ファイアウォールトラバーサルを許容するために設定するプロキシベースのファイアウォールのTCP ポート。名前もしくはIP アドレスを指定するには、FirewallServer を使います。FirewallType でプロトコルを指定します。
プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallUser およびFirewallPassword プロパティは、FirewallType により指定された認証方式に則り、FirewallServer、およびFirewallPort で指定されたプロキシに対しての認証に使われます。
プロキシベースのファイアウォールへの認証に使われるパスワード。
このプロパティは、FirewallType により指定された認証メソッドに則り、FirewallServer およびFirewallPort で指定されたプロキシに渡されます。
このセクションでは、本プロバイダーの接続文字列で設定可能なProxy プロパティの全リストを提供します。
| プロパティ | 説明 |
| ProxyAutoDetect | これは、システムプロキシ設定を使用するかどうかを示します。 |
| ProxyServer | HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。 |
| ProxyPort | ProxyServer プロキシが起動しているTCP ポート。 |
| ProxyAuthScheme | ProxyServer プロキシへの認証で使われる認証タイプ。 |
| ProxyUser | ProxyServer プロキシへの認証に使われるユーザー名。 |
| ProxyPassword | ProxyServer プロキシへの認証に使われるパスワード。 |
| ProxySSLType | ProxyServer プロキシへの接続時に使用するSSL タイプ。 |
| ProxyExceptions | ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。 |
これは、システムプロキシ設定を使用するかどうかを示します。
これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。
HTTP プロキシへの接続には、ProxyServer を参照してください。SOCKS やトンネリングなどの他のプロキシには、FirewallType を参照してください。
HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。HTTP プロキシへの認証には、Sync App はHTTP、Windows(NTLM)、もしくはKerberos 認証タイプを使用することができます。
SOCKS プロキシを経由して接続する、もしくは接続をトンネルするには、FirewallType を参照してください。
デフォルトで、Sync App はsystem プロキシを使います。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定します。
ProxyServer プロキシが起動しているTCP ポート。
HTTP トラフィックをリダイレクトするHTTP プロキシが実行されているポート。ProxyServer でHTTP プロキシを指定します。その他のプロキシタイプについては、FirewallType を参照してください。
ProxyServer プロキシへの認証で使われる認証タイプ。
この値は、ProxyServer およびProxyPort で指定されるHTTP プロキシに認証するために使われる認証タイプを指定します。
Sync App は、デフォルトでsystem proxy settings を使い、追加での設定が不要です。他のプロキシへの接続をする場合には、ProxyServer およびProxyPort に加え、ProxyAutoDetect をfalse に設定します。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。
認証タイプは、次のどれかになります。
SOCKS 5 認証のような他の認証タイプを使用するには、FirewallType を参照してください。
ProxyServer プロキシへの認証に使われるユーザー名。
ProxyUser および ProxyPassword オプションは、ProxyServer で指定されたHTTP プロキシに対して接続および認証するために使用されます。
ProxyAuthScheme で使用可能な認証タイプを選択することができます。HTTP 認証を使う場合、これをHTTP プロキシで識別可能なユーザーのユーザー名に設定します。Windows もしくはKerberos 認証を使用する場合、このプロパティを次の形式のどれかでユーザー名に設定します。
user@domain domain\user
ProxyServer プロキシへの認証に使われるパスワード。
このプロパティは、NTLM(Windows)、Kerberos、もしくはHTTP 認証をサポートするHTTP プロキシサーバーに認証するために使われます。HTTP プロキシを指定するためには、ProxyServer およびProxyPort を設定します。認証タイプを指定するためにはProxyAuthScheme を設定します。
HTTP 認証を使う場合、さらにHTTP プロキシにProxyUser およびProxyPassword を設定します。
NTLM 認証を使う場合、Windows パスワードにProxyUser およびProxyPassword を設定します。Kerberos 認証には、これらを入力する必要があります。
SOCKS 5 認証もしくは、トンネリングは、FirewallType を参照してください。
デフォルトで、Sync App はsystem プロキシを使います。他のプロキシに接続する場合には、これをfalse に設定します。
ProxyServer プロキシへの接続時に使用するSSL タイプ。
このプロパティは、ProxyServer で指定されたHTTP プロキシへの接続にSSL を使用するかどうかを決定します。この値は、AUTO、ALWAYS、NEVER、TUNNEL のいずれかです。有効な値は次のとおりです。
| AUTO | デフォルト設定。URL がHTTPS URL の場合、Sync App は、TUNNEL オプションを使います。URL がHTTP URL の場合、コンポーネントはNEVER オプションを使います。 |
| ALWAYS | 接続は、常にSSL 有効となります。 |
| NEVER | 接続は、SSL 有効になりません。 |
| TUNNEL | 接続は、トンネリングプロキシを経由します。プロキシサーバーがリモートホストへの接続を開き、プロキシを経由して通信が行われます。 |
ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。
ProxyServer は、このプロパティで定義されたアドレスを除くすべてのアドレスに使用されます。セミコロンを使用してエントリを区切ります。
Sync App は、追加設定なしにデフォルトでシステムのプロキシ設定を使います。この接続のプロキシ例外を明示的に構成するには、ProxyAutoDetect をfalse に設定して、ProxyServer およびProxyPort を設定する必要があります。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。
ログファイルに含めるコアモジュール。
指定された（';' で区切られた）モジュールのみがログファイルに含まれます。デフォルトではすべてのモジュールが含まれます。
概要はログ ページを参照してください。
このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。
| プロパティ | 説明 |
| Location | テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。 |
| BrowsableSchemas | このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。 |
| Tables | このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。 |
| Views | 使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。 |
テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
Sync App のスキーマファイル（テーブルとビューの場合は.rsd ファイル、ストアドプロシージャの場合は.rsb ファイル）を含むディレクトリへのパス。このフォルダの場所は、実行ファイルの場所からの相対パスにすることができます。Location プロパティは、定義をカスタマイズしたり（例えば、カラム名を変更する、カラムを無視するなど）、新しいテーブル、ビュー、またはストアドプロシージャでデータモデルを拡張する場合にのみ必要です。
指定しない場合、デフォルトの場所は"%APPDATA%\\CData\\QuickBooks Data Provider\\Schema" となり、%APPDATA% はユーザーのコンフィギュレーションディレクトリに設定されます：
| Platform | %APPDATA% |
| Windows | APPDATA 環境変数の値 |
| Linux | ~/.config |
このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、 パフォーマンスが向上します。
このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
テーブルを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でテーブルのリストを提供すると、Sync App のパフォーマンスが向上します。
このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。
カンマ区切りのリストで使用したいテーブルを指定します。各テーブルは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。 例えば、Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space` です。
複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。
使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。
ビューを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でビューのリストを提供すると、Sync App のパフォーマンスが向上します。
このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。
カンマ区切りのリストで使用したいビューを指定します。各ビューは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。 例えば、Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space` です。
複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。
このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。
| プロパティ | 説明 |
| DelayAfterClose | A delay in milliseconds to be applied each time a connection to QuickBooks POS is closed. |
| MaxRows | クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。 |
| Other | これらの隠しプロパティは特定のユースケースでのみ使用されます。 |
| Pagesize | QuickBooks から返されるページあたりの結果の最大数。 |
| PseudoColumns | このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。 |
| Timeout | タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。 |
| UserDefinedViews | カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。 |
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.
クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
これらの隠しプロパティは特定のユースケースでのみ使用されます。
以下にリストされているプロパティは、特定のユースケースで使用可能です。通常のドライバーのユースケースおよび機能では、これらのプロパティは必要ありません。
複数のプロパティをセミコロン区切りリストで指定します。
| DefaultColumnSize | データソースがメタデータにカラムの長さを提供しない場合に、文字列フィールドのデフォルトの長さを設定します。デフォルト値は2000です。 |
| ConvertDateTimeToGMT | 日時の値を、マシンのローカルタイムではなくGMT グリニッジ標準時に変換するかどうかを決定します。 |
| RecordToFile=filename | 基底のソケットデータ転送を指定のファイルに記録します。 |
QuickBooks から返されるページあたりの結果の最大数。
Pagesize プロパティは、QuickBooks から返されるページあたりの結果の最大数に影響を与えます。より大きい値を設定すると、1ページあたりの消費メモリが増える代わりに、パフォーマンスが向上する場合があります。
このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。
タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。
Timeout の有効期限が切れても処理が完了していない場合は、Sync App は例外をスローします。
Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。
Timeout の有効期限が切れても処理が完了していない場合は、Sync App は例外をスローします。
カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。
ユーザー定義ビューは、UserDefinedViews.json というJSON 形式のコンフィギュレーションファイルで定義されています。Sync App は、このファイルで指定されたビューを自動的に検出します。
また、複数のビュー定義を持ち、UserDefinedViews 接続プロパティを使用して制御することも可能です。このプロパティを使用すると、指定されたビューのみがSync App によって検知されます。
このユーザー定義ビューのコンフィギュレーションファイルは、次のようにフォーマットされています。
次に例を示します。
{
"MyView": {
"query": "SELECT * FROM Customers WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
UserDefinedViews 接続プロパティを使用して、JSON コンフィギュレーションファイルの場所を指定します。次に例を示します。
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json指定されたパスは引用符で囲まれていないことに注意してください。