Plans

[DEPRECATED endpoint, use Prices instead.] Returns the available Plans in Stripe and supports creating, updating, and deleting them.

Table-Specific Information

This table is deprecated. Use the Prices table instead.

SELECT

The add-in uses the Stripe API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the add-in.


Column	Supported Operators
Id	=
Active	=
AccountId	=
ProductId	=
Created	<, >, >=, <, <=, =

You can select:

A plan by specifying its Id:
```
SELECT * FROM Plans WHERE Id = 'gold'
```

Plans created after a specific date:

SELECT * FROM Plans WHERE Created > '2018-01-01'

Created can also be used twice to specify a range:

SELECT * FROM Plans WHERE Created >= '2018-01-01 00:00:00.0' AND Created <= '2018-12-31 23:59:59.0'

INSERT

The ProductId or ProductName, Currency, and Interval columns are required to create a new plan:

INSERT INTO Plans (Active, AggregateUsage, BillingScheme, Currency, Nickname, ProductName, ProductActive, ProductStatementDescriptor, ProductTaxCode, ProductUnitLabel, [Interval], IntervalCount, TrialPeriodDays, UsageType, Tiersmode, TiersAggregate, MetadataAggregate) VALUES (true, 'last_ever', 'tiered', 'aud', 'Test2', 'MYNEWPRODUCT6', true, 'Example statement', 'txcd_30040003', 'TestLabel', 'week', 3, 15, 'metered', 'volume', '[{\"up_to\":\"inf\",\"flat_amount_decimal\":\"356\",\"unit_amount_decimal\":\"785\"}]', '[{\"Test\":\"123\",\"Test1\":\"456\",\"Test2\":\"789\"}]')

Note: There are multiple ways to create a new plan. For more details, refer to Stripe's API documentation.

UPDATE

To update a plan, specify the Id:

UPDATE Plans SET Active = false, MetadataAggregate = '[{\"order_id\":\"124\"}]', Nickname = 'updatednickname', TrialPeriodDays = 25, ProductId = 'prod_PDnk45sKPO58Cy' WHERE Id = 'plan_PDnshHrzdDXeUE'

DELETE

To delete a plan, specify the Id:

DELETE FROM Plans WHERE Id = '123easdas5'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The Id of the plan.
Active	Boolean	False		Indicates whether the plan can be used for new purchases.
Amount	Integer	False		The amount in cents to be charged on the interval specified.
AmountDecimal	Decimal	False		The decimal value of amount in cents to be charged on the interval specified.
UnitAmount	Decimal	True		The amount in cents to be charged on the interval specified.
AggregateUsage	String	False		Specifies a usage aggregation strategy for plans of usage_type=metered. The allowed values are last_during_period, last_ever, max, sum.
BillingScheme	String	False		Describes how to compute the price per period. Either per_unit or tiered. The allowed values are per_unit, tiered.
Created	Datetime	True		The creation date.
Currency	String	False		Currency in which the subscription is charged.
Nickname	String	False		A brief description of the plan, hidden from customers.
ProductId	String	False		The product whose pricing this plan determines.
ProductName	String	False		The product whose pricing this plan determines. The product's name, meant to be displayable to the customer.
ProductActive	Boolean	False		The product whose pricing this plan determines. Indicates whether the product is currently available for purchase. Defaults to true.
ProductMetadata	String	False		The product whose pricing this plan determines. Set of key-value pairs that you can attach to an object.
ProductStatementDescriptor	String	False		The product whose pricing this plan determines. An arbitrary string to be displayed on your customer's credit card or bank statement.
ProductTaxCode	String	False		The product whose pricing this plan determines. A tax code Id.
ProductUnitLabel	String	False		The product whose pricing this plan determines. A label that represents units of this product.
LiveMode	Boolean	True		Indicates whether the object exists in live mode. The value is true in live mode and false in test mode.
Interval	String	False		One of day, week, month or year. The frequency with which a subscription should be billed. The allowed values are day, week, month, year.
IntervalCount	Integer	False		The number of intervals (specified in the interval property) between each subscription billing. For example, interval=month and interval_count=3 bills every 3 months.
TrialPeriodDays	Integer	False		Number of trial period days granted when subscribing a customer to this plan. Null if the plan has no trial period.
MetadataAggregate	String	False		Set of key-value pairs that you can attach to an object.
TiersAggregate	String	False		Each element represents a pricing tier.
TiersMode	String	False		Defines if the tiering price should be graduated or volume based.
TransformUsageDivideBy	Integer	False		Divide usage by this number.
TransformUsageRound	String	False		After division, either round the result up or down.
UsageType	String	False		Configures how the quantity per period should be determined. Can be either metered or licensed.
ProductDefaultPrice	String	True		The product whose pricing this plan determines. The Id of the Price object that is the default price for this product.
ProductDescription	String	True		The product whose pricing this plan determines. The product's description, meant to be displayable to the customer. Use this field to optionally store a long form explanation of the product being sold for your own rendering purposes.
ProductObject	String	True		The product whose pricing this plan determines. String representing the object's type. Objects of the same type share the same value.
ProductCreated	Datetime	True		The product whose pricing this plan determines. The date and time at which the object was created, measured in seconds since the Unix epoch.
ProductImages	String	True		The product whose pricing this plan determines. A list of up to 8 URLs of images for this product, meant to be displayable to the customer.
ProductLivemode	Boolean	True		The product whose pricing this plan determines. Indicates whether the object exists in live mode. The value is true in live mode and false in test mode.
ProductMarketingFeatures	String	True		The product whose pricing this plan determines. A list of up to 15 marketing features for this product. These are displayed in pricing tables.
ProductPackageDimensionsHeight	Double	True		The product whose pricing this plan determines. Height, in inches.
ProductPackageDimensionsLength	Double	True		The product whose pricing this plan determines. Length, in inches.
ProductPackageDimensionsWeight	Double	True		The product whose pricing this plan determines. Weight, in inches.
ProductPackageDimensionsWidth	Double	True		The product whose pricing this plan determines. Width, in inches.
ProductShippable	Boolean	True		The product whose pricing this plan determines. Indicates whether this product is shipped (that is, physical goods).
ProductUpdated	Datetime	True		The product whose pricing this plan determines. The date and time at which the object was last updated, measured in seconds since the Unix epoch.
ProductUrl	String	True		The product whose pricing this plan determines. A URL of a publicly-accessible webpage for this product.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
AccountId	String	The Id of the connected account to get plans for.

Excel Add-In for Stripe