Contents:IntroductionThe Sage 200 API allows developers to create mobile, web and desktop applications quickly and securely, on any platform using the technology of your choice. Harnessing the power of the Sage 200 Professional and Standard Online business management solutions. This guide is intended to familiarise developers with our API documentation, as well as advise on constructing API queries through use of various query parameters and JSON request bodies.  NOTE: Although the Sage 200 API is non-language specific and can therefore be implemented in a variety of solutions Sage do not support the implementation, troubleshooting or debugging of applications that make use of the Sage 200 API programmatically.
Postman CollectionsBelow we have provided a collection of API request examples to show you what can be achieved in Postman when using the Sage 200 API documentation. Please feel free to download this and use in your development using the API. Pre-RequisitesThis section will cover the pre-requisite requirements to begin developing for the Sage 200 API. This includes: - Setup and Configuration of the Sage 200 API
- Creation and Registration of your Sage ID
- Acquisition of a Developer Subscription Key
Setup and Configuration In order to query information via the Sage 200 API, your Sage 200 site must be setup for use with the Sage 200 API. This will vary based on the method you are using to expose your site to the API and the variant of Sage 200 you are using. Please refer to the relevant section of links to further information on your product variant and connection method. Sage 200 Standard Online The Sage 200 API is enabled by default in Sage 200 Standard Online and therefore any users assigned to the site in Sage BMS Online Services will automatically be configured as an API user. Further information on this process can be found here. Sage 200 Professional As Sage 200 Professional is installed in a local environment, you will need to allow connection from external sources to your site. Due to the potential security risks involved, there are 2 methods that can be used to allow external access for the Sage 200 API. Native API The Sage 200 Native API is an API connection method which uses Microsoft 365 Azure Active Directory tunneling to serve external API requests. It does not require any additional hardware or security configuration on your site, simply a valid Microsoft 365 subscription. This is therefore the suggested connection method for customers using Sage 200cloud Professional Spring 2018 and above. Further information on setup, and configuration, of the Sage 200 Native API can be found here. API via Windows Authentication As of Sage 200 Professional Summer 2018 and above, you can use a new local API development method which mirrors the APIM authentication of the Native API method. You do not require Client ID and Secret keys, as all authentication is done locally in IIS with Windows Authentication. This can be set up alongside the Native API method. More information can be found here. Sage ID A Sage ID is required to authenticate all API requests, as well as request a valid access token, and must therefore be created and registered for use with the Sage 200 API before you are able to make a valid request. This will likely have been done during the setup and configuration of your Sage 200 site, however, if not, please see here for further information on creating a Sage ID and here to register your Sage ID for use with the Sage 200 API. Developer Subscription Key Your developer subscription key is a unique ID which is used when making API calls against your account. It is therefore important that these keys are not shared. To acquire your subscription key, you will need to sign up to the Sage API Developer Community and subscribe to the relevant API. This will allow you to generate a Primary and Secondary key on your account. For further information on acquiring your developer subscription key please see here. AuthenticationAll requests made the Sage 200 API must contain a valid access token as part of the authentication header. This section will provide basic information on client credentials as well break down the different methods available to generate an access token. For a more detailed explanation of the Sage 200 API authentication process, see our Authenticate with the Sage 200 API guide, available here. Client Credentials The Sage 200 API supports 2 client types for authentication, Confidential Clients (Web) and Public Clients (Desktop). The type of credentials which you use will vary based on your applications construction and will be used, in combination with your Sage ID, to request a valid access/refresh token. To request development/production credentials, please complete our request form. Access & Refresh Tokens Access tokens are used to authenticate your API request and without one your requests will be denied. Access tokens are set to expire after a specified amount of time, 480 minutes, however, a refresh token can be used to remove the need for the user to sign in each time their access token expires. Refresh tokens have a much longer expiry time than Access Tokens, with a maximum of 90 days, and can be used to generate a valid Access Token, if the Refresh Token has not expired. If the Refresh Token has also expired than a user must login with a valid Sage ID to request a new Access Token and Refresh Token. Please note that the Sage ID, which is used to authenticate the request, authenticating user, determines the operations, including methods and endpoints, available to you. Further information on user roles and features can be found in the relevant application documentation. Generating an Access Token There are various methods of generating an Access Token based on your Development needs: Programmatically We have created a C# sample application and client library to demonstrate how to programmatically request an access token for a Windows Form Application (Public Client) and a Website (Confidential). These are available for download here. This page also details the changes required to update the Web and Desktop sample solution to use your Sage 200 API client credentials. NOTE: Please be aware though that Sage Developer Services only support the API endpoints and not the implementation, troubleshooting or debugging of applications that make use of the Sage 200 API. This is further explained in our support boundaries which can be found here. Postman When testing and developing your API application you may wish to use Postman, an open-source API development environment, which allows you to authenticate and run API queries without needing to program an application, or website. The Get New Access Token option within Postman’s Authorization tab allows you to request an access token for your API request by entering your Confidential client credentials. Developer Services have also produced a utility which allows you to generate an access token using your Public client credentials, which can be copied into the Postman Authorisation header and used to query the Sage 200 API. These methods are further explained in our guide to Using Postman with the Sage 200 RESTful API, see here. NOTE: Postman does not support the use of a refresh token as part of the authentication process and will therefore require a new access token be requested upon expiry of the existing access token. Querying the Sage 200 APIEndpoint Documentation The Sage 200 API documentation contains a list of the available endpoints, support methods and query parameters for Sage 200 Standard Online and Professional. Prior to constructing your API requests, please ensure the endpoint is available in your variant of Sage 200 and the verb you are going to use is supported for that specific endpoint.  TIP: Unless otherwise stated, the endpoints used in the following sections are specific to Sage 200 Standard Online. Please review the Sage 200 Professional API documentation for the appropriate endpoints for this variant. All the endpoints presented in this article are, however, present in Sage 200 Standard Online and Professional.
Site and Company Information In order to make database specific requests via the Sage 200 API, you need to specify the Site ID (X-Site) and Company ID (X-Company) within your request header. To acquire these, a GET request can be ran to sites endpoint to show information for all sites the authenticating user has access to. Please see below an example request and response to the sites endpoint for Sage 200 Standard Online, see here. For Sage 200 Professional, replace the endpoint URL with that provided in Sage 200 Professional API documentation, see here. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/sites
Response: [
{
"company_id": 123,
"company_name": "APITestCompany",
"tenant_id": 1234,
"site_id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
"site_name": "Demodata",
"site_short_name": "xxxxxxxxxx"
}
]
Supported MethodsThe Sage 200 API supports 4 methods, POST, GET, PUT and DELETE, which correspond to the create, read, update and deleted (CRUD) operations respectively. Prior to querying a Sage 200 API endpoint you must first ensure that the method, or verb, you are going to use is supported on this endpoint. This information can be found in the supported verbs section for the endpoint in the relevant Sage 200 API documentation. For example, the customers endpoint currently supports the GET and POST methods when a record ID is not specified as part of the endpoint URL and the GET, PUT and DELETE methods when it is included. The following sections detail how to use each of these methods, in combination with query parameters and JSON request bodies, where applicable, to query the customers endpoint for Sage 200 Standard Online. TIP: For Sage 200 Professional simply replace the base URL with that detailed in the Sage 200 Professional API documentation, see here. GET RequestA GET request allows you to read information from a specific endpoint, e.g. customers, and return this in the response body of the request. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers
Response: This request will return information for all customer records in the response body. For performance purposes this is limited to the first 500 records; we will discuss how to extend this later in this section. Should you know the ID of the customer record you wish to query, you can append this to the end of your customers endpoint URL and return information for that specific record. Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers/123
NOTE: The customer ID relates to the unique reference number applied to the record in the Sage 200 database and is therefore not surfaced within the application. We therefore suggest that the $filter query parameter is used for a known unique field value, such as the customer reference, see below. Query Parameters You can add query parameters to your request URL to influence your API response. The following section will demonstrate how to use, and combine, some of the more common parameters. A full list of the supported query parameters can be found here. $filter The $filter parameter allows you to return record which match your specified criteria. You can user more than one $filter as part of your request by including the and/or operators. TIP: This parameter is commonly used to return information for a specific record when the ID is not known. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$filter=reference eq 'A1D001'
Response: This will return record information for the customer record which has a reference of A1D001. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$filter=reference eq 'A1D001' or name eq 'APITest'
Response: This will return record information for the customer record which has a reference of A1D001 or a name of APITest. $select The $select parameter allows you to limit the fields which are returned in the response of your request. In the following example we combine this parameter with the $filter parameter to return the ID of one of our customer records. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$filter=reference eq 'A1D001'&$select=id
Response: [
{
"id": 27825
}
]
$top The $top parameter allows you to limit the number of records returned in your response body. As previously advised, when this value is not specified the Sage 200 API will limit requests to 500 records, however, for versions of Sage 200 released after February 2019 you can return up to 5000 records, as demonstrated below. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$top=5000
NOTE: If an $orderby parameter is not specified the endpoint’s default will be used. $orderby The $orderby parameter allows you to order the results in your API response body, in both ascending and descending order. In this example we are going to use the $top query parameter to increase the number of records returned to the maximum of 5000 and order the results in descending order by the customer’s balance. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$top=5000&$orderby=balance desc
$expand The $expand parameter allows you to include the content of the record’s sub records in your response body. Example Request: GET - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers?$filter=reference eq 'A1D001'&$expand=contacts
POST RequestsA POST request allows you to create a record using the Sage 200 API. As you are creating a new instance of a record, POST requests do not support the inclusion of an existing ID or query parameters in the request URL. The following example will demonstrate how to create a customer record in Sage 200 Standard Online, with the inclusion of some optional fields. Required fields for a POST requests to an endpoint can be found in the Body Fields section of that endpoints API documentation. For the customers endpoint the required fields for a POST request are the reference and name fields. Example Request: POST - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers
JSON Request Body: {
"reference": "APITEST",
"name": "API Test 02",
"short_name": "APITEST2",
"on_hold": false,
"account_status_type": "AccountStatusActive",
"currency_id": 2103,
"telephone_country_code": "00",
"telephone_area_code": "1234",
"telephone_subscriber_number": "567 8899",
"website": "www.apitest01.co.uk",
"credit_limit": 2500.00,
"country_code_id": 13,
"default_tax_code_id": 1729,
"contacts": [
{
"salutation_id": 0,
"name": "John Smith",
"first_name": "John",
"last_name": "Smith",
"is_default": true
},
],
}
Response: A successful POST request will return a 200 (OK) HTTP status and the response body will show the record information for your created record, including fields which were not populated by your initial POST request. NOTE: The Sage 200 API follows the same rules for record creation as the Sage 200 application. Therefore, you attempt to re-run your POST request, you will receive a 400 (Bad Request) HTTP status with the response body advising that "The account reference already exists. You cannot have two accounts with the same account reference.”. PUT RequestsA PUT request can be used to update information on an existing record. As such you must specify your record ID as part of your request URL. As such, only the information which you intend to update needs to be passed as part of the request body. TIP: Further information on using the $filter query parameter to find a record’s ID can be found in the GET Requests section of this article. The following example demonstrates how to update the short name of the customer record used in our GET Requests $select example. Example Request: PUT - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers/27825
JSON Request Body: {
"short_name": "APIDemo"
}
Response: A successful PUT request will return a 200 (OK) HTTP status and the response body will show all record information, including the updated field, for the ID specified in the PUT request URL. DELETE RequestsThe DELETE method can be used to delete a record from Sage 200. You must therefore specify the record ID as part of your request URL. As the Sage 200 API follows the same rules for deletion as the Sage 200 application, you will only be able to delete a record via the Sage 200 API that can also be deleted in the Sage 200 application. The following example demonstrates how to delete a record via the Sage 200 API. Example Request: DELETE - https://api.columbus.sage.com/uk/sage200/accounts/v1/customers/98839
Response: A successful deletion will return a 200 (OK) HTTP status, with a blank response body. Re-running the DELETE request will return a 404 (Not Found) HTTP status, as the record is no longer present in the database. Should you attempt to delete a record, via the Sage 200 API, that cannot be deleted in the Sage 200 application, you will receive a 400 (Not Found) HTTP status code. The response body will contain the reason why the record cannot be deleted, i.e. {
"Message": "This account has \"Customer Delivery Addresses\" in the SOP or Invoicing module, which must be removed first."
}
What’s NextAlthough this article only references the sites and customers endpoint, the Sage 200 API has a wide variety of available endpoints, exposing several areas of the application. A full list of available endpoints, including the methods (verbs) which they support, and required fields for POST requests, can be found in the API documentation for your Sage 200 program: You may also wish to review our in-depth guide to constructing an API header, see here, as well as our guide to using Postman to query the Sage 200 API, see here. Having IssuesIf you are encountering issues when configuring or querying the Sage 200 API, please see our Common Queries and FAQ article, see here, where we have collated common questions, and answers, from our Business Partner and Developer communities. If you require further assistance, please review our API Set Up & Responsibilities article, see here, for further information on who you should contact. NOTE: Sage Developer Services do not provide support with the implementation, troubleshooting or debugging of applications that make use of the API programmatically. |