How to Export a List of Records
Overview
An PHP example demonstrating how to export a list of records using the v11 /<module>/export/:record_list_id REST GET endpoint.
Exporting Records
Authenticating
First, you will need to authenticate to the Sugar API. An example is shown below:
<?php
$instance_url = "http://{site_url}/rest/v11";
$username = "admin";
$password = "password";
//Login - POST /oauth2/token
$auth_url = $instance_url . "/oauth2/token";
$oauth2_token_arguments = array(
"grant_type" => "password",
//client id - default is sugar.
//It is recommended to create your own in Admin > OAuth Keys
"client_id" => "sugar",
"client_secret" => "",
"username" => $username,
"password" => $password,
//platform type - default is base.
//It is recommend to change the platform to a custom name such as "custom_api" to avoid authentication conflicts.
"platform" => "custom_api"
);
$auth_request = curl_init($auth_url);
curl_setopt($auth_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);
curl_setopt($auth_request, CURLOPT_HEADER, false);
curl_setopt($auth_request, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($auth_request, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($auth_request, CURLOPT_FOLLOWLOCATION, 0);
curl_setopt($auth_request, CURLOPT_HTTPHEADER, array(
"Content-Type: application/json"
));
//convert arguments to json
$json_arguments = json_encode($oauth2_token_arguments);
curl_setopt($auth_request, CURLOPT_POSTFIELDS, $json_arguments);
//execute request
$oauth2_token_response = curl_exec($auth_request);
//decode oauth2 response to get token
$oauth2_token_response_obj = json_decode($oauth2_token_response);
$oauth_token = $oauth2_token_response_obj->access_token;
More information on authenticating can be found in the How to Authenticate and Log Out example and /oauth2/logout endpoint documentation.
Filtering Records
Next, we will need to identify the records we want to export using the /<module>/filter endpoint.
//Identify records to export - POST /<module>/filter
$filter_url = $instance_url . "/Accounts/filter";
$filter_arguments = array(
"filter" => array(
array(
'$or' => array(
array(
//name starts with 'a'
"name" => array(
'$starts'=>"A",
)
),
array(
//name starts with 'b'
"name" => array(
'$starts'=>"b",
)
)
),
),
),
"max_num" => 2,
"offset" => 0,
"fields" => "id",
"order_by" => "date_entered",
"favorites" => false,
"my_items" => false,
);
$filter_request = curl_init($filter_url);
curl_setopt($filter_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);
curl_setopt($filter_request, CURLOPT_HEADER, false);
curl_setopt($filter_request, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($filter_request, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($filter_request, CURLOPT_FOLLOWLOCATION, 0);
curl_setopt($filter_request, CURLOPT_HTTPHEADER, array(
"Content-Type: application/json",
"oauth-token: {$oauth_token}"
));
//convert arguments to json
$json_arguments = json_encode($filter_arguments);
curl_setopt($filter_request, CURLOPT_POSTFIELDS, $json_arguments);
//execute request
$filter_response = curl_exec($filter_request);
//decode json
$filter_response_obj = json_decode($filter_response);
//store ids of records to export
$export_ids = array();
foreach ($filter_response_obj->records as $record)
{
$export_ids[] = $record->id;
}
More information on the filter API can be found in the /<module>/filter documentation.
Request Payload
The data sent to the server is shown below:
{
"filter":[
{
"$or":[
{
"name":{
"$starts":"A"
}
},
{
"name":{
"$starts":"b"
}
}
]
}
],
"max_num":2,
"offset":0,
"fields":"id",
"order_by":"date_entered",
"favorites":false,
"my_items":false
}
Response
The data received from the server is shown below:
{
"next_offset":2,
"records":[
{
"id":"f16760a4-3a52-f77d-1522-5703ca28925f",
"date_modified":"2016-04-05T10:23:28-04:00",
"_acl":{
"fields":{
}
},
"_module":"Accounts"
},
{
"id":"ec409fbb-2b22-4f32-7fa1-5703caf78dc3",
"date_modified":"2016-04-05T10:23:28-04:00",
"_acl":{
"fields":{
}
},
"_module":"Accounts"
}
]
}
Creating a Record List
Once we have the list of ids, we then need to create a record list in Sugar that consists of those ids.
//Create a record list - POST /<module>/record_list
$record_list_url = $instance_url . "/Accounts/record_list";
$record_list_arguments = array(
"records" => $export_ids,
);
$record_list_request = curl_init($record_list_url);
curl_setopt($record_list_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);
curl_setopt($record_list_request, CURLOPT_HEADER, false);
curl_setopt($record_list_request, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($record_list_request, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($record_list_request, CURLOPT_FOLLOWLOCATION, 0);
curl_setopt($record_list_request, CURLOPT_HTTPHEADER, array(
"Content-Type: application/json",
"oauth-token: {$oauth_token}"
));
//convert arguments to json
$json_arguments = json_encode($record_list_arguments);
curl_setopt($record_list_request, CURLOPT_POSTFIELDS, $json_arguments);
//execute request
$record_list_response = curl_exec($record_list_request);
Request Payload
The data sent to the server is shown below:
{
"records":[
"f16760a4-3a52-f77d-1522-5703ca28925f",
"ec409fbb-2b22-4f32-7fa1-5703caf78dc3"
]
}
Response
The data received from the server is shown below:
{
"id":"ef963176-4845-bc55-b03e-570430b4173c",
"assigned_user_id":"1",
"module_name":"Accounts",
"records":[
"f16760a4-3a52-f77d-1522-5703ca28925f",
"ec409fbb-2b22-4f32-7fa1-5703caf78dc3"
],
"date_modified":"2016-04-05 21:39:19"
}
Exporting Records
Once we have the record list created, we can then export the CSV file.
//Export Records - GET /<module>/export/:record_list_id
$export_url = $instance_url . "/Accounts/export/" . $record_list_response_obj->id;
$export_request = curl_init($export_url);
curl_setopt($export_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);
curl_setopt($export_request, CURLOPT_HEADER, true); //needed to return file headers
curl_setopt($export_request, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($export_request, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($export_request, CURLOPT_FOLLOWLOCATION, 0);
curl_setopt($export_request, CURLOPT_HTTPHEADER, array(
"Content-Type: application/json",
"oauth-token: {$oauth_token}"
));
$export_response = curl_exec($export_request);
//set headers from response
list($headers, $content) = explode("\r\n\r\n", $export_response ,2);
foreach (explode("\r\n",$headers) as $header) {
header($header);
}
$content = trim($content);
echo $content;
More information on exporting records can be found in the /<module>/export/:record_list_id documentation.
Response
The data received from the server is shown below:
HTTP/1.1 200 OK Date: Tue, 05 Apr 2016 21:50:32 GMT Server: Apache/2.2.29 (Unix) DAV/2 PHP/5.3.29 mod_ssl/2.2.29 OpenSSL/0.9.8zg X-Powered-By: PHP/5.3.29 Expires: Cache-Control: max-age=10, private Pragma: Content-Disposition: attachment; filename=Accounts.csv Content-transfer-encoding: binary Last-Modified: Tue, 05 Apr 2016 21:50:32 GMT ETag: 9b34f5d74e0298aaf7fd1f27d02e14f2 Content-Length: 1703 Connection: close Content-Type: application/octet-stream; charset=ISO-8859-1 "Name","ID","Website","Office Phone","Alternate Phone","Fax","Billing Street","Billing City","Billing State","Billing Postal Code","Billing Country","Shipping Street","Shipping City","Shipping State","Shipping Postal Code","Shipping Country","Description","Type","Industry","Annual Revenue","Employees","SIC Code","Ticker Symbol","Parent Account ID","Ownership","Campaign ID","Rating","Assigned User Name","Assigned User ID","Team ID","Teams","Team Set ID","Date Created","Date Modified","Modified By Name","Modified By ID","Created By","Created By ID","Deleted","test","Facebook Account","Twitter Account","Google Plus ID","DUNS","Email","Invalid Email","Email Opt Out","Non-primary emails" "Arts & Crafts Inc","ec409fbb-2b22-4f32-7fa1-5703caf78dc3","www.hrinfo.tw","(252) 456-8602","","","777 West Filmore Ln","Los Angeles","CA","77076","USA","777 West Filmore Ln","Los Angeles","CA","77076","USA","","Customer","Hospitality","","","","","","","","","Max Jensen","seed_max_id","West","West, East, Global","dec43cb2-5273-8be2-968a-5703cadee75f","2016-04-05 10:23","2016-04-05 10:23","Administrator","1","Administrator","1","0","","","","","","sugar.sugar.section@example.org","0","0","dev.phone@example.biz,0,0" "B.H. Edwards Inc","f16760a4-3a52-f77d-1522-5703ca28925f","www.sectiondev.edu","(361) 765-0216","","","111 Silicon Valley Road","Persistance","CA","29709","USA","111 Silicon Valley Road","Persistance","CA","29709","USA","","Customer","Apparel","","","","","","","","","Sally Bronsen","seed_sally_id","West","West","West","2016-04-05 10:23","2016-04-05 10:23","Administrator","1","Administrator","1","0","","","","","","info.sugar@example.de","0","1","phone.sales.section@example.tv,0,0"
Download
You can download the full API example here.