As a member of the Professional Services team here at Sugar, one of the services that is often requested of us is migrating a client’s data from their existing CRM into Sugar. In the past, our team would write custom migration scripts that read their data, performed some basic cleansing, transformed it into Sugar format, and then imported it into the database. This was a laborious task given the disparate data formats out there and varying levels of data quality–a task which none of us looked forward to doing.

Recently, however, some of us began experimenting with a tool called Open Studio, which is provided by one of Sugar’s technology partners, Talend. Open Studio is an open source data integration suite licensed under GPLv2 and provides a variety of tools for bridging the gap between disparate applications.

Using Talend Open Studio 2.4, the following are the steps that I followed to setup a migration of Note records from a Salesforce.com CSV file, including copying and renaming the associated attachments. Theser steps will work for Sugar Community Edition, Sugar Professional Edition and Sugar Enterprise Edition.

Note that the copy and rename step isn’t necessary for migrating SFDC Note data since their file attachments are already uniquely named with the record id (as it is in Sugar).  However this step highlights some of the additional functionality provided by Talend and is a useful exercise as other CRM systems don’t save their attachments with a corresponding GUID.

First download and install SugarCRM. Then download and install Talend Open Studio.  Next request a backup of your data from Salesforce.com.

Now open Talend Open Studio and follow these steps:

1. Setup a tFileInputDelimited object that points to the SFDC Note.csv file
2. Create a tFileList object that points to the directory that contains your attachment files (setting up the proper filters if more than the attachments exists within the directory)
3. Create a tFileProperties object
4. Right click the tFileList object and select Row->Iterate, and drag the line over to the tFileProperties object
5. Click on the Component tab for the tFileProperties object and click in the File textbox. Press ctrl+space to bring up the contextual selection menu, and select the CURRENT_FILEPATH property for the tFileList object.
6. Create a tMap object
7. Right click the Notes tFileInputDelimited object and select Row->Main, and drag the line over to the tMap object
8. Right click the tFileProperties object and select Row->Lookup, and drag the line over to the tMap object.
9. Create a tFileCopy object
10. Create a database output object (i.e. tMysqlOutput)
11. Right click the tMap object and select Row->*New Output* (main), and drag the line over to the tFileCopy object. Give this output object a unique name (i.e. file_copy).
12. Right click the tMap object and select Row->*New Output* (main), and drag the line over to the database output object (tMysqlOutput). Give this output object a unique name (i.e. notes)
13. Double click the tMap object to bring up the map editor.
14. On the left hand side of the map drag the column that contains the file name of the attachment down to the basename column on the tFileProperties schema. Select Inner Join.
15. Drag the columns from the Notes schema on the left hand side over to the corresponding notes schema on the Right hand side. Make sure that your schema types are setup properly to avoid runtime errors. (i.e. date fields are set to Date, Boolean fields are set to Boolean, etc)
16. Create a new column called new_file_name on the file_copy schema.
17. Drag the column containing the id from the left hand side notes schema over to the new_file_name column on the file_copy schema
18. Click OK to close the map editor.
19. Click the Component tab for the tFileCopy object.
20. Enter the CURRENT_FILEPATH property for the tFileList object into the File Name text box as you did for the tFileProperties object (step 5).
21. Select your destination directory (for Sugar attachments, this is cache/uploads)
22. Select the Rename checkbox and enter the new_file_name property for the file_copy object into the Destination filename textbox (for the above scenario it would be file_copy.new_file_name)
23. Click the Run tab.
24. Select the Statistics checkbox if you want to see where in the migration the job currently is, plus how fast the data is being migrated over.
25. Click Run.

This should get you going to handle a basic migration of Notes data with copy and rename of the attachments. Hopefully, you will be able to take this and extend it to handle migration of data from any source into Sugar.

