接続の確立

Sync App アプリケーションの接続ページに移動し、接続の追加 パネルで対応するアイコンを選択して、Sage 200 への接続を作成します。Sage 200 アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからSage 200 コネクタをダウンロードおよびインストールします。

必須プロパティは［設定］タブにリストされています。［Advanced］タブには、通常は必要ない接続プロパティが表示されます。

Sage 200 への接続

接続の前に、Sage API にアカウントが登録されていることを確認してください。こちらのリンクhttps://developer.columbus.sage.com/docs/services/api/uk/subscribe に従って簡単に行えます。
You will need to select a subscription that contains the API of your Sage 200 edition (StandardUK or ProfessionalUK). The Sage 200 Unlimited subscription should contain the APIs of both editions.

Sage 200 に接続するには、次の接続プロパティを指定します。

Schema：どのSage 200 エディションに接続するかを指定してください。StandardUK またはProfessionalUK を指定します。
SubscriptionKey：API にサブスクライブ後Sage 200 にログインすると、プロファイル内にサブスクリプションキーが表示されます。

Sage 200 への認証

Sage 200 はOAuth 認証標準を利用しています。認証方法については、OAuth 認証の使用を参照してください。

データアクセスのファインチューニング

Other Properties

CompanyId: The specific company to connect to. It should only be set if you have multiple companies. The CompanyId should be sent to you by Sage when each company is created.

高度な機能

このセクションでは、Sage 200 Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App を使用すると、事前設定されたクエリによって内容が決定されるユーザー定義ビューと呼ばれる仮想テーブルを定義できます。このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。カスタムビューの作成と設定の概要については、ユーザー定義ビューを参照してください。

SSL の設定

SSL の設定を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。詳しくは、接続文字列オプションにあるSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシに合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、Sage 200 にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくはクエリ処理を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログを参照してください。基本的なロギングでは、次の2つの接続プロパティを設定するだけです。LogModules 接続プロパティを使用してログに記録する情報のサブセットを選択できる、より洗練されたロギングをサポートする多数の機能があります。

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
接続をトンネルするには、FirewallType をTUNNEL に設定します。
認証するには、FirewallUser とFirewallPassword を設定します。
SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

データモデル

Overview

This section shows the available API objects and provides more information on executing SQL to Sage 200 APIs.

Key Features

The Sync App models Sage 200 entities like banks, customers, and products as relational views, allowing you to write SQL to query Sage 200 data.
Stored procedures allow you to execute operations to Sage 200, including retrieving the access token and keeping it refreshed in OAuth 2.0.
Live connectivity to these objects means any changes to your Sage 200 account are immediately reflected when using the Sync App.

Sage 200 StandardUK Data Model

Sage200 StandardUK データモデル describes the schemas available to connect to Sage 200 Standard UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by CData Sync App to automate working with Sage 200 data.

Sage 200 ProfessionalUK Data Model

Sage200 ProfessionalUK データモデル describes the schemas available to connect to Sage 200 Professional UK accounts. You can use tables to work with live Sage 200 data. You can use stored procedures provided by CData Sync App to automate working with Sage 200 data.

Sage200 StandardUK データモデル

Views

ビュー describes the available views. Views are statically defined to model Banks, Customers, Products, and more.

Stored Procedures

ストアドプロシージャ are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.

ビュー

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

Sage 200 Connector for CData Sync ビュー


Name	Description
BankPostedTransactions	Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.
Banks	Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.
CompanyDetails	Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.
CostCentres	Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.
Currencies	Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.
CustomerContacts	Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
CustomerDeliveryAddresses	A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.
Customers	Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.
Departments	Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.
FinancialReportLayouts	Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.
FinancialYearPeriods	This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.
NominalCodes	Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.
NominalReportCategories	Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).
NominalTransactionAnalysisCodes	Nominal transaction analysis codes are used to group nominal analysis items on various transaction types
PendingEmails	When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.
PriceBands	A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.
ProductGroups	Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.
ProductPrices	This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.
Products	Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.
ProductTransactions	A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.
PurchaseOrderLines	When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.
PurchaseOrders	Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.
PurchasePostedTransactions	Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.
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.
SalesOrderAdditionalCharges	Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.
SalesOrderLines	When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.
SalesOrderProformaLines	This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.
SalesOrderQuoteLines	This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.
SalesOrders	Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.
SalesPostedTransactions	Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.
SalesTradingPeriods	This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)
SalesTransactions	When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.
StockSettings	These settings to define how to process and manage your stock.
SupplierContacts	Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
Suppliers	Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).
TaxCodes	Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.
WarehouseHoldings	All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.
Warehouses	All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

