Adding a Connection to BigCommerce

To add a connection to BigCommerce:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the BigCommerce icon is not available, click the Add More icon to download and install the BigCommerce connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to BigCommerce

BigCommerce authentication is based on the standard OAuth flow.

Obtain the Store ID

In order to connect to your BigCommerce Store, you will need your StoreId. To find your Store Id please follow these steps:

1. Log in to your BigCommerce account.
2. From the Home Page, select Advanced Settings > API Accounts.
3. Click Create API Account > Create V2/V3 API Token.
4. A text box named API Path appears on your screen.
5. Inside you can see a URL with the following structure: https://api.bigcommerce.com/stores/{Store Id}/v3.
6. As demonstrated above, your Store Id is located between the 'stores/' and '/v3' path paramters.
7. Once you have retrieved your Store Id you can either click Cancel or proceed to creating an API Account if you do not have one.

Obtain a Personal Access Token

Additionally, you must obtain a personal token to test and to access your own data. To obtain the personal token, follow the steps below:

1. Log in to your BigCommerce account.
2. From the Home Page, select Advanced Settings > API Accounts.
3. Click Create API Account > Create V2/V3 API Token.
4. Enter the name of your account.
5. Choose the OAuth Scopes for the API Account you are creating. The Sync App cannot access data marked as "None" and cannot modify data marked as "read-only".
6. Click Save.

Authenticating to BigCommerce

There are two ways to authenticate to BigCommerce:

• Using OAuthAccess tokens generated from the BigCommerce User Interface (UI)
• Using OAuth credentials created by a custom BigCommerce application

Big Commerce UI

Generating an OAuthAccess Token from BigCommerce describes how to obtain the credentials to connect to BigCommerce. The access token authorizes specific account information to be shared. In this flow, there is no web browser required during execution because you explicitly create and accept permissions for your application directly in the BigCommerce UI. This is the easiest way to connect and is recommended for users who are just accessing their personal or company data.

Generating an Access Token

To obtain an OAuthAccessToken directly from your BigCommerce account's UI:

1. Log in to your BigCommerce account.
2. From the Home Page, select Advanced Settings > API Accounts.
3. Selectc Create API Account > Create V2/V3 API Token.
4. Enter the name of your account.
5. Choose the OAuth Scopes for the API Account you are creating. The driver cannot access data marked as "None" and cannot modify data marked as "read-only".
6. Click Save.

Your newly created app's Access Token, and Store Id are displayed on the screen. Make sure to copy them, since this is the only time you have access to these credentials. You can now use these credentials to connect to BigCommerce by setting the following connection properties:

• AuthScheme: Personal access token
• StoreId: Set this to your StoreId. For additional information on how to find your BigCommerce's Store Id, please see Establishing a Connection.
• OAuthAccessToken: Access token

For additional information on how to find your BigCommerce's Store Id, please see Establishing a Connection.

BigCommerce Custom App

Creating a BigCommerce App describes how to create a custom app to connect to BigCommerce. This method details how to create an application for distribution and is required for each of the following sections:

• Desktop applications
• Web applications
• Headless machines

Note that in all these cases, you must set the AuthScheme to OAuth. The following sections assume that you have done so.

Creating an app is more complicated than generating a single OAuthAccess Token, but may be more desireable for client integrations where your design is to connect or allow access to multiple clients, or an administrative scenario where the admin creates an app to be authorized individually by multiple individual users.

Desktop Apps

Follow the steps below to authenticate with the credentials for a custom OAuth app. See "Generating an Access Token" above for more information.

Get an OAuth Access Token

After setting the following properties, you are ready to connect:

• OAuthClientId: Set this to the Client Id in your app settings.
• OAuthClientSecret: Set this to the Client secret in your app settings.
• CallbackURL: Set this to the Redirect URL in your app settings.
• StoreId: Set this to your StoreId. For additional information on how to find your BigCommerce's Store Id, please see Establishing a Connection. .

When you connect the Sync App opens the OAuth endpoint in your default browser. Log in and grant permissions to the application.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of these two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the Sync App on another machine and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.

2. Then configure the Sync App to automatically refresh the access token from the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click BigCommerce OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.

2. Log in and grant permissions to the Sync App. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to the location of the file where the driver saves the OAuth token values that persist across connections.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location grants read and write permissions to the Sync App to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the Sync App to enable the automatic refreshing of the access token.

This section details a selection of advanced features of the BigCommerce Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to BigCommerce and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

This section shows the available API objects and provides more information on executing SQL to BigCommerce APIs.

Key Features

• The Sync App models BigCommerce categories, customers, and orders as relational tables and views, allowing you to write SQL to query BigCommerce data.
• Stored procedures allow you to execute operations to BigCommerce, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your BigCommerce account are immediately reflected when using the Sync App.

Views

Pre-defined Tables and Views are available for read or write access to data from BigCommerce.

Stored Procedures

The Sync App allows you to list your BigCommerce objects and download/upload data to them via Stored Procedures.

System Tables

System Tables contains information about the objects and resources belonging to your database.

The Sync App models the data in BigCommerce as a list of tables in a relational database that can be queried using standard SQL statements.

BigCommerce Connector for CData Sync Tables

Name	Description
Brands	Gets the collection of brands.
Categories	Returns Product Categories.
ChannelListings	Returns a list of all Channel Listings for a specific channel
Channels	GET,CREATE,UPDATE Channels
Coupons	Create,list,update or delete coupons.
Currencies	Returns data from Currencies table.
CustomerAddresses	Returns a list of Customer Addresses. Returns the addresses belonging to a customer.
Customers	Returns data from Customers table.
OrderItems	Returns Items ordered for every order.
Orders	Returns data from Orders table.
OrderShipments	Returns shipments and their corresponding orders.
PriceListRecords	Returns a collection of price List records
PriceLists	Returns a collection of price records
ProductImages	Returns images registered for products.
ProductMetafields	Returns a list of Product Metafields.
ProductOptions	Returns data from Products table.
ProductOptionValues	Returns data from Products table.
ProductReviews	Returns reviews registered for products.
Products	Returns data from products table.
ProductVariants	Returns data from Products table.
ShippingMethods	Lists all shipping methods.
ShippingZones	Lists all shipping zones.

Gets the collection of brands.

Table Specific Information

Select

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

• Id supports the '=, >, <, >=, <=' comparison.
• Name supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Brands WHERE Id = 1 

SELECT * FROM Brands WHERE Id >= 1 AND Id <= 5

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The id of the brand.
Name	String	False		The name of the brand. Must be unique.
PageTitle	String	False		The title shown in the browser while viewing the brand.
MetaKeywords	String	False		Comma-separated list of meta keywords to include in the HTML.
MetaDescription	String	False		A meta description to include.
ImageFile	String	False		A valid image.
SearchKeywords	String	False		A comma-separated list of keywords that can be used to locate this brand.

Returns Product Categories.

Table Specific Information

Select

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

• Id supports the '=,<=,>=,<,>,IN, and NOT_IN' comparisons.
• ParentId supports the '=,<=,>=,<,>,IN, and NOT_IN' comparisons.
• Name supports the '= and LIKE' comparison.
• PageTitle supports the '= and LIKE' comparison.
• IsVisible supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Categories WHERE Id = 18 

SELECT * FROM Categories WHERE Id >= 18 AND Id <= 25

SELECT * FROM Categories WHERE Name = "test"

Insert

To insert a new Categories, you need to specify at least the following columns: ParentId,Name, and Description.

INSERT INTO Categories (ParentId,Name,Description) VALUES (27,'Car Interior121', 'Accessories for testing car.')

Update

UPDATE Categories SET Description='Testing123' where id=18

Delete

DELETE FROM Categories WHERE Id = 18

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of this product category.
ParentId	Integer	False	Categories.Id	The Id of the category.
Name	String	False		Name of the this category.
Description	String	False		System name of the type of order status.
Views	Integer	False		Number of views the category has on the storefront.
SortOrder	Integer	False		Priority this category will be given when included in the menu and category pages.
PageTitle	String	False		Custom title for the category page.
MetaKeywords	String	False		Custom meta keywords for the category page.
MetaDescription	String	False		Custom meta description for the category page.
LayoutFile	String	False		A valid layout file.
ImageUrl	String	False		Image URL used for this category on the storefront.
IsVisible	Boolean	False		Flag to determine whether the product should be displayed to customers browsing the store.
SearchKeywords	String	False		A comma-separated list of keywords that can be used to locate the category when searching the store.
DefaultProductSort	String	False		Determines how the products are sorted on category page load.
UrlPath	String	False		URL Path.
IsCustomized	Boolean	False		Flag to determine whether the url is customized.

Returns a list of all Channel Listings for a specific channel

Table Specific Information

Select

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

• Listingid supports the '='.
• ChannelId supports the '='.
• Datecreated supports the '='.
• Datemodified supports the '='.

ChannelId or (ChannelId and ListingId) are required for SELECT.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM ChannelListings WHERE ChannelId = 667159 AND ListingId = 882998595

