QuickBooks Point of Sale Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

QuickBooks POS Version Support

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

Establish a Connection¶

Connect to QuickBooks¶

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

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

Connect to a Local Company File¶

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

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

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

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

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

Connection Troubleshooting¶

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

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

Use the CData QuickBooks Desktop Gateway¶

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

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

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

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

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

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

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

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

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

You can enable SSL/TLS on the Advanced tab.

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

Important Notes¶

Configuration Files and Their Paths¶

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

CData QuickBooks Desktop Gateway¶

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

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

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

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

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

Users¶

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

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

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

• User: Sets the username. This is required.
• Password: Sets the password for the user. This is required when using Basic authentication (default).
• Company File: Specifies the company file with which the application will communicate. By default this is the company file that is currently open in QuickBooks POS. Set this to the name of the company file (Note: Specifying the entire file path will result in a "Cannot connect to database" error from QuickBooks POS). A company file must be specified in order to access the company file when QuickBooks POS is closed.
• Authentication: Specifies the type of authentication to perform when the user connects. The Desktop Gateway supports the following authentication methods: | | | |-------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | | | | Basic Authentication (default): | Authenticates the user with a username and password. | | Windows Authentication: | Authenticates the user as a Windows user. In this case the Password field is not applicable. When the Desktop Gateway receives a connection request, it will authenticate the user to Windows using the credentials supplied in the request. |
• Application Name: Optionally sets the name of the application as seen by QuickBooks POS. Authentication to QuickBooks POS is handled based on the provided application name.
• Data Access: Specifies the allowed access for the user. | | | |----------------|-------------------------------------------------------------------------------------| | | | | Full: | Allows read and write access for the user. | | Read-only: | Restricts the user to read-only operations. QuickBooks POS data cannot be modified. |

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

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

Status¶

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

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

Advanced¶

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

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

Log Options¶

• Write Logs to a Folder: Enables or disables writing log files to the specified folder in addition to writing logs to the Status tab.
• Folder: Specifies the folder where log files are written.
• Log Rotation: Determines how logs are organized on disk. Creates one file for each day, week, or month, depending on the following values: | | | |----------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------| | | | | Daily (default): | Uses a new log file every day. Files are written with the format "yyyy_MM_dd.txt". For example, "2013_09_23.txt". | | Weekly: | Uses a new log file every week. Files are written with the format "yyyy_ww.txt". For example, "2013_34.txt", where 34 means this is the 34th week of 2013. | | Monthly: | Uses a new log file every month. Files are written with the format "yyyy_mm.txt". For example, "2013_09.txt". |
• Log Mode: Sets the verbosity of the log output. In most situations, Info (the default) is sufficient. The Verbose option is helpful for debugging purposes.

IP Options¶

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

Enable Persistent Connections¶

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

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

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

Enable TLS/SSL¶

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

The following options are used to configure TLS/SSL:

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

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

Command-Line Interface¶

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

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:

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

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

The following command-line options are available:

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

Registry Keys¶

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

Application-Level Settings¶

User-Level Settings¶

Advanced Features¶

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

User Defined Views

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

SSL Configuration

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

Proxy

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

Query Processing

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

User Defined Views¶

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File¶

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Define Views Using DDL Statements¶

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

Create a View¶

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

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

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

Alter a View¶

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

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

The view is then updated in the JSON configuration file.

Drop a View¶

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views¶

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

Work with User Defined Views¶

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

Insert Parent and Child Records¶

Use Case¶

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

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

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

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

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

The following subsection describes how this might be done.

Methods for Inserting Parent/Child Records¶

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

