Sage CRM

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

Sage CRM's SData API is a robust RESTful tool that enables seamless integration with external applications. While SData 1.1 introduced read-only access to the Sage CRM database, SData 2.0 takes functionality to the next level by allowing the creation, update, and deletion of records. This guide covers the essentials of SData 2.0, particularly focusing on how to query data from the Sage CRM database.

This post is part of a series that explores the extensive functionality of SData 2.0 in easy-to-understand segments. At the bottom, you'll find links to other parts of the series for further exploration.

Key Differences: SData 1.1 vs. SData 2.0

Both SData 1.1 and SData 2.0 are supported by modern versions of Sage CRM, but SData 2.0 introduces a broader feature set. In addition to the read-only capabilities of SData 1.1, SData 2.0 offers the ability to create, update, and delete records, providing full interaction with your Sage CRM database. For developers and businesses aiming to leverage Sage CRM’s full potential, SData 2.0 is the clear choice.

Getting Started with SData 2.0

To effectively work with SData 2.0, we recommend using Postman, a popular tool for testing API requests. Postman simplifies the process of sending RESTful calls to API endpoints, making it easy to interact with Sage CRM’s SData API.

SData 2.0 URL Structure

The basic structure for SData queries depends on your server setup. The typical format looks like this:

http://{ServerName}/sdata/{InstallName}j/sagecrm2/-/

After navigating to this URL and providing your credentials, you’ll know your SData 2.0 is correctly configured if you see the authentication prompt. If errors occur, they may be due to misconfigured Tomcat settings, as Tomcat is the service hosting the SData API.

Querying Data with SData 2.0

To demonstrate querying data using SData 2.0, we’ll focus on retrieving information from Sage CRM’s company data. These examples can be applied to other data types within Sage CRM.

1. Retrieving All Companies

To retrieve a list of all company records, use this URL in Postman:

GET http://{ServerName}/sdata/{InstallName}j/sagecrm2/-/Company



After authenticating with your Sage CRM credentials, the response will include metadata, such as pagination information, and the first 10 company records. The response will look like this:

{

  "$resources": [

      {

         "companyID": "19",

         "companyName": "Example Company",

         ...

      },

      ...

  ]

}

2. Retrieving a Specific Company by ID

To retrieve details of a specific company by its ID, append the company ID to the URL:

GET http://{ServerName}/sdata/{InstallName}j/sagecrm2/-/Company('19')



This query will return data specific to the company with ID 19.

3. Retrieving People Associated with a Specific Company

To fetch all Person records related to a specific company, extend the query like this:

GET http://{ServerName}/sdata/{InstallName}j/sagecrm2/-/Company('19')/Person



The response will include all people associated with the company ID 19.

Querying Companies by Criteria

One of the powerful features of SData 2.0 is the ability to filter data using a where clause. Below are a few examples of how to query companies based on specific conditions.

Example 1: Retrieving All Suppliers

GET http://{ServerName}/sdata/{InstallName}j/sagecrm2/-/Company?where=comp_type eq 'Supplier'



This query retrieves all companies categorized as Suppliers.

Example 2: Retrieving Prospect Companies with Revenue Between $1 Million and $5 Million

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



This filters prospect companies with revenues between $1M and $5M.

Example 3: Retrieving Companies in the Finance and Software Sectors

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



This query fetches companies operating in the Software and Finance sectors.

Conclusion

The SData 2.0 API offers a comprehensive toolset for integrating and managing Sage CRM data. From simple data queries to full CRUD (Create, Read, Update, Delete) operations, SData 2.0 provides flexibility for businesses and developers seeking to streamline their operations or build custom integrations.

In the next part of this series, we’ll dive deeper into modifying and updating data within Sage CRM using SData 2.0. Stay tuned for more insights into how SData 2.0 can unlock new possibilities for your Sage CRM implementation.