Insert

To insert a new ChannelListings, you need to specify at least the following columns: Productid, State, Variants, ChannelId and Name.

INSERT INTO ChannelListings (Productid, State, Variants, ChannelId) VALUES (12345, 'active', 'test', 667159)

Update

UPDATE ChannelListings SET name = 'Test' WHERE ChannelId = 667159

Columns

Name	Type	ReadOnly	References	Description
Listingid [KEY]	Long	True		The ID of the channel listing that has been created, returned, or updated
ChannelId [KEY]	Integer	True	Channels.Id	The ID of the channel associated with this channel listing
Datecreated	Datetime	True		Date on which the channel listing was first created
Datemodified	Datetime	True		Date on which the channel listing was most recently changed
Name	String	False		Name of the product for this channel listing specifically This is an optional field that can be used to override the product name in the catalog
Description	String	True		Description of the product for this channel listing specifically This is an optional field that can be used to override the product description in the catalog
Productid	Integer	False		The ID of the product associated with this channel listing
State	String	False		The state of the product assignment or channel listing
Variants	String	False		Product variant associated with the channel listing.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
ExternalId	Integer

GET,CREATE,UPDATE Channels

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with Id, which supports the '=' operator.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Channels

Insert

To insert a new Channel, you need to specify at least the following columns: Name, Type, Platform, and Status.

INSERT INTO Channels (Type, Platform, Status, Name) VALUES ('pos', 'square', 'active', 'tests');

Update

UPDATE Channels SET Name = 'tests' WHERE ID = 123

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the channel.
ConfigMetaAppId	Integer	False		The unique id given to an app registered in DevTools
sections	String	False		If set, when the app is loaded within the control panel, the navigation sections will be directly embedded in the control panel navigation
Datecreated	Datetime	True		Date on which the channel was first create
Datemodified	Datetime	True		Date on which the channel was most recently changed
Externalid	String	False		Associated ID within a system / platform outside of BC
Iconurl	String	False		Link to the platform icon
Isenabled	Boolean	False		Channel with status of prelaunch, active, or connected will have is_enabled set to true. Channel with status of inactive, disconnected, archived, deleted, or terminated will have is_enabled set to false
IslistableFromUi	Boolean	False		Indicates if a channel can create listings from the BigCommerce UI. Default value for this field is based on the channel type and platform combination if not specified on create
Isvisible	Boolean	False		Indicates if a channel is visible within the BigCommerce merchant admin UI (control panel). If false, the channel will not show in Channel Manager nor in any channels dropdown throughout the UI. Default value for this field is true if not specified on create
Name	String	False		Name of the channel as it will appear to merchants in the control panel
Platform	String	False		The name of the platform for the channel channel platform and type must be a valid combination
Status	String	False		The status of the channel channel type, platform, and status must be a valid combination. terminated is not valid for Update or Insert requests
Type	String	False		The type of channel channel platform and type must be a valid combinations

Create,list,update or delete coupons.

Table Specific Information

Select

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

• Id supports the '=, >, <, >=, and <=' comparisons.
• Name supports the '=' comparison.
• Type supports the '= and !=' comparison.
• Code supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Coupons WHERE Id = 1 

SELECT * FROM Coupons WHERE Id >= 1 AND Id <= 5

SELECT * FROM Coupons WHERE Name = "test"

Insert

To insert a new Coupon, you need to specify at least the following columns: Name, Type, Code, Amount, AppliesToEntity and AppliesToIds.

INSERT INTO Coupons (Name, Type, Code, Amount, AppliesToEntity, AppliesToIds) VALUES ('CouponName', 'free_shipping', 'CN100', 500, 'products', '88, 80')

Update

UPDATE Coupons SET Code = 'ABC' WHERE Id = 12

Delete

DELETE FROM Coupons WHERE Id = 16

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The coupons ID.
Name	String	False		The name of the coupon.
Type	String	False		Type of the coupon. Must be one of per_item_discount,per_total_discount,shipping_discount,free_shipping or percentage_discount. The allowed values are per_item_discount, percentage_discount, per_total_discount, shipping_discount, free_shipping, promotion.
Amount	Decimal	False		The discount to apply to an order, as either an amount or a percentage.
MinPurchase	Decimal	False		Specifies a minimum value that an order must have before the coupon can be applied to it.
Expires	Datetime	False		Specifies when a coupon expires.
Enabled	Boolean	False		If the coupon is enabled, this fields value is true; otherwise, false.
Code	String	False		The coupon code that customers uses to receive their discounts.
AppliesToEntity	String	False		What the discount applies to. Can be products or categories.
AppliesToIds	String	False		IDs of either the products or categories
NumUses	Integer	True		Number of times this coupon has been used.
MaxUses	Integer	False		Maximum number of times this coupon can be used.
MaxUsesPerCustomer	Integer	False		Maximum number of times each customer can use this coupon.
DateCreated	Datetime	False		Date Created
ShippingMethods	String	False		List of shipping-method names.
RestrictedToCountries	String	False		Countries where the coupon is restricted.

Returns data from Currencies table.

Table Specific Information

Select

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

• Id supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM currencies where Id=1

Insert

To insert a new Currencies, you need to specify at least the following columns: Code,Name,ExchangeRate,Token,TokenLocation,DecimalToken,ThousandsToken, and DecimalPlaces.

INSERT INTO Currencies (Code,Name,ExchangeRate,Token,TokenLocation,DecimalToken,ThousandsToken,DecimalPlaces) VALUES ('EUR','Euro', '3.0','$','left','.',',','2')

Update

UPDATE Currencies SET Name='Testing123' where id=2

Delete

DELETE FROM Currencies WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the currency.
IsDefault	Boolean	True		Specifies whether this is the store's default currency.
Code	String	False		Three-letter ISO 4217 code for this currency.
Name	String	False		Name of this currency.
ExchangeRate	Double	False		Amount of this currency that is equivalent to one U.S. dollar.
CountryIso	String	False		Two-letter ISO Alpha-2 code.
AutoUpdate	Boolean	False		Whether to autoupdate currency data.
TokenLocation	String	False		Symbol for this currency.
Token	String	False		Name of the currency.
DecimalToken	String	False		Symbol used as the decimal separator in this currency.
ThousandsToken	String	False		Symbol used as the thousands separator in this currency.
DecimalPlaces	Integer	False		Number of decimal places to show for this currency.
Enabled	Boolean	False		Indicates If the currency is active on the store.
IsTransactional	Boolean	False		Indicates if the currency is set as transactional or not. False means display only currency.
LastUpdated	Date	True		Date the currency was last updated, created or modified.

Returns a list of Customer Addresses. Returns the addresses belonging to a customer.

Table Specific Information

Select

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

• Id supports the '=' and 'IN' comparison.
• CustomerId supports the '=' and 'IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM CustomerAddresses WHERE Id = 1 

SELECT * FROM CustomerAddresses WHERE CustomerId = 5

Insert

To insert a new CustomerAddresses, you need to specify at least the following columns: FirstName,LastName,Phone,City,PostalCode,Country,Address1,AddressType,and StateOrProvince.

INSERT INTO CustomerAddresses (FirstName,LastName,Phone,City,PostalCode,Country,Address1,AddressType,StateOrProvince) VALUES ('test','test', '3000','test','12345','test','test','residential','Washington')

Update

UPDATE CustomerAddresses SET FirstName='Testing123' where id=14

Delete

DELETE FROM CustomerAddresses WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		ID of this customer address.
CustomerId	Integer	False		ID of the associated customer.
FirstName	String	False		The customers first name.
LastName	String	False		The customers last name.
Company	String	False		The customers company name.
Address1	String	False		The customers street address, line 1.
Address2	String	False		The customers street address, line 2.
City	String	False		The customers city/town/suburb.
StateOrProvince	String	False		The customers state/province.
PostalCode	String	False		The customers ZIP or postal code.
Country	String	False		The customers country. Must be the full country name.
CountryCode	String	False		2-letter ISO Alpha-2 code for the customers country.
Phone	String	False		The customers phone number.
AddressType	String	False		The type of the address.

Returns data from Customers table.

Table Specific Information

Select

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

• Id supports the '=, and IN' comparisons.
• Company supports the '=, and IN' comparison.
• Email supports the '=, and IN' comparison.
• DateCreated supports the '=,<,>,<=,>=' comparison.
• DateModified supports the '=,<,>,<=,>=' comparison.
• CustomerGroup supports the '=, and IN' comparison.
• StoreCreditAmounts supports the '=' comparison.
• RegistrationIpAddress supports the '=, and IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Customers WHERE Id = 1 

SELECT * FROM Customers where Company = 'DemoFirst'

SELECT * FROM Customers where Email = '[email protected]'

SELECT * FROM Customers where DateCreated > '2021-09-01 11:25:37.0' 

SELECT * FROM Customers where DateModified <= '2023-10-12 02:05:43.0'

Insert

To insert a new Coupon, you need to specify at least the following columns: FirstName,LastName,Email.

