StockItems

Query StockItems in Sage Business Cloud Accounting.

Table Specific Information

Query the available StockItems

Select

The driver uses the Sage Accounting API to process search criteria that refer to Active,Search,UpdatedOrCreatedSince,DeletedSince,OutOfStock,BelowReorderLevel columns. The driver processes other filters client-side within the driver.

Active supports the '=' operator.
Search supports the '=' operator.
UpdatedOrCreatedSince supports the '=' operator.
DeletedSince supports the '=' operator.
OutOfStock supports the '=' operator.
BelowReorderLevel supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM StockItems WHERE Active = false
SELECT * FROM StockItems WHERE Search = 'abc'
SELECT * FROM StockItems WHERE UpdatedOrCreatedSince = '2018-12-15'
SELECT * FROM StockItems WHERE DeletedSince = '2018-12-15'
SELECT * FROM StockItems WHERE OutOfStock = 'abc'
SELECT * FROM StockItems WHERE BelowReorderLevel = 'abc'

Insert

INSERT INTO StockItems (SalesLedgerAccountId, SalesTaxRateId) VALUES ('1', '2')

Update

Updates are performed based on Id.

UPDATE StockItems SET Location = 'abc' WHERE Id = '123'

Delete

You must specify the Id of the StockItems to delete it.

DELETE FROM StockItems WHERE id = '123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
Location	String	False		The location for the stock item
SalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
ReorderQuantity	String	False		The reorder quantity for the stock item
SalesTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
AverageCostPrice	String	False		The average price across all purchases of this stock item
LastCostPrice	String	False		The most recent 'purchase invoice' or 'adjustment in' price
Active	Bool	False		Indicates whether the stock item is active
ItemCode	String	False		The item code for the stock item
PurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
WeightConverted	String	False		The weight of stock item converted to the lowest unit of measurement
SalesPricesAggregate	String	False		The sales prices for the stock item
DeletedAt	Datetime	True		The datetime when the item was deleted
SupplierPartNumber	String	False		The supplier part number for stock item
PurchaseTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Notes	String	False		The notes for the stock item
Weight	String	False		The weight of stock item
AverageCostPriceStockValue	String	False		The value of the current stock in terms of the average cost price
CostPrice	String	False		The cost price of the stock item
UsualSupplierId	String	False	Contacts.Id	The unique identifier for the item
MeasurementUnit	String	False		The unit of measure of weight for stock item
DisplayedAs	String	False		The name of the resource
Description	String	False		The stock item description
PurchaseDescription	String	False		The stock item purchase description
ReorderLevel	String	False		The reorder level for the stock item
LegacyId	Int	False		The legacy ID for the item
SourceGuid	String	False		Used when importing stock items from external sources
Barcode	String	False		The barcode for the stock item
LastCostPriceStockValue	String	False		The value of the current stock in terms of the last cost price
CostPriceLastUpdated	String	False		The date on which the last cost price was last updated
QuantityInStock	String	False		The current quantity of the stock item held by the business
UpdatedAt	Datetime	True		The datetime when the item was last updated
Search	String	False
DeletedSince	Datetime	True
OutOfStock	String	True
BelowReorderLevel	String	True