Sage CRM's RESTful API: SData (Part 4 of 10)

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Sage CRM's RESTful API: SData (Part 4 of 10)

  • Comments 7
  • Likes

In this article I want to consider the way in which the RESTful API allows system administrator to easily extend the reach of the API's access to data across custom entities and views with SData.

It is very useful being able to retrieve data using end points like these,

  • http://[servername]/sdata/[instancename]j/sagecrm/-/company('43')
  • http://[servername]/sdata/[instancename]j/sagecrm/-/company(comp_companyid eq '43')
  • http://[servername]/sdata/[instancename]j/sagecrm/-/company?where=comp_companyid between 43 and 50
  • http://[servername]/sdata/[instancename]j/sagecrm/-/company?where=comp_companyid eq '43'
  • http://[servername]/sdata/[instancename]j/sagecrm/-/company?where=comp_companyid in ('43', '45')
  • http://[servername]/sdata/[instancename]j/sagecrm/-/company?where=comp_updateddate gt @2014-03-14@
  • http://[servername]/sdata/[instancename]j/sagecrm/-/quoteitems?where=quit_productid gt 3 and quit_productid le 10
  • http://[servername]/sdata/[instancename]j/sagecrm/-/quoteitems?where=quit_productid gt sign(-3)
  • http://[servername]/sdata/[instancename]j/sagecrm/-/quoteitems?where=quit_quantity eq abs(-2)

But these examples above are only showing information from fixed entities within Sage CRM and although it is possible to be sophisticated with the query we ask by using predicates and restrictive clauses, ultimately we can not derive new data or do much calculation within the SData request.

Database views on the other hand can be used to derive, calculate, join and transform data from multiple tables into a single source.

The usage of SData continues to open up new possibilities for exchanging data between Sage applications and 3rd party systems. Developers working with Sage CRM can control how SData can allow data from custom entities and views to be published to the web by a simple click.

The image above shows the creation of a view in the context of the Person entity. The view is called vSDataPersonSearch and has been marked as available to SData.

This view then is available as an endpoint within SData.

Using the current SData 1.1 way of working we can establish the columns and detailed available by requesting the schema.

Note: This is an area that is changing within SData 2.0 and I will discuss that in a future article.


An SData URL is therefore available for an external application to call. Not just for the schema but for the application data that sits in the tables.

Building views that are then exposed to SData and the RESTful API masks a huge amount of complexity and allows us to retrieve information that would otherwise be inaccessible to an external system.

If we consider the view above and just use SQL then we should be able to see that it can be used to retrieve contacts where the city is ‘New York’ and we can have the list returned in order of the person's last name.

[code language="sql"]
SELECT DISTINCT Pers_PersonID, Pers_LastName, Pers_FirstName, Comp_Name, Pers_PhoneFullNumber, Pers_EmailAddress, pers_secterr, Pers_AccountId, UPPER(Pers_LastName) FROM vsdatapersonsearch WHERE addr_city LIKE N'New York%' ESCAPE '|' AND Pers_PersonID IS NOT NULL ORDER BY UPPER(Pers_LastName)

Using SData then we can fetch the same data in the same order with a very simple URL.

  • http://[servername]/sdata/[installname]j/sagecrm/-/vsdatapersonsearch?where=addr_city eq 'New York'&orderBy=Pers_LastName

If you want to read more about the creation of views and how these can be exposed through SData then please read the article "Using SData to expose Group-like Data to an External Application in Sage CRM".

In my next article I will consider security within the RESTful APIs.

The articles in the series are:

  1. Sage CRM's RESTful API, An Introduction
  2. Architecture and Web Services
  3. Working with an instance of Sage CRM
  4. Extending query access to custom entities and views in SData
  5. Thinking about security
  7. Limitations of SData 1.1
  8. Compatibility as a priority
  9. What is JSON?
  10. SData 2.0 CRUD
  • Jeff, you mention "This is an area that is changing within SData 2.0 and I will discuss that in a future article" regarding views. Is there an article in relation to this?

  • Caili

    Jump straight to

  • Hi Jeff,

    I'm trying to retrieve all cases of a company that are currently in progress. The query I'm using is the following:

    Company('myCompanyID')/Cases?where=Case_Status eq 'In Progress'

    Unfortunately, this only returns the cases that are assigned to the user that is used for accessing the API.

    What am I missing?

    Thank you!


  • Alex

    I think you will find that the request is return only those cases that the user (whose credentials have been applied) is authorised to see.  That may be only those cases to which the users is assigned.

  • Thank you for replying so fast !!

    The user has an unrestricted profile so it should be allowed to see pretty much anything on the system.

    Furthermore, the following query returns all cases for the company:


    Somehow adding the where clause for the status is making the system add further filtering by the current user assignment.



  • Alex

    What version are you using?  Are you requesting SData 1.1 or SData 2.0?

    I have just tried with the default system using the standard demo data.  I have used both the Admin and WardK profiles.

    http://localhost/sdata/crmj/sagecrm2/-/company('41')/cases?where=Case_Status eq 'In Progress'

    The results were being returned as I expected.  I think if this persists can you open a forum discussion and also log a case with your local support team.

  • Jeff,

    Thank you again for the quick reply.

    I'm using an on premise installation of Sage CRM 2018 R3 with SData 2.0 as per the full path of my requests:

    https://myServer/sdata/myInstanceNamej/sagecrm2/-/Company('myCompanyID')/Cases?where=Case_Status eq 'In Progress'


    Will make a post on the user community.

    Please do let me know if there is something else I should try.