[Salesforce/SugarCRM] Live data synchronization between Salesforce.com and SugarCRM

Introduction

This post will present how to set up a master/slave synchronization between two great CRM: Salesforce and SugarCRM.
I have been working at Salesforce.com as a Technical Architect the last three years, so I have a heavy practice of Salesforce but I am a SugarCRM newbie: coding in Sugar is a brand new experience. My code is probably not 100% best practices so feel free to share your advices in the post comments.
Disclaimer: the provided code samples are straight forward; they are not dedicated to run in a production environment. You might want to pick up some parts but use it at your own risks.
The code is available under the GPL license 3.0.

The scenario

The Salesforce organization is the master data. Each time an account record is created or updated, a REST call is sent to the SugarCRM instance. SugarCRM will create a new record or update an existing one, based on the Salesforce record Id.
This is a one way synchronization, the aim of this post is to keep it simple and providing clear code samples.

Salesforce side

Salesforce Developer Edition

I am working with a Salesforce Developer Edition (DE). This a free edition that brings with the same features as the Unlimited Edition. The main limitations are: 2 crm user licenses and the data storage (up to 5Mb). Sign-up for a DE.

The Apex trigger

An Apex trigger is fired each time a record is created or modified (on the after update event). This process will run when using the HCI (Salesforce in the web browser) and also with the Salesforce API (using the data loader e.g.). So we need to bulkify the code. This is a best practice that will let your trigger process 1 record (HCI) or 200 records as well (data loader, 3rd party tools using the API).
The Apex trigger will call an asynchronous method that will send the REST message to SugarCRM. As it is asynchronous, the end user is not disturbed by the synchronization process as it ran behind the scene.
The trigger code:

trigger AccountAfterUpdate on Account (after update) {
    Set AccountIds=new Set();
    for(SObject a : Trigger.New) AccountIds.Add(a.ID);
    SugarCRM.AccountUpsert(AccountIds);
}

Remark: the id double tags at the end of the code sample is a Syntax Highlighter bug.

Why REST rather than SOAP?
I highly recommend using REST rather than SOAP web services:
- it is quicker, easier, a lower maintenance (no need to import a new wsdl at each release)
- Salesforce meets some limitations when importing wsdl files. E.g, importing an external schema is not supported and provide the following error:
Error: Failed to parse wsdl: Found schema import from location http://schemas.xmlsoap.org/soap/encoding/. External schema import not supported

The Apex Class

Login method
Logs in SugarCRM using the user name and a MD5 hash of the password. The previous post is explaining how to generate the MD5 value.
AccountUpsert method
Sends the data. The method has the @future(call=true) annotation. It means that the execution is asynchronous, it does not impact the governor limits of the current transaction.
Objects cannot be passed as parameters, only primitives can: that is why I am using a set of record Ids.
The REST call is straight forward, the Json is hardcoded. If you want to improve the code and get something smooth, I recommend to built on the fly the Json syntax based on a structure declaration. Read this interesting post: http://www.tgerm.com/2011/10/winter12-jsonparser-serialize.html

The end point:
I recommend to declare the end point as a System Label. My demo is working on a virtual machine that changes its IP address and name at every reboot.
Setup | Administration Setup | Security Controls | Remote Site Settings

Apex Class code:

public class SugarCRM {

    private final static String endpointUrl = 'http://mc-178-20-142-224.ovh.net';

    @future (callout=true)
    public static void AccountUpsert(Set AccountIds) {
        String SessionId = login('admin','1e87fae4d9133d53124fedcfeca02adc');
        CallAccountUpsert(SessionId, AccountIds);
    }
    
    public static String login(String user, String password) {
        String sessionId='';
        Http h = new Http();
        HttpRequest req = new HttpRequest();
        String url=endpointUrl+'/sugar/service/v2/rest.php?';
        url=url+'method=login&input_type=json&response_type=json&rest_data={"user_auth":{"user_name":"'+
            EncodingUtil.urlEncode(user,'UTF-8')+'","password":"'+EncodingUtil.urlEncode(password,'UTF-8')+
            '","version":"0.1"},"application_name":""}';
        req.setEndpoint(url);
        req.setMethod('GET');
        // Send the request, and return a response
        HttpResponse res = h.send(req);
        String Body = res.getBody();
        //System.Debug('##Body='+Body);
        JSONParser parser = JSON.createParser(Body);
        while (parser.nextToken() != null) {
            //System.Debug('##getCurrentToken()='+parser.getCurrentToken()+',parser.getText()='+parser.getText());
            if ((parser.getCurrentToken() == JSONToken.FIELD_NAME) && (parser.getText() == 'id')) {
                parser.nextToken();
                sessionId = parser.gettext();
                break;
            }
        }
        System.Debug('##sessionId='+sessionId);
        return (sessionId);        
    }
    
