Sage 200 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8843 of the connector.

Get Started

Sage 200 Version Support

The connector leverages the Sage200 API to enable bidirectional access to Sage200.

Establish a Connection

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

Authenticate to Sage 200

Sage 200 uses the OAuth authentication standard.

You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.

See Using OAuth Authentication for an authentication guide and details of the OAuth authentication flow.

Create a Custom OAuth Application

Create a Custom OAuth Application

embeds OAuth Application Credentials with branding that can be used when connecting to Sage 200 via a desktop application or a headless machine.

However, you must create a custom OAuth application to connect to Sage 200 via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway. Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure

To obtain the OAuth client credentials, you must contact Sage directly.

To read more about how to do this, see Sage 200 API - Guide to Sage ID Changes.

When you are ready to begin the process:

1. Go to Sage 200 API Credentials Request Form.

2. Fill out the form to configure your custom OAuth application:

   • Specify the desired Refresh token expiry time in days. We suggest you set this to the maximum number of days since you will need to initiate OAuth again once the Refresh token expires.
   • If your custom OAuth application is intended for use with a Web Application, specify a Redirect URL(s). Set this to a trusted URL where users will return after they authorize your application. It is required by Sage to include URLs that start with https.

   Sage creates the desired custom OAuth application and responds with values for the OAuthClientId (Client Id), the OAuthClientSecret (Client Secret), and the Scope.

3. To ensure that the account is registered for API access, follow this link: https://developer.columbus.sage.com/docs/services/api/uk/subscribe.

If you need further assistance from Sage, see https://developer.columbus.sage.com/contactus.

Fine-Tuning Data Access

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.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

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

User Defined Views

The connector 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 connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector 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.

User Defined Views

The Sage 200 connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

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

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

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

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector 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.

Data Model

Overview

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

Key Features

• The connector 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 connector.

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 Sage 200 connector 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 Sage 200 connector to automate working with Sage 200 data.

Sage200 StandardUK Data Model

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

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Sage 200 Connector Views

BankPostedTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Banks

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

CompanyDetails

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

SELECT * FROM CompanyDetails WHERE Name = 'Stationery & Computer Mart UK'
SELECT * FROM CompanyDetails WHERE Telephone = '0191 955 3000'
SELECT * FROM CompanyDetails WHERE Fax = '0191 955 3001'
SELECT * FROM CompanyDetails WHERE Email = 'sales@stationerycomputermartuk.co.uk'
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

CostCentres

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Currencies

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

CustomerContacts

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

CustomerDeliveryAddresses

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

SELECT * FROM CustomerDeliveryAddresses WHERE Id = 27927
SELECT * FROM CustomerDeliveryAddresses WHERE Contact = 'Lee Dalkin'
SELECT * FROM CustomerDeliveryAddresses WHERE Country = 'abc'
SELECT * FROM CustomerDeliveryAddresses WHERE CustomerId = 27825
SELECT * FROM CustomerDeliveryAddresses WHERE Description = 'Registered address'
SELECT * FROM CustomerDeliveryAddresses WHERE Email = 'newbusinessadvice@sage.com'
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

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

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

FinancialReportLayouts

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

FinancialYearPeriods

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

NominalCodes

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

NominalReportCategories

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

NominalTransactionAnalysisCodes

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

PendingEmails

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

PriceBands

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

ProductGroups

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

ProductPrices

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Products

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

ProductTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

PurchaseOrderLines

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

PurchaseOrders

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

PurchasePostedTransactions

Purchase posted transactions are created when transactions, such as purchases made, refunds or credit notes, are posted against the Purchase Ledger. It is not possible to use this API resource to create posted transactions.

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesHistoricalTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesOrderAdditionalCharges

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesOrderLines

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesOrderProformaLines

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesOrderQuoteLines

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesOrders

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesPostedTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesTradingPeriods

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SalesTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

StockSettings

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

SupplierContacts

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Suppliers

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

TaxCodes

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

WarehouseHoldings

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Warehouses

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with Sage 200.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Sage 200, along with an indication of whether the procedure succeeded or failed.

Sage 200 Connector Stored Procedures

GetOAuthAccessToken

Gets an authentication token from Sage200.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with sagebca.

Input

Result Set Columns

Sage200 ProfessionalUK Data Model

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

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Sage 200 Connector Views

BankPostedTransactions

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns

Banks

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

Table Specific Information

Select

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

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

For example, the following queries are processed server side:

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

Columns