INSERT INTO Customers (FirstName,LastName,Email) VALUES ('Testing4321', 'Test','[email protected]')

Update

UPDATE Customers SET FirstName='testing345' where Id=5075

Delete

DELETE FROM Customers WHERE Id = 16

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique numeric ID of this customer.
Company	String	False		The name of the company for which the customer works.
FirstName	String	False		First name of the customer.
LastName	String	False		Last name of the customer.
Email	String	False		Email address of the customer.
Phone	String	False		Phone number of the customer.
DateCreated	Datetime	True		Date on which the customer registered from the storefront or was created in the control panel.
DateModified	Datetime	True		The date on which the customer was modified.
CustomerGroup	Integer	False		The group to which the customer belongs.
Notes	String	False		Store Owner notes on the customer.
TaxExemptCategory	String	False		Used to identify customers who fall into special sales-tax categories.
StoreCreditAmounts	String	False		Amount of Store Credit
RegistrationIpAddress	String	False		The IP address from which this customer was registered.
AcceptsProductReviewAbandonedCartEmails	Boolean	False		Whether the customer is signed up to receive product review emails, abandoned cart emails, or both. True if the customer is signed up for any of these.
ForcePasswordReset	Boolean	True		If true, this customer will be forced to change their password on their next login.
ChannelIds	String	False		Array of channel ids the Customer has access to.

Returns Items ordered for every order.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• OrderId supports the '=, and IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderItems WHERE Id = 1 

SELECT * FROM OrderItems where OrderId=100

Inserting an Existing Product

To insert an existing product to the OrderItems for a specified Order, order options for that table must be specified. For this reason, before inserting an existing product to the OrderItems, you need to populate a temporary OrderItemOptionValues table with the desired options for the selected product and use this table as a value for the LinkedOrderOptions Pseudo-Column during insertion:

