Importing contacts

Intro

SugarCRM is the world's leading Customer Relationship Management (CRM) software available with complete freedom under the GPL license. Because CRM software captures the varied relationships between a company and it's sources of revenue, most businesses really stand to benefit from the intelligence provided by a CRM system. All companies already have 'ad-hoc' methods to track leads, contacts, customer accounts and the company interactions with these (e.g. spreadsheets, lists and email records); or they even have organized CRM solutions in place. Either way, once you decide to adopt an open and standards-based solution like SugarCRM, your first order of business will be to load it with your existing data. SugarCRM has importing (and exporting) utilities that make it easy to do this, so that will not be the focus of this article. Instead, we'll focus on programmatic interaction with the system. Once the CRM system is deployed, a company will also typically want to create one or more pipelines which act as conduits to capture new account, and contact information. This article will show how easy it can be to establish a web service, using wp:SOAP to add contacts and accounts to your SugarCRM installation. As an example, let's suppose that a conference organizer wants to offer a sign-up sheet on their existing website. The existing website could be a drupal content management system (CMS) which not only manages the conference, but also adds company and individual contact details to the SugarCRM system via the web service. The details on creating the form for the capture are beyond the scope of this article but the capture routines could easily handle a form input (HTTP POST) just as easily as we read input from a file . We'll focus on the plumbing of the interaction with the SOAP server.

Goal

Import records into SugarCRM without using existing import utilities, but rather by writing a tool in wp:PHP to do the work. Records should go into Accounts and Contacts, while creating a relationship between the two.

Download and Install SugarCRM

SugarCRM provides easy stack installers which make trying the system as close to a one-click operation as possible. However, in the many scenarios the developer or system administrator will want to install into an existing infrastructure so we'll assume that approach.

1. Visit the SugarCRM homepage, and click the top navbar link to "Sugar Open Source"
2. Skip the "Wizard" and go right to the "Download Page" because you know what you're doing and also have a pre-existing setup of Linux, Apache MySQL and PHP
3. Download SugarCE-5.0.0e.zip (production release) or the later available production release (getting only the application).
4. Visit the recommended "Installation" instructions page at http://www.sugarforge.org/content/installation/
5. Install SugarCRM according to the instructions. Note: I immediately ran into trouble because the instructions did say to chmod 766 all files that needed to be writable by the web_user. This command renders the directories non-executable which manifests in include errors because the web user (www-data) can not see into those directories (to find includes). The following snippet is a fix:

# find directories in the ./crm path and change the mode on them so that all users can execute (see into) the directory 
find ./crm/ -type d |xargs chmod a+x

