Power BI Connector for Sage Business Cloud Accounting

Build 22.0.8342

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

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