With our 5.1 release approaching, one of the features that I was most looking forward to was the revamped Sugar Wireless functionality.  I’m a devoted iPhone user, and I’m usually in need of getting to information in Sugar quickly.. but I don’t want to lug my laptop around with me everywhere I go.

Prior to 5.1, Sugar’s wireless capabilities didn’t provide me with much help as a customer support person, for the simple fact that the Cases module wasn’t exposed.  Sure, I suppose I could have hacked it together at some point in the past, but knowing that we would revamp Wireless eventually, I never got around to it.  Until now!

I spent a few minutes recently seeing how functional Sugar Wireless was for a customer support person….the initial verdict was “not very”.  Yes, Cases are now available, but the absence of Notes is something of a non-starter for companies who are using Sugar Portal to manage their customer interactions (like SugarCRM Inc. ourselves).  The thinking was that Notes were primarily used as a vehicle for file attachments — a decision that I disagree with.  My support engineers use Notes as the most common method of communication with our customers; leaving it out of Wireless cripples our ability to use Wireless.

Would I let that stop me, though?  Of course not!  So I spent a little bit of time over the last couple of days looking into how to customize Sugar Wireless 5.1, and I figured I’d share my findings with the developer community.

(As an aside, I’ll mention here that I haven’t really spent a lot of time understanding MVC and much of the metadata introduced with Sugar 5.0.  I’m not a developer; if I’d filled out Clint’s Developer Survey, I would have called myself a “Fearless Hacker”.  Most of my time spent with my fingers in Sugar’s code dates back before the 5.0 days…)

I set out with the following objectives in mind:

• Modify the Cases views in Wireless to include some custom fields that we rely upon
• Add the Notes module, related to Cases
• Make it all upgrade-safe

There were a few changes that needed to be made to the 5.1 code base in order to support these objectives.  Those changes will be included in the final released version of 5.1, but are not available in 5.1 RC.  Big thanks to SugarCRM software engineer John Mertic for sneaking the necessary changes into Wireless while 5.1 is hunkering down for release.

Customizing the Cases module

First things first, get my custom fields to appear in the Cases module.  Without certain key pieces of information, it would be difficult for a support engineer to glean enough information to do anything useful.

I copied three files from SUGARROOT/modules/Cases/metadata/ to SUGARROOT/custom/modules/Cases/metadata:

• wireless.editviewdefs.php
• wireless.searchdefs.php
• wireless.subpaneldefs.php

Once there, I could edit these files in an upgrade-safe manner, since they’ll override the stock files in SUGARROOT/modules/Cases/metadata.

To wireless.editviewdefs.php, I added lines into the ‘panels’ array, like:

array(’product_category_c’),
array(’sugar_edition_c’),
array(’sugar_version_c’),
array(’deployment_c’),

I also added a line in read-only mode:

array(array(’name’=>’Support_Service_Level_c’, ‘displayParams’=>array(’wireless_detail_only’=>true,)),),

Your field names will most presumably be different than mine, but the rest of the syntax will be the same.

In wireless.searchdefs.php, I added lines into the ‘layout’ array for ‘name’ and ’status’, which would make searching easier.  Especially for ‘name’, or Subject as it would appear through the UI, this is helpful if you don’t know the case number, and Wireless will obey the % wildcard in the search field.

In wireless.subpaneldefs.php, I added this block into the ’subpanel_setup’ array:

‘notes’ => array(
‘order’ => 1,
‘module’ => ‘Notes’,
‘get_subpanel_data’ => ‘notes’,
‘title_key’ => ‘LBL_NOTES_SUBPANEL_TITLE’,
),
More on this later, when we get to adding the Notes module…

Finally, with regards to the Cases module, I wanted to add the Number to the ListView for search results.  Two steps were needed for this:

1)  Create a directory SUGARROOT/custom/modules/Cases/views/ and add a view.wirelesslist.php file to that new directory.  This will allow me to subclass out the main wireless ListView:

require_once(’include/MVC/View/views/view.wirelesslist.php’);

