Introduction
The Sugar API is easy to use and powerful. With the API, Sugar can talk to any external systems: BI systems, ERP, ETL, ESB, custom applications, etc.
I wrote this post in order to help people who need a quick ramp up in writing custom code.
This blog will show how to retrieve records based on:
1- a record id
2- a filter on a module
3- a sql query on a module
4- a report
Quick takeaway
The Sugar API let you make awesome queries that you cannot do with other CRM systems.
Before we start
I recommended to check my 2 others posts related to this topic:
1- SugarCRM: SOAP and REST calls using PHP
2- SugarCRM+Salesforce: Live data synchronization between Salesforce.com and SugarCRM
LOG IN YOUR INSTANCE
Today I will show you how to retrieve records using the API. I wrote the code samples in PHP, you may run them from the command line or add them to a web application.
We are working with REST web services. First of all, you need to log in with your credentials. The Sugar API does not need your password. You will use a md5 transformation. To get your md5 password, run from the command line:
php -r "echo md5('yourPassword').\"\\n\";"
Here the code to log in your instance. We will start all the PHP codes with this bloc.
Define the verbose level (none, debug, info):
use the || boolean operator to combine several log levels. This log level is define in my code and is not related to PHP or to the Sugar framework.
define('NONE', 0);
define('DBG', 1);
define('IFO', 2);
//$dg = IFO || DBG;
$dg = IFO;
In this example my server name is sugarcrm.ubuntu1.
I am calling Sugar API version 4_1 (read "What Version of the API Should I Be Using?" to get your flavor) //
// LOGIN
//
$url = 'http://sugarcrm.ubuntu1/service/v4_1/rest.php';
$curl = curl_init($url);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_HEADER, false);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
$parameters =
array(
"user_auth" =>
array(
'user_name' => "admin",
'password' => "0192023a7bbd73220516f0ffdf18b532",
'version' => "0.1"
),
"application_name" => ''
);
$json = json_encode($parameters);
$postArgs = 'method=login&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if(!is_object($result)) { die("Connection error\n"); }
Check if the login succeeded
if ($dg&DBG) { print_r($result); echo "\n"; }
if (isset($result->number)) { // Invalid Login?
echo $result->name . "\n";
exit;
}
Get the session id
$sessionid = $result->id;
$userid=$result->name_value_list->user_id->value;
if ($dg&DBG) echo "sessionid=$sessionid\n";
if ($dg&DBG) echo "userid=$userid\n";
EXAMPLE 1: retrieve a record based on its ID
Copy/paste the log in code. We need the $sessionid value to make the core call.
We are making the API call get_entry. We already know the record ID. It is an account. We want the API to send back the account name and the shipping city.
//
// Get 1 account (540c66a3-de28-790b-e3b9-4fc9c84d6880)
//
// get_entry(session, module_name, id,select_fields, link_name_to_fields_array)
$parameters = array(
'session' => $sessionid,
'module_name' => 'Accounts',
'id' => '540c66a3-de28-790b-e3b9-4fc9c84d6880',
'select_fields' => array('name','shipping_address_city')
);
$json = json_encode($parameters);
$postArgs = 'method=get_entry&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
if ($dg&IFO) echo "response json=$response\n";
$result = json_decode($response);
if ($dg&IFO) print_r($result);
curl_close($curl);
Print out the result in CSV style:
echo "id;name;city\n";
foreach ($result->entry_list as $i) {
echo $i->id.";";
echo $i->name_value_list->name->value.";";
echo $i->name_value_list->shipping_address_city->value."\n";
}
Sample output:
id;name;city
540c66a3-de28-790b-e3b9-4fc9c84d6880;SugarCRM Inc.;Cupertino
What we are sending & receiving? This serialized data, based on Json.
Sugar's response in Json is:
{"entry_list":[{"id":"540c66a3-de28-790b-e3b9-4fc9c84d6880","module_name":"Accounts","name_value_list":{"name":{"name":"name","value":"SugarCRM Inc."},"shipping_address_city":{"name":"shipping_address_city","value":"Cupertino"}}}],"relationship_list":[]}
In a human readable form (using print_r):
stdClass Object
(
[entry_list] => Array
(
[0] => stdClass Object
(
[id] => 540c66a3-de28-790b-e3b9-4fc9c84d6880
[module_name] => Accounts
[name_value_list] => stdClass Object
(
[name] => stdClass Object
(
[name] => name
[value] => SugarCRM Inc.
)
[shipping_address_city] => stdClass Object
(
[name] => shipping_address_city
[value] => Cupertino
)
)
)
)
[relationship_list] => Array
(
)
)
the property entry_list in an array. We are looping on it. To get the account name, the PHP code is:
$i->name_value_list->name->value
EXAMPLE 2: retrieve records in a module, based on a filter
We want to select all the accounts where the name starts with the letter 'S'.
The SQL query is:
select id,name from accounts where name like 's%';
We use the core call get_entry_list.
Remark: as Sugar is making a soft delete of the records, we need to specify that we are filtering on the existing records with:
'deleted' => '0'
Here is the code, straightforward:
//
// Get accounts "select id,name from accounts where name like 's%'"
//
//get_entry_list(session, module_name, query, $order_by,offset, select_fields,
// link_name_to_fields_array, max_results, deleted)
$parameters = array(
'session' => $sessionid,
'module_name' => 'Accounts',
'query' => "name like 's%'",
'name',
'offset' => 0,
'select_fields' => array('id', 'name'),
'link_name_to_fields_array' => array(),
'max_results' => '100',
'deleted' => '0'
);
$json = json_encode($parameters);
$postArgs = 'method=get_entry_list&input_type=json&response_type=json&rest_data=' . $json;
echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if ($dg<=IFO) print_r($result);
curl_close($curl);
echo "id;name\n";
foreach ($result->entry_list as $i) {
echo $i->id.";";
echo $i->name_value_list->name->value."\n";
}
EXAMPLE 3: retrieve records using a SQL query on a module
We are synchronizing with an external system and need to know which accounts had been modified in the past 3 days. The SQL query is:
select id,name from accounts where date_modified > DATE_ADD(NOW(), INTERVAL -3 DAY);
We do not need to handle date format or date construction, the database is making the job. We are using the core call get_entry_list.
Here is the code:
//
// Get accounts "select id,name from accounts where date_modified > DATE_ADD(NOW(), INTERVAL -3 DAY)
//
//get_entry_list(session, module_name, query, $order_by,offset, select_fields,
// link_name_to_fields_array, max_results, deleted)
$parameters = array(
'session' => $sessionid,
'module_name' => 'Accounts',
'query' => "date_modified > DATE_ADD(NOW(), INTERVAL -3 DAY)",
'name',
'offset' => 0,
'select_fields' => array('id', 'name', 'date_modified'),
'link_name_to_fields_array' => array(),
'max_results' => '100',
'deleted' => '0'
);
$json = json_encode($parameters);
$postArgs = 'method=get_entry_list&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if ($dg&IFO) print_r($result);
curl_close($curl);
echo "id;name\n";
foreach ($result->entry_list as $i) {
echo $i->id.";";
echo $i->name_value_list->name->value.";";
echo $i->name_value_list->date_modified->value."\n";
}
// Get accounts "select id,name from accounts where date_modified > DATE_ADD(NOW(), INTERVAL -3 DAY)
//
//get_entry_list(session, module_name, query, $order_by,offset, select_fields,
// link_name_to_fields_array, max_results, deleted)
$parameters = array(
'session' => $sessionid,
'module_name' => 'Accounts',
'query' => "date_modified > DATE_ADD(NOW(), INTERVAL -3 DAY)",
'name',
'offset' => 0,
'select_fields' => array('id', 'name', 'date_modified'),
'link_name_to_fields_array' => array(),
'max_results' => '100',
'deleted' => '0'
);
$json = json_encode($parameters);
$postArgs = 'method=get_entry_list&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if ($dg&IFO) print_r($result);
curl_close($curl);
echo "id;name\n";
foreach ($result->entry_list as $i) {
echo $i->id.";";
echo $i->name_value_list->name->value.";";
echo $i->name_value_list->date_modified->value."\n";
}
EXAMPLE 4: retrieve records using a report
You need a commercial edition of Sugar.
We want to run a report using the API. The report is a standard report called "All Open Opportunities". We are using the core call get_report_entries and provide the report ID and the fields.
//get_report_entries(session,ids,select_fields)
// report Id=3be50900-e1bb-2da8-be27-4fc769f9f8c1, Name="All Open Opportunities"
$parameters = array(
'session' => $sessionid,
'id' => array('3be50900-e1bb-2da8-be27-4fc769f9f8c1'),
'select_fields' => array('id', 'name', 'date_modified'),
);
$json = json_encode($parameters);
$postArgs = 'method=get_report_entries&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if ($dg&IFO) print_r($result);
curl_close($curl);
foreach ($result->field_list[0] as $i)
echo $i->label.";";
echo "\n";
foreach ($result->entry_list[0] as $record) {
if (!isset($record->name_value_list)) continue;
foreach ($record->name_value_list as $i) echo $i->value.";";
echo "\n";
}
// report Id=3be50900-e1bb-2da8-be27-4fc769f9f8c1, Name="All Open Opportunities"
$parameters = array(
'session' => $sessionid,
'id' => array('3be50900-e1bb-2da8-be27-4fc769f9f8c1'),
'select_fields' => array('id', 'name', 'date_modified'),
);
$json = json_encode($parameters);
$postArgs = 'method=get_report_entries&input_type=json&response_type=json&rest_data=' . $json;
if ($dg&DBG) echo "postArgs=$postArgs\n";
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);
$response = curl_exec($curl);
$result = json_decode($response);
if ($dg&IFO) print_r($result);
curl_close($curl);
foreach ($result->field_list[0] as $i)
echo $i->label.";";
echo "\n";
foreach ($result->entry_list[0] as $record) {
if (!isset($record->name_value_list)) continue;
foreach ($record->name_value_list as $i) echo $i->value.";";
echo "\n";
}
The output in CSV style:
Opportunity Name;Type;Sales Stage;Expected Close Date;Amount;User Name;
Calm Sailing Inc - 1000 units;New Business;Qualification;2012-06-01;$75,000.00;will;
Anytime Air Support Inc - 1000 units;Existing Business;Id. Decision Makers;2012-06-11;$25,000.00;max;
Income Free Investing LP - 1000 units;Existing Business;Id. Decision Makers;2012-06-25;$25,000.00;chris;
Airline Maintenance Co - 1000 units;New Business;Perception Analysis;2012-06-29;$75,000.00;sarah;
etc.
Have fun with the Sugar API !
