Reference

Endpoint Get Account Orders (For all accounts)

Name

get_account_orders

Description

Related Tables

AccountOrders

Parameters

Parameter

Required

Options

Name: PageSize

Label: PageSize

Name: Email

Label: Email

Account email

Name: CustomKey

Label: Account custom key

Name: GlobalKey

Label: Account global key

Name: OrderID

Label: Account orderID

Name: OrderReference

Label: Account orderReference

Name: SubscriptionId

Label: Account subscriptionId

Name: Products

Label: Products

filters results to include only transactions involving the specified product ID(s) / product path(s)

Name: Refunds

Label: Show Returned Only

filters results to include orders with or without returns; response includes returns array with return IDs where applicable

Option	Value
true	true
false	false

Name: SubscriptionStatus

Label: Subscription Status

'active', 'ended', 'canceled', 'started' will return accounts with subscriptions in the corresponding state

Option	Value
active	active
ended	ended
canceled	canceled
started	started

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
Id	`DT_WSTR`	`nvarchar(50)`	50
Reference	`DT_WSTR`	`nvarchar(50)`	50
Item_1	`DT_WSTR`	`nvarchar(100)`	100	First item from this order
Item_2	`DT_WSTR`	`nvarchar(100)`	100	First item from this order
OrderDate	`DT_DBDATE`	`date`
SubtotalInPayoutCurrency	`DT_R8`	`float`
TaxInPayoutCurrency	`DT_R8`	`float`
TotalInPayoutCurrency	`DT_R8`	`float`
DiscountInPayoutCurrency	`DT_R8`	`float`
ProratedTotalInPayoutCurrency	`DT_R8`	`float`
Currency	`DT_WSTR`	`nvarchar(10)`	10
SubtotalDisplay	`DT_WSTR`	`nvarchar(50)`	50
TaxDisplay	`DT_WSTR`	`nvarchar(50)`	50
TotalDisplay	`DT_WSTR`	`nvarchar(50)`	50
CustomerCompany	`DT_WSTR`	`nvarchar(255)`	255
CustomerEmail	`DT_WSTR`	`nvarchar(255)`	255
CustomerFirst	`DT_WSTR`	`nvarchar(255)`	255
CustomerLast	`DT_WSTR`	`nvarchar(255)`	255
CustomerPhone	`DT_WSTR`	`nvarchar(255)`	255
CustomerSubscribed	`DT_BOOL`	`bit`
AddressLine1	`DT_WSTR`	`nvarchar(255)`	255
AddressLine2	`DT_WSTR`	`nvarchar(255)`	255
AddressCity	`DT_WSTR`	`nvarchar(255)`	255
AddressCountry	`DT_WSTR`	`nvarchar(255)`	255
AddressDisplay	`DT_WSTR`	`nvarchar(255)`	255
AddressPostalCode	`DT_WSTR`	`nvarchar(20)`	20
AddressRegionCode	`DT_WSTR`	`nvarchar(20)`	20
AddressRegion	`DT_WSTR`	`nvarchar(20)`	20
AddressRegionDisplay	`DT_WSTR`	`nvarchar(20)`	20
BuyerReference	`DT_WSTR`	`nvarchar(50)`	50
IpAddress	`DT_WSTR`	`nvarchar(50)`	50
Completed	`DT_BOOL`	`bit`
Changed	`DT_I8`	`bigint`
ChangedValue	`DT_I8`	`bigint`
ChangedInSeconds	`DT_I8`	`bigint`
ChangedDisplay	`DT_WSTR`	`nvarchar(50)`	50
ChangedDisplayEmailEnhancements	`DT_WSTR`	`nvarchar(50)`	50
ChangedDisplayEmailEnhancementsWithTime	`DT_WSTR`	`nvarchar(50)`	50
Language	`DT_WSTR`	`nvarchar(10)`	10
Live	`DT_BOOL`	`bit`
PayoutCurrency	`DT_WSTR`	`nvarchar(10)`	10
QuoteId	`DT_WSTR`	`nvarchar(500)`	500
InvoiceUrl	`DT_WSTR`	`nvarchar(500)`	500
SiteId	`DT_WSTR`	`nvarchar(50)`	50
AccountId	`DT_WSTR`	`nvarchar(50)`	50
Total	`DT_R8`	`float`
TotalInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
Tax	`DT_R8`	`float`
TaxInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
Subtotal	`DT_R8`	`float`
SubtotalInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
Discount	`DT_R8`	`float`
DiscountDisplay	`DT_WSTR`	`nvarchar(50)`	50
DiscountInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
DiscountWithTax	`DT_R8`	`float`
DiscountWithTaxDisplay	`DT_WSTR`	`nvarchar(50)`	50
DiscountWithTaxInPayoutCurrency	`DT_R8`	`float`
DiscountWithTaxInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
BillDescriptor	`DT_WSTR`	`nvarchar(50)`	50
PaymentType	`DT_WSTR`	`nvarchar(50)`	50
PaymentCardEnding	`DT_WSTR`	`nvarchar(8)`	8
PaymentBank	`DT_WSTR`	`nvarchar(100)`	100
PaymentMethodType	`DT_WSTR`	`nvarchar(50)`	50
Recipients	`DT_WSTR`	`nvarchar(2000)`	2000
Notes	`DT_WSTR`	`nvarchar(4000)`	4000
AcquisitionTransactionType	`DT_WSTR`	`nvarchar(100)`	100
TaxExemptionData	`DT_WSTR`	`nvarchar(2000)`	2000
ProratedCreditTotal	`DT_R8`	`float`
ProratedCreditTotalDisplay	`DT_WSTR`	`nvarchar(50)`	50
ProratedCreditTotalInPayoutCurrency	`DT_R8`	`float`
ProratedCreditTotalInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
ProratedDebitTotal	`DT_R8`	`float`
ProratedDebitTotalDisplay	`DT_WSTR`	`nvarchar(50)`	50
ProratedDebitTotalInPayoutCurrency	`DT_R8`	`float`
ProratedDebitTotalInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
ProratedTotal	`DT_R8`	`float`
ProratedTotalDisplay	`DT_WSTR`	`nvarchar(50)`	50
ProratedTotalInPayoutCurrencyDisplay	`DT_WSTR`	`nvarchar(50)`	50
IsScsProratedOrder	`DT_BOOL`	`bit`
IsScsProratedOrderUpgrade	`DT_BOOL`	`bit`
LastFourDigits	`DT_WSTR`	`nvarchar(50)`	50
PreviousOrderReference	`DT_WSTR`	`nvarchar(50)`	50
PreviousOrderInvoiceUrl	`DT_WSTR`	`nvarchar(500)`	500
ReturnId_1	`DT_WSTR`	`nvarchar(50)`	50	First return in this order
ReturnAmount_1	`DT_R8`	`float`		First return amount in local currency for this order
ReturnAmountUSD_1	`DT_R8`	`float`		First return amount in USD for this order
SubscriptionId_1	`DT_WSTR`	`nvarchar(50)`	50	First subscription in this order
SubscriptionId_2	`DT_WSTR`	`nvarchar(50)`	50	Second subscription in this order
SubscriptionId_3	`DT_WSTR`	`nvarchar(50)`	50	Third subscription in this order
Tags	`DT_WSTR`	`nvarchar(4000)`	4000