/**
* ViewWirelesslist extends SugarWirelessView and is the view for wireless list views.
*
*/
class CasesViewWirelesslist extends ViewWirelesslist
{
public function display(){
// print the header
$this->wl_header();
// print the select list
$this->wl_select_list();

// check for presence of parent_id — this is the subpanel list view
if (isset($_REQUEST[’parent_id’])){
$this->wl_subpanel_list_view_display();
}
// normal list view display
else{
$this->wl_list_view_display();
}

// display the list view
$this->ss->display(’custom/modules/Cases/tpls/wirelesslist.tpl’);

// print the footer
$this->wl_footer();
}
}

2)  Create a directory SUGARROOT/custom/modules/Cases/tpls/ and copy the file SUGARROOT/include/SugarWireless/tpls/wirelesslist.tpl here, as referenced in the ‘display the list view’ section above.  Then, I built out from this line:

<a href=”index.php?module={$MODULE}&action=wirelessdetail&record={$record.ID}”>{$record.NAME}</a>

To these lines:

{if $MODULE == “Cases”}
<a href=”index.php?module={$MODULE}&action=wirelessdetail&record={$record.ID}”>{$record.CASE_NUMBER}  {$record.NAME}</a>
{else}
<a href=”index.php?module={$MODULE}&action=wirelessdetail&record={$record.ID}”>{$record.NAME}</a>
{/if}

Adding the Notes module

Most of the changes necessary for Notes are upgrade-safe. First was the Cases subpaneldefs, covered above.

Next is creating Wireless metadata files for the Notes module.  Since these were being written from scratch, I copied the following files from SUGARROOT/modules/Calls/metadata/ to SUGARROOT/custom/modules/Notes/metadata:

• wireless.editviewdefs.php
• wireless.searchdefs.php
• wireless.subpaneldefs.php

Then edited those files to replace ‘Calls’ with ‘Notes’.

In wireless.editviewdefs.php:

