DraftOrders
Create, update, delete, and query draft orders.
Table-Specific Information
Select
The provider uses the Shopify API to process search criteria that refer to the Id, Status, and UpdatedAt columns. The supported SQL operators are "=" and "IN" for Id, "=" for Status, and ">" and "<" for UpdatedAt. The provider processes other filters client-side within the provider.
For example, the following queries are processed server-side:
SELECT * FROM DraftOrders WHERE Id = '123'
SELECT * FROM DraftOrders WHERE Id IN ('123', '456')
SELECT * FROM DraftOrders WHERE Status = 'completed'
SELECT * FROM DraftOrders WHERE UpdatedAt > '2018-02-05'
Insert
-
Create a simple draft order with only a product variant Id using aggregates.
INSERT INTO DraftOrders (LineAggregate) VALUES ('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')
-
Create a simple draft order with only a product variant Id using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
INSERT INTO OrdersItems#TEMP (ItemVariantId, ItemQuantity) VALUES ('123', 2)
Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
INSERT INTO DraftOrders (LineAggregate) VALUES ('OrdersItems#TEMP')
-
Create a custom draft order using aggregates.
INSERT INTO DraftOrders (LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES ('[{\"title\":\"Custom Tee\",\"price\":20.15,\"quantity\":5}]', '709015339031', true)
-
Create a custom draft order using temporary table.
INSERT INTO OrdersItems#TEMP (ItemTitle, ItemPrice, ItemQuantity) VALUES ('Custom Tee', 20.15, 5) INSERT INTO DraftOrders (LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES ('OrdersItems#TEMP', '709015339031', true)
Update
-
Add a Note to a draft order:
UPDATE DraftOrders SET Note = 'Customer contacted us about a custom engraving on this iPod' WHERE Id = '123'
-
Set a discount on a draft order:
UPDATE DraftOrders SET AppliedDiscountDescription = 'Custom discount', AppliedDiscountValueType = 'percentage', AppliedDiscountValue = 10.0, AppliedDiscountAmount = 19.90, AppliedDiscountTitle = 'Custom' WHERE Id = '123'
Delete
You must specify the Id of the draft order to delete it.
DELETE FROM DraftOrders WHERE Id = '123'
Columns
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the order. | |
CustomerId | Long | False |
Customers.Id |
A unique numeric identifier for the customer. |
Currency | String | False |
The three letter code (ISO 4217) for the currency used for the payment. | |
String | False |
The customer's email address. | ||
Name | String | True |
The customer's order name as represented by a number. | |
TotalPrice | Decimal | True |
The sum of all the prices of all the items in the order, taxes and discounts included. | |
SubtotalPrice | Decimal | True |
Price of the order before shipping and taxes. | |
TotalTax | Decimal | False |
The sum of all the taxes applied to the order. | |
TaxesIncluded | Bool | False |
States whether or not taxes are included in the order subtotal. | |
InvoiceSentAt | Datetime | True |
This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders. | |
Note | String | False |
The text of an optional note that a shop owner can attach to the order. | |
Tags | String | False |
Additional short descriptors. | |
InvoiceUrl | String | True |
The URL for the invoice. Only available for draft orders. | |
TaxExempt | Bool | True |
States whether or not taxes are exempt for this order. Only available for draft orders. | |
Status | String | True |
The status of the order. Valid values are: open (all open orders), invoice_sent (only closed orders), and completed (cancelled orders). Defaults to 'open'. | |
LineAggregate | String | False |
A JSON aggregate of line items associated with the draft order. | |
BillingAddressFirstName | String | False |
The first name of the person associated with the payment method. | |
BillingAddressLastName | String | False |
The last name of the person associated with the payment method. | |
BillingAddressAddress1 | String | False |
The street address of the billing address. | |
BillingAddressAddress2 | String | False |
An optional additional field for the street address of the billing address. | |
BillingAddressPhone | String | False |
The phone number at the billing address. | |
BillingAddressCity | String | False |
The city of the billing address. | |
BillingAddressCompany | String | False |
The company of the person associated with the billing address. | |
BillingAddressZip | String | False |
The zip or postal code of the billing address. | |
BillingAddressProvince | String | False |
The name of the state or province of the billing address. | |
BillingAddressCountry | String | False |
The name of the country of the billing address. | |
BillingAddressLatitude | Double | False |
The latitude of the billing address. | |
BillingAddressLongitude | Double | False |
The longitude of the billing address. | |
BillingAddressName | String | False |
The full name of the person associated with the payment method. | |
BillingAddressCountryCode | String | False |
The two-letter code for the country of the billing address. | |
BillingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the billing address. | |
BillingAddressDefault | Bool | False |
Whether this address is the default one or not. | |
ShippingAddressFirstName | String | False |
The first name of the person associated with the shipping method. | |
ShippingAddressLastName | String | False |
The last name of the person associated with the shipping method. | |
ShippingAddressAddress1 | String | False |
The street address of the shipping address. | |
ShippingAddressAddress2 | String | False |
An optional additional field for the street address of the shipping address. | |
ShippingAddressPhone | String | False |
The phone number at the shipping address. | |
ShippingAddressCity | String | False |
The city of the shipping address. | |
ShippingAddressCompany | String | False |
The company of the person associated with the shipping address. | |
ShippingAddressZip | String | False |
The zip or postal code of the shipping address. | |
ShippingAddressProvince | String | False |
The name of the state or province of the shipping address. | |
ShippingAddressCountry | String | False |
The name of the country of the shipping address. | |
ShippingAddressLatitude | Double | False |
The latitude of the shipping address. | |
ShippingAddressLongitude | Double | False |
The longitude of the shipping address. | |
ShippingAddressName | String | False |
The full name of the person associated with the shipping method. | |
ShippingAddressCountryCode | String | False |
The two-letter code for the country of the shipping address. | |
ShippingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the shipping address. | |
ShippingAddressDefault | Bool | False |
Whether this address is the default one or not. | |
AppliedDiscountTitle | String | False |
The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | False |
The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | False |
The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | False |
The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | False |
The amount of the applied discount for this order. Only available for draft orders. | |
CreatedAt | Datetime | True |
The date and time when the order was created. | |
UpdatedAt | Datetime | True |
The date and time when the order was last modified. | |
CompletedAt | Datetime | True |
The date and time when the order was completed at. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements, to offer a more granular control over the tuples that are returned from the data source, or as parameters in INSERT statements.
Name | Type | Description |
UseCustomerDefaultAddress | Bool |
Optional boolean that you can send as part of a draft order object to load customer shipping information. Valid values: true or false. |