Excel Add-In for BigCommerce

Build 26.0.9655

Orders

Returns data from the Orders table.

Table Specific Information

Select

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

  • Id supports the =, <, >, <=, and >= comparisons.
  • DateCreated supports the =, >, <, >=, and <= comparisons.
  • DateModified supports the =, >, <, >=, and <= comparisons.

The rest of the filter is executed client-side within the add-in. 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 an order, you must include at least one order item in the INSERT query for that order. First, populate a temporary OrderItems table with the items you want to include in the order. Then, use this table as a source for the LinkedProducts pseudo-column during the insertion process: 

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');

Alternatively, you can insert orders using LinkedProducts as an aggregate. 

 
INSERT INTO Orders (StatusId, CustomerId, BillingFirstName, BillingLastName, BillingStreet1, BillingCity, BillingZip, BillingCountry, BillingState, BillingEmail, BillingPhone, LinkedProducts) VALUES (0, 5253, 'firstname', 'lastname', 'street 1', 'test2', '175024', 'Australia', 'Sent nojses', '[email protected]', '9816198077', '[{\\\"product_id\\\":962, \\\"quantity\\\":1}]');

Update

The following example illustrates how to update Orders: 

UPDATE Orders SET StaffNotes ='Testing123' WHERE Id = 103

Delete

The following example illustrates how to delete a row in Orders whose Id equals 3: 

DELETE FROM Orders WHERE Id = 3

Columns

Name Type ReadOnly Description
Id [KEY] Integer True

The Id of the order.

CustomerId String False

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

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 Decimal True

The total value of the order.

SubtotalTax Decimal 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 Decimal True

The value of handling cost.

HandlingCostTaxClassId Integer True

The Id of handling cost.

ShippingCostTax Decimal True

The value of shipping cost.

ItemsTotal Decimal False

The total number of items in the order.

ItemsShipped Decimal 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 Decimal True

Gift Certificate Amount.

CurrencyId String True

The ID of the currency being used in this transaction.

CurrencyCode String True

The currency code of the display currency.

CurrencyExchangeRate Decimal True

The exchange rate between the default currency and display currency of store.

DefaultCurrencyId String True

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 Decimal True

The number of shipping addresses associated with this transaction.

CouponDiscount Decimal 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 Decimal False

The value of the base handling cost.

BaseShippingCost Decimal False

The value of the base shipping cost.

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

Copyright (c) 2026 CData Software, Inc. - All rights reserved.
Build 26.0.9655