TDV Adapter for QuickBooks POS

Build 21.0.8137

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 Point of Sale 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

Insert

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

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

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

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

Update

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

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

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

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

Columns

Name Type ReadOnly Filter Type Description
ListID [KEY] String True Single

The unique identifier, generated by QuickBooks POS.

CustomerID String False Range

The user-specified Id for the customer.

FullName String True

The full name of the customer.

Salutation String False Range

A salutation, such as Mr., Mrs., etc.

FirstName String False Range

The first name of the customer as stated in the address info.

LastName String False Range

The last name of the customer as stated in the address info.

CompanyName String False Range

The name of the company of the customer.

Phone String False Range

The primary telephone number for the customer.

Phone2 String False Range

An alternate telephone or fax number for the customer.

Phone3 String False Range

An alternate telephone or fax number for the customer.

Email String False Range

The email address of the customer.

IsOkToEMail Boolean False Single

Whether or not the customer can be notified via email.

CustomerType String False Range

A customer type within QuickBooks POS.

Notes String False Range

Notes on this customer.

BillAddress_Street String False

Street address of the billing address of the customer.

BillAddress_Street2 String False

The second line of the street address in the billing address of the customer.

BillAddress_City String False

City name for the billing address of the customer.

BillAddress_State String False

State name for the billing address of the customer.

BillAddress_PostalCode String False

Postal code for the billing address of the customer.

BillAddress_Country String False

Country for the billing address of the customer.

IsNoShipToBilling Boolean False Single

Whether or not the billing address can be used as a shipping address.

DefaultShipAddress String False

The default shipping address of the customer.

ShipAddressesAggregate String False

An aggregate of the shipping address data, which can be used for adding a customer and their shipping address locations.

IsAcceptingChecks Boolean False Single

Indicates whether checks are being accepted from this customer. The default is True.

IsUsingChargeAccount Boolean False Single

Indicates whether the customer is using a charge account.

StoreExchangeStatus String True Single

In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.

TaxCategory String False Range

The sales tax category.

CustomerDiscPercent Double False Range

The suggested customer discount percentage for all items listed on a sales receipt. Note that you cannot assign both a discount percentage and a price level to a customer.

CustomerDiscType String False Single

The customer discount type. Possible values are None, PriceLevel, and Percentage.

PriceLevelNumber String False Single

The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.

LastSale Datetime True Range

The time of the last purchase made by this customer.

AmountPastDue Decimal True Range

The amount past due for a transaction.

IsUsingWithQB Boolean False Single

Whether or not the customer is using QuickBooks POS with QuickBooks.

AccountBalance Decimal True Range

The balance of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.

AccountLimit Decimal True Range

The limit of the account owned by this customer, including subcustomers. Available only when QuickBooks POS is being used with QuickBooks and only if UseWithQB is True.

IsRewardsMember Boolean False Single

Indicates whether the customer is a rewards member.

RewardAggregate String True

An aggregate of the reward data for the customer.

CustomFieldsOwnerID String False Multi

Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).

CustomFields String False

Custom fields returned from QuickBooks POS and formatted into XML.

TimeCreated Datetime True Range

When the customer was created.

TimeModified Datetime True Range

When the customer was last modified.

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 21.0.8137