BankPostedTransactions

Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.

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,TradeItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM BankPostedTransactions WHERE Id=29448
SELECT * FROM BankPostedTransactions WHERE BankId=29401
SELECT * FROM BankPostedTransactions WHERE BankTransactionType='Payment'
SELECT * FROM BankPostedTransactions WHERE EntryDescription='Opening Balance'
SELECT * FROM BankPostedTransactions WHERE Reference='O/Bal'
SELECT * FROM BankPostedTransactions WHERE SecondReference='abc'
SELECT * FROM BankPostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM BankPostedTransactions WHERE PostedDate='2017-09-13T02:00:00.000+02:00'
SELECT * FROM BankPostedTransactions WHERE ChequeValue=-6511.96
SELECT * FROM BankPostedTransactions WHERE ChequeDiscountValue=0
SELECT * FROM BankPostedTransactions WHERE ChequeToAccountCurrencyRate=1
SELECT * FROM BankPostedTransactions WHERE BankChequeToBaseCurrencyRate=1
SELECT * FROM BankPostedTransactions WHERE ChequeCurrencyId=2103
SELECT * FROM BankPostedTransactions WHERE Urn=24
SELECT * FROM BankPostedTransactions WHERE UserName='Migration'
SELECT * FROM BankPostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.467+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Id.
BankId	Long	Banks.Id	Bank Id. See banks
BankTransactionType	String		Transaction type. See bank_transaction_types
EntryDescription	String		Transaction description.
Reference	String		Transaction reference.
SecondReference	String		Second reference.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
ChequeValue	Decimal		Value of the entry.
ChequeDiscountValue	Decimal		Entry discount value.
ChequeToAccountCurrencyRate	Decimal		Exchange rate between the bank account currency and the cheque currency.
BankChequeToBaseCurrencyRate	Decimal		Exchange rate between the bank account and the company base currency.
ChequeCurrencyId	Long		Currency Id. See currencies
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See bank_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See bank_posted_transactions_tax_analysis_items
TradeItems	String		Trade items. See bank_posted_transactions_trade_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Banks

Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: BankAddressId,BankAddressContact,BankAddressCountry,BankAddressCity,BankAddressPostcode,BankAddress_1,BankAddress_2,BankAddress_3,BankAddress_4,BankContacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Banks WHERE Id=29401
SELECT * FROM Banks WHERE Code='1200'
SELECT * FROM Banks WHERE Description='Bank Current Account'
SELECT * FROM Banks WHERE SortCode='233434'
SELECT * FROM Banks WHERE AccountNo='003234234'
SELECT * FROM Banks WHERE BankName='Lloyds Bank PLC'
SELECT * FROM Banks WHERE AccountType='BankAccountTypeCurrent'
SELECT * FROM Banks WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Banks WHERE Balance=-9941.57
SELECT * FROM Banks WHERE BalanceInBase=-9941.57
SELECT * FROM Banks WHERE OverdraftLimit=0
SELECT * FROM Banks WHERE AccountName='Stationery & Computer Mart UK Ltd'
SELECT * FROM Banks WHERE Iban='abc'
SELECT * FROM Banks WHERE Bic='abc'
SELECT * FROM Banks WHERE StatementBalance=0
SELECT * FROM Banks WHERE StatementDate='2019-10-02T11:46:53Z'
SELECT * FROM Banks WHERE AccountCurrencyId=2103
SELECT * FROM Banks WHERE DateTimeUpdated='2018-02-28T15:23:46.070+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Bank account record Id.
Code	String		Bank account code.
Description	String		Bank description.
SortCode	String		Bank sort code.
AccountNo	String		Bank account number.
BankName	String		Bank name.
AccountType	String		Bank account type. See bank_account_types
AccountStatusType	String		The status of the bank account (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
Balance	Decimal		Bank balance.
BalanceInBase	Decimal		Bank balance in base currency.
OverdraftLimit	Decimal		Bank overdraft limit.
AccountName	String		Account name.
Iban	String		International bank account number.
Bic	String		Bank identifier code.
StatementBalance	Decimal		Last statement balance.
StatementDate	Date		Last statement date.
AccountCurrencyId	Long	Currencies.Id	Currency record Id. See currencies
BankAddressId	Long		Bank address. Bank address record id.
BankAddressContact	String		Bank address. Address Contact name.
BankAddressCountry	String		Bank address. Country.
BankAddressCity	String		Bank address. City.
BankAddressPostcode	String		Bank address. Postcode.
BankAddress_1	String		Bank address. Address line 1.
BankAddress_2	String		Bank address. Address line 2.
BankAddress_3	String		Bank address. Address line 3.
BankAddress_4	String		Bank address. Address line 4.
BankContacts	String		Collection of bank contact values.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CompanyDetails

Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEUMember,AddressCountry,Address_1,Address_2,Address_3,Address_4,AddressCity,AddressCounty,AddressPostcode columns.

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

For example, the following queries are processed server side:

SELECT * FROM CompanyDetails WHERE Name='Stationery & Computer Mart UK'
SELECT * FROM CompanyDetails WHERE Telephone='0191 955 3000'
SELECT * FROM CompanyDetails WHERE Fax='0191 955 3001'
SELECT * FROM CompanyDetails WHERE Email='[email protected]'
SELECT * FROM CompanyDetails WHERE Website='http://www.stationerycomputermartuk.co.uk'
SELECT * FROM CompanyDetails WHERE TaxRegistrationNumber='376 6823 94'
SELECT * FROM CompanyDetails WHERE CountryCodeId=13
SELECT * FROM CompanyDetails WHERE YearsAccountsHeldFor=2
SELECT * FROM CompanyDetails WHERE EoriNumber='abc'
SELECT * FROM CompanyDetails WHERE DateTimeUpdated='2018-02-28T15:23:46.040+01:00'

Columns


Name	Type	References	Description
Name [KEY]	String		Company name.
Telephone	String		Telephone number.
Fax	String		Fax number.
Email	String		Email address.
Website	String		Company website address.
TaxRegistrationNumber	String		Tax registration number.
CountryCodeId	Long		Country code record Id. See country_codes
CountryCode	String		Country code record Id. See country_codes
CountryCodeName	String		Country code record Id. See country_codes
CountryCodeEUMember	Boolean		Country code record Id. See country_codes
YearsAccountsHeldFor	Int		Number of years that periods with nominal data exist for up to a maximum of six years.
AddressCountry	String		The company's address Id.
Address_1	String		The company's address. Address line 1.
Address_2	String		The company's address. Address line 2.
Address_3	String		The company's address. Address line 3.
Address_4	String		The company's address. Address line 4.
AddressCity	String		The company's address. City
AddressCounty	String		The company's address. County
AddressPostcode	String		The company's address. Postcode
EoriNumber	String		The company's EORI number.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CostCentres

Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CostCentres WHERE Id=1015
SELECT * FROM CostCentres WHERE Code='abc'
SELECT * FROM CostCentres WHERE Name='abc'
SELECT * FROM CostCentres WHERE DateTimeUpdated='2018-02-28T15:23:46.230+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Cost centre record Id.
Code	String		Cost centre code.
Name	String		Cost centre name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Currencies

Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering 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: CurrencyIsoCode,CurrencyIsoCodeName columns.

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

For example, the following queries are processed server side:

SELECT * FROM Currencies WHERE Id=2103
SELECT * FROM Currencies WHERE Symbol='ﾂ｣'
SELECT * FROM Currencies WHERE Name='Pound Sterling'
SELECT * FROM Currencies WHERE CoreCurrencyRate=1
SELECT * FROM Currencies WHERE EuroCurrencyRate=0.714285
SELECT * FROM Currencies WHERE CurrencyIsoCodeId=49
SELECT * FROM Currencies WHERE IsBaseCurrency=true
SELECT * FROM Currencies WHERE IsEuroCurrency=false
SELECT * FROM Currencies WHERE DateTimeUpdated='2018-02-28T15:23:46.243+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Currency record Id.
Symbol	String		Currency symbol.
Name	String		Currency name.
CoreCurrencyRate	Decimal		Currency rate in use against base.
EuroCurrencyRate	Decimal		Euro rate.
CurrencyIsoCodeId	Long		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCode	String		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCodeName	String		ISO Currency Code Record Id. See currency_iso_codes
IsBaseCurrency	Bool		Flag to indicate if the currency is the base currency.
IsEuroCurrency	Bool		Flag to indicate if the currency is Euro.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CustomerContacts

Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CustomerContacts WHERE Id=42101
SELECT * FROM CustomerContacts WHERE CustomerId='0000000001'
SELECT * FROM CustomerContacts WHERE Name='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Customer contact record Id.
CustomerId	Long		Customer record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent customer. See customer
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Customer emails. See customer_emails
Telephones	String		Customer telephones. See customer_telephones
Mobiles	String		Customer mobiles. See customer_mobiles
Faxes	String		Customer faxes. See customer_faxes
Websites	String		Customer websites. See customer_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CustomerDeliveryAddresses

A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CustomerDeliveryAddresses WHERE Id=27927
SELECT * FROM CustomerDeliveryAddresses WHERE Contact='Lee Dalkin'
SELECT * FROM CustomerDeliveryAddresses WHERE Country='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE CustomerId=27825
SELECT * FROM CustomerDeliveryAddresses WHERE Description='Registered address'
SELECT * FROM CustomerDeliveryAddresses WHERE Email='[email protected]'
SELECT * FROM CustomerDeliveryAddresses WHERE Fax='01742 876 236'
SELECT * FROM CustomerDeliveryAddresses WHERE IsDefault=false
SELECT * FROM CustomerDeliveryAddresses WHERE PostalName='A1 Design Services'
SELECT * FROM CustomerDeliveryAddresses WHERE TaxCodeId=1729
SELECT * FROM CustomerDeliveryAddresses WHERE TaxNumber='GB238 3839 38'
SELECT * FROM CustomerDeliveryAddresses WHERE Telephone='01742 876 234'
SELECT * FROM CustomerDeliveryAddresses WHERE Address1='67a Station Road'
SELECT * FROM CustomerDeliveryAddresses WHERE Address2='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Address3='Blackpool'
SELECT * FROM CustomerDeliveryAddresses WHERE Address4='Lancashire'
SELECT * FROM CustomerDeliveryAddresses WHERE City='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE County='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Postcode='BP12 7HT'
SELECT * FROM CustomerDeliveryAddresses WHERE AddressCountryCodeId=0
SELECT * FROM CustomerDeliveryAddresses WHERE DateTimeUpdated='2018-02-28T15:23:46.867+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		The unique id of the customer delivery address.
Contact	String		The contact associated with the customer delivery address.
Country	String		Country (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
CustomerId	Long		Unique Id of the customer account the customer delivery address is associated with. See customers
Description	String		The description of the customer delivery address.
Email	String		The email address associated with the customer delivery address.
Fax	String		The fax number associated with the customer delivery address.
IsDefault	Bool		Flag to indicate if this is the default customer delivery address for the parent customer. See customer
PostalName	String		Postal name is the name of the person or company who the invoice or sales order is addressed to.
TaxCodeId	Long		The tax code record Id. See tax_codes
TaxNumber	String		The tax number.
Telephone	String		The telephone number associated with the customer delivery address.
Address1	String		Address line 1.
Address2	String		Address line 2.
Address3	String		Address line 3.
Address4	String		Address line 4.
City	String		City (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
County	String		County (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
Postcode	String		Postcode.
AddressCountryCodeId	Long		Country code Id. See country_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Customers

Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Customers WHERE Id=27825
SELECT * FROM Customers WHERE Reference='A1D001'
SELECT * FROM Customers WHERE Name='A1 Design Services'
SELECT * FROM Customers WHERE ShortName='A1 Desig'
SELECT * FROM Customers WHERE Balance=1120.92
SELECT * FROM Customers WHERE OnHold=false
SELECT * FROM Customers WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Customers WHERE CurrencyId=2103
SELECT * FROM Customers WHERE ExchangeRateType='ExchangeRateSingle'
SELECT * FROM Customers WHERE TelephoneCountryCode='44'
SELECT * FROM Customers WHERE TelephoneAreaCode='01742'
SELECT * FROM Customers WHERE TelephoneSubscriberNumber='876234'
SELECT * FROM Customers WHERE FaxCountryCode='44'
SELECT * FROM Customers WHERE FaxAreaCode='01742'
SELECT * FROM Customers WHERE FaxSubscriberNumber='876236'
SELECT * FROM Customers WHERE Website='www.sage.co.uk'
SELECT * FROM Customers WHERE CreditLimit=0
SELECT * FROM Customers WHERE DefaultTaxCodeId=1729
SELECT * FROM Customers WHERE VatNumber='GB238 3839 38'
SELECT * FROM Customers WHERE DunsCode='abc'
SELECT * FROM Customers WHERE AnalysisCode1='Trade'
SELECT * FROM Customers WHERE AnalysisCode2='George'
SELECT * FROM Customers WHERE AnalysisCode3='Lancashire'
SELECT * FROM Customers WHERE AnalysisCode4='abc'
SELECT * FROM Customers WHERE AnalysisCode5='abc'
SELECT * FROM Customers WHERE AverageTimeToPay=0
SELECT * FROM Customers WHERE ValueOfCurrentOrdersInSop=340.38
SELECT * FROM Customers WHERE CountryCodeId=13
SELECT * FROM Customers WHERE DateTimeUpdated='2018-03-05T11:19:18.317+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Customer unique Id.
Reference	String		Customer account reference. NOTE: Not required if customer reference is set to
Name	String		Customer name.
ShortName	String		Customer short name.
Balance	Decimal		Customer account balance.
OnHold	Bool		True if customer account is on hold, else False.
AccountStatusType	String		The status of the customer account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the customer account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the customer.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
DunsCode	String		DUNS number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AverageTimeToPay	Int		The customer average time to pay.
ValueOfCurrentOrdersInSop	Decimal		Value of current sales orders for this customer.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Customer contacts. See customer_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Departments

Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM Departments WHERE Id=1016
SELECT * FROM Departments WHERE Code='abc'
SELECT * FROM Departments WHERE Name='abc'
SELECT * FROM Departments WHERE DateTimeUpdated='2018-02-28T15:23:46.277+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Department record Id.
Code	String		Department code.
Name	String		Department name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

FinancialReportLayouts

Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM FinancialReportLayoutViews WHERE LayoutId = 46151
SELECT * FROM FinancialReportLayoutViews WHERE LayoutAccountReportType = 'ProfitLoss'
SELECT * FROM FinancialReportLayoutViews WHERE LayoutName = 'Sample Profit and Loss Layout'
SELECT * FROM FinancialReportLayoutViews WHERE RowId = 47221
SELECT * FROM FinancialReportLayoutViews WHERE RowLineType = 'Text'
SELECT * FROM FinancialReportLayoutViews WHERE RowLayoutPositionType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE RowLayoutDebitOrCreditType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE RowReportRow = 1
SELECT * FROM FinancialReportLayoutViews WHERE RowTitle = 'Sales'
SELECT * FROM FinancialReportLayoutViews WHERE RowSubtotalGroup = 1
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak1 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak2 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak3 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak4 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak5 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak6 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak7 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak8 = 0
SELECT * FROM FinancialReportLayoutViews WHERE RowGroupBreak9 = 0
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryId = 123
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryCode = 'abc'
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryDescription = 'abc'

Columns


Name	Type	References	Description
LayoutId [KEY]	Long		Financial report layout record Id.
LayoutAccountReportType	String		The type of the financial layout. See financial_report_layout_types
LayoutName	String		financial report layout name.
RowId	Long		An asscoiated financial report row Id.
RowLineType	String		The type of the financial layout. See financial_report_row_line_types
RowLayoutPositionType	String		The type of the financial report row layout position. See financial_report_row_layout_position_types
RowLayoutDebitOrCreditType	String		The type of the financial layout. See financial_report_row_layout_debit_or_credit_types
RowReportRow	Int		Value to show the line number of the report row in the layout.
RowTitle	String		The row title.
RowSubtotalGroup	Int		Value to represent the sub total group.
RowGroupBreak1	Int		Value to represent the report row group break 1.
RowGroupBreak2	Int		Value to represent the report row group break 2.
RowGroupBreak3	Int		Value to represent the report row group break 3.
RowGroupBreak4	Int		Value to represent the report row group break 4.
RowGroupBreak5	Int		Value to represent the report row group break 5.
RowGroupBreak6	Int		Value to represent the report row group break 6.
RowGroupBreak7	Int		Value to represent the report row group break 7.
RowGroupBreak8	Int		Value to represent the report row group break 8.
RowGroupBreak9	Int		Value to represent the report row group break 9.
NominalAccountReportCategoryId	Long		An Id specific to each nominal account report category. See nominal_report_category_types
NominalAccountReportCategoryCode	String		The nominal account report category code.
NominalAccountReportCategoryDescription	String		The nominal account report category description.

FinancialYearPeriods

This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearId=1
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearStartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearEndDate='2018-12-31T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE YearRelativeToCurrentYear=0
SELECT * FROM FinancialYearPeriodViews WHERE NumberOfPeriodsInYear=12
SELECT * FROM FinancialYearPeriodViews WHERE AccountingPeriodId=1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodNumber=1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodStartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE PeriodEndDate='2018-01-31T01:00:00.000+01:00'

Columns


Name	Type	References	Description
FinancialYearId [KEY]	Long		An Id specific to each financial year.
FinancialYearStartDate	Date		Date the financial year starts. The returned string includes the time but this will always be 00:00:00.
FinancialYearEndDate	Date		Date the financial year ends. The returned string includes the time but this will always be 00:00:00.
YearRelativeToCurrentYear	Int		Value to represent the year relative to the current financial year. The current financial year is 0. The first previous year is -1. The first future year is 1.
NumberOfPeriodsInYear	Int		The number of periods in the financial year.
AccountingPeriodId	Long		An Id specific to each accounting period.
PeriodNumber	Int		Value to show the period in relation to the associated year. For example, for a year that starts in January, January will be period 1, February period 2 etc.
PeriodStartDate	Date		Date the period starts. The returned string includes the time but this will always be 00:00:00.
PeriodEndDate	Date		Date the period finished. The returned string includes the time but this will always be 00:00:00.

NominalCodes

Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalCodes WHERE Id=2317
SELECT * FROM NominalCodes WHERE Name='Freehold Property'
SELECT * FROM NominalCodes WHERE Reference='0010'
SELECT * FROM NominalCodes WHERE CostCentreId=1015
SELECT * FROM NominalCodes WHERE CostCentreCode='abc'
SELECT * FROM NominalCodes WHERE DepartmentId=1016
SELECT * FROM NominalCodes WHERE DepartmentCode='abc'
SELECT * FROM NominalCodes WHERE NominalAccountType='NominalAccountTypePosting'
SELECT * FROM NominalCodes WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM NominalCodes WHERE ReportCategoryId=2289
SELECT * FROM NominalCodes WHERE DisplayBalancesInSelectionList=false
SELECT * FROM NominalCodes WHERE BalanceYearToDate=123.3
SELECT * FROM NominalCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.717+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Nominal code record Id.
Name	String		Nominal code name.
Reference	String		Nominal code reference.
CostCentreId	Long	CostCentres.Id	Nominal code cost centre record Id. See cost_centres
CostCentreCode	String		Cost centre code.
DepartmentId	Long	Departments.Id	Nominal code department record Id. See departments
DepartmentCode	String		Department code.
NominalAccountType	String		The type of the nominal code. See nominal_account_types
AccountStatusType	String		The status of the nominal code (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
ReportCategoryId	Long		Financial report category record Id. See nominal_report_categories
DisplayBalancesInSelectionList	Bool		Whether balances for the nominal code can be shown in selection lists.
BalanceYearToDate	Decimal		The nominal account balance, if balances can be shown for the nominal account.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

NominalReportCategories

Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalReportCategories WHERE Id=2258
SELECT * FROM NominalReportCategories WHERE AccountReportType='ProfitLoss'
SELECT * FROM NominalReportCategories WHERE Code='1'
SELECT * FROM NominalReportCategories WHERE Description='Sales'
SELECT * FROM NominalReportCategories WHERE AccountReportCategoryType='NominalReportingCategoryTypeIncome'
SELECT * FROM NominalReportCategories WHERE DateTimeUpdated='2018-02-28T15:23:45.960+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Nominal report category record Id.
AccountReportType	String		The type of the report.. See financial_report_layout_types
Code	String		Nominal report category code.
Description	String		Nominal report category description.
AccountReportCategoryType	String		The type of the report category. See nominal_report_category_types
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

NominalTransactionAnalysisCodes

Nominal transaction analysis codes are used to group nominal analysis items on various transaction types

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalTransactionAnalysisCodes WHERE Code='Promo0715'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Description='Promotion July 2015'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Active=true
SELECT * FROM NominalTransactionAnalysisCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.413+01:00'

Columns


Name	Type	References	Description
Code [KEY]	String		Nominal transaction analysis code.
Description	String		Nominal transaction analysis code description.
Active	Bool		Boolean indicating whether the transaction analysis code is active.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PendingEmails

When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PendingEmails WHERE Id=123
SELECT * FROM PendingEmails WHERE EmailType='abc'
SELECT * FROM PendingEmails WHERE EmailAddress='abc'
SELECT * FROM PendingEmails WHERE EmailSubject='abc'
SELECT * FROM PendingEmails WHERE EmailBody='abc'
SELECT * FROM PendingEmails WHERE UserNumber=123
SELECT * FROM PendingEmails WHERE UserName='abc'
SELECT * FROM PendingEmails WHERE DateTimeUpdated='2019-10-02T11:46:53Z'

Columns


Name	Type	References	Description
Id [KEY]	Long		The unique id of the Sage 200 pending email identifier.
EmailType	String		The type of pending email (which provides the intended purpose).
EmailAddress	String		The email address of the email recipient.
EmailSubject	String		The text to use for the subject of the email.
EmailBody	String		The text to use for the body of the email.
UserNumber	Long		The user id of the email recipient.
UserName	String		The user name of the email recipient.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PriceBands

A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PriceBands WHERE Id=1064
SELECT * FROM PriceBands WHERE Name='Standard'
SELECT * FROM PriceBands WHERE Description='Standard'
SELECT * FROM PriceBands WHERE IsStandardBand=true
SELECT * FROM PriceBands WHERE CurrencyId=2103
SELECT * FROM PriceBands WHERE PriceBandTypeId=0
SELECT * FROM PriceBands WHERE IsActive=true
SELECT * FROM PriceBands WHERE IsTimeBased=false
SELECT * FROM PriceBands WHERE ActiveDateTimeFrom='2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE ActiveDateTimeTo='2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE DateTimeUpdated='2018-02-28T15:23:46.183+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Price band record Id.
Name	String		Price band name.
Description	String		Price band description.
IsStandardBand	Bool		Is this the Standard price band.
CurrencyId	Long		Currency record Id. See currencies
PriceBandTypeId	Long		Price band type record Id; this will be 0 for Universal price bands and 1 for Limited price bands.
IsActive	Bool		Is this price band active.
IsTimeBased	Bool		Is this price band time based.
ActiveDateTimeFrom	Datetime		The date and time this price band is active from.
ActiveDateTimeTo	Datetime		The date and time this price band is active to.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductGroups

Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductGroups WHERE Id=34632
SELECT * FROM ProductGroups WHERE Code='NONS'
SELECT * FROM ProductGroups WHERE Description='Non Stock Item'
SELECT * FROM ProductGroups WHERE ProductType='EnumStockItemTypeMisc'
SELECT * FROM ProductGroups WHERE CanLevelsGoNegative=false
SELECT * FROM ProductGroups WHERE DateTimeUpdated='2018-02-28T15:23:46.310+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product group Id.
Code	String		Product group code.
Description	String		Product group description.
ProductType	String		Product group type. See product_types
CanLevelsGoNegative	Bool		Can stock levels go negative.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductPrices

This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductPriceViews WHERE ProductPriceId=34760
SELECT * FROM ProductPriceViews WHERE ProductId=34751
SELECT * FROM ProductPriceViews WHERE ProductCode='BOARD001'
SELECT * FROM ProductPriceViews WHERE ProductName='Whiteboard - Drywipe (900 x 1200)'
SELECT * FROM ProductPriceViews WHERE ProductStockUnitName='Each'
SELECT * FROM ProductPriceViews WHERE PriceBandId=1064
SELECT * FROM ProductPriceViews WHERE PriceBandName='Standard'
SELECT * FROM ProductPriceViews WHERE ProductPriceUseStandard=true
SELECT * FROM ProductPriceViews WHERE ProductPricePrice=20
SELECT * FROM ProductPriceViews WHERE CurrencyId=2103
SELECT * FROM ProductPriceViews WHERE CurrencyName='Pound Sterling'
SELECT * FROM ProductPriceViews WHERE CurrencySymbol='ﾂ｣'
SELECT * FROM ProductPriceViews WHERE DateTimeUpdated='2018-02-28T15:23:46.787+01:00'

Columns


Name	Type	References	Description
ProductPriceId [KEY]	Long		Product price record Id.
ProductId [KEY]	Long		Product record Id. See products
ProductCode	String		Product code.
ProductName	String		Product name.
ProductStockUnitName	String		The name of the unit the product is sold in. For example box, crate, bottle. The default unit is each.
PriceBandId	Long		Price band record Id.
PriceBandName	String		The name of the price band.
ProductPriceUseStandard	Bool		'True' if uses the price from the standard price band is used otherwise 'False' The price of a product can be linked to the price set in the standard price band. This is used when only some product prices are different to the standard price.
ProductPricePrice	Decimal		The selling price for the shown product and price band.
CurrencyId	Long		Currency record Id. See currencies
CurrencyName	String		The name of the currency used for the price.
CurrencySymbol	String		The symbol of the currency used for the price.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Products

Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

SELECT * FROM Products WHERE Id=34737
SELECT * FROM Products WHERE Code='M'
SELECT * FROM Products WHERE Name='M'
SELECT * FROM Products WHERE Description='abc'
SELECT * FROM Products WHERE Barcode='abc'
SELECT * FROM Products WHERE AllowSalesOrder=true
SELECT * FROM Products WHERE ProductGroupId=34633
SELECT * FROM Products WHERE TaxCodeId=1731
SELECT * FROM Products WHERE DateTimeUpdated='2019-08-01T18:00:33.140+02:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product record Id.
Code	String		Product code.
Name	String		Product name.
Description	String		Product description.
Barcode	String		Product barcode.
AllowSalesOrder	Bool		Allowed on a sales order.
ProductGroupId	Long		Product group record Id. See product_groups
TaxCodeId	Long		Tax code record Id. See tax_codes
WarehouseHoldings	String		If stock setting UseMultipleLocations is true, you must specify warehouse holding(s) when creating products. If stock setting UseMultipleLocations is false, then new product will be automatically linked to the default (home) warehouse. See warehouse_holdings
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductTransactions

A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductTransactionViews WHERE Id=36511
SELECT * FROM ProductTransactionViews WHERE ProductId=34751
SELECT * FROM ProductTransactionViews WHERE ProductCode='BOARD001'
SELECT * FROM ProductTransactionViews WHERE ProductGroupId=34640
SELECT * FROM ProductTransactionViews WHERE ProductGroupCode='0006'
SELECT * FROM ProductTransactionViews WHERE TransactionType='EnumSecondaryActivityTypeStockTakeAdjustmentIn'
SELECT * FROM ProductTransactionViews WHERE TransactionDate='2015-12-31T01:00:00.000+01:00'
SELECT * FROM ProductTransactionViews WHERE SourceAreaReference='abc'
SELECT * FROM ProductTransactionViews WHERE SourceAreaName='Internal'
SELECT * FROM ProductTransactionViews WHERE Reference='abc'
SELECT * FROM ProductTransactionViews WHERE SecondReference='STK TAKE'
SELECT * FROM ProductTransactionViews WHERE WarehouseName='Warehouse'
SELECT * FROM ProductTransactionViews WHERE BinName='Unspecified'
SELECT * FROM ProductTransactionViews WHERE Quantity=2
SELECT * FROM ProductTransactionViews WHERE UnitCostPrice=15
SELECT * FROM ProductTransactionViews WHERE UnitIssuePrice=0
SELECT * FROM ProductTransactionViews WHERE UnitDiscountValue=0
SELECT * FROM ProductTransactionViews WHERE TotalOrderDiscount=0
SELECT * FROM ProductTransactionViews WHERE CostValue=30
SELECT * FROM ProductTransactionViews WHERE IssueValue=0
SELECT * FROM ProductTransactionViews WHERE Memo='abc'
SELECT * FROM ProductTransactionViews WHERE UserName='Migration'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode1='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode2='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode3='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode4='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode5='abc'
SELECT * FROM ProductTransactionViews WHERE DateTimeUpdated='2018-02-28T15:23:46.123+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product transaction Id.
ProductId	Long		Product record Id. See products
ProductCode	String		Product code.
ProductGroupId	Long		Product group record Id. See product_groups
ProductGroupCode	String		Product group code. See product_groups
TransactionType	String		Enum value that describes the transaction that created the product movement. See product_transaction_types
TransactionDate	Date		The date of the transaction.
SourceAreaReference	String		The code of the source of the transaction.
SourceAreaName	String		The name of the source of the transaction.
Reference	String		This can be an optional reference added to the transaction by the user, or an automatically generated reference such as the order number.
SecondReference	String		This can be an additional optional reference added to the transaction by the user, or an automatically generated reference such as the despatch note number.
WarehouseName	String		The warehouse the product was moved from or to. See warehouses
BinName	String		The name of the bin to which the transaction relates to.
Quantity	Decimal		The quantity on the transaction.
UnitCostPrice	Decimal		The cost price per unit for this transaction.
UnitIssuePrice	Decimal		The price used when products are moved out of stock.
UnitDiscountValue	Decimal		The discount amount per unit used for this transaction.
TotalOrderDiscount	Decimal		The total discount value of the transaction.
CostValue	Decimal		The total cost value.
IssueValue	Decimal		The total issue or sales price of the transaction.
Memo	String		Any memo note that was applied to the product transaction.
UserName	String		The name of the Sage 200 user that created the transaction.
AnalysisCode1	String		Analysis Code 1.
AnalysisCode2	String		Analysis Code 2.
AnalysisCode3	String		Analysis Code 3.
AnalysisCode4	String		Analysis Code 4.
AnalysisCode5	String		Analysis Code 5.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PurchaseOrderLines

When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrderLines WHERE PurchaseOrderId=42101
SELECT * FROM PurchaseOrderLines WHERE CurrencyId='0000000001'

Columns


Name	Type	References	Description
PurchaseOrderId [KEY]	Long	PurchaseOrders.Id	Unique ID of the purchase order. See _orders
SupplierDocumentNo	String		Supplier purchase order document number.
CurrencyId	Long		The Id for the currency used for the purchase order. See currencies
CurrencyName	String		The name for the currency used for the purchase order.
CurrencySymbol	String		The symbol of the currency used for the purchase order.
SubtotalValue	Decimal		The subtotal value for the order.
SubtotalDiscountValue	Decimal		The subtotal discount value for the order.
SubtotalLandedCostsValue	Decimal		The subtotal landed costs value for the order.
LineId	Long		Associated purchase order line Id. See _order_lines
LineNumber	Short		Purchase order line number.
LineType	String		Purchase order line type. See order_return_line_types
LineProductCode	String		Purchase order line product code.
LineProductDescription	String		Purchase order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineUnitPrice	Decimal		The buying price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineTotalValue	Decimal		The total value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the _order_line_unit_discount_value from the _order_line_unit_price.
LineLandedCostsValue	Decimal		The value of landed costs for the order.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineDateTimeUpdated	Datetime		The date and time the order line was last updated (UTC).
LineForDirectDelivery	Bool		Indicates if line is marked for direct delivery
LineShowOnSupplierDocs	Bool		Indicates if line will show on supplier documents.
DeliveryAddrPostalName	String		delivery address name.
DeliveryAddrDescription	String		delivery address description.
DeliveryAddrAddress1	String		delivery address line 1.
DeliveryAddrAddress2	String		delivery address line 2.
DeliveryAddrAddress3	String		delivery address line 3.
DeliveryAddrAddress4	String		delivery address line 4.
DeliveryAddrCity	String		delivery address city.
DeliveryAddrCounty	String		delivery address county.
DeliveryAddrPostCode	String		delivery address post code
DeliveryAddrCountry	String		delivery address country.
DeliveryAddrContact	String		delivery address contact
DeliveryAddrTelephone	String		delivery address telephone number
DeliveryAddrFax	String		delivery address line fax number...
DeliveryAddrEmail	String		delivery address line email.
SupplierId	Long		The associated supplier record Id. See suppliers
SupplierReference	String		The associated supplier account reference.
SupplierName	String		The associated supplier name.
ProductId	Long		The associated product record Id. Only returned where _order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where _order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where _order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where _order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

PurchaseOrders

Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrders WHERE Id=42101
SELECT * FROM PurchaseOrders WHERE DocumentNo='0000000001'
SELECT * FROM PurchaseOrders WHERE ExchangeRate='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Purchase order record Id.
DocumentNo	String		Purchase order document number. Note: If the POP setting in Sage 200c Professional, then this property MUST be set.
DocumentDate	Date		Purchase order document date.
DocumentStatus	String		Document status. See pop_document_status_types
AuthorisationStatus	String		Authorisation status. See pop_authorisation_status_types
SupplierId	Long	Suppliers.Id	Supplier record Id. See suppliers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargesNetValue	Decimal		Charges net value.
SubtotalDiscountValue	Decimal		Discount value.
SubtotalLandedCostsValue	Decimal		Landed costs value.
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
SupplierDocumentNo	String		Supplier document number.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent.
DocumentOriginatorId	Long		Document originator id. See users
DocumentOriginatorName	String		Document originator name.
DocumentCreatedById	Long		Document created by id. See users
DocumentCreatedBy	String		Document created by.
RequestedDeliveryDate	Date		Requested delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
Lines	String		The purchase order lines collection. See pop_order_lines
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DeliveryAddressContact	String		The delivery address contact.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PurchasePostedTransactions

Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted 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,AllocationSessionItems,AllocationHistoryItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM PurchasePostedTransactions WHERE Id=33018
SELECT * FROM PurchasePostedTransactions WHERE SupplierId=28936
SELECT * FROM PurchasePostedTransactions WHERE TraderTransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM PurchasePostedTransactions WHERE Reference='O/BAL'
SELECT * FROM PurchasePostedTransactions WHERE SecondReference='Opening Balance'
SELECT * FROM PurchasePostedTransactions WHERE Queried='abc'
SELECT * FROM PurchasePostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM PurchasePostedTransactions WHERE DueDate='2024-01-30T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE DocumentGoodsValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentGrossValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxValue=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE DiscountPercent=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentAllocatedValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentOutstandingValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseGoodsValue=55
SELECT * FROM PurchasePostedTransactions WHERE BaseGrossValue=55
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseAllocatedValue=55
SELECT * FROM PurchasePostedTransactions WHERE ControlValueInBaseCurrency=0
SELECT * FROM PurchasePostedTransactions WHERE ExchangeRate=1
SELECT * FROM PurchasePostedTransactions WHERE SettledImmediately=false
SELECT * FROM PurchasePostedTransactions WHERE VatAdjustmentDocExpected=false
SELECT * FROM PurchasePostedTransactions WHERE DiscountDays=0
SELECT * FROM PurchasePostedTransactions WHERE Urn=11
SELECT * FROM PurchasePostedTransactions WHERE UserName='Migration'
SELECT * FROM PurchasePostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.137+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Id.
SupplierId	Long		Supplier 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.
DocumentGoodsValue	Decimal		Value of the goods.
DocumentGrossValue	Decimal		Gross value.
DocumentTaxValue	Decimal		Tax value.
DocumentDiscountValue	Decimal		Tax 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
VatAdjustmentDocExpected	Bool		Whether a VAT adjustment is expected.
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 purchase_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See purchase_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See purchase_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See purchase_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See purchase_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

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).

SalesOrderAdditionalCharges

Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderAdditionalCharges WHERE Id=42101
SELECT * FROM SalesOrderAdditionalCharges WHERE Code='0000000001'
SELECT * FROM SalesOrderAdditionalCharges WHERE Name='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Sop additional charge record Id.
Code	String		Sop additional charge code.
Name	String		Sop additional charge name.
ChargeValue	Decimal		Sop additional charge value.
NotionalCostValue	Decimal		Sop additional charge notional cost value.
NominalCodeId	Long	NominalCodes.Id	Nominal code record Id. See nominal_codes
TaxCodeId	Long	TaxCodes.Id	Tax code record Id. See tax_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesOrderLines

When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderLines WHERE SalesOrderId=42101
SELECT * FROM SalesOrderLines WHERE CurrencyId=2343234543
SELECT * FROM SalesOrderLines WHERE CurrencyName='Sample'

Columns


Name	Type	References	Description
SalesOrderId [KEY]	Long		Unique Id of the sales order. See sop_orders
LineId [KEY]	Long		Associated sales order line Id. See sop_order_lines
CustomerDocumentNo	String		Customer sales order document number.
CurrencyId	Long		The Id for the currency used for the sales order. See currencies
CurrencyName	String		The name for the currency used for the sales order.
CurrencySymbol	String		The symbol of the currency used for the sales order.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
LineNumber	Short		Sales order line number.
LineType	String		Sales order line type. See order_return_line_types
LineProductCode	String		Sales order line product code.
LineProductDescription	String		Sales order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineAllocatedQuantity	Decimal		The quantity allocated.
LineDespatchReceiptQuantity	Decimal		The quantity despatched.
LineInvoiceCreditQuantity	Decimal		The quantity invoiced.
LineSellingUnitDescription	String		The selling unit description.
LineSellingUnitPrice	Decimal		The selling price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineCostPrice	Decimal		The cost price.
LineTotalValue	Decimal		The total value of the line.
LineLineTaxValue	Decimal		The tax value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_order_line_unit_discount_value from the sop_order_line_selling_unit_price.
LineRequestedDeliveryDate	Date		Requested delivery date.
LinePromisedDeliveryDate	Date		Promised delivery date.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineDateTimeUpdated	Datetime		The date and time the sop order line was last updated (UTC).
DeliveryAddrAddress1	String		SOP delivery address line 1.
DeliveryAddrAddress2	String		SOP delivery address line 2.
DeliveryAddrAddress3	String		SOP delivery address line 3.
DeliveryAddrAddress4	String		SOP delivery address line 4.
DeliveryAddrCity	String		SOP delivery address city.
DeliveryAddrCounty	String		SOP delivery address county.
DeliveryAddrPostCode	String		SOP delivery address post code
DeliveryAddrCountry	String		SOP delivery address country.
DeliveryAddrContact	String		SOP delivery address contact
DeliveryAddrTelephone	String		SOP delivery address telephone number
DeliveryAddrFax	String		SOP delivery address line fax number...
DeliveryAddrEmail	String		SOP delivery address line email.
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrderProformaLines

This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaId=346345654564565
SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaDocumentStatus='Sample'

Columns


Name	Type	References	Description
ProformaId [KEY]	Long		Unique Id of the sales pro forma
ProformaDocumentNo	String		Sales pro forma document number.
ProformaDocumentDate	Date		Sales pro forma date.
ProformaDocumentStatus	String		Sales pro forma status. See sop_document_status_types
ProformaCustomerDocumentNo	String		Customer sales pro forma document number.
ProformaCurrencyId	Long		The Id for the currency used for the sales pro forma. See currencies
ProformaCurrencyName	String		The name for the currency used for the sales pro forma.
ProformaCurrencySymbol	String		The symbol of the currency used for the sales pro forma.
ProformaExchangeRate	Decimal		Exchange rate for the sales pro forma.
ProformaRequestedDeliveryDate	Date		Requested delivery date.
ProformaPromisedDeliveryDate	Date		Promised delivery date.
ProformaAnalysisCode1	String		Analysis code 1.
ProformaAnalysisCode2	String		Analysis code 2.
ProformaAnalysisCode3	String		Analysis code 3.
ProformaAnalysisCode4	String		Analysis code 4.
ProformaAnalysisCode5	String		Analysis code 5.
ProformaDateTimeUpdated	Datetime		The date and time the sales pro forma was last updated (UTC).
ProformaLineId	Long		Associated sales pro forma line Id
ProformaLineNumber	Short		Sales pro forma line number.
ProformaLineType	String		Sales pro forma line type. See order_return_line_types
ProformaLineProductCode	String		Sales pro forma line product code.
ProformaLineProductDescription	String		Sales pro forma line product description. This could be stock item description, free text, additional charge or comment.
ProformaLineQuantity	Decimal		The line quantity.
ProformaLineSellingUnitDescription	String		The selling unit description.
ProformaLineSellingUnitPrice	Decimal		The selling price per item.
ProformaLineUnitDiscountPercent	Decimal		The percentage discount per item.
ProformaLineUnitDiscountValue	Decimal		The discount value per item.
ProformaLineCostPrice	Decimal		The cost price.
ProformaLineTotalValue	Decimal		The total value of the line.
ProformaLineLineTaxValue	Decimal		The tax value of the line.
ProformaLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_proforma_line_unit_discount_value from the sop_proforma_line_selling_unit_price.
ProformaLineRequestedDeliveryDate	Date		Requested delivery date.
ProformaLinePromisedDeliveryDate	Date		Promised delivery date.
ProformaLineAnalysisCode1	String		Analysis code 1.
ProformaLineAnalysisCode2	String		Analysis code 2.
ProformaLineAnalysisCode3	String		Analysis code 3.
ProformaLineAnalysisCode4	String		Analysis code 4.
ProformaLineAnalysisCode5	String		Analysis code 5.
ProformaLineDateTimeUpdated	Datetime		The date and time the sop pro forma line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_proforma_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_proforma_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrderQuoteLines

This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteId=3424324324324
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteDocumentNo='Sample'
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteCustomerDocumentNo='Sample'

Columns


Name	Type	References	Description
QuoteId [KEY]	Long		Unique Id of the sales quote
QuoteDocumentNo	String		Sales quote document number.
QuoteDocumentDate	Date		Sales quote date.
QuoteDocumentStatus	String		Sales quote status. See sop_document_status_types
QuoteCustomerDocumentNo	String		Customer sales quote document number.
QuoteCurrencyId	Long		The Id for the currency used for the sales quote. See currencies
QuoteCurrencyName	String		The name for the currency used for the sales quote.
QuoteCurrencySymbol	String		The symbol of the currency used for the sales quote.
QuoteExchangeRate	Decimal		Exchange rate for the sales quote.
QuoteRequestedDeliveryDate	Date		Requested delivery date.
QuotePromisedDeliveryDate	Date		Promised delivery date.
QuoteAnalysisCode1	String		Analysis code 1.
QuoteAnalysisCode2	String		Analysis code 2.
QuoteAnalysisCode3	String		Analysis code 3.
QuoteAnalysisCode4	String		Analysis code 4.
QuoteAnalysisCode5	String		Analysis code 5.
QuoteDateTimeUpdated	Datetime		The date and time the sales quote was last updated (UTC).
QuoteLineId	Long		Associated sales quote line Id
QuoteLineNumber	Short		Sales quote line number.
QuoteLineType	String		Sales quote line type. See order_return_line_types
QuoteLineProductCode	String		Sales quote line product code.
QuoteLineProductDescription	String		Sales quote line product description. This could be stock item description, free text, additional charge or comment.
QuoteLineQuantity	Decimal		The line quantity.
QuoteLineSellingUnitDescription	String		The selling unit description.
QuoteLineSellingUnitPrice	Decimal		The selling price per item.
QuoteLineUnitDiscountPercent	Decimal		The percentage discount per item.
QuoteLineUnitDiscountValue	Decimal		The discount value per item.
QuoteLineCostPrice	Decimal		The cost price.
QuoteLineTotalValue	Decimal		The total value of the line.
QuoteLineLineTaxValue	Decimal		The tax value of the line.
QuoteLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_quote_line_unit_discount_value from the sop_quote_line_selling_unit_price.
QuoteLineRequestedDeliveryDate	Date		Requested delivery date.
QuoteLinePromisedDeliveryDate	Date		Promised delivery date.
QuoteLineAnalysisCode1	String		Analysis code 1.
QuoteLineAnalysisCode2	String		Analysis code 2.
QuoteLineAnalysisCode3	String		Analysis code 3.
QuoteLineAnalysisCode4	String		Analysis code 4.
QuoteLineAnalysisCode5	String		Analysis code 5.
QuoteLineDateTimeUpdated	Datetime		The date and time the sop quote line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_quote_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_quote_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_quote_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_quote_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrders

Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrders WHERE Id=42101
SELECT * FROM SalesOrders WHERE DocumentNo='0000000001'
SELECT * FROM SalesOrders WHERE CustomerDocumentNo='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Sales order record Id.
DocumentNo	String		Sales order document number. Note: If the SOP setting in Sage 200c Professional or Sage 200 Extra Online is to NOT automatically generate numbers, then this property MUST be set. If the SOP setting in Sage 200 Extra is to automatically generate numbers, or you are using Sage 200c Standard (which doesn't allow you to set this option), then setting this on POST will be ignored.
DocumentDate	Date		Sales order document date.
DocumentStatus	String		Document status. See sop_document_status_types
CustomerId	Long	Customers.Id	Customer record Id. See customers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargeNetValue	Decimal		Charge net value.
SubtotalChargeTaxValue	Decimal		Charge tax value.
SubtotalDiscountValue	Decimal		Discount value
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
CustomerDocumentNo	String		Customer document number.
UseInvoiceAddress	Bool		True if this order uses the customer invoice address, else False.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
Lines	String		The sales order lines collection. See sop_order_lines
DeliveryAddressId	Long		The delivery address contact.
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesPostedTransactions

Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.

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,AllocationSessionItems,AllocationHistoryItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM SalesPostedTransactions WHERE Id=29674
SELECT * FROM SalesPostedTransactions WHERE CustomerId=27849
SELECT * FROM SalesPostedTransactions WHERE TraderTransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM SalesPostedTransactions WHERE Reference='O/BAL'
SELECT * FROM SalesPostedTransactions WHERE SecondReference='Opening Balance'
SELECT * FROM SalesPostedTransactions WHERE Queried='abc'
SELECT * FROM SalesPostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DueDate='2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE FullSettlementDate='2019-04-15T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DocumentGoodsValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentGrossValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxValue=0
SELECT * FROM SalesPostedTransactions WHERE DocumentDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE DiscountPercent=0
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE DocumentAllocatedValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentOutstandingValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseGoodsValue=150
SELECT * FROM SalesPostedTransactions WHERE BaseGrossValue=150
SELECT * FROM SalesPostedTransactions WHERE BaseTaxValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseTaxDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseAllocatedValue=150
SELECT * FROM SalesPostedTransactions WHERE ControlValueInBaseCurrency=0
SELECT * FROM SalesPostedTransactions WHERE ExchangeRate=1
SELECT * FROM SalesPostedTransactions WHERE SettledImmediately=false
SELECT * FROM SalesPostedTransactions WHERE DiscountDays=0
SELECT * FROM SalesPostedTransactions WHERE Urn=1
SELECT * FROM SalesPostedTransactions WHERE UserName='Migration'
SELECT * FROM SalesPostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.330+01:00'

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_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See sales_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See sales_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See sales_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See sales_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesTradingPeriods

This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesTradingPeriods WHERE Id=1
SELECT * FROM SalesTradingPeriods WHERE PeriodNumber='1'
SELECT * FROM SalesTradingPeriods WHERE StartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE EndDate='2018-01-31T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE DateTimeUpdated='2018-02-28T15:23:45.667+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		An unique Id specific to each sales trading period.
PeriodNumber	String		The period number.
StartDate	Date		Date the trading period starts. The returned string includes the time but this will always be 00:00:00.
EndDate	Date		Date the trading period ends. The returned string includes the time but this will always be 00:00:00.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesTransactions

When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesTransactionViews WHERE PostedCustomerTransactionId=29674
SELECT * FROM SalesTransactionViews WHERE TransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM SalesTransactionViews WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesTransactionViews WHERE DueDate='2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE DiscountDays=0
SELECT * FROM SalesTransactionViews WHERE Reference='O/BAL'
SELECT * FROM SalesTransactionViews WHERE SecondReference='Opening Balance'
SELECT * FROM SalesTransactionViews WHERE Query=' '
SELECT * FROM SalesTransactionViews WHERE Status='Full'
SELECT * FROM SalesTransactionViews WHERE UserName='Migration'
SELECT * FROM SalesTransactionViews WHERE LedgerType='LedgerSales'
SELECT * FROM SalesTransactionViews WHERE Urn=1
SELECT * FROM SalesTransactionViews WHERE ExchangeRate=1
SELECT * FROM SalesTransactionViews WHERE SettledImmediately=false
SELECT * FROM SalesTransactionViews WHERE BaseGoodsValue=150
SELECT * FROM SalesTransactionViews WHERE BaseGrossValue=150
SELECT * FROM SalesTransactionViews WHERE BaseDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE BaseTaxValue=0
SELECT * FROM SalesTransactionViews WHERE BaseTaxDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE BaseAllocatedValue=150
SELECT * FROM SalesTransactionViews WHERE BaseOutstandingValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentGoodsValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentGrossValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentAllocatedValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentOutstandingValue=0
SELECT * FROM SalesTransactionViews WHERE CustomerId=27849
SELECT * FROM SalesTransactionViews WHERE CustomerReference='COM001'
SELECT * FROM SalesTransactionViews WHERE CustomerName='Compton Packaging'
SELECT * FROM SalesTransactionViews WHERE CurrencyName='Pound Sterling'
SELECT * FROM SalesTransactionViews WHERE CurrencySymbol='ﾂ｣'

Columns


Name	Type	References	Description
PostedCustomerTransactionId [KEY]	Long		Id of the transaction.
TransactionType	String		Transaction type. See trader_transaction_types
TransactionDate	Date		The date of the transaction.
PostedDate	Date		The date the transaction was posted.
DueDate	Date		The due date of the transaction.
DiscountDays	Short		Days discount valid.
Reference	String		The reference of the transaction.
SecondReference	String		The second reference of the transaction.
Query	String		Query.
Status	String		Status.
UserName	String		User that posted the transaction.
LedgerType	String		Ledger type. See ledger_types
Urn	Long		Unique reference number.
ExchangeRate	Decimal		Exchange Rate.
SettledImmediately	Bool		Immediately settled.
BaseGoodsValue	Decimal		Goods value in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
BaseOutstandingValue	Decimal		Outstanding value in base currency.
DocumentGoodsValue	Decimal		Goods value.
DocumentGrossValue	Decimal		Gross value.
DocumentDiscountValue	Decimal		Discount value.
DocumentTaxValue	Decimal		Tax value.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
CustomerId	Long		Customer Id.
CustomerReference	String		Customer account code.
CustomerName	String		Customer account name.
CurrencyName	String		Currency.
CurrencySymbol	String		Currency symbol.

StockSettings

These settings to define how to process and manage your stock.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM StockSettings WHERE Id=1
SELECT * FROM StockSettings WHERE UseMultipleLocations=true
SELECT * FROM StockSettings WHERE ProfitPercentOfRevenue=true
SELECT * FROM StockSettings WHERE DateTimeUpdated='2018-02-28T15:23:45.543+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Stock setting unique Id.
UseMultipleLocations	Bool		When true this setting allows Sage 200 to track stock at different locations. This can be across several warehouses, or a single warehouse split into separate areas (bins). (Sage 200c Standard value is true and cannot be changed).
ProfitPercentOfRevenue	Bool		Profit can be calculated as a percentage of revenue (true), or as a percentage of cost (false).
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SupplierContacts

Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SupplierContacts WHERE Id=42101
SELECT * FROM SupplierContacts WHERE SupplierId=325434364565
SELECT * FROM SupplierContacts WHERE FirstName='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Supplier contact record Id.
SupplierId	Long		Supplier record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent Supplier. See Supplier
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Supplier emails. See Supplier_emails
Telephones	String		Supplier telephones. See Supplier_telephones
Mobiles	String		Supplier mobiles. See Supplier_mobiles
Faxes	String		Supplier faxes. See Supplier_faxes
Websites	String		Supplier websites. See Supplier_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Suppliers

Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,BankId,BankAccountNumber,BankAccountName,BankIBAN,BankBIC,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Suppliers WHERE Id=28846
SELECT * FROM Suppliers WHERE Reference='CON001'
SELECT * FROM Suppliers WHERE Name='Concept Stationery Supplies'
SELECT * FROM Suppliers WHERE ShortName='Concept'
SELECT * FROM Suppliers WHERE Balance=4346.16
SELECT * FROM Suppliers WHERE OnHold=false
SELECT * FROM Suppliers WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Suppliers WHERE CurrencyId=2103
SELECT * FROM Suppliers WHERE ExchangeRateType='ExchangeRateSingle'
SELECT * FROM Suppliers WHERE TelephoneCountryCode='44'
SELECT * FROM Suppliers WHERE TelephoneAreaCode='0191'
SELECT * FROM Suppliers WHERE TelephoneSubscriberNumber='643 4343'
SELECT * FROM Suppliers WHERE FaxCountryCode='44'
SELECT * FROM Suppliers WHERE FaxAreaCode='0191'
SELECT * FROM Suppliers WHERE FaxSubscriberNumber='643 4345'
SELECT * FROM Suppliers WHERE Website='www.sage.co.uk'
SELECT * FROM Suppliers WHERE CreditLimit=17000
SELECT * FROM Suppliers WHERE CountryCodeId=13
SELECT * FROM Suppliers WHERE DefaultTaxCodeId=1729
SELECT * FROM Suppliers WHERE VatNumber='GB988 3453 23'
SELECT * FROM Suppliers WHERE AnalysisCode1='North East'
SELECT * FROM Suppliers WHERE AnalysisCode2='Stationery'
SELECT * FROM Suppliers WHERE AnalysisCode3='abc'
SELECT * FROM Suppliers WHERE AnalysisCode4='abc'
SELECT * FROM Suppliers WHERE AnalysisCode5='abc'
SELECT * FROM Suppliers WHERE DateTimeUpdated='2018-02-28T15:23:46.253+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Supplier unique Id.
Reference	String		Supplier account reference. NOTE: Not required if supplier reference is set to
Name	String		Supplier name.
ShortName	String		Supplier short name.
Balance	Decimal		Supplier account balance.
OnHold	Bool		True if supplier account is on hold, else False.
AccountStatusType	String		The status of the supplier account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the supplier account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the supplier.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
BankId	String		Supplier bank account id.
BankAccountNumber	String		Supplier bank account number.
BankAccountName	String		Supplier bank account name.
BankIBAN	String		Supplier bank account iban number.
BankBIC	String		Supplier bank account BIC number.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Supplier contacts. See supplier_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

TaxCodes

Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM TaxCodes WHERE Id=1728
SELECT * FROM TaxCodes WHERE Code=0
SELECT * FROM TaxCodes WHERE Name='Zero rated'
SELECT * FROM TaxCodes WHERE TaxRate=0
SELECT * FROM TaxCodes WHERE Terms='EcTermsNotApplicable'
SELECT * FROM TaxCodes WHERE TermsDescription='Not Applicable'
SELECT * FROM TaxCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.133+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Tax code record Id.
Code	Short		Tax code.
Name	String		Name of the tax code.
TaxRate	Decimal		Tax rate.
Terms	String		Name of the terms.
TermsDescription	String		Description of the terms.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

WarehouseHoldings

All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM WarehouseHoldings WHERE Id=34738
SELECT * FROM WarehouseHoldings WHERE WarehouseId=34629
SELECT * FROM WarehouseHoldings WHERE ProductId=34737
SELECT * FROM WarehouseHoldings WHERE ReorderLevel=0
SELECT * FROM WarehouseHoldings WHERE MinimumLevel=0
SELECT * FROM WarehouseHoldings WHERE MaximumLevel=0
SELECT * FROM WarehouseHoldings WHERE ConfirmedQtyInStock=0
SELECT * FROM WarehouseHoldings WHERE UnconfirmedQtyInStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityInStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedSop=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedBom=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocated=0
SELECT * FROM WarehouseHoldings WHERE DateTimeUpdated='2019-08-01T18:00:33.140+02:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Warehouse holding record Id.
WarehouseId	Long	Warehouses.Id	Warehouse record Id. See warehouses
ProductId	Long	Products.Id	Product record Id. See products
ReorderLevel	Decimal		Reorder stock level.
MinimumLevel	Decimal		Minimum stock quantity level.
MaximumLevel	Decimal		Maximum stock quantity level.
ConfirmedQtyInStock	Decimal		Confirmed stock quantity.
UnconfirmedQtyInStock	Decimal		Unconfirmed stock quantity.
QuantityInStock	Decimal		Total confirmed and unconfirmed stock quantity.
QuantityAllocatedStock	Decimal		Allocated stock quantity.
QuantityAllocatedSop	Decimal		Allocated sales order quantity.
QuantityAllocatedBom	Decimal		Allocated bill of materials quantity.
QuantityAllocated	Decimal		Total Allocated stock, sop and bom quantity.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Warehouses

All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM Warehouses WHERE Id=34626
SELECT * FROM Warehouses WHERE Name='Supplied Direct'
SELECT * FROM Warehouses WHERE DateTimeUpdated='2018-02-28T15:23:46.190+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Warehouse record Id.
Name	String		Warehouse name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Sage200 ProfessionalUK データモデル

Views

ビュー describes the available views. Views are statically defined to model Banks, Customers, Products, and more.

Stored Procedures

ストアドプロシージャ are function-like interfaces to Sage 200. Stored procedures allow you to execute operations to Sage 200, such as retrieving the OAuth Access Token.

ビュー

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

Sage 200 Connector for CData Sync ビュー


Name	Description
BankPostedTransactions	Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.
Banks	Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.
CompanyDetails	Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.
CostCentres	Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.
Currencies	Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering transactions.
CustomerContacts	Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
CustomerDeliveryAddresses	A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.
Customers	Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.
Departments	Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.
FinancialReportLayouts	Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.
FinancialYearPeriods	This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.
NominalCodes	Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.
NominalReportCategories	Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).
NominalTransactionAnalysisCodes	Nominal transaction analysis codes are used to group nominal analysis items on various transaction types
PendingEmails	When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.
PriceBands	A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.
ProductGroups	Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.
ProductPrices	This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.
Products	Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.
ProductTransactions	A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.
PurchaseOrderLines	When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.
PurchaseOrders	Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.
PurchasePostedTransactions	Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.
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.
SalesOrderAdditionalCharges	Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.
SalesOrderLines	When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.
SalesOrderProformaLines	This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.
SalesOrderQuoteLines	This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.
SalesOrders	Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.
SalesPostedTransactions	Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.
SalesTradingPeriods	This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)
SalesTransactions	When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.
StockSettings	These settings to define how to process and manage your stock.
SupplierContacts	Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.
Suppliers	Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).
TaxCodes	Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.
WarehouseHoldings	All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.
Warehouses	All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

BankPostedTransactions

Bank posted transactions are created when transactions such as receipts or payments are posted against the Cash Book.

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,TradeItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM BankPostedTransactions WHERE Id=29448
SELECT * FROM BankPostedTransactions WHERE BankId=29401
SELECT * FROM BankPostedTransactions WHERE BankTransactionType='Payment'
SELECT * FROM BankPostedTransactions WHERE EntryDescription='Opening Balance'
SELECT * FROM BankPostedTransactions WHERE Reference='O/Bal'
SELECT * FROM BankPostedTransactions WHERE SecondReference='abc'
SELECT * FROM BankPostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM BankPostedTransactions WHERE PostedDate='2017-09-13T02:00:00.000+02:00'
SELECT * FROM BankPostedTransactions WHERE ChequeValue=-6511.96
SELECT * FROM BankPostedTransactions WHERE ChequeDiscountValue=0
SELECT * FROM BankPostedTransactions WHERE ChequeToAccountCurrencyRate=1
SELECT * FROM BankPostedTransactions WHERE BankChequeToBaseCurrencyRate=1
SELECT * FROM BankPostedTransactions WHERE ChequeCurrencyId=2103
SELECT * FROM BankPostedTransactions WHERE Urn=24
SELECT * FROM BankPostedTransactions WHERE UserName='Migration'
SELECT * FROM BankPostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.467+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Id.
BankId	Long	Banks.Id	Bank Id. See banks
BankTransactionType	String		Transaction type. See bank_transaction_types
EntryDescription	String		Transaction description.
Reference	String		Transaction reference.
SecondReference	String		Second reference.
TransactionDate	Date		Transaction date.
PostedDate	Date		Posted date.
ChequeValue	Decimal		Value of the entry.
ChequeDiscountValue	Decimal		Entry discount value.
ChequeToAccountCurrencyRate	Decimal		Exchange rate between the bank account currency and the cheque currency.
BankChequeToBaseCurrencyRate	Decimal		Exchange rate between the bank account and the company base currency.
ChequeCurrencyId	Long		Currency Id. See currencies
Urn	Long		Unique reference number.
UserName	String		User who entered the transaction.
NominalAnalysisItems	String		Nominal analysis items. See bank_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See bank_posted_transactions_tax_analysis_items
TradeItems	String		Trade items. See bank_posted_transactions_trade_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Banks

Bank accounts can be created in Sage 200 for any type of account that money is paid in/out of, for example, cash, credit cards or, business current account. Bank accounts can be in any currency and all balances are recorded in the currency specific to that bank account.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: BankAddressId,BankAddressContact,BankAddressCountry,BankAddressCity,BankAddressPostcode,BankAddress_1,BankAddress_2,BankAddress_3,BankAddress_4,BankContacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Banks WHERE Id=29401
SELECT * FROM Banks WHERE Code='1200'
SELECT * FROM Banks WHERE Description='Bank Current Account'
SELECT * FROM Banks WHERE SortCode='233434'
SELECT * FROM Banks WHERE AccountNo='003234234'
SELECT * FROM Banks WHERE BankName='Lloyds Bank PLC'
SELECT * FROM Banks WHERE AccountType='BankAccountTypeCurrent'
SELECT * FROM Banks WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Banks WHERE Balance=-9941.57
SELECT * FROM Banks WHERE BalanceInBase=-9941.57
SELECT * FROM Banks WHERE OverdraftLimit=0
SELECT * FROM Banks WHERE AccountName='Stationery & Computer Mart UK Ltd'
SELECT * FROM Banks WHERE Iban='abc'
SELECT * FROM Banks WHERE Bic='abc'
SELECT * FROM Banks WHERE StatementBalance=0
SELECT * FROM Banks WHERE StatementDate='2019-10-02T11:46:53Z'
SELECT * FROM Banks WHERE AccountCurrencyId=2103
SELECT * FROM Banks WHERE DateTimeUpdated='2018-02-28T15:23:46.070+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Bank account record Id.
Code	String		Bank account code.
Description	String		Bank description.
SortCode	String		Bank sort code.
AccountNo	String		Bank account number.
BankName	String		Bank name.
AccountType	String		Bank account type. See bank_account_types
AccountStatusType	String		The status of the bank account (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
Balance	Decimal		Bank balance.
BalanceInBase	Decimal		Bank balance in base currency.
OverdraftLimit	Decimal		Bank overdraft limit.
AccountName	String		Account name.
Iban	String		International bank account number.
Bic	String		Bank identifier code.
StatementBalance	Decimal		Last statement balance.
StatementDate	Date		Last statement date.
AccountCurrencyId	Long	Currencies.Id	Currency record Id. See currencies
BankAddressId	Long		Bank address. Bank address record id.
BankAddressContact	String		Bank address. Address Contact name.
BankAddressCountry	String		Bank address. Country.
BankAddressCity	String		Bank address. City.
BankAddressPostcode	String		Bank address. Postcode.
BankAddress_1	String		Bank address. Address line 1.
BankAddress_2	String		Bank address. Address line 2.
BankAddress_3	String		Bank address. Address line 3.
BankAddress_4	String		Bank address. Address line 4.
BankContacts	String		Collection of bank contact values.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CompanyDetails

Every instance of Sage 200 is configured for the company using it. A company is identified in Sage 200 by its name. Sage 200 stores separate company details for each company that is created. The information stored within company details is typical company information such as VAT registration number and company contact details.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEUMember,AddressCountry,Address_1,Address_2,Address_3,Address_4,AddressCity,AddressCounty,AddressPostcode columns.

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

For example, the following queries are processed server side:

SELECT * FROM CompanyDetails WHERE Name='Stationery & Computer Mart UK'
SELECT * FROM CompanyDetails WHERE Telephone='0191 955 3000'
SELECT * FROM CompanyDetails WHERE Fax='0191 955 3001'
SELECT * FROM CompanyDetails WHERE Email='[email protected]'
SELECT * FROM CompanyDetails WHERE Website='http://www.stationerycomputermartuk.co.uk'
SELECT * FROM CompanyDetails WHERE TaxRegistrationNumber='376 6823 94'
SELECT * FROM CompanyDetails WHERE CountryCodeId=13
SELECT * FROM CompanyDetails WHERE YearsAccountsHeldFor=2
SELECT * FROM CompanyDetails WHERE EoriNumber='abc'
SELECT * FROM CompanyDetails WHERE DateTimeUpdated='2018-02-28T15:23:46.040+01:00'

Columns


Name	Type	References	Description
Name [KEY]	String		Company name.
Telephone	String		Telephone number.
Fax	String		Fax number.
Email	String		Email address.
Website	String		Company website address.
TaxRegistrationNumber	String		Tax registration number.
CountryCodeId	Long		Country code record Id. See country_codes
CountryCode	String		Country code record Id. See country_codes
CountryCodeName	String		Country code record Id. See country_codes
CountryCodeEUMember	Boolean		Country code record Id. See country_codes
YearsAccountsHeldFor	Int		Number of years that periods with nominal data exist for up to a maximum of six years.
AddressCountry	String		The company's address Id.
Address_1	String		The company's address. Address line 1.
Address_2	String		The company's address. Address line 2.
Address_3	String		The company's address. Address line 3.
Address_4	String		The company's address. Address line 4.
AddressCity	String		The company's address. City
AddressCounty	String		The company's address. County
AddressPostcode	String		The company's address. Postcode
EoriNumber	String		The company's EORI number.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CostCentres

Cost centres are typically used to aid collating related costs and reporting on a specific segment of an organisation i.e. a company division such as sales or production.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CostCentres WHERE Id=1015
SELECT * FROM CostCentres WHERE Code='abc'
SELECT * FROM CostCentres WHERE Name='abc'
SELECT * FROM CostCentres WHERE DateTimeUpdated='2018-02-28T15:23:46.230+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Cost centre record Id.
Code	String		Cost centre code.
Name	String		Cost centre name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Currencies

Sage 200 works in multiple currencies and each company can configure up to 99 currencies to use, and all can be configured to use a single exchange rate, period exchange rates, or both. One currency must be configured as the base currency and this cannot be changed once transactions have been entered. If a currency is configured as amendable, the exchange rate can be modified when entering 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: CurrencyIsoCode,CurrencyIsoCodeName columns.

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

For example, the following queries are processed server side:

SELECT * FROM Currencies WHERE Id=2103
SELECT * FROM Currencies WHERE Symbol='ﾂ｣'
SELECT * FROM Currencies WHERE Name='Pound Sterling'
SELECT * FROM Currencies WHERE CoreCurrencyRate=1
SELECT * FROM Currencies WHERE EuroCurrencyRate=0.714285
SELECT * FROM Currencies WHERE CurrencyIsoCodeId=49
SELECT * FROM Currencies WHERE IsBaseCurrency=true
SELECT * FROM Currencies WHERE IsEuroCurrency=false
SELECT * FROM Currencies WHERE DateTimeUpdated='2018-02-28T15:23:46.243+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Currency record Id.
Symbol	String		Currency symbol.
Name	String		Currency name.
CoreCurrencyRate	Decimal		Currency rate in use against base.
EuroCurrencyRate	Decimal		Euro rate.
CurrencyIsoCodeId	Long		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCode	String		ISO Currency Code Record Id. See currency_iso_codes
CurrencyIsoCodeName	String		ISO Currency Code Record Id. See currency_iso_codes
IsBaseCurrency	Bool		Flag to indicate if the currency is the base currency.
IsEuroCurrency	Bool		Flag to indicate if the currency is Euro.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CustomerContacts

Information can be stored for multiple contacts against each customer account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CustomerContacts WHERE Id=42101
SELECT * FROM CustomerContacts WHERE CustomerId='0000000001'
SELECT * FROM CustomerContacts WHERE Name='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Customer contact record Id.
CustomerId	Long		Customer record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent customer. See customer
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Customer emails. See customer_emails
Telephones	String		Customer telephones. See customer_telephones
Mobiles	String		Customer mobiles. See customer_mobiles
Faxes	String		Customer faxes. See customer_faxes
Websites	String		Customer websites. See customer_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

CustomerDeliveryAddresses

A customer can have multiple delivery addresses stored for their account which can be used when creating an invoice or sales order.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM CustomerDeliveryAddresses WHERE Id=27927
SELECT * FROM CustomerDeliveryAddresses WHERE Contact='Lee Dalkin'
SELECT * FROM CustomerDeliveryAddresses WHERE Country='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE CustomerId=27825
SELECT * FROM CustomerDeliveryAddresses WHERE Description='Registered address'
SELECT * FROM CustomerDeliveryAddresses WHERE Email='[email protected]'
SELECT * FROM CustomerDeliveryAddresses WHERE Fax='01742 876 236'
SELECT * FROM CustomerDeliveryAddresses WHERE IsDefault=false
SELECT * FROM CustomerDeliveryAddresses WHERE PostalName='A1 Design Services'
SELECT * FROM CustomerDeliveryAddresses WHERE TaxCodeId=1729
SELECT * FROM CustomerDeliveryAddresses WHERE TaxNumber='GB238 3839 38'
SELECT * FROM CustomerDeliveryAddresses WHERE Telephone='01742 876 234'
SELECT * FROM CustomerDeliveryAddresses WHERE Address1='67a Station Road'
SELECT * FROM CustomerDeliveryAddresses WHERE Address2='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Address3='Blackpool'
SELECT * FROM CustomerDeliveryAddresses WHERE Address4='Lancashire'
SELECT * FROM CustomerDeliveryAddresses WHERE City='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE County='abc'
SELECT * FROM CustomerDeliveryAddresses WHERE Postcode='BP12 7HT'
SELECT * FROM CustomerDeliveryAddresses WHERE AddressCountryCodeId=0
SELECT * FROM CustomerDeliveryAddresses WHERE DateTimeUpdated='2018-02-28T15:23:46.867+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		The unique id of the customer delivery address.
Contact	String		The contact associated with the customer delivery address.
Country	String		Country (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
CustomerId	Long		Unique Id of the customer account the customer delivery address is associated with. See customers
Description	String		The description of the customer delivery address.
Email	String		The email address associated with the customer delivery address.
Fax	String		The fax number associated with the customer delivery address.
IsDefault	Bool		Flag to indicate if this is the default customer delivery address for the parent customer. See customer
PostalName	String		Postal name is the name of the person or company who the invoice or sales order is addressed to.
TaxCodeId	Long		The tax code record Id. See tax_codes
TaxNumber	String		The tax number.
Telephone	String		The telephone number associated with the customer delivery address.
Address1	String		Address line 1.
Address2	String		Address line 2.
Address3	String		Address line 3.
Address4	String		Address line 4.
City	String		City (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
County	String		County (if using segmented addresses in Sage 200c Professional or Sage 200 Extra Online).
Postcode	String		Postcode.
AddressCountryCodeId	Long		Country code Id. See country_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Customers

Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Customers WHERE Id=27825
SELECT * FROM Customers WHERE Reference='A1D001'
SELECT * FROM Customers WHERE Name='A1 Design Services'
SELECT * FROM Customers WHERE ShortName='A1 Desig'
SELECT * FROM Customers WHERE Balance=1120.92
SELECT * FROM Customers WHERE OnHold=false
SELECT * FROM Customers WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Customers WHERE CurrencyId=2103
SELECT * FROM Customers WHERE ExchangeRateType='ExchangeRateSingle'
SELECT * FROM Customers WHERE TelephoneCountryCode='44'
SELECT * FROM Customers WHERE TelephoneAreaCode='01742'
SELECT * FROM Customers WHERE TelephoneSubscriberNumber='876234'
SELECT * FROM Customers WHERE FaxCountryCode='44'
SELECT * FROM Customers WHERE FaxAreaCode='01742'
SELECT * FROM Customers WHERE FaxSubscriberNumber='876236'
SELECT * FROM Customers WHERE Website='www.sage.co.uk'
SELECT * FROM Customers WHERE CreditLimit=0
SELECT * FROM Customers WHERE DefaultTaxCodeId=1729
SELECT * FROM Customers WHERE VatNumber='GB238 3839 38'
SELECT * FROM Customers WHERE DunsCode='abc'
SELECT * FROM Customers WHERE AnalysisCode1='Trade'
SELECT * FROM Customers WHERE AnalysisCode2='George'
SELECT * FROM Customers WHERE AnalysisCode3='Lancashire'
SELECT * FROM Customers WHERE AnalysisCode4='abc'
SELECT * FROM Customers WHERE AnalysisCode5='abc'
SELECT * FROM Customers WHERE AverageTimeToPay=0
SELECT * FROM Customers WHERE ValueOfCurrentOrdersInSop=340.38
SELECT * FROM Customers WHERE CountryCodeId=13
SELECT * FROM Customers WHERE DateTimeUpdated='2018-03-05T11:19:18.317+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Customer unique Id.
Reference	String		Customer account reference. NOTE: Not required if customer reference is set to
Name	String		Customer name.
ShortName	String		Customer short name.
Balance	Decimal		Customer account balance.
OnHold	Bool		True if customer account is on hold, else False.
AccountStatusType	String		The status of the customer account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the customer account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the customer.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
DunsCode	String		DUNS number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
AverageTimeToPay	Int		The customer average time to pay.
ValueOfCurrentOrdersInSop	Decimal		Value of current sales orders for this customer.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Customer contacts. See customer_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Departments

Departments are a subdivision of cost centres and are used where specific costs or revenue need to be collated and reported on separately. For example, a cost centre could be a sales area such as North East or South West and different departments could be Administration and Training.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM Departments WHERE Id=1016
SELECT * FROM Departments WHERE Code='abc'
SELECT * FROM Departments WHERE Name='abc'
SELECT * FROM Departments WHERE DateTimeUpdated='2018-02-28T15:23:46.277+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Department record Id.
Code	String		Department code.
Name	String		Department name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

FinancialReportLayouts

Financial statement layouts are designed to provide a clear representation of your accounts. This resource returns the details of all financial statement layouts. The default sort (orderby) field are by 'financial_report_layout_id' and 'financial_report_row_report_row' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportLayoutId = 46151
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportLayoutAccountReportType = 'ProfitLoss'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportLayoutName = 'Sample Profit and Loss Layout'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowId = 47221
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLineType = 'Text'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLayoutPositionType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowLayoutDebitOrCreditType = 'Unknown'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowReportRow = 1
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowTitle = 'Sales'
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowSubtotalGroup = 1
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak1 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak2 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak3 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak4 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak5 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak6 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak7 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak8 = 0
SELECT * FROM FinancialReportLayoutViews WHERE FinancialReportRowGroupBreak9 = 0
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryId = 123
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryCode = 'abc'
SELECT * FROM FinancialReportLayoutViews WHERE NominalAccountReportCategoryDescription = 'abc'

Columns


Name	Type	References	Description
FinancialReportLayoutId [KEY]	Long		Financial report layout record Id.
FinancialReportLayoutAccountReportType	String		The type of the financial layout. See financial_report_layout_types
FinancialReportLayoutName	String		financial report layout name.
FinancialReportRowId	Long		An asscoiated financial report row Id.
FinancialReportRowLineType	String		The type of the financial layout. See financial_report_row_line_types
FinancialReportRowLayoutPositionType	String		The type of the financial report row layout position. See financial_report_row_layout_position_types
FinancialReportRowLayoutDebitOrCreditType	String		The type of the financial layout. See financial_report_row_layout_debit_or_credit_types
FinancialReportRowReportRow	Int		Value to show the line number of the report row in the layout.
FinancialReportRowTitle	String		The row title.
FinancialReportRowSubtotalGroup	Int		Value to represent the sub total group.
FinancialReportRowGroupBreak1	Int		Value to represent the report row group break 1.
FinancialReportRowGroupBreak2	Int		Value to represent the report row group break 2.
FinancialReportRowGroupBreak3	Int		Value to represent the report row group break 3.
FinancialReportRowGroupBreak4	Int		Value to represent the report row group break 4.
FinancialReportRowGroupBreak5	Int		Value to represent the report row group break 5.
FinancialReportRowGroupBreak6	Int		Value to represent the report row group break 6.
FinancialReportRowGroupBreak7	Int		Value to represent the report row group break 7.
FinancialReportRowGroupBreak8	Int		Value to represent the report row group break 8.
FinancialReportRowGroupBreak9	Int		Value to represent the report row group break 9.
NominalAccountReportCategoryId	Long		An Id specific to each nominal account report category. See nominal_report_category_types
NominalAccountReportCategoryCode	String		The nominal account report category code.
NominalAccountReportCategoryDescription	String		The nominal account report category description.

FinancialYearPeriods

This resource returns the details of all accounting periods for all financial years held for the company. A company always has five future financial years and can have unlimited previous years, depending on how long the company has been operating. The default sort (orderby) field are by 'financial_year_start_date' and 'period_number' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearId=1
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearStartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE FinancialYearEndDate='2018-12-31T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE YearRelativeToCurrentYear=0
SELECT * FROM FinancialYearPeriodViews WHERE NumberOfPeriodsInYear=12
SELECT * FROM FinancialYearPeriodViews WHERE AccountingPeriodId=1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodNumber=1
SELECT * FROM FinancialYearPeriodViews WHERE PeriodStartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM FinancialYearPeriodViews WHERE PeriodEndDate='2018-01-31T01:00:00.000+01:00'

Columns


Name	Type	References	Description
FinancialYearId [KEY]	Long		An Id specific to each financial year.
FinancialYearStartDate	Date		Date the financial year starts. The returned string includes the time but this will always be 00:00:00.
FinancialYearEndDate	Date		Date the financial year ends. The returned string includes the time but this will always be 00:00:00.
YearRelativeToCurrentYear	Int		Value to represent the year relative to the current financial year. The current financial year is 0. The first previous year is -1. The first future year is 1.
NumberOfPeriodsInYear	Int		The number of periods in the financial year.
AccountingPeriodId	Long		An Id specific to each accounting period.
PeriodNumber	Int		Value to show the period in relation to the associated year. For example, for a year that starts in January, January will be period 1, February period 2 etc.
PeriodStartDate	Date		Date the period starts. The returned string includes the time but this will always be 00:00:00.
PeriodEndDate	Date		Date the period finished. The returned string includes the time but this will always be 00:00:00.

NominalCodes

Nominal accounts are fundamental to the successful management of company finances. The value of every transaction entered into Sage 200 is posted to a nominal account and, is where the revenue and expenditure of the business is recorded. Nominal accounts include a code, and cost centre and a department.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalCodes WHERE Id=2317
SELECT * FROM NominalCodes WHERE Name='Freehold Property'
SELECT * FROM NominalCodes WHERE Reference='0010'
SELECT * FROM NominalCodes WHERE CostCentreId=1015
SELECT * FROM NominalCodes WHERE CostCentreCode='abc'
SELECT * FROM NominalCodes WHERE DepartmentId=1016
SELECT * FROM NominalCodes WHERE DepartmentCode='abc'
SELECT * FROM NominalCodes WHERE NominalAccountType='NominalAccountTypePosting'
SELECT * FROM NominalCodes WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM NominalCodes WHERE ReportCategoryId=2289
SELECT * FROM NominalCodes WHERE DisplayBalancesInSelectionList=false
SELECT * FROM NominalCodes WHERE BalanceYearToDate=123.3
SELECT * FROM NominalCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.717+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Nominal code record Id.
Name	String		Nominal code name.
Reference	String		Nominal code reference.
CostCentreId	Long	CostCentres.Id	Nominal code cost centre record Id. See cost_centres
CostCentreCode	String		Cost centre code.
DepartmentId	Long	Departments.Id	Nominal code department record Id. See departments
DepartmentCode	String		Department code.
NominalAccountType	String		The type of the nominal code. See nominal_account_types
AccountStatusType	String		The status of the nominal code (Sage 200c Standard and versions of Professional released after February 2018). For example, this can either be 'Active' or 'Hidden'. See account_status_types
ReportCategoryId	Long		Financial report category record Id. See nominal_report_categories
DisplayBalancesInSelectionList	Bool		Whether balances for the nominal code can be shown in selection lists.
BalanceYearToDate	Decimal		The nominal account balance, if balances can be shown for the nominal account.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

NominalReportCategories

Nominal report categories are used in financial statements (PL, Balance Sheet) to group nominal codes under headings and to determine whether the nominal code value is reported on the layout for the Balance Sheet (Asset and Liability) or Profit and Loss (Income and Expense).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalReportCategories WHERE Id=2258
SELECT * FROM NominalReportCategories WHERE AccountReportType='ProfitLoss'
SELECT * FROM NominalReportCategories WHERE Code='1'
SELECT * FROM NominalReportCategories WHERE Description='Sales'
SELECT * FROM NominalReportCategories WHERE AccountReportCategoryType='NominalReportingCategoryTypeIncome'
SELECT * FROM NominalReportCategories WHERE DateTimeUpdated='2018-02-28T15:23:45.960+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Nominal report category record Id.
AccountReportType	String		The type of the report.. See financial_report_layout_types
Code	String		Nominal report category code.
Description	String		Nominal report category description.
AccountReportCategoryType	String		The type of the report category. See nominal_report_category_types
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

NominalTransactionAnalysisCodes

Nominal transaction analysis codes are used to group nominal analysis items on various transaction types

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM NominalTransactionAnalysisCodes WHERE Code='Promo0715'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Description='Promotion July 2015'
SELECT * FROM NominalTransactionAnalysisCodes WHERE Active=true
SELECT * FROM NominalTransactionAnalysisCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.413+01:00'

Columns


Name	Type	References	Description
Code [KEY]	String		Nominal transaction analysis code.
Description	String		Nominal transaction analysis code description.
Active	Bool		Boolean indicating whether the transaction analysis code is active.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PendingEmails

When working with Microsoft Flow, it is useful to be able to access pending emails to send as notifications to the user from Sage 200.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PendingEmails WHERE Id=123
SELECT * FROM PendingEmails WHERE EmailType='abc'
SELECT * FROM PendingEmails WHERE EmailAddress='abc'
SELECT * FROM PendingEmails WHERE EmailSubject='abc'
SELECT * FROM PendingEmails WHERE EmailBody='abc'
SELECT * FROM PendingEmails WHERE UserNumber=123
SELECT * FROM PendingEmails WHERE UserName='abc'
SELECT * FROM PendingEmails WHERE DateTimeUpdated='2019-10-02T11:46:53Z'

Columns


Name	Type	References	Description
Id [KEY]	Long		The unique id of the Sage 200 pending email identifier.
EmailType	String		The type of pending email (which provides the intended purpose).
EmailAddress	String		The email address of the email recipient.
EmailSubject	String		The text to use for the subject of the email.
EmailBody	String		The text to use for the body of the email.
UserNumber	Long		The user id of the email recipient.
UserName	String		The user name of the email recipient.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PriceBands

A price band is simply a price list. They're used to offer different prices to different customers. Product prices are set in the each price band. The price a customer receives for a product depends on the price band their account is linked to.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PriceBands WHERE Id=1064
SELECT * FROM PriceBands WHERE Name='Standard'
SELECT * FROM PriceBands WHERE Description='Standard'
SELECT * FROM PriceBands WHERE IsStandardBand=true
SELECT * FROM PriceBands WHERE CurrencyId=2103
SELECT * FROM PriceBands WHERE PriceBandTypeId=0
SELECT * FROM PriceBands WHERE IsActive=true
SELECT * FROM PriceBands WHERE IsTimeBased=false
SELECT * FROM PriceBands WHERE ActiveDateTimeFrom='2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE ActiveDateTimeTo='2019-10-02T11:46:53Z'
SELECT * FROM PriceBands WHERE DateTimeUpdated='2018-02-28T15:23:46.183+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Price band record Id.
Name	String		Price band name.
Description	String		Price band description.
IsStandardBand	Bool		Is this the Standard price band.
CurrencyId	Long		Currency record Id. See currencies
PriceBandTypeId	Long		Price band type record Id; this will be 0 for Universal price bands and 1 for Limited price bands.
IsActive	Bool		Is this price band active.
IsTimeBased	Bool		Is this price band time based.
ActiveDateTimeFrom	Datetime		The date and time this price band is active from.
ActiveDateTimeTo	Datetime		The date and time this price band is active to.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductGroups

Product groups are used to group stock items together with similar characteristics or requirements. Product Groups provide several default settings for stock items and, have their own unique code and description.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductGroups WHERE Id=34632
SELECT * FROM ProductGroups WHERE Code='NONS'
SELECT * FROM ProductGroups WHERE Description='Non Stock Item'
SELECT * FROM ProductGroups WHERE ProductType='EnumStockItemTypeMisc'
SELECT * FROM ProductGroups WHERE CanLevelsGoNegative=false
SELECT * FROM ProductGroups WHERE DateTimeUpdated='2018-02-28T15:23:46.310+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product group Id.
Code	String		Product group code.
Description	String		Product group description.
ProductType	String		Product group type. See product_types
CanLevelsGoNegative	Bool		Can stock levels go negative.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductPrices

This returns the selling prices of your products. A price is returned for each price band associated with a product. A price band is simply a price list. The price a customer receives for a product depends on the price band their account is linked to. The 'Standard' price band is the default. The prices returned here do not include any customer specific discounts. The default sort (orderby) field is by 'product_code' ascending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductPriceViews WHERE ProductPriceId=34760
SELECT * FROM ProductPriceViews WHERE ProductId=34751
SELECT * FROM ProductPriceViews WHERE ProductCode='BOARD001'
SELECT * FROM ProductPriceViews WHERE ProductName='Whiteboard - Drywipe (900 x 1200)'
SELECT * FROM ProductPriceViews WHERE ProductStockUnitName='Each'
SELECT * FROM ProductPriceViews WHERE PriceBandId=1064
SELECT * FROM ProductPriceViews WHERE PriceBandName='Standard'
SELECT * FROM ProductPriceViews WHERE ProductPriceUseStandard=true
SELECT * FROM ProductPriceViews WHERE ProductPricePrice=20
SELECT * FROM ProductPriceViews WHERE CurrencyId=2103
SELECT * FROM ProductPriceViews WHERE CurrencyName='Pound Sterling'
SELECT * FROM ProductPriceViews WHERE CurrencySymbol='ﾂ｣'
SELECT * FROM ProductPriceViews WHERE DateTimeUpdated='2018-02-28T15:23:46.787+01:00'

Columns


Name	Type	References	Description
ProductPriceId	Long		Product price record Id.
ProductId	Long		Product record Id. See products
ProductCode [KEY]	String		Product code.
ProductName	String		Product name.
ProductStockUnitName	String		The name of the unit the product is sold in. For example box, crate, bottle. The default unit is each.
PriceBandId	Long		Price band record Id.
PriceBandName	String		The name of the price band.
ProductPriceUseStandard	Bool		'True' if uses the price from the standard price band is used otherwise 'False' The price of a product can be linked to the price set in the standard price band. This is used when only some product prices are different to the standard price.
ProductPricePrice	Decimal		The selling price for the shown product and price band.
CurrencyId	Long		Currency record Id. See currencies
CurrencyName	String		The name of the currency used for the price.
CurrencySymbol	String		The symbol of the currency used for the price.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Products

Products are used to track stock within Sage 200. This is not just for physical items, but also for items that are ordered direct from suppliers, services, and time or labour that can be included on customer invoices.

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

SELECT * FROM Products WHERE Id=34737
SELECT * FROM Products WHERE Code='M'
SELECT * FROM Products WHERE Name='M'
SELECT * FROM Products WHERE Description='abc'
SELECT * FROM Products WHERE Barcode='abc'
SELECT * FROM Products WHERE AllowSalesOrder=true
SELECT * FROM Products WHERE ProductGroupId=34633
SELECT * FROM Products WHERE TaxCodeId=1731
SELECT * FROM Products WHERE DateTimeUpdated='2019-08-01T18:00:33.140+02:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product record Id.
Code	String		Product code.
Name	String		Product name.
Description	String		Product description.
Barcode	String		Product barcode.
AllowSalesOrder	Bool		Allowed on a sales order.
ProductGroupId	Long		Product group record Id. See product_groups
TaxCodeId	Long		Tax code record Id. See tax_codes
WarehouseHoldings	String		If stock setting UseMultipleLocations is true, you must specify warehouse holding(s) when creating products. If stock setting UseMultipleLocations is false, then new product will be automatically linked to the default (home) warehouse. See warehouse_holdings
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

ProductTransactions

A product transaction is created each time a product is moved in, or out of stock. Product transactions are created from various sources such as purchase orders, sales orders, transfers between warehouses etc.. This resource returns the details of product transactions. The default sort (orderby) field is by 'id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM ProductTransactionViews WHERE Id=36511
SELECT * FROM ProductTransactionViews WHERE ProductId=34751
SELECT * FROM ProductTransactionViews WHERE ProductCode='BOARD001'
SELECT * FROM ProductTransactionViews WHERE ProductGroupId=34640
SELECT * FROM ProductTransactionViews WHERE ProductGroupCode='0006'
SELECT * FROM ProductTransactionViews WHERE TransactionType='EnumSecondaryActivityTypeStockTakeAdjustmentIn'
SELECT * FROM ProductTransactionViews WHERE TransactionDate='2015-12-31T01:00:00.000+01:00'
SELECT * FROM ProductTransactionViews WHERE SourceAreaReference='abc'
SELECT * FROM ProductTransactionViews WHERE SourceAreaName='Internal'
SELECT * FROM ProductTransactionViews WHERE Reference='abc'
SELECT * FROM ProductTransactionViews WHERE SecondReference='STK TAKE'
SELECT * FROM ProductTransactionViews WHERE WarehouseName='Warehouse'
SELECT * FROM ProductTransactionViews WHERE BinName='Unspecified'
SELECT * FROM ProductTransactionViews WHERE Quantity=2
SELECT * FROM ProductTransactionViews WHERE UnitCostPrice=15
SELECT * FROM ProductTransactionViews WHERE UnitIssuePrice=0
SELECT * FROM ProductTransactionViews WHERE UnitDiscountValue=0
SELECT * FROM ProductTransactionViews WHERE TotalOrderDiscount=0
SELECT * FROM ProductTransactionViews WHERE CostValue=30
SELECT * FROM ProductTransactionViews WHERE IssueValue=0
SELECT * FROM ProductTransactionViews WHERE Memo='abc'
SELECT * FROM ProductTransactionViews WHERE UserName='Migration'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode1='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode2='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode3='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode4='abc'
SELECT * FROM ProductTransactionViews WHERE AnalysisCode5='abc'
SELECT * FROM ProductTransactionViews WHERE DateTimeUpdated='2018-02-28T15:23:46.123+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Product transaction Id.
ProductId	Long		Product record Id. See products
ProductCode	String		Product code.
ProductGroupId	Long		Product group record Id. See product_groups
ProductGroupCode	String		Product group code. See product_groups
TransactionType	String		Enum value that describes the transaction that created the product movement. See product_transaction_types
TransactionDate	Date		The date of the transaction.
SourceAreaReference	String		The code of the source of the transaction.
SourceAreaName	String		The name of the source of the transaction.
Reference	String		This can be an optional reference added to the transaction by the user, or an automatically generated reference such as the order number.
SecondReference	String		This can be an additional optional reference added to the transaction by the user, or an automatically generated reference such as the despatch note number.
WarehouseName	String		The warehouse the product was moved from or to. See warehouses
BinName	String		The name of the bin to which the transaction relates to.
Quantity	Decimal		The quantity on the transaction.
UnitCostPrice	Decimal		The cost price per unit for this transaction.
UnitIssuePrice	Decimal		The price used when products are moved out of stock.
UnitDiscountValue	Decimal		The discount amount per unit used for this transaction.
TotalOrderDiscount	Decimal		The total discount value of the transaction.
CostValue	Decimal		The total cost value.
IssueValue	Decimal		The total issue or sales price of the transaction.
Memo	String		Any memo note that was applied to the product transaction.
UserName	String		The name of the Sage 200 user that created the transaction.
AnalysisCode1	String		Analysis Code 1.
AnalysisCode2	String		Analysis Code 2.
AnalysisCode3	String		Analysis Code 3.
AnalysisCode4	String		Analysis Code 4.
AnalysisCode5	String		Analysis Code 5.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PurchaseOrderLines

When making larger requests for purchase order information, the Purchase Order Line Views resource is a faster alternative to the 'Purchase Orders' resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrderLines WHERE PurchaseOrderId=42101
SELECT * FROM PurchaseOrderLines WHERE CurrencyId='0000000001'

Columns


Name	Type	References	Description
PurchaseOrderId [KEY]	Long	PurchaseOrders.Id	Unique ID of the purchase order. See _orders
SupplierDocumentNo	String		Supplier purchase order document number.
CurrencyId	Long		The Id for the currency used for the purchase order. See currencies
CurrencyName	String		The name for the currency used for the purchase order.
CurrencySymbol	String		The symbol of the currency used for the purchase order.
SubtotalValue	Decimal		The subtotal value for the order.
SubtotalDiscountValue	Decimal		The subtotal discount value for the order.
SubtotalLandedCostsValue	Decimal		The subtotal landed costs value for the order.
LineId	Long		Associated purchase order line Id. See _order_lines
LineNumber	Short		Purchase order line number.
LineType	String		Purchase order line type. See order_return_line_types
LineProductCode	String		Purchase order line product code.
LineProductDescription	String		Purchase order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineUnitPrice	Decimal		The buying price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineTotalValue	Decimal		The total value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the _order_line_unit_discount_value from the _order_line_unit_price.
LineLandedCostsValue	Decimal		The value of landed costs for the order.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineAnalysisCode6	String		Analysis code 6.
LineAnalysisCode7	String		Analysis code 7.
LineAnalysisCode8	String		Analysis code 8.
LineAnalysisCode9	String		Analysis code 9.
LineAnalysisCode10	String		Analysis code 10.
LineAnalysisCode11	String		Analysis code 11.
LineAnalysisCode12	String		Analysis code 12.
LineAnalysisCode13	String		Analysis code 13.
LineAnalysisCode14	String		Analysis code 14.
LineAnalysisCode15	String		Analysis code 15.
LineAnalysisCode16	String		Analysis code 16.
LineAnalysisCode17	String		Analysis code 17.
LineAnalysisCode18	String		Analysis code 18.
LineAnalysisCode19	String		Analysis code 19.
LineAnalysisCode20	String		Analysis code 20.
LineDateTimeUpdated	Datetime		The date and time the order line was last updated (UTC).
LineForDirectDelivery	Bool		Indicates if line is marked for direct delivery
LineShowOnSupplierDocs	Bool		Indicates if line will show on supplier documents.
DeliveryAddrPostalName	String		delivery address name.
DeliveryAddrDescription	String		delivery address description.
DeliveryAddrAddress1	String		delivery address line 1.
DeliveryAddrAddress2	String		delivery address line 2.
DeliveryAddrAddress3	String		delivery address line 3.
DeliveryAddrAddress4	String		delivery address line 4.
DeliveryAddrCity	String		delivery address city.
DeliveryAddrCounty	String		delivery address county.
DeliveryAddrPostCode	String		delivery address post code
DeliveryAddrCountry	String		delivery address country.
DeliveryAddrContact	String		delivery address contact
DeliveryAddrTelephone	String		delivery address telephone number
DeliveryAddrFax	String		delivery address line fax number...
DeliveryAddrEmail	String		delivery address line email.
SupplierId	Long		The associated supplier record Id. See suppliers
SupplierReference	String		The associated supplier account reference.
SupplierName	String		The associated supplier name.
ProductId	Long		The associated product record Id. Only returned where _order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where _order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where _order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where _order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where _order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

PurchaseOrders

Purchase orders are used to represent the purchase of goods or services from a supplier. They are central to a business and define the terms (price, quantity and times) by which the products or services will be received.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM PurchaseOrders WHERE Id=42101
SELECT * FROM PurchaseOrders WHERE DocumentNo='0000000001'
SELECT * FROM PurchaseOrders WHERE ExchangeRate='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Purchase order record Id.
DocumentNo	String		Purchase order document number. Note: If the POP setting in Sage 200c Professional, then this property MUST be set.
DocumentDate	Date		Purchase order document date.
DocumentStatus	String		Document status. See pop_document_status_types
AuthorisationStatus	String		Authorisation status. See pop_authorisation_status_types
SupplierId	Long	Suppliers.Id	Supplier record Id. See suppliers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargesNetValue	Decimal		Charges net value.
SubtotalDiscountValue	Decimal		Discount value.
SubtotalLandedCostsValue	Decimal		Landed costs value.
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
SupplierDocumentNo	String		Supplier document number.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent.
DocumentOriginatorId	Long		Document originator id. See users
DocumentOriginatorName	String		Document originator name.
DocumentCreatedById	Long		Document created by id. See users
DocumentCreatedBy	String		Document created by.
RequestedDeliveryDate	Date		Requested delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
Lines	String		The purchase order lines collection. See pop_order_lines
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DeliveryAddressContact	String		The delivery address contact.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

PurchasePostedTransactions

Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted 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,AllocationSessionItems,AllocationHistoryItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM PurchasePostedTransactions WHERE Id=33018
SELECT * FROM PurchasePostedTransactions WHERE SupplierId=28936
SELECT * FROM PurchasePostedTransactions WHERE TraderTransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM PurchasePostedTransactions WHERE Reference='O/BAL'
SELECT * FROM PurchasePostedTransactions WHERE SecondReference='Opening Balance'
SELECT * FROM PurchasePostedTransactions WHERE Queried='abc'
SELECT * FROM PurchasePostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM PurchasePostedTransactions WHERE DueDate='2024-01-30T01:00:00.000+01:00'
SELECT * FROM PurchasePostedTransactions WHERE DocumentGoodsValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentGrossValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxValue=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE DiscountPercent=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentTaxDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE DocumentAllocatedValue=55
SELECT * FROM PurchasePostedTransactions WHERE DocumentOutstandingValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseGoodsValue=55
SELECT * FROM PurchasePostedTransactions WHERE BaseGrossValue=55
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseTaxDiscountValue=0
SELECT * FROM PurchasePostedTransactions WHERE BaseAllocatedValue=55
SELECT * FROM PurchasePostedTransactions WHERE ControlValueInBaseCurrency=0
SELECT * FROM PurchasePostedTransactions WHERE ExchangeRate=1
SELECT * FROM PurchasePostedTransactions WHERE SettledImmediately=false
SELECT * FROM PurchasePostedTransactions WHERE VatAdjustmentDocExpected=false
SELECT * FROM PurchasePostedTransactions WHERE DiscountDays=0
SELECT * FROM PurchasePostedTransactions WHERE Urn=11
SELECT * FROM PurchasePostedTransactions WHERE UserName='Migration'
SELECT * FROM PurchasePostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.137+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Id.
SupplierId	Long		Supplier 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.
DocumentGoodsValue	Decimal		Value of the goods.
DocumentGrossValue	Decimal		Gross value.
DocumentTaxValue	Decimal		Tax value.
DocumentDiscountValue	Decimal		Tax 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
VatAdjustmentDocExpected	Bool		Whether a VAT adjustment is expected.
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 purchase_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See purchase_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See purchase_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See purchase_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See purchase_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

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).

SalesOrderAdditionalCharges

Additional charges can be added to sales orders to account for items such as carriage and insurance. Within the Sage 200 application, users can setup and maintain the list of additional charges that can be referenced when creating a sales order. This API resource can be used to determine what each additional charge is for.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderAdditionalCharges WHERE Id=42101
SELECT * FROM SalesOrderAdditionalCharges WHERE Code='0000000001'
SELECT * FROM SalesOrderAdditionalCharges WHERE Name='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Sop additional charge record Id.
Code	String		Sop additional charge code.
Name	String		Sop additional charge name.
ChargeValue	Decimal		Sop additional charge value.
NotionalCostValue	Decimal		Sop additional charge notional cost value.
NominalCodeId	Long	NominalCodes.Id	Nominal code record Id. See nominal_codes
TaxCodeId	Long	TaxCodes.Id	Tax code record Id. See tax_codes
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesOrderLines

When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderLines WHERE SalesOrderId=42101
SELECT * FROM SalesOrderLines WHERE CurrencyId=2343234543
SELECT * FROM SalesOrderLines WHERE CurrencyName='Sample'

Columns


Name	Type	References	Description
SalesOrderId [KEY]	Long		Unique Id of the sales order. See sop_orders
CustomerDocumentNo	String		Customer sales order document number.
CurrencyId	Long		The Id for the currency used for the sales order. See currencies
CurrencyName	String		The name for the currency used for the sales order.
CurrencySymbol	String		The symbol of the currency used for the sales order.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
LineId	Long		Associated sales order line Id. See sop_order_lines
LineNumber	Short		Sales order line number.
LineType	String		Sales order line type. See order_return_line_types
LineProductCode	String		Sales order line product code.
LineProductDescription	String		Sales order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity	Decimal		The quantity ordered.
LineAllocatedQuantity	Decimal		The quantity allocated.
LineDespatchReceiptQuantity	Decimal		The quantity despatched.
LineInvoiceCreditQuantity	Decimal		The quantity invoiced.
LineSellingUnitDescription	String		The selling unit description.
LineSellingUnitPrice	Decimal		The selling price per item.
LineUnitDiscountPercent	Decimal		The percentage discount per item.
LineUnitDiscountValue	Decimal		The discount value per item.
LineCostPrice	Decimal		The cost price.
LineTotalValue	Decimal		The total value of the line.
LineLineTaxValue	Decimal		The tax value of the line.
LineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_order_line_unit_discount_value from the sop_order_line_selling_unit_price.
LineRequestedDeliveryDate	Date		Requested delivery date.
LinePromisedDeliveryDate	Date		Promised delivery date.
LineAnalysisCode1	String		Analysis code 1.
LineAnalysisCode2	String		Analysis code 2.
LineAnalysisCode3	String		Analysis code 3.
LineAnalysisCode4	String		Analysis code 4.
LineAnalysisCode5	String		Analysis code 5.
LineAnalysisCode6	String		Analysis code 6.
LineAnalysisCode7	String		Analysis code 7.
LineAnalysisCode8	String		Analysis code 8.
LineAnalysisCode9	String		Analysis code 9.
LineAnalysisCode10	String		Analysis code 10.
LineAnalysisCode11	String		Analysis code 11.
LineAnalysisCode12	String		Analysis code 12.
LineAnalysisCode13	String		Analysis code 13.
LineAnalysisCode14	String		Analysis code 14.
LineAnalysisCode15	String		Analysis code 15.
LineAnalysisCode16	String		Analysis code 16.
LineAnalysisCode17	String		Analysis code 17.
LineAnalysisCode18	String		Analysis code 18.
LineAnalysisCode19	String		Analysis code 19.
LineAnalysisCode20	String		Analysis code 20.
LineDateTimeUpdated	Datetime		The date and time the sop order line was last updated (UTC).
DeliveryAddrAddress1	String		SOP delivery address line 1.
DeliveryAddrAddress2	String		SOP delivery address line 2.
DeliveryAddrAddress3	String		SOP delivery address line 3.
DeliveryAddrAddress4	String		SOP delivery address line 4.
DeliveryAddrCity	String		SOP delivery address city.
DeliveryAddrCounty	String		SOP delivery address county.
DeliveryAddrPostCode	String		SOP delivery address post code
DeliveryAddrCountry	String		SOP delivery address country.
DeliveryAddrContact	String		SOP delivery address contact
DeliveryAddrTelephone	String		SOP delivery address telephone number
DeliveryAddrFax	String		SOP delivery address line fax number...
DeliveryAddrEmail	String		SOP delivery address line email.
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_order_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_order_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_order_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrderProformaLines

This resource returns a view of sales pro formas, sales pro forma lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_proforma_id' and 'sop_proforma_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaId=346345654564565
SELECT * FROM SalesOrderProformaLines WHERE SalesOrderProformaDocumentStatus='Sample'

Columns


Name	Type	References	Description
SalesOrderProformaId [KEY]	Long		Unique Id of the sales pro forma
SalesOrderProformaDocumentNo	String		Sales pro forma document number.
SalesOrderProformaDocumentDate	Date		Sales pro forma date.
SalesOrderProformaDocumentStatus	String		Sales pro forma status. See sop_document_status_types
SalesOrderProformaCustomerDocumentNo	String		Customer sales pro forma document number.
SalesOrderProformaCurrencyId	Long		The Id for the currency used for the sales pro forma. See currencies
SalesOrderProformaCurrencyName	String		The name for the currency used for the sales pro forma.
SalesOrderProformaCurrencySymbol	String		The symbol of the currency used for the sales pro forma.
SalesOrderProformaExchangeRate	Decimal		Exchange rate for the sales pro forma.
SalesOrderProformaRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderProformaPromisedDeliveryDate	Date		Promised delivery date.
SalesOrderProformaAnalysisCode1	String		Analysis code 1.
SalesOrderProformaAnalysisCode2	String		Analysis code 2.
SalesOrderProformaAnalysisCode3	String		Analysis code 3.
SalesOrderProformaAnalysisCode4	String		Analysis code 4.
SalesOrderProformaAnalysisCode5	String		Analysis code 5.
SalesOrderProformaDateTimeUpdated	Datetime		The date and time the sales pro forma was last updated (UTC).
SalesOrderProformaLineId	Long		Associated sales pro forma line Id
SalesOrderProformaLineNumber	Short		Sales pro forma line number.
SalesOrderProformaLineType	String		Sales pro forma line type. See order_return_line_types
SalesOrderProformaLineProductCode	String		Sales pro forma line product code.
SalesOrderProformaLineProductDescription	String		Sales pro forma line product description. This could be stock item description, free text, additional charge or comment.
SalesOrderProformaLineQuantity	Decimal		The line quantity.
SalesOrderProformaLineSellingUnitDescription	String		The selling unit description.
SalesOrderProformaLineSellingUnitPrice	Decimal		The selling price per item.
SalesOrderProformaLineUnitDiscountPercent	Decimal		The percentage discount per item.
SalesOrderProformaLineUnitDiscountValue	Decimal		The discount value per item.
SalesOrderProformaLineCostPrice	Decimal		The cost price.
SalesOrderProformaLineTotalValue	Decimal		The total value of the line.
SalesOrderProformaLineLineTaxValue	Decimal		The tax value of the line.
SalesOrderProformaLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_proforma_line_unit_discount_value from the sop_proforma_line_selling_unit_price.
SalesOrderProformaLineRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderProformaLinePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderProformaLineAnalysisCode1	String		Analysis code 1.
SalesOrderProformaLineAnalysisCode2	String		Analysis code 2.
SalesOrderProformaLineAnalysisCode3	String		Analysis code 3.
SalesOrderProformaLineAnalysisCode4	String		Analysis code 4.
SalesOrderProformaLineAnalysisCode5	String		Analysis code 5.
SalesOrderProformaLineAnalysisCode6	String		Analysis code 6.
SalesOrderProformaLineAnalysisCode7	String		Analysis code 7.
SalesOrderProformaLineAnalysisCode8	String		Analysis code 8.
SalesOrderProformaLineAnalysisCode9	String		Analysis code 9.
SalesOrderProformaLineAnalysisCode10	String		Analysis code 10.
SalesOrderProformaLineAnalysisCode11	String		Analysis code 11.
SalesOrderProformaLineAnalysisCode12	String		Analysis code 12.
SalesOrderProformaLineAnalysisCode13	String		Analysis code 13.
SalesOrderProformaLineAnalysisCode14	String		Analysis code 14.
SalesOrderProformaLineAnalysisCode15	String		Analysis code 15.
SalesOrderProformaLineAnalysisCode16	String		Analysis code 16.
SalesOrderProformaLineAnalysisCode17	String		Analysis code 17.
SalesOrderProformaLineAnalysisCode18	String		Analysis code 18.
SalesOrderProformaLineAnalysisCode19	String		Analysis code 19.
SalesOrderProformaLineAnalysisCode20	String		Analysis code 20.
SalesOrderProformaLineDateTimeUpdated	Datetime		The date and time the sop pro forma line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_proforma_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_proforma_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_proforma_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_proforma_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrderQuoteLines

This resource returns a view of sales quotes, sales quote lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_quote_id' and 'sop_quote_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteId=3424324324324
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteDocumentNo='Sample'
SELECT * FROM SalesOrderQuoteLines WHERE SalesOrderQuoteCustomerDocumentNo='Sample'

Columns


Name	Type	References	Description
SalesOrderQuoteId [KEY]	Long		Unique Id of the sales quote
SalesOrderQuoteDocumentNo	String		Sales quote document number.
SalesOrderQuoteDocumentDate	Date		Sales quote date.
SalesOrderQuoteDocumentStatus	String		Sales quote status. See sop_document_status_types
SalesOrderQuoteCustomerDocumentNo	String		Customer sales quote document number.
SalesOrderQuoteCurrencyId	Long		The Id for the currency used for the sales quote. See currencies
SalesOrderQuoteCurrencyName	String		The name for the currency used for the sales quote.
SalesOrderQuoteCurrencySymbol	String		The symbol of the currency used for the sales quote.
SalesOrderQuoteExchangeRate	Decimal		Exchange rate for the sales quote.
SalesOrderQuoteRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderQuotePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderQuoteAnalysisCode1	String		Analysis code 1.
SalesOrderQuoteAnalysisCode2	String		Analysis code 2.
SalesOrderQuoteAnalysisCode3	String		Analysis code 3.
SalesOrderQuoteAnalysisCode4	String		Analysis code 4.
SalesOrderQuoteAnalysisCode5	String		Analysis code 5.
SalesOrderQuoteAnalysisCode6	String		Analysis code 6.
SalesOrderQuoteAnalysisCode7	String		Analysis code 7.
SalesOrderQuoteAnalysisCode8	String		Analysis code 8.
SalesOrderQuoteAnalysisCode9	String		Analysis code 9.
SalesOrderQuoteAnalysisCode10	String		Analysis code 10.
SalesOrderQuoteAnalysisCode11	String		Analysis code 11.
SalesOrderQuoteAnalysisCode12	String		Analysis code 12.
SalesOrderQuoteAnalysisCode13	String		Analysis code 13.
SalesOrderQuoteAnalysisCode14	String		Analysis code 14.
SalesOrderQuoteAnalysisCode15	String		Analysis code 15.
SalesOrderQuoteAnalysisCode16	String		Analysis code 16.
SalesOrderQuoteAnalysisCode17	String		Analysis code 17.
SalesOrderQuoteAnalysisCode18	String		Analysis code 18.
SalesOrderQuoteAnalysisCode19	String		Analysis code 19.
SalesOrderQuoteAnalysisCode20	String		Analysis code 20.
SalesOrderQuoteDateTimeUpdated	Datetime		The date and time the sales quote was last updated (UTC).
SalesOrderQuoteLineId	Long		Associated sales quote line Id
SalesOrderQuoteLineNumber	Short		Sales quote line number.
SalesOrderQuoteLineType	String		Sales quote line type. See order_return_line_types
SalesOrderQuoteLineProductCode	String		Sales quote line product code.
SalesOrderQuoteLineProductDescription	String		Sales quote line product description. This could be stock item description, free text, additional charge or comment.
SalesOrderQuoteLineQuantity	Decimal		The line quantity.
SalesOrderQuoteLineSellingUnitDescription	String		The selling unit description.
SalesOrderQuoteLineSellingUnitPrice	Decimal		The selling price per item.
SalesOrderQuoteLineUnitDiscountPercent	Decimal		The percentage discount per item.
SalesOrderQuoteLineUnitDiscountValue	Decimal		The discount value per item.
SalesOrderQuoteLineCostPrice	Decimal		The cost price.
SalesOrderQuoteLineTotalValue	Decimal		The total value of the line.
SalesOrderQuoteLineLineTaxValue	Decimal		The tax value of the line.
SalesOrderQuoteLineDiscountedUnitPrice	Decimal		The discounted unit price of the line. This is calculated by subtracting the sop_quote_line_unit_discount_value from the sop_quote_line_selling_unit_price.
SalesOrderQuoteLineRequestedDeliveryDate	Date		Requested delivery date.
SalesOrderQuoteLinePromisedDeliveryDate	Date		Promised delivery date.
SalesOrderQuoteLineAnalysisCode1	String		Analysis code 1.
SalesOrderQuoteLineAnalysisCode2	String		Analysis code 2.
SalesOrderQuoteLineAnalysisCode3	String		Analysis code 3.
SalesOrderQuoteLineAnalysisCode4	String		Analysis code 4.
SalesOrderQuoteLineAnalysisCode5	String		Analysis code 5.
SalesOrderQuoteLineAnalysisCode6	String		Analysis code 6.
SalesOrderQuoteLineAnalysisCode7	String		Analysis code 7.
SalesOrderQuoteLineAnalysisCode8	String		Analysis code 8.
SalesOrderQuoteLineAnalysisCode9	String		Analysis code 9.
SalesOrderQuoteLineAnalysisCode10	String		Analysis code 10.
SalesOrderQuoteLineAnalysisCode11	String		Analysis code 11.
SalesOrderQuoteLineAnalysisCode12	String		Analysis code 12.
SalesOrderQuoteLineAnalysisCode13	String		Analysis code 13.
SalesOrderQuoteLineAnalysisCode14	String		Analysis code 14.
SalesOrderQuoteLineAnalysisCode15	String		Analysis code 15.
SalesOrderQuoteLineAnalysisCode16	String		Analysis code 16.
SalesOrderQuoteLineAnalysisCode17	String		Analysis code 17.
SalesOrderQuoteLineAnalysisCode18	String		Analysis code 18.
SalesOrderQuoteLineAnalysisCode19	String		Analysis code 19.
SalesOrderQuoteLineAnalysisCode20	String		Analysis code 20.
SalesOrderQuoteLineDateTimeUpdated	Datetime		The date and time the sop quote line was last updated (UTC).
CustomerId	Long		The associated customer record Id. See customers
CustomerReference	String		The associated customer account reference.
CustomerName	String		The associated customer name.
ProductId	Long		The associated product record Id. Only returned where sop_quote_line_type = EnumLineTypeStandard. See products
ProductCode	String		The code of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductName	String		The name of the product ordered. Only returned where sop_quote_line_type = EnumLineTypeStandard
ProductDescription	String		The associated product description. Only returned where sop_quote_line_type = EnumLineTypeStandard
WarehouseId	Long		The id of the warehouse the product is stored in. Only returned where sop_quote_line_type = EnumLineTypeStandard. See warehouses
WarehouseName	String		The name of the warehouse. Only returned where sop_quote_line_type = EnumLineTypeStandard
TaxCodeId	Long		The id of the tax code set on the line. See tax_codes
TaxCodeCode	Short		The associated tax code.
TaxCodeName	String		The associated tax code name.
TaxCodeRate	Decimal		The associated tax rate.

SalesOrders

Sales orders are used to represent the sale of goods or services to a customer. They are central to a business and define the terms (price, quantity and times) by which the products or services will be delivered. Note: Adding traceable items to sales orders. If you're using traceable stock items and an item is set to be sold from a single batch, then the order quantity must be less than or equal to quantity in the batch. If you try to order more than the quantity in the batch, the order cannot be posted via the API.

Columns


Name	Type	References	Description
Id [KEY]	Long		Sales order record Id.
DocumentNo	String		Sales order document number. Note: If the SOP setting in Sage 200c Professional or Sage 200 Extra Online is to NOT automatically generate numbers, then this property MUST be set. If the SOP setting in Sage 200 Extra is to automatically generate numbers, or you are using Sage 200c Standard (which doesn't allow you to set this option), then setting this on POST will be ignored.
DocumentDate	Date		Sales order document date.
DocumentStatus	String		Document status. See sop_document_status_types
CustomerId	Long	Customers.Id	Customer record Id. See customers
ExchangeRate	Decimal		Exchange rate.
SubtotalGoodsValue	Decimal		Goods value (sum of stock items and free text).
SubtotalChargeNetValue	Decimal		Charge net value.
SubtotalChargeTaxValue	Decimal		Charge tax value.
SubtotalDiscountValue	Decimal		Discount value
TotalNetValue	Decimal		Net value (sum of all line types).
TotalTaxValue	Decimal		Tax value.
TotalGrossValue	Decimal		Gross value.
CustomerDocumentNo	String		Customer document number.
UseInvoiceAddress	Bool		True if this order uses the customer invoice address, else False.
SettlementDiscountDays	Short		Settlement discount days.
SettlementDiscountPercent	Decimal		Settlement discount percent.
DocumentDiscountPercent	Decimal		Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy	String		The person who created the SOP Order
RequestedDeliveryDate	Date		Requested delivery date.
PromisedDeliveryDate	Date		Promised delivery date.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
Lines	String		The sales order lines collection. See sop_order_lines
DeliveryAddressId	Long		The delivery address contact.
DeliveryAddress_1	String		The delivery address line 1.
DeliveryAddress_2	String		The delivery address line 2.
DeliveryAddress_3	String		The delivery address line 3.
DeliveryAddress_4	String		The delivery address line 4.
DeliveryAddressCity	String		The delivery address city.
DeliveryAddressCounty	String		The delivery address county.
DeliveryAddressPostcode	String		The delivery address postcode.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesPostedTransactions

Sales posted transactions are created when transactions, such as orders invoiced, receipts, refunds or credit notes, are posted against the Sales Ledger. It is not possible to use this API resource to create posted transactions. To post a transaction, the necessary API resource must be invoked. When making larger requests for customer information, the Sales Transactions Views resource can be a faster alternative to this resource.

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,AllocationSessionItems,AllocationHistoryItems columns.

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

For example, the following queries are processed server side:

SELECT * FROM SalesPostedTransactions WHERE Id=29674
SELECT * FROM SalesPostedTransactions WHERE CustomerId=27849
SELECT * FROM SalesPostedTransactions WHERE TraderTransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM SalesPostedTransactions WHERE Reference='O/BAL'
SELECT * FROM SalesPostedTransactions WHERE SecondReference='Opening Balance'
SELECT * FROM SalesPostedTransactions WHERE Queried='abc'
SELECT * FROM SalesPostedTransactions WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DueDate='2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesPostedTransactions WHERE FullSettlementDate='2019-04-15T02:00:00.000+02:00'
SELECT * FROM SalesPostedTransactions WHERE DocumentGoodsValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentGrossValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxValue=0
SELECT * FROM SalesPostedTransactions WHERE DocumentDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE DiscountPercent=0
SELECT * FROM SalesPostedTransactions WHERE DocumentTaxDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE DocumentAllocatedValue=150
SELECT * FROM SalesPostedTransactions WHERE DocumentOutstandingValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseGoodsValue=150
SELECT * FROM SalesPostedTransactions WHERE BaseGrossValue=150
SELECT * FROM SalesPostedTransactions WHERE BaseTaxValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseTaxDiscountValue=0
SELECT * FROM SalesPostedTransactions WHERE BaseAllocatedValue=150
SELECT * FROM SalesPostedTransactions WHERE ControlValueInBaseCurrency=0
SELECT * FROM SalesPostedTransactions WHERE ExchangeRate=1
SELECT * FROM SalesPostedTransactions WHERE SettledImmediately=false
SELECT * FROM SalesPostedTransactions WHERE DiscountDays=0
SELECT * FROM SalesPostedTransactions WHERE Urn=1
SELECT * FROM SalesPostedTransactions WHERE UserName='Migration'
SELECT * FROM SalesPostedTransactions WHERE DateTimeUpdated='2018-02-28T15:23:46.330+01:00'

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_posted_transactions_nominal_analysis_items
TaxAnalysisItems	String		Tax analysis items. See sales_posted_transactions_tax_analysis_items
BankItems	String		Bank items. See sales_posted_transactions_bank_items
AllocationSessionItems	String		Allocation session items. See sales_posted_transactions_allocation_session_items
AllocationHistoryItems	String		Allocation history items. See sales_posted_transactions_allocation_history_items
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesTradingPeriods

This resource returns the details of all sales trading periods for all financial years held for the company. A company can have the trading periods set to be the same as the accounting periods or as different periods. (Sage 200c Professional and Sage 200 Extra Online Only, Sage 200c Standard defaults to financial year periods)

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesTradingPeriods WHERE Id=1
SELECT * FROM SalesTradingPeriods WHERE PeriodNumber='1'
SELECT * FROM SalesTradingPeriods WHERE StartDate='2018-01-01T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE EndDate='2018-01-31T01:00:00.000+01:00'
SELECT * FROM SalesTradingPeriods WHERE DateTimeUpdated='2018-02-28T15:23:45.667+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		An unique Id specific to each sales trading period.
PeriodNumber	String		The period number.
StartDate	Date		Date the trading period starts. The returned string includes the time but this will always be 00:00:00.
EndDate	Date		Date the trading period ends. The returned string includes the time but this will always be 00:00:00.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SalesTransactions

When making larger API requests for transaction information from the Sales ledger, the Sales Transaction Views resource can be a faster alternative to the 'Sales Posted Transactions' resource. The Sales Transaction Views resource also returns a wider breadth of information than the Sales Posted Transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SalesTransactionViews WHERE PostedCustomerTransactionId=29674
SELECT * FROM SalesTransactionViews WHERE TransactionType='TradingAccountEntryTypeInvoice'
SELECT * FROM SalesTransactionViews WHERE TransactionDate='2017-12-31T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE PostedDate='2018-09-13T02:00:00.000+02:00'
SELECT * FROM SalesTransactionViews WHERE DueDate='2018-02-14T01:00:00.000+01:00'
SELECT * FROM SalesTransactionViews WHERE DiscountDays=0
SELECT * FROM SalesTransactionViews WHERE Reference='O/BAL'
SELECT * FROM SalesTransactionViews WHERE SecondReference='Opening Balance'
SELECT * FROM SalesTransactionViews WHERE Query=' '
SELECT * FROM SalesTransactionViews WHERE Status='Full'
SELECT * FROM SalesTransactionViews WHERE UserName='Migration'
SELECT * FROM SalesTransactionViews WHERE LedgerType='LedgerSales'
SELECT * FROM SalesTransactionViews WHERE Urn=1
SELECT * FROM SalesTransactionViews WHERE ExchangeRate=1
SELECT * FROM SalesTransactionViews WHERE SettledImmediately=false
SELECT * FROM SalesTransactionViews WHERE BaseGoodsValue=150
SELECT * FROM SalesTransactionViews WHERE BaseGrossValue=150
SELECT * FROM SalesTransactionViews WHERE BaseDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE BaseTaxValue=0
SELECT * FROM SalesTransactionViews WHERE BaseTaxDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE BaseAllocatedValue=150
SELECT * FROM SalesTransactionViews WHERE BaseOutstandingValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentGoodsValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentGrossValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentTaxDiscountValue=0
SELECT * FROM SalesTransactionViews WHERE DocumentAllocatedValue=150
SELECT * FROM SalesTransactionViews WHERE DocumentOutstandingValue=0
SELECT * FROM SalesTransactionViews WHERE CustomerId=27849
SELECT * FROM SalesTransactionViews WHERE CustomerReference='COM001'
SELECT * FROM SalesTransactionViews WHERE CustomerName='Compton Packaging'
SELECT * FROM SalesTransactionViews WHERE CurrencyName='Pound Sterling'
SELECT * FROM SalesTransactionViews WHERE CurrencySymbol='ﾂ｣'

Columns


Name	Type	References	Description
PostedCustomerTransactionId [KEY]	Long		Id of the transaction.
TransactionType	String		Transaction type. See trader_transaction_types
TransactionDate	Date		The date of the transaction.
PostedDate	Date		The date the transaction was posted.
DueDate	Date		The due date of the transaction.
DiscountDays	Short		Days discount valid.
Reference	String		The reference of the transaction.
SecondReference	String		The second reference of the transaction.
Query	String		Query.
Status	String		Status.
UserName	String		User that posted the transaction.
LedgerType	String		Ledger type. See ledger_types
Urn	Long		Unique reference number.
ExchangeRate	Decimal		Exchange Rate.
SettledImmediately	Bool		Immediately settled.
BaseGoodsValue	Decimal		Goods value in base currency.
BaseGrossValue	Decimal		Gross value in base currency.
BaseDiscountValue	Decimal		Discount value in base currency.
BaseTaxValue	Decimal		Tax value in base currency.
BaseTaxDiscountValue	Decimal		Tax discount value in base currency.
BaseAllocatedValue	Decimal		Allocated value in base currency.
BaseOutstandingValue	Decimal		Outstanding value in base currency.
DocumentGoodsValue	Decimal		Goods value.
DocumentGrossValue	Decimal		Gross value.
DocumentDiscountValue	Decimal		Discount value.
DocumentTaxValue	Decimal		Tax value.
DocumentTaxDiscountValue	Decimal		Tax discount value.
DocumentAllocatedValue	Decimal		Allocated value.
DocumentOutstandingValue	Decimal		Outstanding value.
CustomerId	Long		Customer Id.
CustomerReference	String		Customer account code.
CustomerName	String		Customer account name.
CurrencyName	String		Currency.
CurrencySymbol	String		Currency symbol.

StockSettings

These settings to define how to process and manage your stock.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM StockSettings WHERE Id=1
SELECT * FROM StockSettings WHERE UseMultipleLocations=true
SELECT * FROM StockSettings WHERE ProfitPercentOfRevenue=true
SELECT * FROM StockSettings WHERE DateTimeUpdated='2018-02-28T15:23:45.543+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Stock setting unique Id.
UseMultipleLocations	Bool		When true this setting allows Sage 200 to track stock at different locations. This can be across several warehouses, or a single warehouse split into separate areas (bins). (Sage 200c Standard value is true and cannot be changed).
ProfitPercentOfRevenue	Bool		Profit can be calculated as a percentage of revenue (true), or as a percentage of cost (false).
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

SupplierContacts

Information can be stored for multiple contacts against each Supplier account. Each contact can have its own details such as telephone numbers, fax numbers, email address and website.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM SupplierContacts WHERE Id=42101
SELECT * FROM SupplierContacts WHERE SupplierId=325434364565
SELECT * FROM SupplierContacts WHERE FirstName='Sample'

Columns


Name	Type	References	Description
Id [KEY]	Long		Supplier contact record Id.
SupplierId	Long		Supplier record Id.
Name	String		Contact Name - Concatenated first_name, middle_name and last_name.
FirstName	String		Contact first name.
MiddleName	String		Contact middle name.
LastName	String		Contact surname.
IsDefault	Bool		Is this the default contact for the parent Supplier. See Supplier
DefaultTelephone	String		Default telephone number.
DefaultEmail	String		Default email address.
SalutationId	Long		Contact salutation id.
SalutationIsDefault	Boolean		Contact salutation.
SalutationCode	String		Contact salutation code.
SalutationDescription	String		Contact salutation description.
Emails	String		Supplier emails. See Supplier_emails
Telephones	String		Supplier telephones. See Supplier_telephones
Mobiles	String		Supplier mobiles. See Supplier_mobiles
Faxes	String		Supplier faxes. See Supplier_faxes
Websites	String		Supplier websites. See Supplier_websites
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Suppliers

Suppliers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. purchase orders, purchase receipts, etc).

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,BankId,BankAccountNumber,BankAccountName,BankIBAN,BankBIC,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

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

For example, the following queries are processed server side:

SELECT * FROM Suppliers WHERE Id=28846
SELECT * FROM Suppliers WHERE Reference='CON001'
SELECT * FROM Suppliers WHERE Name='Concept Stationery Supplies'
SELECT * FROM Suppliers WHERE ShortName='Concept'
SELECT * FROM Suppliers WHERE Balance=4346.16
SELECT * FROM Suppliers WHERE OnHold=false
SELECT * FROM Suppliers WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Suppliers WHERE CurrencyId=2103
SELECT * FROM Suppliers WHERE ExchangeRateType='ExchangeRateSingle'
SELECT * FROM Suppliers WHERE TelephoneCountryCode='44'
SELECT * FROM Suppliers WHERE TelephoneAreaCode='0191'
SELECT * FROM Suppliers WHERE TelephoneSubscriberNumber='643 4343'
SELECT * FROM Suppliers WHERE FaxCountryCode='44'
SELECT * FROM Suppliers WHERE FaxAreaCode='0191'
SELECT * FROM Suppliers WHERE FaxSubscriberNumber='643 4345'
SELECT * FROM Suppliers WHERE Website='www.sage.co.uk'
SELECT * FROM Suppliers WHERE CreditLimit=17000
SELECT * FROM Suppliers WHERE CountryCodeId=13
SELECT * FROM Suppliers WHERE DefaultTaxCodeId=1729
SELECT * FROM Suppliers WHERE VatNumber='GB988 3453 23'
SELECT * FROM Suppliers WHERE AnalysisCode1='North East'
SELECT * FROM Suppliers WHERE AnalysisCode2='Stationery'
SELECT * FROM Suppliers WHERE AnalysisCode3='abc'
SELECT * FROM Suppliers WHERE AnalysisCode4='abc'
SELECT * FROM Suppliers WHERE AnalysisCode5='abc'
SELECT * FROM Suppliers WHERE DateTimeUpdated='2018-02-28T15:23:46.253+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Supplier unique Id.
Reference	String		Supplier account reference. NOTE: Not required if supplier reference is set to
Name	String		Supplier name.
ShortName	String		Supplier short name.
Balance	Decimal		Supplier account balance.
OnHold	Bool		True if supplier account is on hold, else False.
AccountStatusType	String		The status of the supplier account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId	Long	Currencies.Id	Currency record Id. See currencies
ExchangeRateType	String		The type of exchange rate used on the supplier account. See exchange_rate_types
TelephoneCountryCode	String		Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode	String		Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber	String		Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode	String		Fax country code.
FaxAreaCode	String		Fax area code.
FaxSubscriberNumber	String		Fax subscriber number.
Website	String		Website address.
CreditLimit	Decimal		Credit limit for the supplier.
CountryCodeId	Long		Country code record Id.
CountryCode	String		The customers country code.
CountryCodeName	String		The customers country code name.
CountryCodeEuMember	Boolean		Shows if the customer is Eu Member.
DefaultTaxCodeId	Long	TaxCodes.Id	Default tax code record Id. See tax_codes
VatNumber	String		VAT registration number.
AnalysisCode1	String		Analysis code 1.
AnalysisCode2	String		Analysis code 2.
AnalysisCode3	String		Analysis code 3.
AnalysisCode4	String		Analysis code 4.
AnalysisCode5	String		Analysis code 5.
AnalysisCode6	String		Analysis code 6.
AnalysisCode7	String		Analysis code 7.
AnalysisCode8	String		Analysis code 8.
AnalysisCode9	String		Analysis code 9.
AnalysisCode10	String		Analysis code 10.
AnalysisCode11	String		Analysis code 11.
AnalysisCode12	String		Analysis code 12.
AnalysisCode13	String		Analysis code 13.
AnalysisCode14	String		Analysis code 14.
AnalysisCode15	String		Analysis code 15.
AnalysisCode16	String		Analysis code 16.
AnalysisCode17	String		Analysis code 17.
AnalysisCode18	String		Analysis code 18.
AnalysisCode19	String		Analysis code 19.
AnalysisCode20	String		Analysis code 20.
BankId	String		Supplier bank account id.
BankAccountNumber	String		Supplier bank account number.
BankAccountName	String		Supplier bank account name.
BankIBAN	String		Supplier bank account iban number.
BankBIC	String		Supplier bank account BIC number.
MainAddress_1	String		The customers main address line 1.
MainAddress_2	String		The customers main address line 2.
MainAddress_3	String		The customers main address line 3.
MainAddress_4	String		The customers main address line 4.
MainAddressCity	String		The customers main address city.
MainAddressCounty	String		The customers main address county.
MainAddressPostcode	String		The customers main address postcode.
Contacts	String		Supplier contacts. See supplier_contacts
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

TaxCodes

Tax codes are normally used for UK business VAT Rates. They are accessed by Sage 200 modules in order to: - Analyse the rates to Nominal Ledger accounts. - Analyse the rates to the VAT Return. - Read percentages for calculations in transactions.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM TaxCodes WHERE Id=1728
SELECT * FROM TaxCodes WHERE Code=0
SELECT * FROM TaxCodes WHERE Name='Zero rated'
SELECT * FROM TaxCodes WHERE TaxRate=0
SELECT * FROM TaxCodes WHERE Terms='EcTermsNotApplicable'
SELECT * FROM TaxCodes WHERE TermsDescription='Not Applicable'
SELECT * FROM TaxCodes WHERE DateTimeUpdated='2018-02-28T15:23:46.133+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Tax code record Id.
Code	Short		Tax code.
Name	String		Name of the tax code.
TaxRate	Decimal		Tax rate.
Terms	String		Name of the terms.
TermsDescription	String		Description of the terms.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

WarehouseHoldings

All stock within Sage 200 regardless of type (Stock, Service/labour, or Miscellaneous), require a holding location. The location indicates where an item is stored and the stock level settings for each product in the warehouse i.e. the re-order level, the minimum and maximum stock levels. Items with a type of 'Stock' have levels recorded for each warehouse location and the levels are used when allocating, issuing and receiving stock. If the stock setting UseMultipleLocations is true, a product can be linked to multiple warehouse holdings. You cannot delete the last warehouse holding associated with a product.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM WarehouseHoldings WHERE Id=34738
SELECT * FROM WarehouseHoldings WHERE WarehouseId=34629
SELECT * FROM WarehouseHoldings WHERE ProductId=34737
SELECT * FROM WarehouseHoldings WHERE ReorderLevel=0
SELECT * FROM WarehouseHoldings WHERE MinimumLevel=0
SELECT * FROM WarehouseHoldings WHERE MaximumLevel=0
SELECT * FROM WarehouseHoldings WHERE ConfirmedQtyInStock=0
SELECT * FROM WarehouseHoldings WHERE UnconfirmedQtyInStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityInStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedStock=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedSop=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocatedBom=0
SELECT * FROM WarehouseHoldings WHERE QuantityAllocated=0
SELECT * FROM WarehouseHoldings WHERE DateTimeUpdated='2019-08-01T18:00:33.140+02:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Warehouse holding record Id.
WarehouseId	Long	Warehouses.Id	Warehouse record Id. See warehouses
ProductId	Long	Products.Id	Product record Id. See products
ReorderLevel	Decimal		Reorder stock level.
MinimumLevel	Decimal		Minimum stock quantity level.
MaximumLevel	Decimal		Maximum stock quantity level.
ConfirmedQtyInStock	Decimal		Confirmed stock quantity.
UnconfirmedQtyInStock	Decimal		Unconfirmed stock quantity.
QuantityInStock	Decimal		Total confirmed and unconfirmed stock quantity.
QuantityAllocatedStock	Decimal		Allocated stock quantity.
QuantityAllocatedSop	Decimal		Allocated sales order quantity.
QuantityAllocatedBom	Decimal		Allocated bill of materials quantity.
QuantityAllocated	Decimal		Total Allocated stock, sop and bom quantity.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

Warehouses

All stock within Sage 200, regardless of type (Stock, Service/labour, or Miscellaneous), requires a holding location. By default, the HOME warehouse is created within Sage 200, you can then create your own warehouses.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

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

For example, the following queries are processed server side:

SELECT * FROM Warehouses WHERE Id=34626
SELECT * FROM Warehouses WHERE Name='Supplied Direct'
SELECT * FROM Warehouses WHERE DateTimeUpdated='2018-02-28T15:23:46.190+01:00'

Columns


Name	Type	References	Description
Id [KEY]	Long		Warehouse record Id.
Name	String		Warehouse name.
DateTimeUpdated	Datetime		The date and time this entity was last updated (UTC).

接続文字列オプション

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

接続を確立する方法について詳しくは、接続の確立を参照してください。

Authentication

プロパティ	説明
SubscriptionKey	Your subscription key.

OAuth

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
Scope	This determines the scopes that are requested from Sage.

SSL

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Firewall

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

Proxy

プロパティ	説明
ProxyAutoDetect	これは、システムプロキシ設定を使用するかどうかを示します。これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。
ProxyServer	HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
ProxyPort	ProxyServer プロキシが起動しているTCP ポート。
ProxyAuthScheme	ProxyServer プロキシへの認証で使われる認証タイプ。
ProxyUser	ProxyServer プロキシへの認証に使われるユーザー名。
ProxyPassword	ProxyServer プロキシへの認証に使われるパスワード。
ProxySSLType	ProxyServer プロキシへの接続時に使用するSSL タイプ。
ProxyExceptions	ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

Logging

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

Schema

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。
Schema	Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Miscellaneous

プロパティ	説明
CompanyId	The ID of your company.
MaxRows	クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。
MaxThreads	Specifies the number of concurrent requests.
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

Authentication

このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。

プロパティ	説明
SubscriptionKey	Your subscription key.

SubscriptionKey

Your subscription key.

解説

Your subscription key. Obtained by subscribing to the appropriate edition API here: https://developer.columbus.sage.com/docs/services/api/uk.

OAuth

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
Scope	This determines the scopes that are requested from Sage.

OAuthClientId

OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId 値、およびクライアントシークレットOAuthClientSecret が提供されます。

OAuthClientSecret

OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId が提供されます。また、コンシューマーシークレットと呼ばれるクライアントシークレットも提供されます。クライアントシークレットをOAuthClientSecret プロパティに設定します。

Scope

This determines the scopes that are requested from Sage.

解説

By default, the following scopes will be requested: 'openid profile email offline_access'. If you want to override this, you can set this property to a space-separated list of scopes.

SSL

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

SSLServerCert

TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

解説

TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：


説明	例
フルPEM 証明書（例では省略されています）	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
MD5 Thumbprint （hex 値はスペースおよびコロン区切り）	ecadbdda5a1529c58a1e9e09828d70e4
SHA1 Thumbprint （hex 値はスペースおよびコロン区切り）	34a929226ae0819f2ec14b4a3d904f801cbb150d

これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。

すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。

Firewall

このセクションでは、本プロバイダーの接続文字列で設定可能なFirewall プロパティの全リストを提供します。

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

FirewallType

プロキシベースのファイアウォールで使われるプロトコル。

解説

このプロパティは、Sync App がFirewallServer プロキシ経由でトンネルトラフィックを使うためのプロトコルを指定します。デフォルトでは、Sync App はシステムプロキシに接続します。この動作を無効化し次のプロキシタイプのどれかで接続するには、ProxyAutoDetect をfalse に設定します。


タイプ	デフォルトポート	説明
TUNNEL	80	これが設定されている場合、Sync App はSage 200 への接続を開き、プロキシを経由して通信が行われます。
SOCKS4	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 4 プロキシ経由で送信し、接続リクエストが許容されるかどうかを決定します。
SOCKS5	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 5 プロキシ経由で送信します。プロキシに認証が必要な場合には、FirewallUser およびFirewallPassword をプロキシが認識する認証情報に設定します。

HTTP プロキシへの接続には、ProxyServer およびProxyPort ポートを使ってください。HTTP プロキシへの認証には、ProxyAuthScheme、ProxyUser、およびProxyPassword を使ってください。

FirewallServer

プロキシベースのファイアウォールの名前もしくはIP アドレス。

解説

ファイアウォールトラバーサルを許容するために設定するIP アドレス、DNS 名、もしくはプロキシホスト名を指定するプロパティです。プロトコルはFirewallType で指定されます。このプロパティとFirewallServer を使って、SOCKS 経由での接続、もしくはトンネリングが可能です。HTTP プロキシへの接続には、ProxyServer を使用します。

Sync App はデフォルトでシステムプロキシを使うので注意してください。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定してください。

FirewallPort

プロキシベースのファイアウォールのTCP ポート。

解説

ファイアウォールトラバーサルを許容するために設定するプロキシベースのファイアウォールのTCP ポート。名前もしくはIP アドレスを指定するには、FirewallServer を使います。FirewallType でプロトコルを指定します。

FirewallUser

プロキシベースのファイアウォールに認証するために使うユーザー名。

解説

FirewallUser およびFirewallPassword プロパティは、FirewallType により指定された認証方式に則り、FirewallServer、およびFirewallPort で指定されたプロキシに対しての認証に使われます。

FirewallPassword

プロキシベースのファイアウォールへの認証に使われるパスワード。

解説

このプロパティは、FirewallType により指定された認証メソッドに則り、FirewallServer およびFirewallPort で指定されたプロキシに渡されます。

Proxy

このセクションでは、本プロバイダーの接続文字列で設定可能なProxy プロパティの全リストを提供します。

プロパティ	説明
ProxyAutoDetect	これは、システムプロキシ設定を使用するかどうかを示します。これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。
ProxyServer	HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
ProxyPort	ProxyServer プロキシが起動しているTCP ポート。
ProxyAuthScheme	ProxyServer プロキシへの認証で使われる認証タイプ。
ProxyUser	ProxyServer プロキシへの認証に使われるユーザー名。
ProxyPassword	ProxyServer プロキシへの認証に使われるパスワード。
ProxySSLType	ProxyServer プロキシへの接続時に使用するSSL タイプ。
ProxyExceptions	ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

ProxyAutoDetect

これは、システムプロキシ設定を使用するかどうかを示します。これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。

解説

これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。

HTTP プロキシへの接続には、ProxyServer を参照してください。SOCKS やトンネリングなどの他のプロキシには、FirewallType を参照してください。

ProxyServer

HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。

解説

HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。HTTP プロキシへの認証には、Sync App はHTTP、Windows(NTLM)、もしくはKerberos 認証タイプを使用することができます。

SOCKS プロキシを経由して接続する、もしくは接続をトンネルするには、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定します。

ProxyPort

ProxyServer プロキシが起動しているTCP ポート。

解説

HTTP トラフィックをリダイレクトするHTTP プロキシが実行されているポート。ProxyServer でHTTP プロキシを指定します。その他のプロキシタイプについては、FirewallType を参照してください。

ProxyAuthScheme

ProxyServer プロキシへの認証で使われる認証タイプ。

解説

この値は、ProxyServer およびProxyPort で指定されるHTTP プロキシに認証するために使われる認証タイプを指定します。

Sync App は、デフォルトでsystem proxy settings を使い、追加での設定が不要です。他のプロキシへの接続をする場合には、ProxyServer およびProxyPort に加え、ProxyAutoDetect をfalse に設定します。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

認証タイプは、次のどれかになります。

BASIC： Sync App はHTTP BASIC 認証を行います。
DIGEST： Sync App はHTTP DIGEST 認証を行います。
NEGOTIATE： Sync App は認証において有効なプロトコルに応じて、NTLM もしくはKereros トークンを取得します。
PROPRIETARY： Sync App はNTLM もしくはKerberos トークンを発行しません。このトークンを、HTTP リクエストのAuthorization ヘッダーに含める必要があります。

SOCKS 5 認証のような他の認証タイプを使用するには、FirewallType を参照してください。

ProxyUser

ProxyServer プロキシへの認証に使われるユーザー名。

解説

ProxyUser および ProxyPassword オプションは、ProxyServer で指定されたHTTP プロキシに対して接続および認証するために使用されます。

ProxyAuthScheme で使用可能な認証タイプを選択することができます。HTTP 認証を使う場合、これをHTTP プロキシで識別可能なユーザーのユーザー名に設定します。Windows もしくはKerberos 認証を使用する場合、このプロパティを次の形式のどれかでユーザー名に設定します。

user@domain
domain\user

ProxyPassword

ProxyServer プロキシへの認証に使われるパスワード。

解説

このプロパティは、NTLM(Windows)、Kerberos、もしくはHTTP 認証をサポートするHTTP プロキシサーバーに認証するために使われます。HTTP プロキシを指定するためには、ProxyServer およびProxyPort を設定します。認証タイプを指定するためにはProxyAuthScheme を設定します。

HTTP 認証を使う場合、さらにHTTP プロキシにProxyUser およびProxyPassword を設定します。

NTLM 認証を使う場合、Windows パスワードにProxyUser およびProxyPassword を設定します。Kerberos 認証には、これらを入力する必要があります。

SOCKS 5 認証もしくは、トンネリングは、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシに接続する場合には、これをfalse に設定します。

ProxySSLType

ProxyServer プロキシへの接続時に使用するSSL タイプ。

解説

このプロパティは、ProxyServer で指定されたHTTP プロキシへの接続にSSL を使用するかどうかを決定します。この値は、AUTO、ALWAYS、NEVER、TUNNEL のいずれかです。有効な値は次のとおりです。


AUTO	デフォルト設定。URL がHTTPS URL の場合、Sync App は、TUNNEL オプションを使います。URL がHTTP URL の場合、コンポーネントはNEVER オプションを使います。
ALWAYS	接続は、常にSSL 有効となります。
NEVER	接続は、SSL 有効になりません。
TUNNEL	接続は、トンネリングプロキシを経由します。プロキシサーバーがリモートホストへの接続を開き、プロキシを経由して通信が行われます。

ProxyExceptions

ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

解説

ProxyServer は、このプロパティで定義されたアドレスを除くすべてのアドレスに使用されます。セミコロンを使用してエントリを区切ります。

Sync App は、追加設定なしにデフォルトでシステムのプロキシ設定を使います。この接続のプロキシ例外を明示的に構成するには、ProxyAutoDetect をfalse に設定して、ProxyServer およびProxyPort を設定する必要があります。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

Logging

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

LogModules

ログファイルに含めるコアモジュール。

解説

指定された（';' で区切られた）モジュールのみがログファイルに含まれます。デフォルトではすべてのモジュールが含まれます。

概要はログページを参照してください。

Schema

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。
Schema	Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Location

テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。

解説

Sync App のスキーマファイル（テーブルとビューの場合は.rsd ファイル、ストアドプロシージャの場合は.rsb ファイル）を含むディレクトリへのパス。このフォルダの場所は、実行ファイルの場所からの相対パスにすることができます。Location プロパティは、定義をカスタマイズしたり（例えば、カラム名を変更する、カラムを無視するなど）、新しいテーブル、ビュー、またはストアドプロシージャでデータモデルを拡張する場合にのみ必要です。

Note： このSync App は複数のスキーマをサポートするため、Sage 200 カスタムスキーマファイルの構造は以下のようになります。

各スキーマには、そのスキーマ名に対応するフォルダが作られます。
これらのスキーマフォルダは親フォルダに含まれています。
親フォルダは、個々のスキーマのフォルダではなく、Location として設定する必要があります。

指定しない場合、デフォルトの場所は"%APPDATA%\\CData\\Sage200 Data Provider\\Schema" となり、%APPDATA% はユーザーのコンフィギュレーションディレクトリに設定されます：

BrowsableSchemas

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、パフォーマンスが向上します。

Tables

このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。

解説

テーブルを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でテーブルのリストを提供すると、Sync App のパフォーマンスが向上します。

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいテーブルを指定します。各テーブルは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Views

使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

解説

ビューを複数のデータベースからリストすると、負荷がかかる可能性があります。接続文字列でビューのリストを提供すると、Sync App のパフォーマンスが向上します。

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいビューを指定します。各ビューは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Schema

Used to specify what Sage200 Api to use. The default one is the Standard UK API.

解説

Used to specify what Sage200 Api to use. The default one is the Standard UK API.

Miscellaneous

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

プロパティ	説明
CompanyId	The ID of your company.
MaxRows	クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。
MaxThreads	Specifies the number of concurrent requests.
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

CompanyId

The ID of your company.

解説

The ID of your company. If you have multiple companies connected to your Sage200 Account, by default we use the first one.

MaxRows

クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。

解説

クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。

MaxThreads

Specifies the number of concurrent requests.

解説

This property allows you to issue multiple requests simultaneously, thereby improving performance.

Other

これらの隠しプロパティは特定のユースケースでのみ使用されます。

解説

以下にリストされているプロパティは、特定のユースケースで使用可能です。通常のドライバーのユースケースおよび機能では、これらのプロパティは必要ありません。

複数のプロパティをセミコロン区切りリストで指定します。

統合およびフォーマット


DefaultColumnSize	データソースがメタデータにカラムの長さを提供しない場合に、文字列フィールドのデフォルトの長さを設定します。デフォルト値は2000です。
ConvertDateTimeToGMT	日時の値を、マシンのローカルタイムではなくGMT グリニッジ標準時に変換するかどうかを決定します。
RecordToFile=filename	基底のソケットデータ転送を指定のファイルに記録します。

PseudoColumns

このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。

解説

Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。

Timeout

タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

解説

Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。

Timeout の有効期限が切れても処理が完了していない場合は、Sync App は例外をスローします。

UserDefinedViews

カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

解説

ユーザー定義ビューは、UserDefinedViews.json というJSON 形式のコンフィギュレーションファイルで定義されています。Sync App は、このファイルで指定されたビューを自動的に検出します。

また、複数のビュー定義を持ち、UserDefinedViews 接続プロパティを使用して制御することも可能です。このプロパティを使用すると、指定されたビューのみがSync App によって検知されます。

このユーザー定義ビューのコンフィギュレーションファイルは、次のようにフォーマットされています。

各ルートエレメントはビューの名前を定義します。
各ルートエレメントには、query と呼ばれる子エレメントが含まれており、この子エレメントにはビューのカスタムSQL クエリが含まれています。

次に例を示します。

{
	"MyView": {
		"query": "SELECT * FROM Banks WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

UserDefinedViews 接続プロパティを使用して、JSON コンフィギュレーションファイルの場所を指定します。次に例を示します。

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"