Power BI Connector for BigCommerce

Build 21.0.7930

PriceListRecords

Returns a collection of price List records

Table Specific Information

Select

The {i will use the BigCommerce API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the {i.

  • CalculatedPrice supports the '=' comparison.
  • DateCreated supports the '=' comparison.
  • DateModified 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.

For example, the following queries are processed server side:

SELECT * FROM PriceListRecords Where PriceListId IN (Select Id from PriceLists)

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 new PriceListRecord, you need to specify at least the following columns:VariantId,Currency,Price,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 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 Integer False

The variant with which this price set is associated.

Sku String False

The variant with which this price set is associated

Currency 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 Integer 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.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930