Temporary (#TEMP) tables¶

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

Reference the #TEMP table with the following syntax:

TableName#TEMP

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

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

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

For example:

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

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

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

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

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

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

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

Direct XML Insertion¶

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

For example:

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

OR

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

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

Now insert the values:

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

OR

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

Example for QuickBooks POS¶

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

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

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


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

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


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



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

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

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

SSL Configuration¶

Customize the SSL Configuration¶

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

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

Data Model¶

The Data Model has two parts: Tables and Views.

Tables

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

Views

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

Tables¶

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

Jitterbit Connector for QuickBooks POS Tables¶

Customers¶

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

Table Specific Information¶

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

Select¶

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

SELECT * FROM Customers WHERE FirstName LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014' AND AccountBalance > 100.00

Insert¶

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

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

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

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

Update¶

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

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

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

UPDATE Customers SET ShipAddressesAggregate = '<CustomerShipAddresses>
<Row><ShipAddressAddressName>Office</ShipAddressAddressName><ShipAddressCompany>MyCompany</ShipAddressCompany></Row>
</CustomerShipAddresses>'
WHERE ListId = '-1234567890123456789'

Columns¶

CustomerShipAddresses¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM CustomerShipAddresses WHERE FirstName LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

Create a new customer:

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

Add a shipping address to an existing customer:

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

Update¶

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

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

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

Delete¶

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

DELETE FROM CustomerShipAddresses WHERE ListId = '-1234567890123456789' AND ShipAddressAddressName = 'Home'

Columns¶

Departments¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM Departments WHERE DepartmentName LIKE '%WEAR%'

Insert¶

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

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

Update¶

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

UPDATE Departments SET DefaultMarkupPercent = '20.50' WHERE ListId = '-1234567890123456789'

Columns¶

Employees¶

Create, update, and query QuickBooks POS Employees.

Table Specific Information¶

Select¶

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

SELECT * FROM Employees WHERE LoginName LIKE '%test%'

Insert¶

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

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

Update¶

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

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

Delete¶

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

Columns¶

InventoryCostAdjustmentItems¶

Create and query QuickBooks POS Inventory Cost Adjustment Items.

Table Specific Information¶

Select¶

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

SELECT * FROM InventoryCostAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

INSERT INTO InventoryCostAdjustmentItems (TxnId, ItemListId, ItemNewCost)
VALUES ('-1234567890123456789', '-1000000000000000004', '2.00')

Columns¶

InventoryCostAdjustments¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM InventoryCostAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

UPDATE InventoryCostAdjustments SET ItemsAggregate = '<InventoryCostAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewCost>19.99</ItemNewCost></Row>
</InventoryCostAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'

Columns¶

InventoryQtyAdjustmentItems¶

Create and query QuickBooks POS Inventory Quantity Adjustment Items.

Table Specific Information¶

Select¶

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

SELECT * FROM InventoryQtyAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

INSERT INTO InventoryQtyAdjustmentItems (TxnId, ItemListId, ItemNewQuantity)
VALUES ('-1234567890123456789', '-1000000000000000004', '10')

Columns¶

InventoryQtyAdjustments¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM InventoryCostAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

UPDATE InventoryQtyAdjustments SET ItemsAggregate = '<InventoryQtyAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewQuantity>30</ItemNewQuantity></Row>
</InventoryQtyAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'

Columns¶

ItemPictures¶

Create, update, and query QuickBooks POS Item Pictures.

Columns¶

Items¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM Items WHERE QuantityOnHand < 5

Insert¶

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

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

Update¶

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

UPDATE Items SET Size = 'L' WHERE ListId = '-1000000000000000001'

Columns¶

PriceAdjustmentItems¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM PriceAdjustmentItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

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

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

Update¶

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

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

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

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

Delete¶

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

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

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

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

DELETE FROM PriceAdjustmentItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'

Columns¶

PriceAdjustments¶

Create, update, and query QuickBooks POS Price Adjustments.

Table Specific Information¶

Select¶

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

SELECT * FROM PriceAdjustments WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

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

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

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

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

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

UPDATE PriceAdjustments SET ItemsAggregate = '<PriceAdjustmentItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemNewPrice>30.00</ItemNewPrice></Row>
</PriceAdjustmentItems>'
WHERE TxnId = '-1234567890123456789'

Columns¶

PriceDiscountItems¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM PriceDiscountItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

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

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

Update¶

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

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

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

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

Delete¶

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

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

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

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

DELETE FROM PriceDiscountItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'

Columns¶

PriceDiscounts¶

Create, update, and query QuickBooks POS Price Discounts.

Table Specific Information¶

Select¶

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

SELECT * FROM PriceDiscounts WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

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

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

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

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

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

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

Columns¶

PurchaseOrderItems¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM PurchaseOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

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

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

Update¶

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

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

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

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

Delete¶

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

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

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

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

DELETE FROM PurchaseOrderItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'

Columns¶

PurchaseOrders¶

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

Table Specific Information¶

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

Select¶

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

SELECT * FROM PurchaseOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

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

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

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

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

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

UPDATE PurchaseOrders SET ItemsAggregate = '<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>30.00</ItemCost><ItemQty>10</ItemQty></Row>
</PurchaseOrderItems>'
WHERE TxnId = '-1234567890123456789'

Columns¶

SalesOrderItems¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM SalesOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

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

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

Update¶

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

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

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

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

Delete¶

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

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

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

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

DELETE FROM SalesOrderItems WHERE TxnId = '-1234567890123456789' AND ItemTxnLineId = '3'

Columns¶

SalesOrders¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM SalesOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

Update¶

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

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

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

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

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

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

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

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

UPDATE SalesOrders SET ItemsAggregate = '<SalesOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>40.00</ItemCost><ItemQty>1</ItemQty></Row>
</SalesOrderItems>'
WHERE TxnId = '-1234567890123456789'

Columns¶

SalesReceiptItems¶

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

Table Specific Information¶

Select¶

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

SELECT * FROM SalesReceiptItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert¶

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

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

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

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

INSERT INTO SalesReceiptItems (TxnId, CashTenderAmount, ItemListId, ItemPrice, ItemQty)
VALUES ('-1234567890123456789', 2.15, '-1000000000000000001', 2.00, 1)

Columns¶