    public static String CallAccountUpsert(String SessionId, Set AccountIds) {
        Http h = new Http();
        HttpRequest req = new HttpRequest();
        String url=endpointUrl+'/sugar/service/v2/rest.php?';
        String url1='method=set_entries&input_type=json&response_type=json&rest_data='+
          '{"session":"'+SessionId+'","module_name":"Accounts","name_value_lists":[';
        for(Account a : [Select Id, Name, Phone From Account Where Id IN :AccountIds]) {
            url1+='[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"'+EncodingUtil.urlEncode(a.Name,'UTF-8')+'"},'+
              '{"name":"phone_office","value":"'+EncodingUtil.urlEncode(a.Phone,'UTF-8')+'"},'+
              '{"name":"salesforceid_c","value":"'+a.Id+'"}],';
        }
        url1=url1.replaceAll(',$','');
        url1+=']}';
        //url1=EncodingUtil.urlEncode(url1,'UTF-8');
        url+=url1;
        System.Debug('##url='+url);
        req.setEndpoint(url);
        req.setMethod('GET');
        // Send the request, and return a response
        HttpResponse res = h.send(req);
        String Body = res.getBody();
        System.Debug('##Body='+Body);
        return Body;
    }
}

What is an upsert?

Let 's start with the Wikipedia definition: http://en.wikipedia.org/wiki/Upsert.
<< The term "Upsert" refers to any database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. The term upsert is a portmanteau of update and insert and is common slang among database developers. >>
The upsert is a common DML statement Salesforce. I did not find the equivalent in SugarCRM and coded my own Upsert behavior.

The test methods

Before deploying your code to the production environment, you must write some test methods and get a minimum code coverage of 75%. We will not write the test methods as they are not needed in a DE environment.

SugarCRM

Assumptions

You have a SugarCRM installation reachable by Salesforce. I deployed the open source version of SugarCRM: the community edition version 6.4.0 running on a virtual Ubuntu 10 64 bits hosted by OVH Cloud. I will not detail the installation walk through in this post in order to keep focus on the code.

change the data model

The Salesforce Account Id will match a foreign key called salesforce_id.
Create the foreign key
Go to Admin | Developer Tools | Studio. Click on the Accounts Icon.

 

 Click on the "Fields" icon, then the "Add Fields" button. Create a string field called salesforceid. The name will be salesforceid_c. Set the length to 18.

 Then click on the layouts icons. Edit the "View" page layout. Add a section that contains the new custom field. Do not add the field to the "Edit" page layout because we want only Salesforce to be able to change the value.

SugarCRM beginner Best practices

use the sugar logs to trace the execution. Go to Admin | System Settings | View Log. Set the log level to "Debug". This spot is really useful when it comes to trace all the application behavior, especially when you are dealing with hooks.
Use a MySQL Gui or command line client, I am using "emma" (for Extendable Mysql Managing Assistant) on Ubuntu, a fancy editor.

Add a Hook

A hook is a piece of code that will be triggered during an event.It may be compared as a data base trigger or a Salesforce trigger. More on SugarCRM events in the developer documentation.
As the upsert action does not exist in SugarCRM, I coded my own. This is my first hook and I had some hard time to dig the web, read the docs, parse the code, check the data base and make my own stuff. All critics are welcome.
I set the log level to "debug" to highlight the unexpected behaviors: Sugar was executing the hook several times (because I am saving a new account record inside the hook). To prevent multiple executions, I added a semaphore behavior inside the hook class: an array that contains the ID of the recorded already processed (cf private static array $canTrigger)

First of all, you must declare the event:
$SUGARHOME/custom/modules/Accounts/logic_hooks.php

// Do not store anything in this file that is not part of the array or the hook version.  This file will 
// be automatically rebuilt in the future. 
$hook_version = 1; 
$hook_array = Array(); 
// position, file, function 
$hook_array['after_save'] = Array(); 
$hook_array['after_save'][] = Array(1, 'Account From SFDC', 'custom/modules/Accounts/AccountFromSFDC-AfterUpdate.php','AccountFromSFDC', 'AccountUpsert'); 

Then, the Hook:
I am tracking when entering/exiting the class with the logs. The semaphore prevents the save method from re-executing the hook code. I do not know if this is a best practice as I was not able to find on Google a sample of code to perform an Upsert.
$SUGARHOME/custom/modules/Accounts/AccountFromSFDC-AfterUpdate.php

//AccountsTestHook.php
//AccountsTestHook.php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
class AccountFromSFDC{

 static private $canTrigger = array();