$viewdefs[’Calls’][’EditView’] = array(

became

$viewdefs[’Notes’][’EditView’] = array(

And equivalent changes in the other two files.

Upgrade-Safe?

Up to this point, everything should be upgrade-safe. Unfortunately, there’s one step that won’t be upgrade-safe…

In the file SUGARROOT/include/MVC/Controller/wireless_module_registry.php, I needed to add Notes to the registry:

‘Notes’ => array(),

[EDIT, August 11, 2008:  John Mertic pointed out to me how to make this step upgrade-safe as well.]

One more change to make, and this one is upgrade-safe as well.  In the directory SUGARROOT/custom/include/MVC/Controller, create a file named wireless_module_registry.php, which will contain this:

<?php

?>
This controls the hyperlinks within the Wireless application to keep your browser within Wireless, instead of going back to the main Sugar application.  Without this step, the links will send you to your main Sugar application, which isn’t optimized for your mobile browser.

Conclusion

All in all, not too painful!  I did my development on a local instance, and tweaked things to the point where I was happy with the appearance, then made my changes in our production instance.  From a testing perspective, you can use your computer’s browser by logging into the mobile interface:

http://YOUR.SUGAR.SITE/index.php?module=Users&action=Login&mobile=1

I hope you find this useful.  If you come up with other ways to extend Sugar Wireless, let’s hear them!

You love the default dashboard charts that come with Sugar 5.0, but you want a new chart dashlet.  With Sugar Professional you can quickly build a report and then publish the report chart to your Sugar dashboard or homepage.  But what if your are using Sugar Community Edition or you want to create a chart that is not possible to build with Sugar Reports?

One of our top community members, kuske, has written up an excellent forum post on the step-by-step process of how to build a new chart dashlet.  Enjoy!

So you’ve created a new module in Sugar 5.0 with Module Builder, but now you want to import records into that module. Unfortunately in Sugar 5.0, the import function is not automatically enabled for new custom modules. We have addressed this issue in the upcoming Sugar 5.1 but that doesn’t help you today.

For those of you working with Sugar 5.0, here is a link to a sample module where I enabled the import function. You will need to take a look at the code to see how to do the same for your custom modules. I built this in Sugar Community Edition, so it will only install on Sugar 5.0.0 CE. However the approach is exactly the same for the Sugar Professional and Enterprise Editions.

Details
This module package contains a very basic module called “Work”. I built this module in Module Builder (only took 2 minutes, gotta love MB!) based on the Issue template.

When you unzip the module package, you will see directories called EnableImport, icons and SugarModules. Everything in the icons and SugarModules directories is exactly what you get when you create a module with Module Builder and publish the module package.

The EnableImport directory is where I put the necessary code to enable the import function. This directory contains some sub-directories and five files. I will explain those five files below. I also modified the manifest.php for this package to copy over the required files for enabling import and adding in the menu item on the Work module.

Required Files

1. EnableImport\menus\Import_zz_Work.php - This file is used by the manifest.php to add the Import item to the Work module’s shortcuts menu.
2. EnableImport\modules\Import\config.php - This file registers the Work module (zz_Work) as a module that can be imported.
   NOTE: This is the one out-of-the-box file that you must modify which means this change is not upgrade-safe. You will need to manually merge the one-line change in this file back into your code when ever you do upgrades in the future.
3. EnableImport\modules\Import\Importzz_Work.php - Contains the logic for actually doing the import. If you need to do any special logic (data manipulation, creating special relationships, etc), you will create special functions in this file that get executed at import.
4. EnableImport\modules\zz_Work\field_arrays.php - This file is necessary but unfortunately a relic from the past. Up until Sugar 5.1, the Import module does not use the standard vardefs for defining a module’s required fields but rather keys off of this field_arrays.php file that we used back in Sugar 1.0 and 2.0. So you will need to register all of your module’s required fields in this file otherwise your import will fail. Thankfully Sugar 5.1 finally puts this dependency to rest.
5. EnableImport\modules\zz_Work\Import.php - This file actually calls the Import module code when you click on the Import button.

On a final note, be sure to take a look through the manifest.php file for this package to see how I modified it to install these required files.

Have fun and let me know how this works for you.

Clint

I have recently been asked to answer this question a few times, “Does SugarCRM require GUIDs for primary keys in the database? ”

At a high level, Sugar was designed to allow the primary keys to be replaced by any unique string. This could either be a different GUID algorithm, a key that has some meaning (such as bean type first, followed by info), an external key, and/or auto-incrementing numbers.

The GUIDs in Sugar are generally only used as unique primary keys.  They have no other special meaning.  We don’t bother to look at what is in the string and do not rely on what they contain other than the ability to match them against records in the DB and having the include/utils.php function create_guid() return a valid one. Because all of our relationship tables are typed, we link two records (such as an account record with a contact record) with a specified ID in the record type relationship table (e.g. accounts_contacts) instead of just storing the ID and searching to find out what type has that ID.

We chose GUIDs rather than auto-incrementing keys to allow for easier data synchronization across databases. This data synchronization issue comes into play when the Sugar Offline Client (part of Sugar Enterprise) syncs data between the main Sugar installation and the Offline Client or when developers use the Sugar SOAP APIs or a tool like Talend for data synchronization.

The strategy of the Offline Client using GUIDs for primary keys was very easy to implement and we don’t have to worry about data conflicts. If you change the system to use some other ID scheme and do need to accommodate data synchronization across data stores, then you would have to either partition the IDs ahead of time or work out a system similar to what we have for cases and bugs where we have a server ID that is globally unique and a incrementing case or bug number. This is a moderate customization though which would require some careful planning and implementation.

However if you don’t care about data synchronization issues, then you can certainly change the primary key format to some other unique string.

To implement a new primary key method or to import existing data with a different primary key format and then rely on the existing GUID mechanism for new records, there are a few things to look out for:

• The system expects primary keys to be string types and will format the SQL with quotes. If you change the primary key types to an integer type, you might have SQL errors to deal with since we put all ID values in quotes in our generated SQL. The database might be able to be able to ignore this issue. MySQL running in Safe mode will have issues for instance.
• Case-sensitivity can be an issue. IDs “abc” and “ABC” are typically treated the same in MySQL. Some other CRM systems from which people were migrating to SugarCRM were using case sensitive strings as their IDs on export. If this is the case, and you are running MySQL, you need to run an algorithm on the data to make sure all of the IDs are unique. One simple algorithm is to MD5 the ids that they provide. A quick check will let you know if there is a problem. If you imported 80,000 leads and there are only 60,000 in the system, some might have been lost due to non-unique primary keys resulting from this case-sensitivity issue.
• Sugar only tracks the first 36 characters in the primary key. Any replacement primary key will either require changing all of the ID columns with one of an appropriate size or to make sure you don’t run into any truncation or padding issues. MySQL in some versions has had issues with Sugar where the IDs were not matching because it was adding spaces to pad the row out to the full size. MySQL’s handling of char and varchar padding have changed in some of the more recent versions. To protect against this, you will want to make sure the GUIDs are not padded with blanks in the DB.

Jacob

I’ve been doing some testing over the last few months on our new Data Center Edition (DCE) of SugarCRM. One of the key components that’s been giving me trouble was our NFS. I could successfully test with hundreds of users on local disk, but switching to NFS would cause a cascading failure where the system would become almost unresponsive.

The tests on NFS started out fine. I routinely had response times around 170ms for the login page. That’s the average response for the local disk tests on these servers. The problem started as soon as my "users" would start going down multiple paths of code. The CPU load would spike and response times would jump. Both would continue to get worse the longer the test ran.

Took some research and talking with one of the core engineers at Zend before I finally isolated the problem. PHP’s realpath_cache_size setting was the culprit.

realpath_cache_size is used by PHP to keep from having to look up file names. Every time you perform any of the various file functions or include/require a file and use a relative path, PHP has to look up where that file really exists. PHP caches those values so it doesn’t have to search the current working directory and include_path for the file you’re working on.

Since the default setting of realpath_cache_size is only 16K, Sugar was filling that up pretty quickly with 50 instances of Sugar running at the same time on one server. I had never seen any ill-effects from this on the local disk runs, but NFS was the perfect breeding ground for this problem. Simple lstat calls take roughly 10x longer to respond than the standard local disk because of the network overhead. Those slight delays—ten-thousandths of a second—were just enough to cause response times to jump.

The fix was easy enough. Up the realpath_cache_size setting. Finding the sweet spot required some trial and error though. My current setting is 128k. I ran tests with 32k and 64k. 32k is still too small and 64k was ok but I still saw random spikes which I think were caused by the cache filling up. Since there’s no way to expose the realpath cache data in PHP, there’s no way to know for sure what is happening inside it. If I have some downtime in the next week or two, I may throw together a quick extension for getting that data so I can make a quick monitor script.

In the meantime, setting realpatch_cache_size to a value between 64k and 128k should work fine for you if you’re serving Sugar off of an NFS. You’ll notice some benefits on a local disk too, but a local disk can keep up with Sugar even without the the optimal realpath caching. Keep in mind that each PHP process allocates memory for the cache, regardless of how much of it is used. For example, if your web server can handle 100 requests to PHP files and all of them run at the same time, PHP will allocate 12.5 megs of memory if your realpatch_cache_size is set to 128k.

I experimented with the realpath_cache_ttl setting some too. My tests ran the same regardless of whether it was configured to have a TTL of a few hours or the default 120 seconds.

One of our top community members, andopes (199 forum posts in the past month!), has documented in detail in the Sugar Wiki how to configure a Linux system with Zend Core to connect to a MS SQL Server database using the FreeTDS library.

Very cool!

Only a few days ago I disagreed with how Matt Asay had titled his blog post regarding the US government. So, when I read his post today on “Enterprise Software is dead - ask the Silicon Valley”, I thought, “great - more fodder to disagree with!” However, I was quickly humbled by his insight on the changing landscape of enterprise software.

This quote from Matt Asay’s post:

“As research from IDC shows, enterprise IT departments are increasingly the new software “vendors.” Building off open-source components, these enterprise IT staffs are finding great success in serving their own needs rather than shoveling dollars out the door to big vendors with one-size-fits-all value propositions. This is the 21st Century’s response to the 20th Century’s pillaging of enterprise IT budgets by the big proprietary vendors.”

From my own experience, I could not agree with this more. Increasingly I work with sophisticated enterprise organizations that have significant developer resources at their disposal. I’ve been very pleased to work with these savvy organizations because they “just get it”.

For this exact reason, vendors like Sales”farce”.com cannot compete with a product offering like SugarCRM. The SaaS model is perfect for those organizations, particularly the SMBs, that cannot successfully rollout a CRM system (at least from a technical level). However, there are plenty of SMEs and other enterprises that are not only completely capable of adapting SugarCRM to their businesses, but they need to.

Sugar is making it easier than ever with frameworks like the new MVC architecture released as part of 5.0 that allow presentation and controller extensibility that you just can’t do with SFDC. A great example is the forthcoming Wireless platform Sugar will be releasing. Another great example is the Module Builder. I was able to build a multi-componet module with complex relationships to create, record, and report on surveys for other modules. I was able to quickly build the module without ever having built a module before and then add the methods that I needed for the classes (modules) that I created. I cut my development time by probably two days. I leveraged other parts of Sugar to complete my module as well - like workflow, email templates and lead capture forms.

If you are a developer who is part of an IT team, and you’re reading this blog… then you are the new CRM vendor. How have you extended your CRM system in a way that can’t be done without code?

Technorati Tags: framework, Matt Asay, module builder, MVC, open source, platform

GPLv3 Q&A doesn’t seem like the hot topic we expected it to be. Andy Dreisch presented at SugarCON on the ramifications of GPLv3 on Sugar. I think from the tone of his post he left his presentation under-whelmed by the response (or lack thereof).

For those of you who could not attend Andy’s session, but want to be part of the GPLv3 discussion - The 451 Group is hosting a webinar on Feb 20th, complete with Q&A. I recommend that you attend if you want to understand the future of GPL and the Open Source.

Technorati Tags: open source, GPLv3

In honor of Valentine’s Day:

Rose are red,
Violets are blue,
Don’t know about vardefs?
Then you’re probably screwed.

I love the concept of vardefs. Simply put, Vardefs is a multi-dimensional array in PHP for each module that contains the attributes, properties, and metadata about each field/column in the database. Each vardefs file is located in it’s parent module. Jacob and Ken and probably countless others have document this well on the wiki and in the forums.

So, how practical is the information? Well it comes up all of the time - people want to make stock fields required or audited that aren’t already that way. Here’s an example from the Contacts Module. The use case is that I want to make the birth date field in Contacts, required (in the change log). This comes to us from ./modules/Contacts/vardefs.php:

	‘birthdate’ =>
		array (
			‘name’ => ‘birthdate’,
			‘vname’ => ‘LBL_BIRTHDATE’,
			‘massupdate’ => false,
			‘type’ => ‘date’,
			‘comment’ => ‘The birthdate of the contact’,
			// Added below code to make it required
			‘required’ => true,
		),

That’s it, just refresh the page and it is now a required field subject to JS client-side validation.

Additionally you can add alter the db in the vardefs file as necessary. A quick “Rebuild Database” in Admin >> Repair >> Rebuild Database and away ya go!

Technorati Tags: php, database, vardefs