INSERT INTO OrderItemOptionValues#TEMP (Id, Value) Values (117, 177);
INSERT INTO OrderItemOptionValues#TEMP (Id, Value) Values (116, 176);
INSERT INTO OrderItems (OrderId, ProductId, QuantityOrdered, LinkedOrderOptions) VALUES (1104, 960, 3, OrderItemOptionValues#TEMP)");

Note: The OrderItemOptionValue inserted to the temporary table must belong to the product that is being inserted into OrderItems. To verify this, check the ProductOptionValues table.

Inserting a New Product

New, custom products, can be added to the OrderItems table as follows:

INSERT INTO OrderItems (OrderId, Name, CustomSKU, PriceIncTax, PriceExTax, QuantityOrdered) VALUES (1107, 'TSS Phone Case', 'PHC-232453', 6.55, 5.75, 1);

Note: OrderItems does not allow for UPDATEs or Deletes. Once items have been added to an order, they cannot be removed or modified.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Numeric ID of this product within this order.
ProductId	Integer	False	Products.Id	Numeric ID of the product.
CustomSKU	String	False		User defined product code/stock keeping unit (SKU).
OrderId	Integer	False	Orders.Id	Numeric ID of the associated order.
Name	String	False		The product name.
PriceExTax	Decimal	False		The price of the product, excluding tax.
PriceIncTax	Decimal	False		The price of the product, including tax.
QuantityOrdered	Integer	False		Quantity of the product ordered.
QuantityShipped	Integer	True		Quantity of the product shipped.
TotalExtax	Decimal	True		Total base price, excluding tax.
TotalIncTax	Decimal	True		Total base price, including tax.
IsRefunded	Boolean	True		Whether the product has been refunded.
ExternalId	Integer	False		ID of the order in another system.
AppliedDiscounts	String	True		Array of objects containing discounts applied to the product.
BaseCostPrice	Decimal	False		The product's cost price.
BasePrice	Decimal	False		The product�s base price.
BaseTotal	Decimal	False		Total base price.
BaseWrappingCost	Decimal	False		The value of the base wrapping cost.
BinPickingNumber	Integer	False		Bin picking number for the physical product.
Brand	String	False		The product's brand.
ConfigurableFields	String	False		The price of the product, excluding tax.
CostPriceExTax	Decimal	False		The product's cost price excluding tax.
CostPriceIncTax	Decimal	False		The product's cost price including tax.
CostPriceTax	Decimal	False		Tax applied to the product�s cost price.
Depth	Decimal	False		Depth of the product.
DiscountedTotalIncTax	Decimal	False		Represent the correct total amount of the line item after deducting all the discounts and including the tax.
EbayItemId	String	False		Item ID for this product on eBay.
EbayTransactionId	String	False		Transaction ID for this product on eBay.
EventDate	String	False		Date of the promotional event/scheduled delivery.
EventName	String	False		Name of promotional event/delivery date.
FixedShippingCost	Decimal	False		Fixed shipping cost for this product.
FulfillmentSource	String	False		The source of the fulfillment.
GiftCertificateId	String	False		ID of the associated gift certificate.
Height	Decimal	False		Height of the product
IsBundledProduct	Boolean	False		Whether this product is bundled with other products.
NameCustomer	String	False		The product name that is shown to customer in storefront.
NameMerchant	String	False		The product name that is shown to merchant in Control Panel.
OptionSetId	Integer	False		Numeric ID of the option set applied to the product.
OrderAddressId	Integer	False		Numeric ID of the associated order address. Value is 0 for items that are not fulfilled by a pickup method.
OrderPickupMethodId	Integer	False		ID of the pickup fulfillment method for this item. Default value is 0 when the item is not fulfilled by pickup method.
ParentOrderProductId	Integer	False		ID of a parent product.
PriceTax	Decimal	False		Amount of tax applied to a single product.
RefundAmount	Decimal	False		The amount to be refunded.
ReturnId	Integer	False		Numeric ID for the refund.
TotalTax	Decimal	False		Total tax applied to products.
Type	String	False		Type of product. The allowed values are physical, digital.
Upc	String	False		Universal Product Code. Can be written to for custom products and catalog products.
VariantId	Integer	False		Products variant_id.
Weight	Decimal	False		Weight of the product.
Width	Decimal	False		Width of the product.
WrappingCostExTax	Decimal	False		The value of the wrapping cost, excluding tax.
WrappingCostIncTax	Decimal	False		The value of the wrapping cost, including tax.
WrappingCostTax	Decimal	False		Tax applied to gift-wrapping option.
WrappingId	Integer	False		The price of the product, excluding tax.
WrappingMessage	String	False		Message to accompany gift-wrapping option.
WrappingName	String	False		Name of gift-wrapping option.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
LinkedOrderOptions	String	Column for the aggregate table name holding option values.

Returns data from Orders table.

Table Specific Information

Select

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

• Id supports the '=,<,>,<=,>=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Orders WHERE Id = 1 

SELECT * FROM Orders where Id > 104

SELECT * FROM Orders where Id < 104

SELECT * FROM Orders where Id >= 104

SELECT * FROM Orders where Id <= 104

Insert

To insert a new Order, at least one order item is required to be attached to the INSERT query for this order. To do so, you can first populate a temporary OrderItems table with the desired items for the order you are about to create, and later using this table as a value for the LinkedProducts Pseudo-Columns during insertion:

INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (961, 4);
INSERT INTO OrderItems#TEMP (ProductId, QuantityOrdered) VALUES (962, 3);
INSERT INTO Orders (CustomerId, LinkedProducts, StatusId, ItemsTotal, ItemsShipped, PaymentMethod, OrderIsDigital, BillingFirstName, BillingLastName, BillingCompany, BillingCountry, BillingZip, BillingState, BillingEmail, BillingPhone) VALUES (1, OrderItems#TEMP, 4, 6, 2, 'cash', 'true', 'TSS', 'Dev', 'London Corp.', 'United Kingdom', 23433, 'London', '[email protected]', '+355534445');

Update

UPDATE Orders SET StaffNotes='Testing123' where id=103

Delete

DELETE FROM Orders WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the order.
CustomerId	String	False	Customers.Id	The Id of the customer assigned to this order.
DateCreated	Datetime	True		The date of creation for the order.
DateModified	Datetime	True		The last modification date of the order.
DateShipped	Datetime	True		The date of shipment for the order.
StatusId	String	False	OrderStatuses.Id	The Id of the status for this order.
CartId	String	True		The Id of the cart from which this order is assigned.
Status	String	True		Order Statuses.
TotalTax	String	True		The total value of the order.
SubtotalTax	String	True		The value for subtotal tax.
SubtotalExTax	Decimal	False		The value for subtotal, excluding tax.
SubtotalIncTax	Decimal	False		The value for subtotal, including tax.
ShippingCostExTax	Decimal	False		The value of shipping cost, excluding tax.
ShippingCostIncTax	Decimal	False		The value of shipping cost, including tax.
HandlingCostExTax	Decimal	False		The value of handling cost, excluding tax.
HandlingCostIncTax	Decimal	False		The value of handling cost, including tax.
IpAddress	String	False		IPv4 Address of the customer, if known.
WrappingCostExTax	Decimal	False		The value of wrapping cost, excluding tax.
WrappingCostIncTax	Decimal	False		The value of wrapping cost, including tax.
TotalExTax	Decimal	False		The total value of the order, excluding tax.
TotalIncTax	Decimal	False		The total value of the order, including tax.
HandlingCostTax	String	True		The value of handling cost.
HandlingCostTaxClassId	Integer	True		The Id of handling cost.
ShippingCostTax	String	True		The value of shipping cost.
ItemsTotal	Integer	False		The total number of items in the order.
ItemsShipped	Integer	False		The total number of items that have been shipped.
PaymentMethod	String	False		The payment method for this order.
PaymentProviderId	String	False		The external Transaction ID/Payment ID within this order's payment provider (if a payment provider was used).
PaymentStatus	String	True		The payment status for this order.
RefundedAmount	Decimal	False		The amount refunded from this transaction.
GiftCertificateAmount	String	True		Gift Certificate Amount.
CurrencyId	String	True	Currencies. Id	The ID of the currency being used in this transaction.
CurrencyCode	String	True		The currency code of the display currency.
CurrencyExchangeRate	String	True		The exchange rate between the default currency and display currency of store.
DefaultCurrencyId	String	True	Currencies.Id	The ID of the default currency for the store.
DefaultCurrencyCode	String	False		The currency code of the transactional currency the shopper pays in.
StaffNotes	String	False		Any additional notes for staff.
CustomerMessage	String	False		Message that the customer entered.
DiscountAmt	Decimal	False		Amount of discount for this transaction.
EbayOrderId	String	False		Ebay order number if order is placed through Ebay.
GeoIpCountryIso2	String	False		The country where the customer made the purchase, in ISO2 format.
ShippingAddressCount	Integer	True		The number of shipping addresses associated with this transaction.
CouponDiscount	String	True		Discount of the coupon.
OrderSource	String	True		Source of the order.
IsDeleted	Boolean	True		Indicates whether the order was deleted (archived).
OrderIsDigital	Boolean	False		Indicates whether this is an order for digital products.
ExternalSource	String	False		A value identifying the system used to generate the order (for orders submitted or modified via the API).
ExternalId	String	False		ID of the order in another system.
ExternalMerchantId	String	False		Id of the external merchant.
ChannelId	String	False		Shows where the order originated.
TaxProviderId	String	False		BasicTaxProvider - Tax is set to manual; AvaTaxProvider - This is for when the tax provider has been set to automatic and the order was NOT created by the API; (blank) - When the tax provider is unknown.
ProductsUrl	String	False		Url of the products.
ProductsResource	String	False		Resource of the products.
BillingFirstName	String	False		Addressee first name.
BillingLastName	String	False		Addressee last name.
BillingCompany	String	False		Addressee company.
BillingStreet1	String	False		Street address (first line).
BillingStreet2	String	False		Street address (second line).
BillingCity	String	False		Addressee city
BillingZip	String	False		ZIP or postal code
BillingCountry	String	False		Addressee's country
BillingCountryIso2	String	False		Addressee's country code
BillingState	String	False		The name of the state or province. Should be spelled out in full, for example, California.
BillingEmail	String	False		Recipient's email address.
BillingPhone	String	False		Recipient's telephone number.
BaseHandlingCost	String	False		The value of the base handling cost.
BaseShippingCost	String	False		The value of the base shipping cost.
BaseWrappingCost	String	False		The value of the base wrapping cost.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
LinkedProducts	String	Column for the aggregate table name holding order products.

Returns shipments and their corresponding orders.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• OrderId supports the '=, and IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderShipments WHERE Id = 1 

SELECT * FROM OrderShipments where OrderId=1428

Insert

To insert a new Order Shipment, at least one Shipment Item is required to be attached to the INSERT query. For this reason, you can first populate a temporary ShipmentItems table with the desired items for the shipment you are about to create, and later using this table as a value for the LinkedProducts Pseudo-Columns during insertion:

INSERT INTO ShipmentItems#TEMP (Id, Quantity) VALUES (2519, 1);
INSERT INTO OrderShipments (OrderId, OrderAddressId, LinkedProducts) VALUES (1106, 1007, ShipmentItems#TEMP);

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Numeric ID of this shipment within this order.
OrderId	Integer	False	Orders.Id	Numeric ID of the associated order.
DateCreated	Datetime	True		Creation date for the shipment.
TrackingNumber	String	False		Tracking number of the shipment.
TrackingCarrier	String	False		Tracking carrier for the shipment.
TrackingLink	String	True		Returns a tracking link from the shipping service.
ShippingProvider	String	False		Enum of the BigCommerce shipping-carrier integration/module.
ShippingMethod	String	False		Enum of the BigCommerce shipping-carrier integration/module.
Comments	String	False		Comments the shipper wishes to add.
OrderAddressId	Integer	False	OrderShippingAddresses.Id	ID of this shipping address.
CustomerId	String	False		ID of this order�s customer.
ShippingProviderDisplayName	String	True		The human-readable name for the shipping_provider.
MerchantShippingCost	Decimal	True		The shipping merchant cost.
BillingAddressCity	String	True		The billing address city.
BillingAddressCompany	String	True		The billing address company.
BillingAddressCountry	String	True		The billing address country
BillingAddressCountryIso2	String	True		The billing address country iso2.
BillingAddressEmail	String	True		The email of the Addressee.
BillingAddressFirstName	String	True		Addressee's First Name.
BillingAddressLastName	String	True		Addressee's Last Name.
BillingAddressPhone	String	True		Addressee's Phone.
BillingAddressState	String	True		Addressee's State.
BillingAddressStreet1	String	True		Street address (first line).
BillingAddressStreet2	String	True		Street address (second line).
BillingAddressZip	String	True		ZIP or postal code.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
LinkedProducts	String	Column for the aggregate table name holding order products.

Returns a collection of price List records

Table Specific Information

Select

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

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

PriceListId or (PriceListId and VariantId and Currency) are required for select.

The rest of the filter is executed client-side within the Sync App.

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

Returns a collection of price records

Table Specific Information

Select

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

• Id supports the '=' comparison.
• DateCreated supports the '=' comparison.
• DateModified supports the '=' comparison.
• Name supports the '=,LIKE' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM PriceLists WHERE Id = 1 

SELECT * FROM PriceLists WHERE DateCreated = '2021-05-26'

SELECT * FROM PriceLists WHERE DateModified = '2021-05-26'

SELECT * FROM PriceLists WHERE Name LIKE 'Wholesale group1'

Insert

To insert a new PriceList, you need to specify the following columns: Name and Active

INSERT INTO PriceLists (Name, Active) VALUES ('Wholesalegroup', false)

Update

To Update the PriceList, you need to specify the following columns: Name and ID

UPDATE PriceLists SET Name = 'Wholesalegroup' WHERE Id = '4'

Delete

To Delete the PriceList, you need to specify the following columns:ID

DELETE From PriceLists WHERE Id = '4'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the PriceList.
Active	Boolean	False		The status of the PriceList.
DateCreated	Datetime	True		The date of creation for the PriceList.
DateModified	Datetime	True		The last modification date of the PriceList.
Name	String	False		The name of the PriceList.

Returns images registered for products.

Table Specific Information

Select

SELECT * FROM ProductImages 

There are two alternatives to inserting new product images for BigCommerce, ImageFile and ImageUrl.

Insert using ImageFile

To insert a new Product Image using ImageFile, you only need the ProductId and the local path of your ImageFile written as a string using forward slashes as directory separators. BigCommerce does not allow for additional parameters when inserting an image using ImageFile. Trying to do so, will return an error.

INSERT INTO ProductImages (ProductId, ImageFile) VALUES (963, 'C:/dev/tests/v19/ProviderBigCommerce/DDHU.jpg');

Insert using ImageUrl

In addition to using ImageFiles, you can add Product Images by using ImageUrl-s of images on the Web. When inserting by ImageUrl-s, you can add additional fields to your query such as Description, SortOrder, and IsThumbnail..

INSERT INTO ProductImages (ProductId, ImageUrl) VALUES (955, 'http://oi50.tinypic.com/kexbfq.jpg');

Update

UPDATE ProductImages SET Description='Testing123' where Id=265

Delete

DELETE FROM ProductImages WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The Id of the image.
ProductId [KEY]	Integer	False	Products.Id	The Id of the corresponding product.
ImageFile	String	False		The local path to the original image file uploaded to BigCommerce.
StandardUrl	String	False		Standard Image URL for the product.
ThumbnailUrl	String	False		The thumbnail URL for this image. By default, this is the image size used on the category page and in side panels.
IsThumbnail	Boolean	False		Flag for identifying whether the image is used as the product's thumbnail.
SortOrder	Integer	False		The order in which the image will be displayed on the product page.
Description	String	False		The description for the image.
DateModified	Datetime	True		The last modification date of the image.
TinyUrl	String	False		Tiny URL for the product.
ZoomUrl	String	False		Zoom URL for the product.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
ImageUrl	String	The local path to the original image file to be uploaded to BigCommerce.

Returns a list of Product Metafields.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with Id and ProductId, which supports the '=' operator.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM ProductMetafields where ProductId=77
SELECT * FROM ProductMetafields where ProductId=77 and Id=2

Insert

To insert a new ProductMetafields, you need to specify at least the following columns: ProductId, Description, Namespace, Key, Value, and PermissionSet.

Insert Into ProductMetafields(ProductId,Description,Namespace,Key,Value,PermissionSet) Values(88,test,Warehouse,location,88,app_only)

Update

Update ProductMetafields Set Description ='updatedtest' where ProductId=77 and Id=3

Delete

Delete FROM ProductMetafields where ProductId=77 and Id=3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique ID of the Metafield.
ProductId [KEY]	Integer	True	Products.Id	The Id of the corresponding product.
DateCreated	Datetime	True		Date and time of the metafield?s creation.
DateModified	Datetime	True		Date and time when the metafield was last updated.
Description	String	False		Description for the metafields.
Key	String	False		The name of the field.
Value	String	False		The value of the field,must enter a JSON formatted string for ShipperHQ metafields.
Namespace	String	False		Namespace for the metafield, for organizational purposes.
OwnerClientId	String	True		ID of metafield's creator.
PermissionSet	String	False		Determines the visibility and writeability of the field by other API consumers.Must be one of app_only, read, write, read_and_sf_access or write_and_sf_access. The allowed values are app_only, read, write, read_and_sf_access, write_and_sf_access.
ResourceId	Integer	True		The ID of the resource with which the metafield is associated.
ResourceType	String	True		The type of resource with which the metafield is associated.

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductOptions

Insert

To insert a new ProductOption, a set of OptionValues are required to be inserted along with it. This can be done by populating a temporary ProductOptionValues table with the desired values for the option you are creating, and later using this table as a value for the LinkedOptionValues Pseudo-Columns during insertion:

INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Classic', 0, true);
INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Elegance', 1, false);
INSERT INTO ProductOptionValues#TEMP (Label, SortOrder, IsDefault) VALUES ('Avantgarde', 2, false);
INSERT INTO ProductOptions (ProductId, DisplayName, Type, Name, SortOrder, LinkedOptionValues) VALUES (955, 'ModelSeries', 'dropdown', 'Series', 0, ProductOptionValues#TEMP);

Update

UPDATE ProductOptions SET DisplayName='Testing123' where Id=109

Delete

DELETE FROM ProductOptions WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the option.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
DisplayName	String	False		The name of the option shown on the storefront.
Name	String	False		The unique option name, auto-generated from the display name, a timestamp, and the product ID.
Type	String	False		The type of option, which determines how it will display on the storefront. Acceptable values: radio_buttons, rectangles, dropdown, product_list, product_list_with_images, swatch.
SortOrder	Integer	False		The order in which the option will be displayed on the product page.
ConfigDefaultValue	String	False		The default value. Shown on a date option as an ISO-8601�formatted string, or on a text option as a string.
ConfigCheckedByDefault	Boolean	False		Flag for setting the checkbox to be checked by default.
ConfigCheckboxLabel	String	False		Label displayed for the checkbox option.
ConfigDateLimited	Boolean	False		Flag to limit the dates allowed to be entered on a date option.
ConfigDateLimitMode	String	False		The type of limit that is allowed to be entered on a date option. The allowed values are earliest, range, latest.
ConfigDateEarliestValue	String	False		The earliest date allowed to be entered on the date option, as an ISO-8601 formatted string.
ConfigDateLatestValue	String	False		The latest date allowed to be entered on the date option, as an ISO-8601 formatted string.
ConfigFileTypesMode	String	False		The kind of restriction on the file types that can be uploaded with a file upload option. Values: specific - restricts uploads to particular file types; all - allows all file types. The allowed values are specific, all.
ConfigFileTypesSupported	String	False		The type of files allowed to be uploaded if the file_type_option is set to specific.
ConfigFileTypesOther	String	False		A list of other file types allowed with the file upload option.
ConfigFileMaxSize	Integer	False		The maximum size for a file that can be used with the file upload option. This will still be limited by the server.
ConfigTextCharactersLimited	Boolean	False		Flag to validate the length of a text or multi-line text input.
ConfigTextMinLength	Integer	False		The minimum length allowed for a text or multi-line text option.
ConfigTextMaxLength	Integer	False		The maximum length allowed for a text or multi line text option.
ConfigTextLinesLimited	Boolean	False		Flag to validate the maximum number of lines allowed on a multi-line text input.
ConfigTextMaxLines	Integer	False		The maximum number of lines allowed on a multi-line text input.
ConfigNumberLimited	Boolean	False		Flag to limit the value of a number option.
ConfigNumberLimitMode	String	False		The type of limit on values entered for a number option. The allowed values are lowest, highest, range.
ConfigNumberLowestValue	Integer	False		The lowest allowed value for a number option if number_limited is true.
ConfigNumberHighestValue	Integer	False		The highest allowed value for a number option if number_limited is true.
ConfigNumberIntegersOnly	Boolean	False		Flag to limit the input on a number option to whole numbers only.
ConfigProductListAdjustsInventory	Boolean	False		Flag for automatically adjusting inventory on a product included in the list.
ConfigProductListAdjustsPricing	Boolean	False		Flag to add the optional product's price to the main product's price.
ConfigProductListShippingCalc	String	False		How to factor the optional product's weight and package dimensions into the shipping quote. The allowed values are none, weight, package.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
LinkedOptionValues	String	Column for the aggregate table name holding option values.

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductOptionValues 

Insert

To insert a new ProductOptionValues, you need to specify at least the following columns: ProductId, OptionId, Label, SortOrder, and any one among ValueDataImgUrl and ValueDataColors.

Inserting ProductOptionValues with ValueDataImgUrl.

INSERT INTO ProductOptionValues (OptionId,ProductId,Label, SortOrder,ValueDataImgUrl,ValueDataColors) VALUES ('143','77','testingexceptionfornow','2','https://cdn11.bigcommerce.com/s-dr2j9p39ga/product_images/attribute_value_images/357.preview.jpg?t=1696324267','#e35e22')

Inserting ProductOptionValues with ValueDataColors.

INSERT INTO ProductOptionValues (OptionId,ProductId,Label, SortOrder,ValueDataColors) VALUES ('143','77','toasting123','2','#e35e20')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the option value.
OptionId [KEY]	Integer	True	ProductOptions.Id	The ID of the option.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
Label	String	False		The label of the option value shown on the storefront.
IsDefault	Boolean	False		Whether this option value is the default for this option.
SortOrder	Integer	False		The order in which the option value will be displayed on the product page.
ValueDataColors	String	False		The colors contained in the value data.
ValueDataImgUrl	String	False		The image url contained in the value data.

Returns reviews registered for products.

Table Specific Information

Select

SELECT * FROM ProductReviews 

Insert

To insert a new ProductReviews, you need to specify at least the following columns: Title,Status, Id,ProductId,DateReviewed.

INSERT INTO ProductReviews (Title,Status, Id,ProductId,DateReviewed) VALUES ('Car Interior121', 'approved','6','103','2019-08-24T14:15:22Z')

Update

UPDATE ProductReviews SET Title='Testing123' where Id=7

Delete

DELETE FROM ProductReviews WHERE Id = 16

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The Id of the review.
ProductId [KEY]	Integer	True	Products.Id	The Id of the corresponding product.
Title	String	False		Title of the review.
Text	String	False		Text content of the review.
Status	String	False		The status of the product review. Must be one of approved, disapproved, or pending.
Rating	Integer	False		The rating of the product review. Must be one of 0, 1, 2, 3, 4, or 5.
Email	String	False		The email of the reviewer. Must be a valid email, or an empty string.
Name	String	False		The name of the reviewer.
DateCreated	Datetime	True		The date of creation for the review.
DateModified	Datetime	True		The last modification date of the review.
DateReviewed	Datetime	False		Date the product was reviewed.

Returns data from products table.

Table Specific Information

Select

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

• 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.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

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)

Inserting Products with more than one Variants using Temp table or Aggregegates.

INSERT INTO ProductVariantValues#TEMP (Label, DisplayName, id) VALUES ('Blue', 'Color', 1)
INSERT INTO ProductVariantValues#TEMP (Label, DisplayName, id) VALUES ('Yellow', 'Color', 2)

INSERT INTO ProductVariants#TEMP (SKU, LinkedOptionValues, id) VALUES ('SKU-AB', 'ProductVariantValues#TEMP', 1)
INSERT INTO ProductVariants#TEMP (SKU, LinkedOptionValues, id) VALUES ('SKU-CD', 'ProductVariantValues#TEMP', 2)

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-8', 'physical', 60, 5700, 'ProductVariants#TEMP')

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-95', 'physical', 99, 5800, '[{"sku": "SKU-MM","option_values": [{"option_display_name": "Song","id": "1","label": "Mary"}]}, {"sku": "SKU-DE","option_values": [{"option_display_name": "Song","id": "2","label": "Jane"}]}]')

Inserting Products with one Variant

INSERT INTO ProductVariantValues#TEMP (Label, DisplayName) VALUES ('Blue', 'Color')

INSERT INTO ProductVariants#TEMP (SKU, LinkedOptionValues) VALUES ('SKU-AB', 'ProductVariantValues#TEMP')

INSERT INTO Products (Name, Type, Weight, Price, ProductVariants) VALUES ('BC-8', 'physical', 60, 5700, 'ProductVariants#TEMP')

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	References	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.
MapPrice	Decimal	False		Map price.
ProductTaxCode	String	False		Tax Codes.
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.
ReviewsRatingSum	Integer	True		The total (cumulative) rating for the product.
ReviewsCount	Integer	True		The number of times the product has been rated.
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.
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.
OpenGraphUseMetaDescription	Boolean	False		Flag to determine if product description or open graph description is used.
OpenGraphUseProductName	Boolean	False		Flag to determine if product name or open graph name is used.
OpenGraphUseImage	Boolean	False		Flag to determine if product image or open graph image is used.
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.
GTIN	String	False		Global Trade Item Number.
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.
CustomFields	String	False		200 maximum custom fields per product. 255 maximum characters per custom field.
ManufacturerPartNumber	String	False		Manufacturer Part Number.
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.
PrimaryImageProductId	Integer	True		ProductId of the primary image.
PrimaryImageIsThumbnail	Boolean	True		Primary image Is Thumbnail or not.
PrimaryImageSortOrder	String	True		Sort Order of the primary image.
PrimaryImageDescription	String	True		Description of the primary image.
PrimaryImageImageFile	String	True		Image file of the primary image.
PrimaryImageUrlZoom	String	True		Zoom Url of the primary image.
PrimaryImageStandardUrl	String	True		Standard url of the primary image.
PrimaryImageUrlThumbnail	String	True		Thumbnail url of the primary image.
PrimaryImageUrlTiny	String	True		Tiny url of the primary image.
PrimaryImageDateModified	Datetime	True		Modified Date of the primary image.
GiftWrappingOptionsType	String	True		Type of gift-wrapping options.
GiftWrappingOptionsList	String	True		Type of gift-wrapping option IDs.
BaseVariantId	String	True		Base Variant Id.

Pseudo-Columns

Pseudo column fields are used to enable the user to INSERT Fields that are non-readable but required during creation of new records.

Name	Type	Description
ProductVariants	String	Variants of the Products

Returns data from Products table.

Table Specific Information

Select

SELECT * FROM ProductVariants

Insert

To create a new ProductVariant, a set of OptionValues are required to be inserted along with it. This can be done by populating a temporary ProductVariantValues table with the desired values for the option you are creating, and later using this table as a value for the LinkedOptionValues Pseudo-Columns during insertion:

INSERT INTO ProductVariantValues#TEMP (Id, OptionId, Label, DisplayName) VALUES (181, 118, 'Elegance', 'Series');
INSERT INTO ProductVariants (ProductId, LinkedOptionValues, SKU) VALUES (955, ProductVariantValues#TEMP, 'DSFMGG');

Update

UPDATE ProductVariants SET Weight='23' where Id=64

Delete

DELETE FROM ProductVariants WHERE Id = 3

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the product variant.
ProductId [KEY]	Integer	True	Products.Id	The ID of the product.
SKU	String	False		User defined product code/stock keeping unit (SKU).
SKU_ID	String	False		The ID for User defined product code/stock keeping unit (SKU).
Price	Decimal	False		The price of the product variant. The price should include or exclude tax, based on the store settings.
CalculatedPrice	Integer	False		The variant’s calculated price on the storefront.
SalePrice	Integer	False		The variant’s sale price on the storefront.
RetailPrice	Integer	False		The variant’s retail price on the storefront.
MapPrice	Integer	False		The Minimum Advertised Price.
Weight	String	False		This variant’s base weight on the storefront. If this value is null, the product’s default weight (set in the Product resource’s weight field) will be used as the base weight.
Calculated_Weight	Integer	False		The variant’s calculated weight on the storefront.
Width	Long	False		Width of the variant, which can be used when calculating shipping costs.
Height	Long	False		The Height of the variant, which can be used when calculating shipping costs.
Depth	Long	False		The Depth of the variant, which can be used when calculating shipping costs.
Is_Free_Shipping	Boolean	False		The Flag used to indicate whether the variant has free shipping. If true, the shipping cost for the variant will be zero.
FixedCostShippingPrice	Integer	False		The fixed shipping cost for the variant. If defined, this value will be used during checkout instead of normal shipping-cost calculation.
Purchasing_Disabled	String	False		Accepts AvaTax System Tax Codes, which identify products and services that fall into special sales-tax categories.
Purchasing_Disabled_Message	String	False		Accepts AvaTax System Tax Codes, which identify products and services that fall into special sales-tax categories.
Image_Url	String	False		The Image url for the variant.
CostPrice	Integer	False		The cost price of the variant.
Upc	String	False		The UPC code used in feeds for shopping comparison sites and external channel integrations.
Mpn	String	False		The Manufacturer Part Number (MPN) for the variant.
Gtin	String	False		The Global Trade Item Number.
InventoryLevel	Integer	False		The Inventory level for the variant, which is used when the product’s inventory_tracking is set to variant.
InventoryWarningLevel	Integer	False		The Inventory warning level for the product
BinPickingNumber	String	False		Identifies where in a warehouse the variant is located.
LinkedOptionValues	String	False		The Option Value Id.

Lists all shipping methods.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• ZoneId supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following query is processed server-side:

SELECT * FROM ShippingMethods

SELECT * FROM ShippingMethods WHERE ZoneId = 1

SELECT * FROM ShippingMethods WHERE ZoneId = 1 and Id = 1

Insert

To insert a new ShippingMethod, Name, Type, Settings and ZoneId are required to be inserted along with it. Settings has to be passed as a JSON Object.

INSERT INTO ShippingMethods (Name, Type, Enabled, ZoneId, HandlingFeesFixedSurcharge, Settings) VALUES ('Flat Rate 2', 'perorder', true, 1, 0, '{ "rate" : 7 }')

Update

To update a ShippingMethod, Name, Type, Settings, ZoneId and Id are required.

UPDATE ShippingMethods Set Name = 'Flat Rate 1', Type = 'perorder', Settings = '{ "rate" : 7 }', HandlingFeesPercentageSurcharge = 1 WHERE Id = 9 and ZoneId = 1

Delete

To delete a shipping method, Id and ZoneId are required.

DELETE FROM ShippingMethods WHERE Id = 9 and ZoneId = 1

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Shipping Method Id.
Name	String	False		Shipping Method name.
Type	String	False		Shipping Method type.
Enabled	Boolean	False		Minimum subtotal of the free shipping.
HandlingFeesPercentageSurcharge	Decimal	False		Percentage surcharge of the handling fee.
HandlingFeesFixedSurcharge	Decimal	False		Fixed surcharge of the handling fee.
IsFallback	Boolean	True		Whether or not this shipping zone is the fallback if all others are not valid for the order.
Settings	String	False		Shipping method settings.
ZoneId	Integer	True		The Shipping Zone Id

Lists all shipping zones.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with Id, which supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following query is processed server-side:

SELECT * FROM ShippingZones WHERE Id = 1 

Insert

To insert a new ShippingZone, a set of Locations are required to be inserted along with it. This can be done by populating a temporary ShippingZoneLocations table with the desired values for the option you are creating, and later using this table as a value for the Locations column during insertion:

INSERT INTO ShippingZoneLocations#Temp (CountryIso2, Zip) values ('US', '11103')
INSERT INTO ShippingZones (Name, Type, locations) VALUES ('United States', 'zip', 'ShippingZoneLocations#Temp')

Update

UPDATE ShippingZones SET Enabled=true WHERE Id = 7

Delete

DELETE FROM ShippingZones WHERE Id = 7

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Zone Id.
Name	String	False		Zone name.
Type	String	False		Zone type. The allowed values are zip, country, state, global.
FreeShippingEnabled	Boolean	False		Indicator if free shipping is enabled.
FreeShippingMinimumSubTotal	Decimal	False		Minimum subtotal of the free shipping.
FreeShippingExcludeFixedShippingProducts	Boolean	False		Indicator whether or not to exclude fixed shipping on products.
HandlingFeesDisplaySeparately	Boolean	False		Indicator whether or not to display the handling fees separately.
HandlingFeesPercentageSurcharge	Decimal	False		Percentage surcharge of the handling fee.
HandlingFeesFixedSurcharge	Decimal	False		Fixed surcharge of the handling fee.
Enabled	Boolean	False		Whether this shipping zone is enabled.
Locations	String	False		Array of zone locations.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

BigCommerce Connector for CData Sync Views

Name	Description
Blogposts	Returns all Blog Posts.
BlogTags	Returns a list of Blog Tags.
CustomerGroups	Returns a list of Customer Groups.
DownloadConsignments	Returns consignments and their corresponding downloads.
EmailConsignments	Returns consignments and their corresponding emails.
GiftCertificates	Lists all gift certificates.
OrderCoupons	Lists all order coupons.
OrderItemOptionValues	Returns data from OrderItems table.
OrderMessages	Gets the messages associated with an order.
OrderRefunds	Returns a list of refunds.
OrderRefundsItems	List of Items refunded.
OrderRefundsPayments	Refund payments made to payment providers.
OrderShippingAddresses	Returns shipping addresses and their corresponding orders.
OrderStatuses	Returns order statuses.
OrderTaxes	Gets all order taxes related to an order.
Pages	Lists all pages.
PaymentMethods	Lists all payment methods.
PickupConsignments	Returns consignments and their corresponding pickups.
PriceListAssignments	Returns an array of Price List Assignments matching a particular Customer Group and Price List and Channel.
ProductBulkPricingRules	Returns bulk pricing rules applied to a product.
ProductCustomFields	Lists all tax classes.
ProductRules	Returns rules that modify the properties of a product, such as weight, price, and product image.
ProductSkus	Returns Stock Keeping Unit identifiers associated with products or product options.
ProductVariantValues	Returns data from Products table.
ProductVideos	Returns Embedded videos displayed on product listings.
Redirects	Lists all redirect URLs.
ShipmentItems	Returns data from Items within a shipment.
ShippingConsignmentQuotes	Gets all shipping quotes persisted on an order for a shipping consignment.
ShippingConsignments	Returns consignments and their corresponding shipping orders.
Stores	Lists all Stores.
TaxClasses	Lists all tax classes.
Transactions	Lists all transactions.

Returns all Blog Posts.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• Url supports the '=' comparison.
• IsPublished supports the '=' comparison.
• PublishedDate supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM Blogposts WHERE Id = "1234" 

SELECT * FROM Blogposts WHERE IsPublished = "true"

Columns

Name	Type	References	Description
Id [KEY]	Integer		Id of the blogpost
Title	String		Title of this blog post.
URL	String		URL for the public blog post.
PreviewURL	String		URL to preview the blog post.
Body	String		Text body of the blog post.
Summary	String		Summary of the blog post.
IsPublished	Boolean		Whether the blog post is published.
PublishedDate	Datetime		Date when the blogpost was published.
PublishedTimezone	String		Timezone when the blogpost was published.
PublishedTimezoneType	String		Type of the timezone.
PublishedDateISO	String		Published date in ISO8601 format.
MetaDescription	String		Description text for this blog posts meta element.
MetaKeywords	String		Keywords for this blog posts meta element.
Author	String		Name of the blog posts author.
ThumbnailPath	String		Local path to a thumbnail image within the product_images folder to accompany the blog post.

Returns a list of Blog Tags.

Columns

Name	Type	References	Description
BlogIds [KEY]	String		Id of the blogpost
Tag [KEY]	String		Tag which belongs to the blog.

Returns a list of Customer Groups.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• Name supports the '=' comparison.
• IsDefault supports the '=' comparison.
• IsGroupForGuests supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM CustomerGroups WHERE Id = 1 

SELECT * FROM CustomerGroups WHERE IsDefault = "true"

SELECT * FROM CustomerGroups WHERE IsGroupForGuests = "true"

Columns

Name	Type	References	Description
Id [KEY]	Integer		Id of the customer group
Name	String		Name of the group
IsDefault	Boolean		Determines whether new customers are assigned to this group by default.
CategoryAccessType	String		Type of the category access.
CategoryAccessCategories	String		An array of category IDs.
IsGroupForGuests	Boolean		Describes whether the group is for guests. There can only be one customer group for guests at a time.
DateCreated	Date		Date on which the customer group was created.
DateModified	Date		Date on which the customer group was last modified.
DiscountRulesAmount	String		A collection of discount rules that are automatically applied to customers who are members of the group.

Returns consignments and their corresponding downloads.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with OrderId, which supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM DownloadConsignments WHERE OrderId = 123 

Columns

Name	Type	References	Description
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
RecipientEmail	String		The recipient email of the digital consignment.
DownloadLineItemResources	String		URL where you can use a GET request to get the downloads line items for the order consignment.
DownloadLineItemUrl	String		Path where you can use a GET request to get the downloads line items for the order consignment.

Returns consignments and their corresponding emails.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with OrderId, which supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM EmailConsignments WHERE OrderId = 123 

Columns

Name	Type	References	Description
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
RecipientEmail	String		The recipient email of the gift certificate.
GiftCertificatesLineItemResources	String		URL where you can use a GET request to get the gift certificate line items for the order consignment.
GiftCertificatesLineItemUrl	String		Path where you can use a GET request to get the gift certificate line items for the order consignment.

Lists all gift certificates.

Table Specific Information

Select

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

• Id supports the '=, >=, >, <=, <' comparison.
• Code supports the '=' comparison.
• ToName supports the '=' comparison.
• ToEmail supports the '=' comparison.
• OrderId supports the '=' comparison.
• FromEmail supports the '=' comparison.
• FromName supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM GiftCertificates WHERE Id = 1 

SELECT * FROM GiftCertificates WHERE IsDefault = "true"

SELECT * FROM GiftCertificates WHERE IsGroupForGuests = "true"

Columns

Name	Type	References	Description
Id [KEY]	Integer		The ID of the gift certificate.
Code	String		A unique string that the customer can input to redeem the gift certificate.
Amount	Decimal		Value of the gift certificate.
Status	String		Status of the gift certificate.
Balance	Decimal		Remaining value of the gift certificate.
ToName	String		Name of the recipient.
ToEmail	String		Email of the recipient.
OrderId	Integer		The ID of the order.
Template	String		The email theme to use in the message sent to the recipient.
Message	String		Text that will be sent to the recipient, such as Congratulations.
FromName	String		Name of the customer who purchased the gift certificate.
FromEmail	String		Email of the customer who purchased the gift certificate.
CustomerId	Integer		The id of the customer placing the order.
ExpiryDate	Datetime		Date on which the gift certificate is set to expire.
PurchaseDate	Datetime		Date the gift certificate was purchased.
CurrencyCode	String		The currency code.

Lists all order coupons.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• OrderId supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderCoupons WHERE Id = 1 

SELECT * FROM OrderCoupons WHERE OrderId = 2

Columns

Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer		Numeric ID of the associated order.
CouponId	Integer		The coupon id.
Code	String		The code of the coupon.
Amount	Decimal		The amount of the coupon.
Type	Integer		The type of the coupon.
Discount	Decimal		The discount of the coupon.

Returns data from OrderItems table.

Table Specific Information

Select

The ID of the corresponding Order-Item is required to perform a SELECT query on OrderItemOptionValues:

SELECT * FROM [OrderItemOptionValues] WHERE OrderItemId = 2522;

Columns

Name	Type	References	Description
Id [KEY]	Integer		The numerical ID of the option value within the group of ordered items.
OptionId	Integer	ProductOptions.Id	The ID of the option from Product Options table.
OrderItemId [KEY]	Integer	OrderItems.Id	The ID of the product.
OrderItemOptionId	Integer		The numerical ID of the option within the group of ordered items.
DisplayName	String		The name of the option shown on the storefront.
DisplayValue	String		The value of the option shown on the storefront.
Value	String		The actual value of the option.
Name	String		The unique option name, auto-generated from the display name, a timestamp, and the product ID.
Type	String		The type of option, which determines how it will display on the storefront. Acceptable values: radio_buttons, rectangles, dropdown, product_list, product_list_with_images, swatch.
DisplayStyle	String		The specific style which the order will be displayed as.

Gets the messages associated with an order.

Table Specific Information

Select

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

• Id supports the '=, <=, <, >=, >' comparison.
• OrderId supports the '=' comparison.
• CustomerId supports the '=' comparison.
• Status supports the '=' comparison.
• IsFlagged supports the '=' comparison.
• DateCreated supports the '=, <=, <, >=, >' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderMessages WHERE Id = 1 

SELECT * FROM OrderMessages WHERE OrderId = 2

SELECT * FROM OrderMessages WHERE IsFlagged = "true"

SELECT * FROM OrderMessages WHERE DateCreated >= "2019-01-01" AND DateCreated <= "2020-01-01"

Columns

Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer		Numeric ID of the associated order.
StaffId	Integer		The staff id.
CustomerId	Integer		The customer id.
Type	String		Type of the message.
Subject	String		Subject of the message.
Message	String		Message content.
Status	String		Status of the message.
IsFlagged	Boolean		Indicator if the message is flagged.
DateCreated	Datetime		Datetime when the message was first created.

Returns a list of refunds.

Table Specific Information

Select

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

• Id supports the '=, IN' comparison.
• OrderId supports the '=, IN' comparison.
• Created supports the '=, >, <, IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderRefunds WHERE Id = 1 

SELECT * FROM OrderRefunds WHERE OrderId = 2

Columns

Name	Type	References	Description
Id [KEY]	Integer		Refund resource ID.
Created [KEY]	Datetime		Timestamp of when this refund was created.
OrderId	Integer		Reference to order id.
Reason	String		Reason for refund.
TotalAmount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers.
TotalTax	Decimal		Total tax amount refunded back to the shopper. Note: order_level_amount does not affect tax liability.
UserId	Integer		Reference to the user's id who create this refund.
UsesMerchantOverrideValues	Boolean		Whether refund amount and tax are provided explicitly by merchant override

List of Items refunded.

Table Specific Information

Select

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

• OrderId supports the '=, IN' comparison.
• OrderRefundsId supports the '=, IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderRefundsItems WHERE OrderRefundId = 2

SELECT * FROM OrderRefundsItems WHERE OrderId = 2

Columns

Name	Type	References	Description
ItemId [KEY]	Integer		order_product.id corresponding to the item_types of PRODUCT, GIFT_WRAPPING. order_address.id corresponding to the item_types of SHIPPING, HANDLING. order.id corresponding to the item_type of ORDER.
OrderRefundId [KEY]	Integer	OrderRefunds.Id	The ID of the order refund.
OrderId	Integer	OrderRefunds.OrderId	Reference to order id.
ItemType	String		Type of item that was refunded.
Quantity	Integer		Quantity of item refunded. Note: this will only be populated for item_type PRODUCT
Reason	String		Reason for refunding an item.
RequestedAmount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers..

Refund payments made to payment providers.

Table Specific Information

Select

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

• OrderId supports the '=, IN' comparison.
• OrderRefundsId supports the '=, IN' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderRefundsItems WHERE OrderRefundId = 2

SELECT * FROM OrderRefundsPayments WHERE OrderId = 2

Columns

Name	Type	References	Description
Id [KEY]	Integer		The ID of the refund payment.
OrderRefundId	Integer	OrderRefunds.Id	The ID of the order refund.
OrderId	Integer	OrderRefunds.OrderId	Reference to order id.
ProviderId	String		Reference to payment provider.
Amount	Decimal		A non-negative 2 decimal place rounded value that represents the amount that can be charged/refunded via payment providers.
Offline	Boolean		Indicate whether payment was offline.
IsDeclined	Boolean		Indicate if this payment has been declined by payment provider.
DeclinedMessage	String		Message indicate why payment was declined.

Returns shipping addresses and their corresponding orders.

Columns

Name	Type	References	Description
Id [KEY]	Integer		ID of this shipping address.
OrderId	Integer		ID of the order.
FirstName	String		Addressee first name.
LastName	String		Addressee last name.
Company	String		Addressee company.
Street1	String		Street address (first line).
Street2	String		Street address (second line).
City	String		Addressee city.
Zip	String		ZIP or postal code.
Country	String		Addressee's country.
State	String		The name of the state or province. Should be spelled out in full, for example, California.
Email	String		Recipient's email address.
Phone	String		Recipient's telephone number.
BaseCost	Decimal		The base value of the order's items.
BaseHandlingCost	Decimal		The base handling charge.
CostExTax	Decimal		The value of the order's items, excluding tax.
CostIncTax	Decimal		The value of the order's items, including tax.
CostTax	Decimal		The tax amount on the order.
CostTaxClassId	String		The ID of the tax class applied to the product.
CountryIso2	String		2-letter ISO Alpha-2 code for the country.
HandlingCostExTax	Decimal		The handling charge, excluding tax.
HandlingCostIncTax	Decimal		The handling charge, including tax.
HandlingCostTax	Decimal		The handling charge.
HandlingCostTaxClassId	String		A read-only value. Do not attempt to set or modify this value in a POST or PUT operation.
ItemsShipped	Integer		The number of items that have been shipped.
ItemsTotal	Integer		The total number of items in the order.
ShippingMethod	String		Text code identifying the BigCommerce shipping module selected by the customer.
ShippingZoneId	Integer		Numeric ID of the shipping zone.
ShippingZoneName	String		Name of the shipping zone.

Returns order statuses.

Columns

Name	Type	References	Description
Id [KEY]	Integer		The Id of the type of order status
Name	String		Name of the type of order status.
Systemlabel	String		System name of the type of order status.
CustomLabel	String		Custom order status label given in the Control Panel.
SystemDescription	String		System description of the order status.

Gets all order taxes related to an order.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• OrderId supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following queries are processed server-side:

SELECT * FROM OrderTaxes WHERE Id = 1 

SELECT * FROM OrderTaxes WHERE OrderId = 2

Columns

Name	Type	References	Description
Id [KEY]	Integer		Numeric ID of this product within this order.
OrderId	Integer		Numeric ID of the associated order.
OrderAddressId	Integer		The unique numeric identifier of the order address object associated with the order.
TaxRateId	Integer		The unique numeric identifier of the tax rate.
TaxClassId	Integer		The unique numeric identifier of the tax class object.
Name	String		The name of the tax class object.
Class	String		The name of the type of tax that was applied.
Rate	Decimal		The tax rate.
Priority	Integer		The order in which the tax is applied.
PriorityAmount	Decimal		The amount of tax calculated on the order.
LineAmount	Decimal		The line amount.
OrderProductId	String		If the line_item_type is item or handling then this field will be the order product id. Otherwise the field will return as null.
LineItemType	String		Type of tax on item.

Lists all pages.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with Id, which supports the '=' comparison.

The rest of the filter is executed client-side within the Sync App.

For example, the following query is processed server-side:

SELECT * FROM Pages WHERE Id = 1 

Columns

Name	Type	References	Description
Id [KEY]	Integer		Id of the page.
ChannelId	Integer	Channels.Id	Channel Id of the page.
Name	String		Name of the page.
Email	String		Email of the page.
MetaTitle	String		Title of the page.
Body	String		Body of the page.
IsVisible	Boolean		Indicator if page is visible.
ParentId	Integer		Parent id of the page.
SortOrder	Integer		Sort order of the page.
MetaKeywords	String		Keywords of the page.
Type	String		Type of the page.
MetaDescription	String		Description of the page.
IsHomepage	Boolean		Indicator if the page is homepage.
IsCustomersOnly	Boolean		Indicator if the page is customer only.
SearchKeywords	String		Search keywords of the page.
Url	String		Url of the page.

Lists all payment methods.

Columns

Name	Type	References	Description
Code [KEY]	String		The code of the payment method.
Name	String		The name of the payment method.
TestMode	Boolean		Indicator if the payment method is in test mode.

Returns consignments and their corresponding pickups.

Table Specific Information

Select

The Sync App uses the BigCommerce API to process WHERE clause conditions built with OrderId, supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM PickupConsignments WHERE OrderId = 123 

Columns

Name	Type	References	Description
Id	Integer		The ID of the pickup consignment to update.
OrderId	Integer	Orders.Id	Identifier for the BigCommerce Order with which this transaction is associated.
PickupMethodId	Integer		The ID of the pickup consignment to update.
PickupMethodDisplayName	String		A name for the pickup method that can be displayed to shoppers.
CollectionInstructions	String		A message for shoppers explaining how to collect their pickup order.
CollectionTimeDescription	String		A message for shoppers indicating the estimated time their pickup order will be ready for collection.
LocationId	Integer		ID of the location.
LocationName	String		The name of the pickup location.
LocationCode	String		The code of the pickup location.
LocationAddressLine1	String		Pickup location's Street address (first line)
LocationAddressLine2	String		Pickup location's Street address (second line).
LocationCity	String		Pickup location's city.
LocationState	String		Pickup location's state.
LocationPostalCode	String		Pickup location's postal code.
LocationCountryAlpha2	String		2-letter ISO Alpha-2 code for the country.
LocationEmail	String		Pickup location's email address
LocationPhone	String		Pickup location's phone number.
PickupsLineItemResources	String		URL where you can use a GET request to get the pickups line items for the order consignment.
PickupsLineItemUrl	String		Path where you can use a GET request to get the pickups line items for the order consignment.

Returns an array of Price List Assignments matching a particular Customer Group and Price List and Channel.

Table Specific Information

Select

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

• Id supports the '=' comparison.
• CustomerGroupId supports the '=,IN' comparison.
• PriceListId supports the '=,IN' comparison.
• ChannelId supports the '=,IN' comparison.

The rest of the filter is executed client-side within the Sync App.

SELECT * FROM PriceListAssignments WHERE CustomerGroupId IN (1, 2)

SELECT * FROM PriceListAssignments WHERE PriceListId IN (1, 2)

SELECT * FROM PriceListAssignments WHERE PriceListId = 1

SELECT * FROM PriceListAssignments WHERE CustomerGroupId = 1

Columns

Name	Type	References	Description
Id	Integer		The ID of the PriceList Assignment.
CustomerGroupId	Integer		The ID of the customer group.
PriceListId	Integer		The ID of the PriceList.
ChannelId	Integer		ID of the Channel.