There is one more 'gotcha' that I ran into with the installation. The application wants to create a .htaccess file (which doesn't exist in the distribution and would not necessarily be writable to the web user.) As a failsafe, the .htaccess content is printed to the screen in the installer. However it doesn't properly display (lacking newline characters) so the content is not suitable for copy and paste into the file. I resorted to the source which generates the .htaccess content, and used that. An alternative is to simply touch and chmod 777 a .htaccess file prior to running the installer; and then chmod'ing it go=r after install.

Requirements

My self-imposed requirement was to capture all of the data in my sample assuming the owner of the data collected it for a reason and that they wouldn't be too happy with a loss of data in a migration to a new application. I did not define new fields, but I did 'draw' a map of my data source and how it corresponds to the data definition of the Accounts and Contacts tables. Because I had parsed the data source for column headings, I simply exported that variable and then used it as a comment right in my code workup as I went.

print '<pre>'; var_export ($arrFields); print '</pre>';
// copy and paste that output; then add comments about which fields go into which table
// comment the whole block so that it remains as a reference in the tool
array (
  0 => 'Company Name',// a:name                           
  1 => 'Industry',    // a:industry                       
  2 => 'First Name',  //                                  c:first_name
  3 => 'Last Name',   //                                  c:last_name
  4 => 'Fax',         // a:phone_fax                      c:phone_fax
  5 => 'Address',     // a:billing_address_street         c:primary_address_street
  6 => 'City',        // a:billing_address_city           c:primary_address_city
  7 => 'State',       // a:billing_address_state          c:primary_address_state
  8 => 'Zip Code',    // a:billing_address_postalcode     c:primary_address_postalcode
  9 => 'Country',     // a:billing_address_country        c:primary_address_country
  10 => 'Notes',      // a:description                    
  11 => 'Work Phone', // a:phone_office                   c:phone_work
  12 => 'Other Phone',// a:phone_alternate                c:phone_other
  13 => 'Email',      // a:email                          c:email1
  14 => 'website',    // a:website                        
  15 => 'employees',  // a:employees                      
  16 => 'ticker_symbol// a:ticker_symbol                  
',
)

Using a Form

The SOAP Intro and Practical Examples, shows a simple form-based processing script for leads.

The form can be found in the "examples" directory in the source. Note: the example did not work as an application entry point (because even though it defined ('sugarEntry', true); there was also an IF that pre-empted that definition -- because requests inside the SugarCRM install directory will automatically bootstrap the SugarCRM system). To work around this, either put the example form outside the application directory or define sugarEntry as true without the 'if' conditional. Using the lead capture form is illustrated at http://www.sugarcrm.com/wiki/index.php?title=Creating_a_lead_capture_form_for_your_website If your needs are more complex than the simple example, you can learn more by looking at the modules dealing with Import (dataMaps), the database abstraction layer (SugarBeans and VarDefs) or the database directly to get a clearer picture of everything going on in the SugarCRM system.

Use the Source Luke

Answers are always found in the source, with the caveat that it can be confusing and/or time-consuming to find those answers.

Looking at the full application import routines and the four-step forms for importing leads, there is a lot of example machinery that you could use to create a sophisticated web front-end using SugarCRM internals. For this exercise, I want to assume that we'll be operating between two websites, and so I will simply insert records into the database communicating with SugarCRM's SOAP server.

Tip

You can peer into the SugarCRM environment with a call to PHP's 'get_defined_vars()'. This will give you some information about field maps etc.

# temp.php to show a concise view of the variables defined in the config script
define('sugarEntry', true);
include ('./modules/Import/config.php');
print "Defined variables: <br />\n<pre>"; print_r (get_defined_vars()); print "\n</pre>\n";
# end temp.php
# partial output:
            [salesforce_contacts_field_map] => Array
                (
                    [Salutation] => salutation
                    [Description] => description
                    [First Name] => first_name
                    [Last Name] => last_name
                    [Title] => title
                    [Department] => department
                    [Birthdate] => birthdate
                    [Lead Source] => lead_source
                    [Assistant] => assistant
                    [Asst. Phone] => assistant_phone
                    [Contact ID] => id
                    [Mailing Street] => primary_address_street
                    [Mailing Address Line1] => primary_address_street_2
                    [Mailing Address Line2] => primary_address_street_3
                    [Mailing Address Line3] => primary_address_street_4
                    [Mailing City] => primary_address_city
                    [Mailing State] => primary_address_state
                    [Mailing Zip/Postal Code] => primary_address_postalcode
                    [Mailing Country] => primary_address_country
                    [Other Street] => alt_address_street
                    [Other Address Line 1] => alt_address_street_2
                    [Other Address Line 2] => alt_address_street_3
                    [Other Address Line 3] => alt_address_street_4
                    [Other City] => alt_address_city
                    [Other State] => alt_address_state
                    [Other Zip/Postal Code] => alt_address_postalcode
                    [Other Country] => alt_address_country
                    [Phone] => phone_work
                    [Mobile] => phone_mobile
                    [Home Phone] => phone_home
                    [Other Phone] => phone_other
                    [Fax] => phone_fax
                    [Email] => email1
                    [Email Opt Out] => email_opt_out
                    [Do Not Call] => do_not_call
                    [Account Name] => account_name
                    [Account ID] => account_id
                )

            [salesforce_accounts_field_map] => Array
                (
                    [Account Name] => name
                    [Annual Revenue] => annual_revenue
                    [Type] => account_type
                    [Ticker Symbol] => ticker_symbol
                    [Rating] => rating
                    [Industry] => industry
                    [SIC Code] => sic_code
                    [Ownership] => ownership
                    [Employees] => employees
                    [Description] => description
                    [Account ID] => id
                    [Billing Street] => billing_address_street
                    [Billing Address Line1] => billing_address_street_2
                    [Billing Address Line2] => billing_address_street_3
                    [Billing City] => billing_address_city
                    [Billing State] => billing_address_state
                    [Billing Zip/Postal Code] => billing_address_postalcode
                    [Billing Country] => billing_address_country
                    [Shipping Street] => shipping_address_street
                    [Shipping Address Line1] => shipping_address_street_2
                    [Shipping Address Line2] => shipping_address_street_3
                    [Shipping City] => shipping_address_city
                    [Shipping State] => shipping_address_state
                    [Shipping Zip/Postal Code] => shipping_address_postalcode
                    [Shipping Country] => shipping_address_country
                    [Phone] => phone_office
                    [Fax] => phone_fax
                    [Website] => website
                )

Using SOAP

Note: SOAP examples can obviously change slightly over time with SugarCRM, so you must be careful about what example you use, and generally rely on the source to be the definitive source ;-)

• soap/SoapPortalUsers.php
• soap/SoapSugarUsers.php
• soap/SoapData.php
• soap/SoapDeprecated.php

