Create a connection to Sage 200 by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Sage 200 icon is not available, click the Add More icon to download and install the Sage 200 connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Sage 200

Before connecting, you must ensure that your account is registered for Sage API access. You can easily do this by following this link: 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.

To connect to Sage 200, specify the following connection properties:

• Schema: Determines which Sage 200 edition you are connecting to. Specify either StandardUK or ProfessionalUK.
• SubscriptionKey: After subscribing to the API, you can find the subscription key in your profile after logging into Sage 200.

Authenticating to Sage 200

Sage 200 uses the OAuth authentication standard. See Using OAuth Authentication for an authentication guide.

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.

This section details a selection of advanced features of the Sage 200 Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Sage 200 and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to 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 Data Model 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 Data Model 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

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

Stored Procedures

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.

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Sage 200 Connector for CData Sync Views

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

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

Stored Procedures

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.

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Sage 200 Connector for CData Sync Views

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