Power BI Connector for Square

Build 21.0.8137

Createorder

Creates an Order that can then be referenced as OrderId in a request to the Charge endpoint.

Table Specific Information

AutoCharge

If you want to use the AutoCharge feature, AutoCharge must be set to "True" and you must also include in your query either of the following:
  • A value for the CardNonce parameter (to charge a card nonce generated with the SqPaymentForm).
  • Values for the CustomerCardId and CustomerId parameters (to charge a customer's card on file).

Order Items

You cannot create an order without order items. To create orderItems, you must insert data in a temporary table called 'OrderItem#TEMP'.

Example: Create three order items

Insert INTO OrderItem#TEMP(Name,Quantity,BasePriceMoneyAmount,BasePriceMoneyCurrency) VALUES ('New York Strip Steak','1',1599,'USD')
Insert INTO OrderItem#TEMP(Name,Quantity,BasePriceMoneyAmount,BasePriceMoneyCurrency) VALUES ('New York Strip Steak-Special','1',2599,'USD')
Insert INTO OrderItem#TEMP(quantity,CatalogObjectId,Modifiers,Discounts) VALUES ('2','BEMYCSMIJL46OCDV4KYIKXIB','[\n" +
        "        {\n" +
        "          \"catalog_object_id\": \"CHQX7Y4KY6N5KINJKZCFURPZ\"\n" +
        "        }\n" +
        "      ]','[\n" +
        "        {\n" +
        "          \"name\": \"Sale - $1.00 off\",\n" +
        "          \"amount_money\": {\n" +
        "            \"amount\": 100,\n" +
        "            \"currency\": \"USD\"\n" +
        "          }\n" +
        "        }\n" +
        "      ]')
The columns available for this temporary table are the following:
  • Name
  • Quantity
  • BasePriceMoneyAmount
  • BasePriceMoneyCurrency
  • VariationName
  • CatalogObjectId
  • Modifiers
  • Taxes
  • Discounts

The last three columns are aggregates. You need to create the JSON structure to provide the values to insert. For more information about the specific JSON structure, visit the following link: https://docs.connect.squareup.com/api/connect/v2#type-createorderrequestlineitem

Execute

After creating at least one order item, you can execute the stored procedure.

If you want to charge this order, you must set AutoCharge to 'true'.

Note: For adding more than one values to "Taxes" and "Discounts", you can use the OrderTaxes#TEMP and OrderDiscounts#TEMP tables or create the JSON structure to provide the values to insert. Please refer the columns available for OrderTaxes and OrderDiscounts from Orders View

INSERT INTO OrderTaxes#TEMP (TaxName, TaxPercentage) VALUES ('State Sales Tax', '9')
INSERT INTO OrderTaxes#TEMP (TaxName, TaxPercentage) VALUES ('Central Sales Tax', '9')
INSERT INTO OrderDiscounts#TEMP (DiscountName, DiscountPercentage) VALUES ('Labor Day Sale', '5')
INSERT INTO OrderDiscounts#TEMP (DiscountName, DiscountPercentage) VALUES ('New Year Sale', '10')
EXECUTE CreateOrder  AutoCharge=true, CardNonce='fake-card-nonce-ok', ReferenceId='my-order-001', OrderLineItems = 'OrderItem#TEMP', OrderTaxes = 'OrderTaxes#TEMP', OrderDiscounts = 'OrderDiscounts#TEMP'

EXECUTE CreateOrder  AutoCharge=true, CardNonce='fake-card-nonce-ok', ReferenceId='my-order-001', OrderLineItems = 'OrderItem#TEMP', OrderTaxes= '[{"OrderTaxName":"State Sales Tax","OrderTaxPercentage": 9}]', OrderDiscounts = '[{"OrderDiscountName":"Labor Day Sale","OrderDiscountPercentage":5}]'

Input

Name Type Required Description
AutoCharge Boolean False Charge the order automatically
CardNonce String False A nonce generated from the SqPaymentForm that represents the card to charge.
CustomerCardId String False The ID of the customer card on file to charge.
CustomerId String False The ID of the customer to associate this transaction with.
IdempotencyKey String False A value you specify that uniquely identifies this order among orders you've created.
ReferenceId String False An optional ID you can associate with the transaction for your own purposes.
OrderLineItems String False The aggregate for the order line items.
OrderTaxAmountMoneyAmount Integer False Only used for ad hoc taxes. The monetary amount of the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAmountMoneyCurrency String False Only used for ad hoc taxes. The currency of the tax monetary amount. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAppliedMoneyAmount Integer False Only used for ad hoc taxes. The applied amount by the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAppliedMoneyCurrency String False Only used for ad hoc taxes. The currency of the tax applied amount. This column input will not be accepted if OrderTaxes column is added.
OrderTaxCatalogObjectId String False Only used for catalog taxes. The catalog object ID of an existing CatalogTax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxName String False Only used for ad hoc taxes. The tax's name. Do not provide a value for this field if you set catalogobjectid. This column input will not be accepted if OrderTaxes column is added.
OrderTaxPercentage String False Only used for ad hoc taxes. The percentage of the tax, as a string representation of a decimal number. This column input will not be accepted if OrderTaxes column is added.
OrderTaxScope String False Only used for ad hoc taxes. Indicates the level at which the tax applies. Possible values are OTHER_TAX_SCOPE, LINE_ITEM, ORDER. This column input will not be accepted if OrderTaxes column is added.
OrderTaxType String False Only used for ad hoc taxes. The type of the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxUID String False Only used for ad hoc taxes. Unique ID that identifies the tax only within this order. This column input will not be accepted if OrderTaxes column is added.
OrderTaxes String False The aggregate for the order taxes.
OrderDiscountAmountMoneyAmount Integer False Only used for ad hoc discounts. The monetary amount of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAmountMoneyCurrency String False Only used for ad hoc discounts. The currency of the discount monetary amount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAppliedMoneyAmount Integer False Only used for ad hoc discounts. The applied amount of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAppliedMoneyCurrency String False Only used for ad hoc discounts. The currency of the discount applied amount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountCatalogObjectId String False Only used for catalog discounts. The catalog object ID from exsiting CatalogDiscount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountName String False Only used for ad hoc discounts. The discount's name. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountPercentage String False Only used for ad hoc discounts. The percentage of the discount, as a string representation of a decimal number. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountScope String False Only used for ad hoc discounts. Indicates the level at which the discount applies. Possible values are OTHER_DISCOUNT_SCOPE, LINE_ITEM, ORDER. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountType String False Only used for ad hoc discounts. The type of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountUID String False Only used for ad hoc discounts. Unique ID that identifies the discount only within this order. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscounts String False The aggregate for the order discounts.

Result Set Columns

Name Type Description
Success Boolean Whether the transaction was successful.
OrderId String The transaction's unique ID, issued by Square payments servers.
Charged Boolean Whether the order is charged or not.
TransactionId String The transaction ID created after charging the order.

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 21.0.8137