CatalogItems

Retrieves detailed information about each item in a Square seller's catalog, including names, descriptions, pricing variations, availability, and location settings.

Table Specific Information

Select

Retrieve all catalog items:

SELECT * FROM CatalogItems

Insert

To create an item, you will need to specify the Name and Variations columns. All the fields that are not read-only can be specified. You can supply a value to the Variations by passing aggregates or using #TEMP tables.

INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname1', 'VARIABLE_PRICING');
INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname2', 'VARIABLE_PRICING');
INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname3', 'VARIABLE_PRICING');
INSERT INTO CatalogItems (Name, DescriptionHtml, Variations) VALUES ('newname2', 'item desc', CatalogItemVariations#TEMP);

Creating an item, by specifying aggregate columns. You must supply the TemporaryItemId pseudo-column with the value of the 'item_id' field of the variations.

INSERT INTO CatalogItems (Name, DescriptionHtml, TemporaryItemId, Variations) VALUES ('newname', 'item desc', '#temporaryId', '[   {     "type": "ITEM_VARIATION",     "is_deleted": false,     "catalog_v1_ids": [       {         "catalog_v1_id": "2255feb6-157b-4d8c-b8da-79a30523c67c",         "location_id": "9J0M984DXGA7W"       }     ],     "present_at_all_locations": false,     "present_at_location_ids": [       "9J0M984DXGA7W"     ],  "id": "#red",   "item_variation_data": {        "item_id": "#temporaryId",       "name": "Small 55",       "sku": "1",       "ordinal": 1,       "pricing_type": "FIXED_PRICING",       "price_money": {         "amount": 200,         "currency": "USD"       },       "channels": [         "CH_0RauN49qbI5pcbqhVJDJi5H7qfkJuvBygSTD3QlQuYC"       ]     }   } ]');

Update

Modify the details of an existing catalog by providing the Id of the Items object:

UPDATE CatalogItems SET DescriptionHtml = 'TestDesc1', Name = 'TestName1', Abbreviation = 'TestAbbreviation1', AvailableForPickup = true WHERE ID = 'KAT75OWHFBAMA3GYS2LU32L5';

Updating the variation of an item:

INSERT INTO CatalogItemVariations#TEMP (Id, PricingType, PriceMoneyAmount, PriceMoneyCurrency, Version) VALUES ('QXSMA2IIPLOU4L5LNVSWCLD6', 'FIXED_PRICING', 10, 'USD', 1690797325339);
UPDATE CatalogItems SET Variations = 'CatalogItemVariations#TEMP', DescriptionHtml = 'TestDesc1', Name = 'TestName1', Abbreviation = 'TestAbbreviation1', AvailableForPickup = true WHERE ID = 'KAT75OWHFBAMA3GYS2LU32L5';

Delete

Delete a item object by providing the Id of the customer:

DELETE FROM CatalogItems WHERE Id = '5XZRRMRESJ6N6CGQQ6BFA7AG'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The item's Id.
Name	String	False	The item's name.
UpdatedAt	Datetime	True	Date and time when the item was last modified.
IsDeleted	Boolean	False	If true, the object has been deleted from the database. When deleted, the UpdatedAt field will equal the deletion time.
DescriptionHtml	String	False	The item's description as expressed in valid HTML elements.
DescriptionPlaintext	String	True	A server-generated plaintext version of the description_html field, without formatting tags.
Abbreviation	String	False	The text of the item's display label in the Square Point of Sale app. Only up to the first five characters of the string are used.
LabelColor	String	False	The color of the item's display label in the Square Point of Sale app.
AvailableOnline	Boolean	False	If true, the item can be added to shipping orders from the merchant's online store.
AvailableForPickup	Boolean	False	If true, the item can be added to pickup orders from the merchant's online store.
AvailableElectronically	Boolean	False	If true, the item can be added to electronically fulfilled orders from the merchant's online store.
Categories	String	False	The list of categories.
TaxIds	String	False	A set of tax identifiers indicating the CatalogTaxes that are enabled for this item.
ModifierListInfo	String	False	Info about the modifier lists that apply to this item.
ProductType	String	False	The product type of the item. May not be changed once an item has been created.
SkipModifierScreen	Boolean	False	If true, the Square Point of Sale app will immediately add the item to the cart with the pre-selected modifiers, and merchants can edit modifiers by drilling down onto the item's details.
PresentAtAllLocations	Boolean	False	If true, this object is present at all locations (including future locations), except where specified in the AbsentAtLocationIds field. If false, this object is not present at any locations (including future locations), except where specified in the PresentAtLocationIds field. If not specified, defaults to true.
PresentAtLocationIds	String	False	A list of locations where the object is present, even if PresentAtAllLocations is false.
AbsentAtLocationIds	String	False	A list of locations where the object is not present, even if PresentAtAllLocations is true.
Version	Int64	False	The version of the object. When updating an object, the version supplied must match the version in the database, otherwise the write will be rejected as conflicting.
ImageIds	String	False	A list of image object identifiers that are linked to the item.
Variations	String	False	It represents a product for sale in the Catalog object model. While updating, provide the whole Variations aggregate object otherwise it will override with the new value.
CreatedAt	Datetime	True	Date and time when the item was first created in the catalog.
CatalogV1Ids	String	True	The Connect v1 IDs for this object at each location where it is present, where they differ from the object's Connect V2 ID.
Channels	String	True	A list of identifiers representing channels, such as a Square Online site, where the item can be made visible or available.
EcomAvailable	Boolean	False	True if the item can be sold through Square’s eCommerce integrations.
EcomImageURIs	String	True	List of image URIs associated with the item for Square Online listings.
EcomVisibility	String	True	Specifies whether the item is visible to customers in the online store (for example, public or hidden).
IsTaxable	Boolean	False	Indicates whether the item is taxable (true) or non-taxable (false).
Visibility	String	False	Defines how the item is displayed in sales channels (for example, visible to all or hidden from customers).
ItemOptions	String	False	List of item options identifiers for this item. Used to manage and group item variations in a specified order.
SortName	String	False	A name to sort the item by. If this name is unspecified, namely, the sort_name field is absent, the regular name field is used for sorting. Its value must not be empty.
IsArchived	Boolean	False	Indicates whether this item is archived (true) or not (false).
EcomSeoDataPageTitle	String	False	The SEO title used for the Square Online store.
EcomSeoDataPageDescription	String	False	The SEO title description for the Square Online store.
EcomSeoDataPagePermalink	String	False	The SEO title permalink for the Square Online store.
FoodAndBeverageDetailsCalorieCount	Integer	False	The calorie count (in the unit of kcal) for the FOOD_AND_BEV type of items.
FoodAndBeverageDetailsDietaryPreferences	String	False	The dietary preferences for the FOOD_AND_BEV item.
FoodAndBeverageDetailsIngredients	String	False	The ingredients for the FOOD_AND_BEV type item.
ReportingCategoryId	String	False	The Id of the object's reporting category.
ReportingCategoryOrdinal	Int64	False	The order of the object within the context of the reporting category.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements (unless stated otherwise) and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
TemporaryItemId	String	When inserting variations using aggregates, supply it with the value of the 'item_id' of the variations.

SSIS Components for Square