JDBC Driver for QuickBooks POS

Build 22.0.8462

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.

Single Filters Single filters make direct comparisons by using the = comparison.
Range Filters Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator.

To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available.

To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.


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

Insert

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

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

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

Update

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

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

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

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

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

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

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

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

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

Columns

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

The unique identifier, generated by QuickBooks POS.

PriceDiscountName String False Range

The name of the price discount.

PriceDiscountReason String False Range

The status of the price discount.

PriceDiscountType String False Single

The type of price discount.

Associate String False Range

The employee making the adjustment.

LastAssociate String True Range

The employee who made the last adjustment transaction.

ItemsCount String True Range

The number of line items in the insert that added the transaction.

ItemsAggregate String False

An aggregate of the line item data, which can be used for adding the transaction and its line items.

StartDate Datetime False Range

The starting date of the discount.

StopDate Datetime False Range

The ending date of the discount.

IsInactive Boolean False Single

Determines whether the discount is active.

PriceDiscountPriceLevels String False

The price levels the discount should be applied to.

PriceDiscountXValue Decimal False Range

The discount price over the X value.

PriceDiscountYValue Decimal False Range

The discount price over the Y value.

IsApplicableOverXValue Boolean False Single

Determines whether the discount is applicable over the X value.

StoreExchangeStatus String True Single

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

CustomFieldsOwnerID String False Multi

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

CustomFields String False

Custom fields returned from QuickBooks POS and formatted into XML.

TimeCreated Datetime True Range

When the transaction was created.

TimeModified Datetime True Range

When the transaction was last modified.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462