Getting on with the soap service, it is easy to discover the service profile and import the data

From the soap.php page (which shows the WSDL), we can get a definition of the available SOAP calls. For example, here is the definition of the 'create_account' method

Name: create_account
Binding: sugarsoapBinding
Endpoint: http://freephile.com/crm/soap.php
SoapAction: http://freephile.com/crm/soap.php/create_account
Style: rpc
Input:
  use: encoded
  namespace: http://www.sugarcrm.com/sugarcrm
  encodingStyle: http://schemas.xmlsoap.org/soap/encoding/
  message: create_accountRequest
  parts:
    user_name: xsd:string
    password: xsd:string
    name: xsd:string
    phone: xsd:string
    website: xsd:string
Output:
  use: encoded
  namespace: http://www.sugarcrm.com/sugarcrm
  encodingStyle: http://schemas.xmlsoap.org/soap/encoding/
  message: create_accountResponse
  parts:
    return: xsd:string
Namespace: http://www.sugarcrm.com/sugarcrm
Transport: http://schemas.xmlsoap.org/soap/http
Documentation: 

And the create_contact method:

Name: create_contact
Binding: sugarsoapBinding
Endpoint: http://freephile.com/crm/soap.php
SoapAction: http://freephile.com/crm/soap.php/create_contact
Style: rpc
Input:
  use: encoded
  namespace: http://www.sugarcrm.com/sugarcrm
  encodingStyle: http://schemas.xmlsoap.org/soap/encoding/
  message: create_contactRequest
  parts:
    user_name: xsd:string
    password: xsd:string
    first_name: xsd:string
    last_name: xsd:string
    email_address: xsd:string
Output:
  use: encoded
  namespace: http://www.sugarcrm.com/sugarcrm
  encodingStyle: http://schemas.xmlsoap.org/soap/encoding/
  message: create_contactResponse
  parts:
    return: xsd:string
Namespace: http://www.sugarcrm.com/sugarcrm
Transport: http://schemas.xmlsoap.org/soap/http
Documentation: 

Even more detail can be had by inspecting the service.

$get_available_modules_params = array (
  'session' => $session_id,
);
$result = $soapclient->call('get_available_modules', $get_available_modules_params);
print '<pre>'; var_export ($result); print '</pre>';

// output:

See Importing_contacts/sugar_modules

$interestedModules = array('Accounts', 'Contacts');

foreach ($interestedModules as $module_name) {
  $get_module_fields_params = array (
    'session' => $session_id,
    'module_name' => $module_name
  );
  $result = $soapclient->call('get_module_fields', $get_module_fields_params);
  print "\n $module_name fields:<br />\n<pre>\n"; var_export ($result); print "\n</pre>\n";
}

Accounts fields:

Importing_contacts/Accounts See the SOAP profile for Accounts

Contacts fields:

Importing_contacts/Contacts See the SOAP profile for Contacts

Setting Records

// read in our data from source CSV file format
$arrData = file('./account_contact_list.csv');
$separator = ',';
// initialize an array to hold structured data; we'll determine what columns we want and map our data
$leadData = array();
// take the first (header) row off the CSV file
$arrFields = explode($separator, array_shift($arrData));
// flipping the array gives us the integer value we need defined by it's name
// in other words, we now have an associative array of integers
$fieldKeys = array_flip($arrFields);

// break up our records
foreach ($arrData as $k => $v) {
  $arrData[$k] = explode($separator, $v);
}

