x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

SData Query Language - Examples Available for Download

1 minute read time.

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)".

Parents

  • 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

    localhost/.../company ne ''

    or

    localhost/.../company gt ''

    But we can not search for 'Booleans'.

    E.g.

    localhost/.../company eq true

    Returns

    {

    "$diagnoses": [

    {

    "$severity": "Error",

    "$applicationCode": "sdata648497",

    "$message": "BadRequest ",

    "$stackTrace": "sdata648497",

    "status": "BAD_REQUEST",

    "$sdataCode": "ApplicationDiagnosis"

    }

    ],

    }

Comment

  • 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

    localhost/.../company ne ''

    or

    localhost/.../company gt ''

    But we can not search for 'Booleans'.

    E.g.

    localhost/.../company eq true

    Returns

    {

    "$diagnoses": [

    {

    "$severity": "Error",

    "$applicationCode": "sdata648497",

    "$message": "BadRequest ",

    "$stackTrace": "sdata648497",

    "status": "BAD_REQUEST",

    "$sdataCode": "ApplicationDiagnosis"

    }

    ],

    }

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City