PriceRules
Create, update, delete, and query price rules.
Table-Specific Information
Select
The connector processes all filters client-side within the connector. The following queries are the only ones processed server-side:SELECT * FROM PriceRules WHERE Id = '123'
Insert
You must specify the Title, TargetType, TargetSelection, ValueType, Value, CustomerSelection, StartsAt, and AllocationMethod columns to create a price rule. For example:
INSERT INTO PriceRules (Title, TargetType, TargetSelection, ValueType, Value, CustomerSelection, StartsAt, AllocationMethod) VALUES ('SUMMERSALE10OFF', 'line_item', 'all', 'fixed_amount', '-10.0', 'all', '2017-01-19T17:59:10Z', 'across')
Update
You must specify the Id to update a price rule. For example:
UPDATE PriceRules SET Title = 'SUMMERSALE10OFF' WHERE Id = '123'
Delete
You must specify the Id of the price rule to delete it.
DELETE FROM PriceRules WHERE Id = '123'
Columns
| Name | Type | ReadOnly | References | Description |
| Id [KEY] | Long | True |
A unique numeric identifier for the price rule. | |
| AllocationMethod | String | False |
The allocation method of the price rule. Valid values: each: The discount is applied to each of the entitled items. across: The calculated discount amount will be applied across the entitled items. | |
| CustomerSelection | String | False |
The customer selection for the price rule. Valid values: all: The price rule is valid for all customers. prerequisite: The customer must either belong to one of the customer saved searches specified by PrerequisiteSavedSearchIds. | |
| EntitledCollectionIds | String | False |
A list of Ids of collections whose products will be eligible to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. It can't be used in combination with EntitledProductIds or EntitledVariantIds. | |
| EntitledCountryIds | String | False |
A list of Ids of shipping countries that will be entitled to the discount. It can be used only with TargetType set to shipping_line and TargetSelection set to entitled. | |
| EntitledProductIds | String | False |
A list of Ids of products that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. | |
| EntitledVariantIds | String | False |
A list of Ids of product variants that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. | |
| OncePerCustomer | Bool | False |
Whether the generated discount code will be valid only for a single use per customer. This is tracked using customer Id. | |
| PrerequisiteCustomerIds | String | False |
A list of customer Ids which for the price rule to be applicable, the customer must match one of the specified customers. If PrerequisiteCustomerIds is populated, then PrerequisiteSavedSearchIds must be empty. | |
| PrerequisiteQuantityRange | String | False |
The minimum number of items for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The quantity of an entitled cart item must be greater than or equal to this value. | |
| PrerequisiteSavedSearchIds | String | False |
A list of customer saved search Ids. For the price rule to be applicable, the customer must be in the group of customers matching a customer saved search. If PrerequisiteSavedSearchIds is populated, then PrerequisiteCustomerIds must be empty. | |
| PrerequisiteShippingPriceRange | String | False |
The maximum shipping price for the price rule to be applicable. It has the following property: less_than_or_equal_to: The shipping price must be less than or equal to this value. | |
| PrerequisiteSubtotalRange | String | False |
The minimum subtotal for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The subtotal of the entitled cart items must be greater than or equal to this value for the discount to apply. | |
| TargetSelection | String | False |
The target selection method of the price rule. Valid values: all: The price rule applies the discount to all line items in the checkout, entitled: The price rule applies the discount to selected entitlements only. | |
| TargetType | String | False |
The target type that the price rule applies to. Valid values: line_item: The price rule applies to the cart's line items, shipping_line: The price rule applies to the cart's shipping lines. | |
| UsageLimit | Int | False |
The maximum number of times the price rule can be used, per discount code. | |
| PrerequisiteProductIds | String | False |
List of product ids that will be a prerequisites for a Buy X Get Y type discount. The PrerequisiteProductIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product variant is included in PrerequisiteVariantIds, then PrerequisiteProductIds can't include the ID of the product associated with that variant. | |
| PrerequisiteVariantIds | String | False |
List of variant ids that will be a prerequisites for a Buy X Get Y type discount. The EntitledVariantIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product is included in PrerequisiteProductIds, then PrerequisiteVariantIds can't include the ID of any variants associated with that product. | |
| PrerequisiteCollectionIds | String | False |
List of collection ids that will be a prerequisites for a Buy X Get Y discount. The EntitledCollectionIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Cannot be used in combination with PrerequisiteProductIds or PrerequisiteVariantIds. | |
| Value | Decimal | False |
The value of the price rule. If if the value of target_type is shipping_line, then only -100 is accepted. The value must be negative. | |
| ValueType | String | False |
The value type of the price rule. Valid values: fixed_amount: Applies a discount of value as a unit of the store's currency. For example, if value is -30 and the store's currency is USD, then $30 USD is deducted when the discount is applied, percentage: Applies a percentage discount of value. For example, if value is -30, then 30% will be deducted when the discount is applied. If TargetType is shipping_line, then only percentage is accepted. | |
| PrerequisiteToEntitlementQuantityRatio | String | False |
Buy/Get ratio for a Buy X Get Y discount. prerequisite_quantity defines the necessary 'buy' quantity and entitled_quantity the offered 'get' quantity. The PrerequisiteToEntitlementQuantityRatio can be used only with: ValueType set to percentage, TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each, PrerequisiteProductIds or PrerequisiteVariantIds or PrerequisiteCollectionIds defined and EntitledProductIds or EntitledVariantIds or EntitledCollectionIds defined. | |
| Images | String | False |
A list of image objects. | |
| ProductType | String | False |
A categorization that a product can be tagged with, commonly used for filtering and searching. | |
| Options | String | True |
Custom product property names. | |
| PublishedScope | String | False |
Indicates whether the product is published to the Point of Sale channel. | |
| Tags | String | False |
A categorization that a product can be tagged with. Use commas to separate categories. | |
| TemplateSuffix | String | True |
The suffix of the liquid template being used. | |
| Title | String | False |
The name of the product. | |
| Vendor | String | False |
The name of the vendor of the product. | |
| StartsAt | Datetime | False |
The date and time when the price rule starts. | |
| EndsAt | Datetime | False |
The date and time when the price rule ends. Must be after StartsAt. | |
| PublishedAt | Datetime | True |
The date and time when the product was published to the Online Store channel. | |
| CreatedAt | Datetime | True |
The date and time when the price rule was created. | |
| UpdatedAt | Datetime | True |
The date and time when the price rule was last modified. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.
| Name | Type | Description |
| Published | Bool |
Set to true to publish the product or to false to not publish it |
| MetafieldsGlobalTitleTag | String |
The name of the product, to be used for SEO purposes. |
| MetafieldsGlobalDescriptionTag | String |
The description of the product, to be used for SEO purposes. |