How to Get Aggregate Results with REST API

​REST API in Calem allows one to fetch data from a data table such as work orders, assets and locations. In release R2019e the REST API allows one to fetch aggregate results from a data table. 

Prior to R2019e one can fetch a data list through a where, order by and limit clauses ("w", "o", and "l"):

w	URL encoded query string used in “where” clause in a MySQL query. For instance, 
cm_asset.asset_no like 'pump%' and cm_asset.last_modified>='2014-12-11'

o	URL encoded order by used in “order by” clause in a MySQL query. For instance,
asset_no DESC

l	URL encoded limited fetch used in the “limit” clause in MySQL query. 
The format is “5,10” (start from 5 and count of rows to fetch is 10).
 

In release R2019e two more clauses are added ("cs" and "g"):

cs	Custom selection of fields. For instance, 
“cm_wo.category_id, count(*) as count, round(sum(cm_wo. actual_labor_hours), 2) as lab_hours”


g	Group by. For instance, “cm_wo.category_id”
 

​Here is a PHP code snippet to get total PM work order count from Calem.

//PM work orders are included
$w=urlencode("ifnull(cm_wo.is_planned, 0)=1");
//Aggregate count as result
$cs=urlencode("count(*) as count");
      
$response = \Httpful\Request::get('https://acme.calemeam.com/api/search/cm_wo?'
    .'w='.$w.'&cs='.$cs)
  ->addHeader('Authorization', $auth)->send();
//Results JSON objects
$res=json_decode($response, true);
//Results JSON: {"status":0,"count":1,"data":[{"count":"16167"}]}
 

Additional resources

• Calem Integration with REST API
• Calem Enterprise REST API Guide (customer account required)
• User Guide and Admin Guide (customer account required)
• Calem Enterprise Training Site
  
• Calem Enterprise Blogs
  
• Calem Enterprise demo