 function AccountUpsert(&$bean, $event, $arguments) {
  $GLOBALS['log']->warn("######## AccountFromSFDC-AfterUpdate.AccountFromSFDC - START");
  $GLOBALS['log']->warn("#### self::\$canTrigger=".implode(',',self::$canTrigger));
  if (array_search($bean->id,self::$canTrigger)===false) {
   $GLOBALS['log']->warn("##### continue: entering in the hook Account After Save");
   array_push(self::$canTrigger, $bean->id);
   if (empty($bean->fetched_row['id']) && ($bean->salesforceid_c!='')) { // on after insert
    $query = "select id_c from accounts_cstm where salesforceid_c='".$bean->salesforceid_c."' and id_c!='".$bean->id."' limit 1";
    $GLOBALS['log']->warn("##### query=$query");
    $res=$bean->db->query($query);
    $row=$bean->db->fetchByAssoc($res); 
    $GLOBALS['log']->warn("##### count(\$row)=".count($row).', $row[\'id_c\']='.$row['id_c']);
    if ($row['id_c'] != '') {// foreign key match
     $GLOBALS['log']->warn("##### \$bean->id=".$bean->id);
     $b = clone $bean;
     $b->id = $row['id_c'];
     array_push(self::$canTrigger, $b->id);
     $result = $bean->db->query("delete from accounts where id='".$bean->id."'");
     $result = $bean->db->query("delete from accounts_cstm where id_c='".$bean->id."'");
     $b->save();
     
     /* this process should only happen through the API as the salesforceid_c is not on the Edit page layout
      * otherwise you should redirect to the new page:
              $site = $sugar_config['site_url'];
     $GLOBALS['log']->warn("##### {$site}/index.php?module=Accounts&action=DetailView&record={$b->id}");
     $GLOBALS['log']->warn("######## AccountFromSFDC-AfterUpdate.AccountFromSFDC - END (return)");
     SugarApplication::redirect('index.php?module=Accounts&action=DetailView&record='.$b->id);
              */
    }
   }
  } 
  else {
   $GLOBALS['log']->warn("######## exit: do not enter in the hook Account After Save");
   return;
  }

 }
}

The home made upsert process:

Entering condition: new record ($bean->fetched_row['id'] is empty), but a new record had been created.
Case 1: no Salesforceid_c. Do nothing.
Case 2: the record does not match a Salesforceid_c. Do nothing.
Case 3: the record matches with a Salesforceid_c: delete the previous record in the data base, set the previous record id to the new record.

Synchronization: mapping

Salesforce	SugarCRM
Id	Salesforceid_c
Name	Name
Phone	Phone_office

Now let's test!

To improve your tests, I recommend starting by executing Anonymous Apex code, and following the results using the "System Logs" or the "Debug Logs". The "Debug Logs" will trace all the code execution, including the @Future call (asynchronous call).

Salesforce: with the browser

Update 1 account
Salesforce: go to the Account tab, choose an account. Make any update you like on the account in order to fire the trigger.

Then go to Sugar, make a search filtered on the account name, woohoo, it works!

Update again the account
In Salesforce, change the Phone value and click on the save button:

In Sugar, refresh the detail page of the account:

Salesforce: bulk load

The bulk load is a common behavior when using the API: instead of pushing the records 1 by 1, we are pushing them by 200. This makes the overall process quicker as all the user validation rules, workflow, triggers, assignation, case escalation, roll up summary fields are updated during the load.
Better than providing a ton of data loader screenshots, I will make the demonstration using a an Apex anonymous code that will send two records in the bulk.

anonymous Apex:

upserting 2 existing Accounts.

Set AccountIds=new Set();
for(Account a : [Select ID From account Where ID IN('0018000000US5Jk','0018000000sjhVI')]) AccountIds.Add(a.ID);
SugarCRM.AccountUpsert(AccountIds);

The two records with those Ids are sent to Sugar. Here is the REST message (from the Salesforce debug logs):

04:06:38.780 (780110000)|CALLOUT_REQUEST|[57]|System.HttpRequest[Endpoint=http://mc-178-20-142-224.ovh.net/sugar/service/v2/rest.php?method=set_entries&input_type=json&response_type=json&rest_data={"session":"64c916f1b6e369543d40b0b39a3d0b18","module_name":"Accounts","name_value_lists":[[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"United+Oil+%26+Gas%2C+UK"},{"name":"phone_office","value":"%2B44+191+4956300"},{"name":"salesforceid_c","value":"0018000000US5JkAAL"}],[{"name":"assigned_user_id","value":"1"},{"name":"name","value":"United+Oil%2C+Manchester"},{"name":"phone_office","value":"01020304066"},{"name":"salesforceid_c","value":"0018000000sjhVIAAY"}]]}, Method=GET]

And Sugar's answer:

04:06:39.128 (1128089000)|USER_DEBUG|[59]|DEBUG|##Body={"ids":["5d122a1a-1a6f-fa15-7f32-4ecf0626cc95","617f4722-7121-2719-523b-4ecf068caf6d"]}

The End

I hope you enjoyed this post, please feel free to share your views in the post comments.