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

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?

  • Hi Mary,

    While I haven't used the exports API myself just yet, from the /help page, it looks like the response that comes in from the API is the actual code for the CSV file. Once you have this in a PHP variable, you can create the file yourself in PHP wherever you want, populating it with the data that came in from the API. Let me know if you have any questions or would like for me to throw together an example of this for you!

    -Alan

  • Have you ever worked with building an API event into a custom scheduler? 

  • 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:

    <?php
    array_push($job_strings, 'export_accounts');
    
    function export_accounts() {
      $base_url = "{site_url}/rest/v10";
      $username = "admin";
      $password = "password";
    
      //Login - POST /oauth2/token
      $url = $base_url . "/oauth2/token";
      $oauth2_token_arguments = array(
         "grant_type" => "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 /<module>/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 /<module>/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 /<module>/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("\r\n\r\n", $result ,2);
        foreach (explode("\r\n",$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