Power BI Connector for Sage 200

Build 20.0.7587

SalesHistoricalTransactions

Sales historical transactions are created when transactions are archived from the Sales Ledger. It is not possible to use this API resource to create historical transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: NominalAnalysisItems,TaxAnalysisItems,BankItems columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesHistoricalTransactions WHERE Id=123
SELECT * FROM SalesHistoricalTransactions WHERE CustomerId=123
SELECT * FROM SalesHistoricalTransactions WHERE TraderTransactionType='abc'
SELECT * FROM SalesHistoricalTransactions WHERE Reference='abc'
SELECT * FROM SalesHistoricalTransactions WHERE SecondReference='abc'
SELECT * FROM SalesHistoricalTransactions WHERE Queried='abc'
SELECT * FROM SalesHistoricalTransactions WHERE TransactionDate='2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE PostedDate='2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE DueDate='2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE FullSettlementDate='2019-10-02T11:46:53Z'
SELECT * FROM SalesHistoricalTransactions WHERE DocumentGoodsValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentGrossValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentTaxValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentDiscountValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DiscountPercent=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentTaxDiscountValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentAllocatedValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE DocumentOutstandingValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseGoodsValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseGrossValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseTaxValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseDiscountValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseTaxDiscountValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE BaseAllocatedValue=123.3
SELECT * FROM SalesHistoricalTransactions WHERE ControlValueInBaseCurrency=123.3
SELECT * FROM SalesHistoricalTransactions WHERE ExchangeRate=123.3
SELECT * FROM SalesHistoricalTransactions WHERE SettledImmediately='abc'
SELECT * FROM SalesHistoricalTransactions WHERE DiscountDays='abc'
SELECT * FROM SalesHistoricalTransactions WHERE Urn=123
SELECT * FROM SalesHistoricalTransactions WHERE UserName='abc'
SELECT * FROM SalesHistoricalTransactions WHERE DateTimeUpdated='2019-10-02T11:46:53Z'

Columns

Name Type References Description
Id [KEY] Long Id.
CustomerId Long Customer Id. See customers
TraderTransactionType String Transaction type. See trader_transaction_types
Reference String Transaction reference.
SecondReference String Second reference.
Queried String Query flag.
TransactionDate Date Transaction date.
PostedDate Date Posted date.
DueDate Date Due date.
FullSettlementDate Date Full settlement date.
DocumentGoodsValue Decimal Value of the goods.
DocumentGrossValue Decimal Gross value.
DocumentTaxValue Decimal Tax value.
DocumentDiscountValue Decimal Document discount value.
DiscountPercent Decimal Percentage discount.
DocumentTaxDiscountValue Decimal Tax discount value.
DocumentAllocatedValue Decimal Allocated value.
DocumentOutstandingValue Decimal Outstanding value.
BaseGoodsValue Decimal Value of the goods in base currency.
BaseGrossValue Decimal Gross value in base currency.
BaseTaxValue Decimal Tax value in base currency.
BaseDiscountValue Decimal Discount value in base currency.
BaseTaxDiscountValue Decimal Tax discount value in base currency.
BaseAllocatedValue Decimal Allocated value in base currency.
ControlValueInBaseCurrency Decimal Control value in base currency.
ExchangeRate Decimal Exchange rate used for transaction.
SettledImmediately Bool Immediately settled.
DiscountDays Short Number of days discount valid.
Urn Long Unique reference number.
UserName String User who entered the transaction.
NominalAnalysisItems String Nominal analysis items. See sales_historical_transactions_nominal_analysis_items
TaxAnalysisItems String Tax analysis items. See sales_historical_transactions_tax_analysis_items
BankItems String Bank items. See sales_historical_transactions_bank_items
DateTimeUpdated Datetime The date and time this entity was last updated (UTC).

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587