Sage 200 への接続を追加

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

必須プロパティについては、設定タブを参照してください。

通常必須ではない接続プロパティについては、高度な設定タブを参照してください。

Sage 200 への接続

1. https://developer.columbus.sage.com/docs/services/api/uk/subscribe に移動します。
2. ご利用のSage 200 エディション（StandardUK またはProfessionalUK）のAPI を含むサブスクリプションを選択します。Sage 200 Unlimited サブスクリプションは、両方のエディションのAPI を含む必要があります。
3. 契約しているサービスにログインし、自身のプロファイルを確認します。そこに表示されているSubscriptionKey をメモしてください。

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

• Schema：使用するSage 200 のエディションを指定します。StandardUK またはProfessionalUK のいずれかです。
• SubscriptionKey：上記のサブスクリプションプロファイルから取得した値。

Sage 200 への認証

すべての認証フローで、OAuth 経由で接続するにはAuthScheme をOAuth に設定する必要があります。

以下のサブセクションでは、利用可能なOAuth フローでのSage 200 への認証について詳しく説明します。 カスタムOAuth アプリケーションの作成についての情報と、すでに埋め込みOAuth 認証情報を持つ認証フローでもカスタムOAuth アプリケーションを作成したほうがよい場合の説明については、カスタムOAuth アプリケーションの作成 を参照してください。 Sage 200 で利用可能な接続文字列プロパティの全リストは、Connection を参照してください。

アクセストークンの期限が切れたときは、Sync App は自動でアクセストークンをリフレッシュします。

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 設定のカスタマイズ

デフォルトでは、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.

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.

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Sage 200 Connector for CData Sync ビュー

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Sage 200 Connector for CData Sync ビュー

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

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

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

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

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