foreach ($arrData as $record) {
  // Accounts
  $set_entry_params = array(
    'session' => $session_id,
    'module_name' => 'Accounts',
    'name_value_list'=>array(
        array('name'=>'name',                       'value'=>$record[$fieldKeys['Company Name']]), // required
        array('name'=>'industry',                   'value'=>$record[$fieldKeys['last_name']]),
        array('name'=>'phone_fax',                  'value'=>$record[$fieldKeys['Fax']]),
        array('name'=>'billing_address_street',     'value'=>$record[$fieldKeys['Address']]),
        array('name'=>'billing_address_city',       'value'=>$record[$fieldKeys['City']]),
        array('name'=>'billing_address_state',      'value'=>$record[$fieldKeys['State']]),
        array('name'=>'billing_address_postalcode', 'value'=>$record[$fieldKeys['Zip Code']]),
        array('name'=>'billing_address_country',    'value'=>$record[$fieldKeys['Country']]),
        array('name'=>'description',                'value'=>$record[$fieldKeys['Notes']]),
        array('name'=>'phone_office',               'value'=>$record[$fieldKeys['Work Phone']]),
        array('name'=>'phone_alternate',            'value'=>$record[$fieldKeys['Other Phone']]),
        array('name'=>'email',                      'value'=>$record[$fieldKeys['Email']]),
        array('name'=>'website',                    'value'=>$record[$fieldKeys['website']]),
        array('name'=>'employees',                  'value'=>$record[$fieldKeys['employees']]),
        array('name'=>'ticker_symbol',              'value'=>$record[$fieldKeys['ticker_symbol']]),
        array('name'=>'assigned_user_id',           'value'=>$user_guid)
    )
  );
  // make the soap call
  $result = $soapclient->call('set_entry',$set_entry_params);
  // print "\n <br />Entry results:<br />\n<pre>\n"; var_export ($result); print "\n</pre>\n";
  // the return value will give us the unique identifier for the Account record, which we
  // then use to create a relationship entry when creating the Contact
  $accountId = $result['id'];

  // Contacts
  $set_entry_params = array(
    'session' => $session_id,
    'module_name' => 'Contacts',
    'name_value_list'=>array(
        array('name'=>'first_name',                 'value'=>$record[$fieldKeys['First Name']]),
        array('name'=>'last_name',                  'value'=>$record[$fieldKeys['Last Name']]),
        array('name'=>'phone_fax',                  'value'=>$record[$fieldKeys['Fax']]),
        array('name'=>'primary_address_street',     'value'=>$record[$fieldKeys['Address']]),
        array('name'=>'primary_address_city',       'value'=>$record[$fieldKeys['City']]),
        array('name'=>'primary_address_state',      'value'=>$record[$fieldKeys['State']]),
        array('name'=>'primary_address_postalcode', 'value'=>$record[$fieldKeys['Zip Code']]),
        array('name'=>'primary_address_country',    'value'=>$record[$fieldKeys['Country']]),
        array('name'=>'phone_office',               'value'=>$record[$fieldKeys['Work Phone']]),
        array('name'=>'phone_other',                'value'=>$record[$fieldKeys['Other Phone']]),
        array('name'=>'email',                      'value'=>$record[$fieldKeys['Email']]),
        array('name'=>'account_name',               'value'=>$record[$fieldKeys['Company Name']]), // relation
        array('name'=>'account_id',                 'value'=>$accountId), // relation
        array('name'=>'assigned_user_id',           'value'=>$user_guid)
    )
  );
  // make the soap call to create the (related) Contact entry
  $result = $soapclient->call('set_entry',$set_entry_params);
  print "\n <br />Contact Entry results:<br />\n<pre>\n"; var_export ($result); print "\n</pre>\n";
  $contactId = $result['id'];
  
  
  /**
  // alternately, if you have both ids, you can set the relationship for these two records using set_relationship
  $set_relationship_params = array(
    'session'     => $session_id,
    'set_relationship_value' => array(
      'module1'     => 'Contacts',
      'module1_id'  => $contactId,
      'module2'     => 'Accounts',
      'module2_id'  => $accountId
    )
  );
  
  $result = $soapclient->call('set_relationship', $set_relationship_params);
  print "\n <br />Relationship results:<br />\n<pre>\n"; var_export ($result); print "\n</pre>\n";

  */
}

Some Improvements for SugarCRM

1. "Import Step 2: Upload Export File" is a confusing title on the second step of the "Import Contacts" wizard. How can one use the words 'import', 'upload', 'export' all at once? It could read "Import Step 2: Select Data File to load"
2. Typographical bug: I saw that the term "custom_delimeted" in the language files. It doesn't actually cause any errors, but b/c the term is used elsewhere (spelled correctly), this typo could potentially lead to a real bug down the line.
   1. ./modules/Import/language/en_us.lang.php
   2. ./modules/Import/ImportStep1.html
   3. ./modules/Import/ImportStep2.php
3. The source is formatted poorly, or practically not at all in some cases, due to various editors using different space and tab settings, and line endings. PHPBeautifier used in a commit hook would solve this in the repo. Coding standards and configuration files like vim modelines would solve this on the developer desktop.

Being open source, I can pass this information on to the developers (or even get involved myself) and will likely see a positive response to these suggested fixes.

Further Resources

• http://www.sugarcrm.com/wiki/index.php?title=SOAP_Intro_and_Practical_Examples
• http://freephile.com/crm/index.php?module=Home&action=TrainingPortal
• http://freephile.com/crm/soap.php
• http://www.sugarcrm.com/wiki/index.php?title=SOAP_in_PHP
• http://dietrich.ganx4.com/nusoap/
• http://www.beanizer.org/site/index.php/en/Articles/Sugar-CRM-integration-with-custom-PHP-applications-I.html

General Public License = A sane copyright license

HyperText Transfer Protocol = the foundation of data communication for the World Wide Web. See HTML

PHP is the HyperText Preprocessor = the most popular programming language for the web