PriceListRecords

Returns a collection of price list records.

Table Specific Information

Select

The connector uses the BigCommerce API to process WHERE clause conditions built with the following columns and operators:

CalculatedPrice supports the = comparison.
DateCreated supports the =, >, <, >=, and <= comparisons.
DateModified supports the =, >, <, >=, and <= comparisons.
ProductId supports the = comparison.
PriceListId supports the = comparison.
VariantId supports the = comparison.
Sku supports the = comparison.
Currency supports the = comparison.
Price supports the = comparison.
SalePrice supports the = comparison.
RetailPrice supports the = comparison.
MapPrice supports the = comparison.

The rest of the filter is executed client-side within the connector. For example, the following queries are processed server-side:

SELECT * FROM PriceListRecords WHERE PriceListId = 1 

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND SalePrice = 23

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND MapPrice = 17.99

SELECT * FROM PriceListRecords WHERE PriceListId = 1 AND VariantId = 361 AND Currency = 'USD'

Bulk Update

To update a price list record, specify the following columns: VariantId, Currency, Price, and PriceListId.

INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId) VALUES (361, 'USD', 27, 23, 1)
INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId) VALUES (362, 'USD', 28, 24, 1)
UPDATE PriceListRecords (VariantId, Currency, Price, SalePrice) SELECT VariantId, Currency, Price, SalePrice FROM PriceListRecords#TEMP

Bulk Update Using Aggregates:

INSERT INTO PriceListRecords#TEMP (VariantId, Currency, Price, SalePrice, PriceListId, BulkPricingTiers) VALUES (361, 'USD', 27, 23, 1,
                   '{ 
                    \"quantity_min\": 10," 
                    \"quantity_max\" : 12," 
                     \"type\" : \"percent\","
                      \"amount\": 1
                    }'
UPDATE PriceListRecords ( VariantId, Currency, Price, SalePrice, BulkPricingTiers) SELECT VariantId, Currency, Price, SalePrice, BulkPricingTiers FROM PriceListRecords#TEMP

Columns


Name	Type	ReadOnly	References	Description
CalculatedPrice	Double	True		The price of the variant as seen on the storefront if a price record is in effect
DateCreated	Datetime	True		The date of creation for the Price Entry.
DateModified	Datetime	True		The last modification date of the Price Entry.
ProductId	Integer	True		The id of the Product this Price Record's variant_id is associated with.
PriceListId [KEY]	Integer	True		The Price List ID with which this price set is associated
VariantId [KEY]	Integer	False		The variant with which this price set is associated.
Sku	String	False		The variant with which this price set is associated
Currency [KEY]	String	False		The 3-letter currency code with which this price set is associated
Price	Double	False		The list price for the variant mapped in a Price List
SalePrice	Double	False		The sale price for the variant mapped in a Price List
RetailPrice	Double	False		The retail price for the variant mapped in a Price List
MapPrice	Double	False		The MAP (Manufacturers Advertised Price) for the variant mapped in a Price List
BulkPricingTiers	String	False		The minimum quantity of associated variant in the cart needed to qualify for this tiers pricing
QuantityMin	Integer	False		The minimum quantity of associated variant in the cart needed to qualify for this tiers pricing
QuantityMax	Integer	False		The maximum allowed quantity of associated variant in the cart to qualify for this tiers pricing.
QuantityType	String	False		The type of adjustment that is made.
QuantityAmount	Double	False		The maximum allowed quantity of associated variant in the cart to qualify for this tiers pricing.

Power BI Connector for BigCommerce

PriceListRecords

Table Specific Information

Select

Bulk Update

Columns