Using SData 2.0 in Sage CRM: Part 1 - Introduction and Querying

A perhaps lesser-known feature of Sage CRM is the SData RESTful API. This API was initially developed to allow developers read-only access to the Sage

A perhaps lesser-known feature of Sage CRM is the SData RESTful API.  This API was initially developed to allow developers read-only access to the Sage CRM database for use in other applications (SData 1.1), but Sage released a newer version of SData (SData 2.0) that allowed not only querying data from Sage CRM, but also inserting new records, updating existing records and deleting records in the Sage CRM database.

This guide will take you through the features provided in SData 2.0.  It has been split into multiple articles as SData 2.0 has a massive amount of functionality and it will be way too much to cover in a single post. You can find links to the other articles at the bottom of this article.

SData 1.1 vs SData 2.0

As mentioned in the introduction, SData 2.0 was released more recently and provides additional functionality that the old version (SData 1.1) did not provide.  Both versions are available in newer versions of Sage CRM and you can use either version. I highly suggest you make use of SData 2.0 though as it has the same features as SData 1.1 as well as other, more powerful features.  If you would like to read more about the SData 2.0 rewrite, you can read the following article on the SData Specification website.

Before we begin, I recommend you get the free version of Postman if you don't have it already.  Postman is a powerful tool that lets you configure and send various REST calls to an API endpoint and it will drastically improve your experience while working with the SData 2.0 API.  

The SData 2.0 URL

The base URL for SData will vary based on your install name and server.  The URL will have the following format (substitute your server name and install name - and that j after the install name is meant to be there!):

http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/

If you navigate to the URL and are prompted for credentials (such as below) then your SData 2.0 should be working fine:

If you receive any errors, then you will have to fix your Tomcat settings as the Tomcat service is used to host SData 2.0 (I am hoping to post about fixing Tomcat soon).

Querying Data

Before we start making changes to the Sage CRM database, let's go over some basics for querying data from Sage CRM using SData 2.0.  For these examples we will be querying the Companies in the Sage CRM demo data. This means if you have the demo data installed you should be able to reproduce these results in the examples.

Retrieving list of all companies

The most basic query we can make is to simply return a list of records of a particular entity.  This requires no WHERE clause and we simply need to specify the entity name (in this case Company).

In Postman, enter the following REST call to query a list of Companies and see what data is returned (replace {Server Name} with your Sage CRM server name and {Install Name} with your Sage CRM installation name):

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company

You will need to provide credentials in order to make the REST call.  In Postman, go to the Authorization tab, select Basic Auth from the Type dropdown and enter your Sage CRM credentials.

When you Send the request you should get a response similar to the following:

You can see that in the response we are first given some metadata about the response such as links to the $first page, $next page etc.  We also see that it only returned 10 items.  This is to prevent excessive data transfer.  If we want to get the next 10 items, we can query the $next URL.

In the $resources we see the holy grail of the response - the company array!

Retrieving a particular company by ID

SData 2.0 provides us with super simple syntax for getting the details for a single record of an entity using just its ID.  In this case, I will query the company with the ID of 19:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company('19')

As you can see, we simply place ('{ID}') after the entity name in the URL to retrieve the particular company.

When sending this, you should get a response similar to the following:

The format of the response is slightly different as it only returned data for a single company.

Retrieving all people associated with a particular company

Sage CRM makes it easy to retrieve all the Person, Address, Phone, Library, Case, Opportunity and other entity records associated with a particular company.  If we have selected the company, it provides us with a convenient syntax to select any of these associated records.

In this example we want to select all the Person records associated with the company.  To do this we are going to use the query to select the particular company (as above) but append /Person to the end to get all the Person records belonging to that company.  Let's call the following URL in Postman:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company('19')/Person

The response should look somethings like this:

You can see that the $resources now contain a collection of all the person records associated to the company.

Retrieving companies matching a particular criteria

When querying an entity, we are able to provide certain criteria in our query to narrow down our search.  This is all done through the ?where= URL parameter of our query.  We will cover a few examples of using the where parameter, but you can find documentation for all the different operators available on the SData 2.0 Specification website here (take a look at what's available - it's really powerful).

Retrieve all suppliers

In this example we will retrieve all companies who have a Type of Supplier.  In order to do this, let's call the following URL:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company?where=comp_type eq 'Supplier'

A word of warning: Postman will automatically make sure the URL gets encoded correctly, but when using the SData 2.0 API in your own client, make sure that the URL is getting encoded correctly to avoid any errors.

When we Send the request, we should see a response similar to the following:

You can see that only three results were returned.  If we scroll through the data returned, we can see that all the companies are Suppliers meaning it worked!

Retrieve all prospect companies with a revenue of $1 million - $5 million

Let's take it up a notch. We are now going to query all prospect companies in the system with a revenue of $1 million - $5 million.  This will require us to use an and operator to combine the two conditions.  To do this, we will call the following URL:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company?where=comp_type eq 'Prospect' and comp_revenue eq '$1M_$5M'

When you Send the request, you should get a response similar to the following:

In the metadata, you can see that only six records were returned.  If we scroll through the results, you will see that our query worked again! Woohoo!

Retrieve all companies in the Finance and Software sector

In this last example, we will retrieve all the companies that are in the Finance and Software sector. For this example, we will make use of the in operator to check if a value is contained within a specified set of values. To do this, call the following URL:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company?where=comp_sector in ('Software', 'Finance')

When you Send the response, you should get a response similar to the following:

When you scroll through the Company records returned, you should see that they are all part of Software or Finance sector, hence our query worked!

Closing

I hope this article has shed some light on the powerful features that SData 2.0 provides.  In the coming articles we will be unpacking other features such as modifying data in Sage CRM so stay tuned.  If you have any questions regarding SData 2.0, please contact me.  I am also keen to hear how you are using SData 2.0 in your projects!