Products

Returns data from products table.

Columns

Name

Type

ReadOnly

Description

Id [KEY]

Integer

True

The Id of the product.

Name

String

False

The product name.

Type

String

False

The product type.

Sku

String

True

User-defined product code/stock keeping unit (SKU)

Description

String

False

Product description, which can include HTML formatting.

SearchKeywords

String

False

A comma-separated list of keywords that can be used to locate the product when searching the store.

AvailabilityDescription

String

False

Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product.

Price

Decimal

False

The products price.

CostPrice

Decimal

False

The products cost price.

RetailPrice

Decimal

False

The products retail cost.

SalePrice

Decimal

False

Sale price.

CalculatedPrice

Decimal

True

Price as displayed to guests, adjusted for applicable sales and rules.

SortOrder

Integer

False

Priority to give this product when included in product lists on category pages and in search results.

IsVisible

Boolean

False

Flag to determine whether or not the product should be displayed to customers browsing.

IsFeatured

Boolean

False

Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store.

RelatedProducts

String

False

Defaults to -1, which causes the store to automatically generate a list of related products.

InventoryLevel

String

False

Current inventory level of the product.

InventoryWarningLevel

String

False

Inventory Warning level for the product.

Warranty

String

False

Warranty information displayed on the product page.

Weight

Decimal

False

Weight of the product, which can be used when calculating shipping costs.

Width

Decimal

False

Width of the product, which can be used when calculating shipping costs.

Height

Decimal

False

Height of the product, which can be used when calculating shipping costs.

Depth

Decimal

False

Depth of the product, which can be used when calculating shipping costs.

FixedCostShippingPrice

Decimal

False

A fixed shipping cost for the product.

IsFreeShipping

Boolean

False

Flag used to indicate whether or not the product has free shipping.

InventoryTracking

String

False

The type of inventory tracking for the product.

RatingTotal

Integer

False

The total rating for the product.

RatingCount

Integer

False

The total number of ratings the product has had.

TotalSold

Integer

False

Total quantity of this product sold through transactions.

DateCreated

Datetime

False

The date of which the product was created.

BrandId

Integer

True

The products brand

ViewCount

Integer

False

The number of times the product has been viewed.

PageTitle

String

False

Custom title for the products page.

MetaKeywords

String

False

Custom meta keywords for the product page.

MetaDescription

String

False

Custom meta description for the product page.

LayoutFile

String

False

The layout template file used to render this product category.

IsPriceHidden

Boolean

False

The default false value indicates that this products price should be shown on the product page.

PriceHiddenLabel

String

False

By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price.

Categories

Int[]

False

An array of IDs for the categories this product belongs to. When updating a product, if an array of categories is supplied, then all product categories will be overwritten.

DateModified

Datetime

False

The date that the product was last modified.

EventDateFieldName

String

False

Name of the field to be displayed on the product page when selecting the event/delivery date.

EventDateType

String

False

Event Date type.

EventDateStart

Datetime

False

When the product requires the customer to select an event/delivery date, this date is used as the after date.

EventDateEnd

Datetime

False

When the product requires the customer to select an event/delivery date, this date is used as the before date.

MYOBAssetAccount

String

False

MYOB Asset Account.

MYOBIncomeAccount

String

False

MYOB Income Account.

MYOBExpenseAccount

String

False

MYOB Expense/COS Account.

PeachtreeGlAccount

String

False

Peachtree General Ledger Account.

Condition

String

False

The products condition.

IsConditionShown

Boolean

False

Flag used to determine whether the products condition will be shown to the customer on the product page.

PreorderReleaseDate

Datetime

False

Pre-order release date.

IsPreorderOnly

Boolean

False

If set to false, the product will not change its availability from preorder to available on the release date.

PreorderMessage

String

False

Custom expected-date message to display on the product page.

OrderQuantityMinimum

Integer

False

The minimum quantity an order must contain in order to purchase this product.

OrderQuantityMaximum

Integer

False

The maximum quantity an order can contain when purchasing the product.

OpenGraphType

String

False

Type of product.

OpenGraphTitle

String

False

Title of the product. If not specified, the products name will be used instead.

OpenGraphDescription

String

False

Description to use for the product.

IsOpenGraphThumbnail

Boolean

False

If set to true, the product thumbnail image will be used as the open graph image.

UPC

String

False

The product UPC code, which is used in feeds for shopping comparison sites.

DateLastImported

Datetime

False

The date on which the product was last imported using the bulk importer.

OptionSetId

Integer

True

The ID of the option set applied to the product.

TaxClassId

Integer

True

The ID of the tax class applied to the product.

OptionSetDisplay

String

True

The position on the product page where options from the option set will be displayed.

BinPickingNumber

String

False

The BIN picking number for the product.

CustomUrl

String

