SData Query Language - Examples Available for Download

Hints, Tips and Tricks

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

SData Query Language - Examples Available for Download

  • Comments 5
  • Likes

The SData 1.1 Standard provides a comprehensive query language that can filter and restrict data in any way that is needed. Sage CRM uses SData feeds within its own Interactive Dashboard gadgets and can consume feeds from external Sage applications. In turn those External Applications can also access read only SData feeds that expose data from Sage CRM tables and views.

I have written a series of articles about using SData for development in Sage CRM.

The SData query language provides for the filtering of data using a where query parameter.

The SData conditions are used within the Sage CRM client side API as part of the Ajax capabilities

I have discussed the easy to use Ajax API option in Sage CRMin a pair of articles.

The online documentation can be used to understand how to build the query conditions passed within the SData URLs, but it is typically much easier to understand what can be done by viewing examples.

The new file available for developers contains Sage CRM specific examples of the use of the SData query language conditions.

e.g.

  • http://localhost/sdata/crmj/sagecrm/-/person?where=concat(pers_firstname, pers_lastname) eq 'William Agnew'
  • http://localhost/sdata/crmj/sagecrm/-/company?where=comp_updateddate gt @2014-03-14@
  • http://localhost/sdata/crmj/sagecrm/-/company?startIndex=1&count=10&where=comp_name like 'A%' or comp_name like 'C%'
  • http://localhost/sdata/crmj/sagecrm/-/quoteitems?where=quit_productid gt 3 and quit_productid le 10

Note: The examples in the file assume that the instance of Sage CRM being used is called 'CRM' and is being referenced locally.

Members of the developer program can download the file from the folder "General Development Resources (All Versions)".

Comments
  • In the last line of this post the link for  - Members of the developer program can download the file from the folder "General Development Resources (All Versions)". seems to be broken.

  • The link is working but you are getting the message because your community account doesn't have the all the rights you need to view the post.

  • How does one query by a boolean/checkbox field? Thanks

  • REST API does not allow for searching for null values nor Booleans.  I have logged an enhancement request.

    The Sage CRM REST API is a development of the SDATA.  The Query Language for SDATA is described here:

    sage.github.io/.../0212

    This forms the basis of the way in which we can request data from Sage CRM via the REST API.

    The SData 2.0 syntax should support Boolean for JSON returned values.

    sage.github.io/.../05-0700

    This is true for the Sage CRM REST API

    The information transported through JSON is always in the form of the following basic types:

    strings: double-quoted Unicode (UTF-8 by default), with backslash escaping

    numbers

    Boolean values: true or false

    null

    See:  community.sagecrm.com/.../sage-crm-2019-r2-working-with-data-and-the-rest-api.aspx

    But

    SData and the REST doesn't seem to have a way of finding records by null values in fields nor does it have a way of selecting for Boolean values.

    We can look for null values for Strings using a 'trick'.

    e.g. Find all those companies without a 'source' listed

    http://localhost/sdata/crmj/sagecrm2/-/company?where=comp_source ne ''

    or

    http://localhost/sdata/crmj/sagecrm2/-/company?where=comp_source gt ''

    But we can not search for 'Booleans'.

    E.g.

    http://localhost/sdata/crmj/sagecrm2/-/company?where=comp_promote eq true

    Returns

    {

    "$diagnoses": [

     {

    "$severity": "Error",

    "$applicationCode": "sdata648497",

    "$message": "BadRequest ",

    "$stackTrace": "sdata648497",

    "status": "BAD_REQUEST",

    "$sdataCode": "ApplicationDiagnosis"

    }

    ],

    }

  • Thank you for the extended explanation. I saw the documentation myself but the lack of support for something like that was rather unexpected so I thought I'd better ask about it to be sure.