If the column you are looking for is missing, consider customizing FastSpring Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use FastSpring Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the AccountOrders table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - FastSpring

Read / write FastSpring data related to Orders, Subscriptions, Quotes, Products, Accounts and more without coding.

FastSpring

AccountOrders

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the AccountOrders table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get account orders:

API Destination - FastSpring

Read / write FastSpring data related to Orders, Subscriptions, Quotes, Products, Accounts and more without coding.

FastSpring

AccountOrders

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get account orders by email id

Use below query to search for accounts and orders with search criteria

SELECT * from AccountOrders
WITH(
	  Email='bob@abc.com'
)
	  
--search by one or more parameters below
/*
WITH(
	  Email='X'
	, CustomKey='X'
	, GlobalKey='X'
	, OrderID='X'
	, OrderReference='X'
	, SubscriptionId='X'
	, Products='PROD-1,PROD-2,PROD-3'
	, Refunds='true'
	, SubscriptionStatus='active'
)
*/

get_account_orders endpoint belongs to AccountOrders table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get account orders by email id

Use below query to search for accounts and orders with search criteria

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from AccountOrders
WITH(
	  Email=''bob@abc.com''
)
	  
--search by one or more parameters below
/*
WITH(
	  Email=''X''
	, CustomKey=''X''
	, GlobalKey=''X''
	, OrderID=''X''
	, OrderReference=''X''
	, SubscriptionId=''X''
	, Products=''PROD-1,PROD-2,PROD-3''
	, Refunds=''true''
	, SubscriptionStatus=''active''
)
*/';

EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY];

get_account_orders endpoint belongs to AccountOrders table(s), and can therefore be used via those table(s).