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.

http://[servername]/sdata/[installname]j/sagecrm/-/vsdatapersonsearch/$schema

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)
[/code]

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
6. SOAP CRUD vs REST CRUD
7. Limitations of SData 1.1
8. Compatibility as a priority
9. What is JSON?
10. SData 2.0 CRUD