Scheduled export of a filtered list of accounts to be used in another system

Question

Sugar Professional 7.6.0

I have been asked to created a scheduled export of a couple of fields from accounts to be imported (automatically) into another system. The format that would be best would be a json list.

I have read about the export api, but I'm not sure how to incorporate that into the schedulers.

Also has anyone worked with this kind of event before? Any advice on how to add a location to where the file exports to?

Mary Warner · Accepted Answer

Hi Mary, I haven't before, but I have now! Here is how I accomplished this. First, follow these instructions . I named my job "export_accounts", so I replaced "custom_job" with that. Here is what my export_accounts.php file looks like: "password", "client_id" => "{client_id}", "client_secret" => "{client_secret}", "username" => $username, "password" => $password, "platform" => "scheduler" ); $oauth2_token_response = call($url, '', 'POST', $oauth2_token_arguments); //Identify records to export - GET //filter $filter_arguments = array( "filter" => array( array( '$or' => array( array( "name" => array( '$starts'=>"a", ) ), array( "name" => array( '$starts'=>"b", ) ) ), ), ), "max_num" => 500, "offset" => 0, "fields" => "id", "order_by" => "", "favorites" => false, "my_items" => false, ); $url = $base_url . "/Accounts/filter"; $filter_response = call($url, $oauth2_token_response->access_token, 'GET', $filter_arguments); //store ids of records to export $export_ids = array(); foreach ($filter_response->records as $record) { $export_ids[] = $record->id; } //Create a record list - POST //record_list $url = $base_url . "/Accounts/record_list"; $record_list_arguments = array( "records" => $export_ids, ); $record_list_response = call($url, $oauth2_token_response->access_token, 'POST', $record_list_arguments); //Export Records - GET //export/:record_list_id $url = $base_url . "/Accounts/export/" . $record_list_response->id; $export_response = call($url, $oauth2_token_response->access_token, 'GET', array(), true, true); // Create/Write file $export_file = fopen("export_accounts.csv", "w") or die("Unable to open file!"); fwrite($export_file, $export_response); fclose($export_file); return true; } function call($url, $oauthtoken='', $type='GET', $arguments=array(), $encodeData=true, $returnHeaders=false) { $type = strtoupper($type); if ($type == 'GET') { $url .= "?" . http_build_query($arguments); } $curl_request = curl_init($url); if ($type == 'POST') { curl_setopt($curl_request, CURLOPT_POST, 1); } elseif ($type == 'PUT') { curl_setopt($curl_request, CURLOPT_CUSTOMREQUEST, "PUT"); } elseif ($type == 'DELETE') { curl_setopt($curl_request, CURLOPT_CUSTOMREQUEST, "DELETE"); } curl_setopt($curl_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0); curl_setopt($curl_request, CURLOPT_HEADER, $returnHeaders); curl_setopt($curl_request, CURLOPT_SSL_VERIFYPEER, 0); curl_setopt($curl_request, CURLOPT_RETURNTRANSFER, 1); curl_setopt($curl_request, CURLOPT_FOLLOWLOCATION, 0); if (!empty($oauthtoken)) { $token = array("oauth-token: {$oauthtoken}"); curl_setopt($curl_request, CURLOPT_HTTPHEADER, $token); } if (!empty($arguments) && $type !== 'GET') { if ($encodeData) { $arguments = json_encode($arguments); } curl_setopt($curl_request, CURLOPT_POSTFIELDS, $arguments); } $result = curl_exec($curl_request); if ($returnHeaders) { list($headers, $content) = explode(" ", $result ,2); foreach (explode(" ",$headers) as $header) { header($header); } return trim($content); } curl_close($curl_request); $response = json_decode($result); return $response; } After creating this, run a Quick Repair, create the Scheduler job in Admin > Schedulers, then run cron. This should create a CSV file in the root directory of Sugar that is an export of all Accounts starting with "A" or "B". Let me know if you have any questions or troubles with this! -Alan