Commodities
Create, Update and Query Commodities (Products and Services) within Sage UK 50 Accounts.
Table Specific Information
The Commodities table allows you to select, insert, and update Commodities (Products and Services) within a Sage 50 UK company dataset.
Select
The Commodities table returns the products and services for a Sage 50 UK company dataset.
SELECT * FROM Commodities WHERE ProductCode LIKE 'ABC%'
Insert
To create a new Commodity record, the Description field is required.
INSERT INTO Commodities (ProductCode, Description, SupplierReference, CatalogueNumber, WebDescription, Cost) VALUES ('MNP', 'MyNewProduct', 'UNI001', 'U143', 'My Web Description', 5.49)
Update
Any field that is not read-only can be updated.
UPDATE Commodities SET Description = 'My New Description' WHERE CommodityUUID = '8c47b43a-7115-7419-9679-7c72586e67d1'
Columns
Name | Type | ReadOnly | Description |
CommodityUUID [KEY] | String | True |
Commodity UUID |
CommodityIdentifierType | String | False |
Commodity CommodityIdentifier Type |
ProductCode | String | False |
Commodity Product Code |
Reference2 | String | False |
Commodity Reference2 |
Status | String | False |
Commodity Status |
Description | String | False |
Commodity Description |
WebDescription | String | False |
Commodity Web Description |
AlternativeDescription2 | String | False |
Commodity Alternative Description 2 |
Type | String | True |
Commodity Type (Category) |
BuyingAllowedFlag | Boolean | False |
Commodity Buying Allowed Flag |
SellingAllowedFlag | Boolean | False |
Commodity Selling Allowed Flag |
CatalogueNumber | String | False |
Commodity Catalogue (Part) Number |
SupplierReference | String | False |
Commodity Supplier reference |
ManufacturerCompany | String | False |
Commodity Manufacturer Company |
Weight | Decimal | False |
Commodity Weight |
Classification | String | False |
Commodity Classification |
Cost | Decimal | False |
Commodity Cost |
InStock | Decimal | True |
Commodity Amount in Stock |
Allocated | Decimal | True |
Commodity Amount Allocated |
FreeStock | Decimal | True |
Commodity Free Stock |
OnOrder | Decimal | True |
Commodity Amount on Order |
SalesPrice | Decimal | True |
Commodity Sales Price in base currency |
UnitDescription | String | True |
Commodity UnitOfMeasure Description |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Updated | Datetime |
An input used internally to filter records updated at a specific datetime. |