False

Custom URL (if set) overriding the structure dictated in the stores settings.

IsCustomized

Boolean

False

Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides.

Url

String

False

Product URL on the storefront.

Availability

String

False

Availability of the product.

PrimaryImageId

Integer

True

Id of the primary image.

PrimaryImageStandardUrl

String

True

Table Specific Information

Select

The driver 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 driver.

Id supports the '=, >=, >, <=, <' comparison.
Name supports the '=' comparison.
Sku supports the '=' comparison.
Description supports the '=' comparison.
Price supports the '=' comparison.
IsVisible supports the '=' comparison.
IsFeatured supports the '=' comparison.
InventoryLevel supports the '=, >=, >, <=, <' comparison.
DateCreated supports the '=, >=, >, <=, <' comparison.
BrandId supports the '=' comparison.
DateModified supports the '=, >=, >, <=, <' comparison.
Condition supports the '=' comparison.
DateLastImported supports the '=, >=, >, <=, <' comparison.
Availability supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Products SELECT * FROM Products WHERE Id > 5 AND Id < 10 SELECT * FROM Products WHERE IsVisible = "true" AND DateCreated >= "2020-01-01" AND DateCreated <= "2020-02-01"

Insert

To insert a new Product, you need to specify at least the following columns: Name, Type, Description, Price, Categories, Availability and Weight

INSERT INTO Products (Name, Type, Description, Price, Categories, Availability, Weight) VALUES ("Plain T-Shirt","physical","This is a test description",29.99,18,"available",0.5)

Bulk Update

Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details','80','hello123','OTL','19,23','1,2','"pqr","xyz"',false,'/orbit-terrarium-large/'Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details1','86','example','ABS','23,21','3,4','"abc","an"',false,'/able-brewing-system/'UPDATE products (Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) Select Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url from Update#TEMP

Bulk Update Using Aggregates.

Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,CustomUrl) values ('details1','77','name4456','SLCTBS','23,18','10','"abcd","ab"', '{ "is_customized": False, "url" : "/fog-linen-chambray-towel-beige-stripe/" }')UPDATE products (Description,id,name,sku,categories,RelatedProducts,CustomUrl) Select Description,id,name,sku,categories,RelatedProducts,CustomUrl from Update#TEMP

Columns

Name

Type

ReadOnly

Description

Id [KEY]

Integer

True

The Id of the product.

Name

String

False

The product name.

Type

String

False

The product type.

Sku

String

True

User-defined product code/stock keeping unit (SKU)

Description

String

False

Product description, which can include HTML formatting.

SearchKeywords

String

False

A comma-separated list of keywords that can be used to locate the product when searching the store.

AvailabilityDescription

String

False

Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product.

Price

Decimal

False

The products price.

CostPrice

Decimal

False

The products cost price.

RetailPrice

Decimal

False

The products retail cost.

SalePrice

Decimal

False

Sale price.

CalculatedPrice

Decimal

True

Price as displayed to guests, adjusted for applicable sales and rules.

SortOrder

Integer

False

Priority to give this product when included in product lists on category pages and in search results.

IsVisible

Boolean

False

Flag to determine whether or not the product should be displayed to customers browsing.

IsFeatured

Boolean

False

Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store.

RelatedProducts

String

False

Defaults to -1, which causes the store to automatically generate a list of related products.

InventoryLevel

String

False

Current inventory level of the product.

InventoryWarningLevel

String

False

Inventory Warning level for the product.

Warranty

String

False

Warranty information displayed on the product page.

Weight

Decimal

False

Weight of the product, which can be used when calculating shipping costs.

Width

Decimal

False

Width of the product, which can be used when calculating shipping costs.

Height

Decimal

False

Height of the product, which can be used when calculating shipping costs.

Depth

Decimal

False

Depth of the product, which can be used when calculating shipping costs.

FixedCostShippingPrice

Decimal

False

A fixed shipping cost for the product.

IsFreeShipping

Boolean

False

Flag used to indicate whether or not the product has free shipping.

InventoryTracking

String

False

The type of inventory tracking for the product.

RatingTotal

Integer

False

The total rating for the product.

RatingCount

Integer

False

The total number of ratings the product has had.

TotalSold

Integer

False

Total quantity of this product sold through transactions.

DateCreated

Datetime

False

The date of which the product was created.

BrandId

Integer

True

The products brand

ViewCount

Integer

False

The number of times the product has been viewed.

PageTitle

String

False

Custom title for the products page.

MetaKeywords

String

False

Custom meta keywords for the product page.

MetaDescription

String

False

Custom meta description for the product page.

LayoutFile

String

False

The layout template file used to render this product category.

IsPriceHidden

Boolean

False

The default false value indicates that this products price should be shown on the product page.

PriceHiddenLabel

String

False

By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price.