SugarCRM Customization: Custom Workflow E-mails

E-mail has become a popular tool for communication and for a number of purposes.

Within the scope of SugarCRM, the topic usually comes up in relation to the automation of some aspect of a business process, such as alerting a user when a record within the database has been assigned to them.  SugarCRM includes such a feature out of the box, but what if one wanted a bit more flexibility?

Suppose we encounter the following scenario:

Our deployment uses the Leads module to track and qualify potential customers.  Leads enter the system via two different ways: manual entry and web-to-lead.

Entries are required to have an e-mail address or they are not considered valid as our internal process use it for communications and other purposes.  Whenever a new lead is entered, it is automatically assigned to a specific user, who in turn receives an e-mail alerting them of the new record.  However, we want to expand this functionality to also automatically send a generic "Welcome/Thank You For Your Interest" message to the  Lead itself.

How do we go about accomplishing the above?

SugarCRM + IMAP + SSL Problems?

Been away from the blog for a few days.  Busy building some cool tools and others to come.. ;)

Anyway, here is an interesting tidbit which may help save you some headaches when using the SugarCRM e-mail client.

IMAP based e-mail accounts that require the use of SSL have a tendency to cause problems within SugarCRM's e-mail client.  In fairness, the majority of these problems are usually the result of one of the following:

1. Connectivity issues (e.g. firewalls)
2. PHP Configuration

Where SugarCRM could benefit is within the area where the e-mail settings are validated and tested.  SugarCRM does not check for potential problems with either of those potential traps and it tends to complicate the troubleshooting process.  Let's take a more in-depth look at one of these situations and hopefully save ourselves some future hair pulling.

SugarCRM In-Depth: E-mail Folders

The e-mail functionality in SugarCRM is one of the more intricate areas of the application.  It utilizes multiple tables for a variety of tasks, including storing and organizing messages.

Lets take a closer look at how the latter part works in this in-depth analysis of the e-mail folders system of SugarCRM's e-mail client.

To begin, lets take a look at what the default e-mail folder configuration looks like:

Notice that the following three folders automatically appear:

• My Email
• My Drafts
• My Sent Email

These are system folders and are used to house downloaded incoming e-mail, drafts and already sent messages respectively.  They should never be deleted as doing so may result in a loss of data.


Ironically, there are occasions on which the folders will disappear through no action of the user, thus making it difficult to access the e-mail messages they contain.


Troubleshooting this problem helps one obtain a good understanding of the underlying architecture of the e-mail folders system, so lets dive in and take a look.

Calling all GoldMine users....

Are you a GoldMine user considering a switch to SugarCRM?

Perhaps you already took a look at SugarCRM, but want to get a better representation of the experience by using it with some of your old GoldMine data.  

If so, the FREE SugarCRM Express Conversion (SEC) utility was specifically designed with you in mind.

SugarCRM Customization: Search Popups

Sometimes the SugarCRM framework makes it so easy to customize certain things, one is left to wonder if really is that easy. Here is a good example that helps illustrate the point.

Suppose one wants to modify the popup search window that appears when one clicks the Select button in the Contacts subpanel or in general, when relating a record to a contact, e.g. a Note.

Some common customization needs include:

• Adding or removing columns to the ListView

• Adding or removing search fields

To modify the popup search window to apply such customizations:

1. Navigate to /modules/Contacts/metadata and locate the file popupdefs.php
2. Copy popupdefs.php to /custom/modules/Contacts/metadata. (If the folder doesn't exist, create it.)
3. Open popupdefs.php from /custom/modules/Contacts/metadata using a text editor

Taking a close look at popupdefs.php will reveal two areas of importance pertinent to our example.


The listviewdefs array is used to define the list of fields displayed within the ListView of the popup search window. In a very similar manner, the searchdefs array is used to define the fields that appear in the search area of the popup.

Changes to either of those arrays will modify the popup search window.

NOTE: It may be necessary to clear /cache/modules/Contacts for the changes to take effect.

Top 3 Reasons Why I Like SQLyog

Technology circles have a tendency to produce heated, fanatical debates over which software, hardware, design philosophy, etc. is the best. While debate is good, more often than not, those types of debates become more a clash of differing ideologies rather than a civil discussion on the merits of the various options that are available.

There seems to be little value to such discussions and my general philosophy is that no single vendor/design philosophy/technology is the best solution to all problems. One should use the best tool for the task at hand. Sometimes that means using open source solutions like Linux or phpMyAdmin, while on other occasions, it might mean using C# or other Microsoft technology.

That being said, below are the top three reasons why I prefer SQLyog for MySQL administration. Please do not read this as an article on the reasons why SQLyog is better than phpMyAdmin, Navicat or other similar tools. The best solution for you will be determined by your specific needs, not mine.

1. Performance

Performing database operations within SQLyog is usually a matter of 1 or 2 clicks and being a desktop application, refreshes are almost instantaneous. In addition, the multi-pane and tabbed interface makes it easy to view different bits of data without having to navigate to an entirely different area within the application. For example, results for a query are displayed directly below it. If one wishes to modify the query, it is a simple matter of editing the query, below is an image depicting the SQLyog interface:

2. Data Transfer

Ever have a need to move data from one MySQL server to another? SQLyog makes it a cinch.

Built-in functionality allows one to connect to establish silmutaneous connections to multiple MySQL servers and then move databases between them. One can also make a copy of a database within the same server. It is a very simple process, requiring minimal input, as illustrated below:

3. Export options

Often times, it is necessary to export data to other formats for additional analysis, backup or other purposes. SQLyog's export capabilities simplify this process tremendously and offer multiple file formats including HTML, XML and SQL.

Below is a view of the simple, one window interface for exporting data:

By no means is the above a comprehensive list of features found in SQLyog (Community Edition), but they do represent a sampling of features that make it a very useful tool. It should also be noted that the vendor, Webyog, offers an Enterprise level version of SQLyog which includes additional features.

Common SugarCRM Import Problems

Having problems importing data into SugarCRM? Below is a list of some common problems and solutions.

PROBLEM: Import process appears to be working, but ends abruptly (usually at the same point in the overall process on every occasion). e.g. Attempting to import 100 records always stops at record 38.

SOLUTION: More often than not, these situations are the result of improperly formatted data. Some common issues that cause these type of problems include:

• Errant commas within the data
• Carriage return values within the data
• Field mismatches between records

Below is an example of a set of records that demonstrates proper formatting:

Row 1:first,last,phone_home,email

Row 2:angel,magana,888.555.1212,angel@example.com

In the above example, Row 1 represents the column headers or labels, while Row 2 represents an actual record. Were one to assume that each value in the rows is separated by a comma, inspection of Row 1 and Row 2 would reveal that there is an equal number of values in Row 1 as there are in Row 2. That's exactly what we want. Any type of difference would cause a problem.

The folowing demonstrates a file that would cause a problem:

Row 1:first,last,phone_home,email

Row 2:angel,magana,888.555.1212,angel@example.com

Row 3:john,doe,888.555.1313,john@example.com,123 Main Street

Other common problems can be addressed via PHP.INI modifications and are related to:

• Low max_execution_time, max_input_time, post_max_size or upload_max_filesize values

The first two values control the amount of time PHP will allow to expire before it stops processing a script. A setting of 300 should be more than sufficient for either.

The last two settings are used to control the amount of data that can be accepted. A value of 40M or higher should suffice.

Please visit the official PHP site for further details.

SugarCRM Scheduler Explained

The SugarCRM scheduler functionality seems to be a source of confusion for some users. Hopefully this article will help add some clarity to its intended use and mechanics. To begin with, lets examine how the process works as a whole. First, a schedule or time table is defined by an admin level user via the Admin Control Panel. Each entry within the schedule includes not only the parameters for when things should run, but also which task should be performed. Some of the possible tasks are:

• Prune database
• Send campaign e-mails
• Check inbound e-mail

However, in order for any of the tasks to occur, one must process the schedule. The processing is performed outside of Sugar and can be invoked on an ad-hoc basis or periodically via an external tool such as cron (Linux) or scheduler (Windows).

Instructions on which command to use to process the schedule are normally found in the scheduler page. Below is an example:

cd C:\xampp\xampp\htdocs\sugarce52 php.exe -f cron.php

Thus, it is necessary to do at least two things in order to get the scheduler to work. So far, so good.

Confusion usually starts to creep in when one realizes that cron and Windows scheduler also have a time table. The time table for both of those indicate the frequency in which the defined task is performed.

For example, one could configure Windows scheduler or cron to execute the command required by SugarCRM every hour, every day of the week.

NOTE: The rate at which the command is executed via cron or Windows scheduler is only indirectly associated with the time table defined by the admin user within SugarCRM.

Below is an example that illustrates the point:

SugarCRM scheduler is configured to send e-mail campaign messages at midnight, every day of the week.

cron is then configured to run the required execution command at 10 PM, every day of the week.

Despite cron executing its part correctly, Sugar will not perform any actions.

The reason: when cron runs the execution command at 10 PM, the process will examine the time table defined in SugarCRM in order to determine whether or not something should happen.  


Because the Sugar defined schedule is configured to perform its task at 12:00 AM, the process ignores it, since it is not midnight at the time it is examined.

To address this situation, one must either adjust the time at which Sugar is scheduled to perform its task. Alternatively, one can choose to reconfigure the cron schedule to run every hour, at midnight or a few minutes thereafter.

The key to understanding this functionality is comprehending that SugarCRM merely creates the time table, but doesn't actually do anything at the specified times. Utilities such as cron or Windows scheduler are responsible for the latter.

SugarCRM Upgrade Problems

Sometimes technical issues seem to come in waves. This past week, the Sugar Forums highlighted that point quite well. Multiple users reported unusual behavior when attempting to perform upgrades to varying versions of SugarCRM.

Some of the challenges that were reported included:

• Errors such as: Warning: Invalid argument supplied for foreach() in /home/xxxxx/public_html/xxxx/data/SugarBean.php on line 2322
• Inability to click Browse button to select upgrade files or Browse button missing
• Errors when clicking Upgrade Wizard link that prevent upgrade wizard from displaying altogether

The common thread between all of these issues is that SugarCRM in part relies on files stored in the /cache/upload directory in order for the feature to work correctly. Temporarily renaming the /cache folder prior to attempting to access the Upgrade Wizard feature appears to correct the problem.

It is strongly advised that one NOT delete the existing /cache directory as an alternate solution. The danger in doing so is that the /cache/upload directory is used to store e-mail attachments, documents and other files related to Sugar. Deleting that directory would in turn cause those files to be lost. DO NOT DELETE the directory.

Once you have successfully upgraded -- assuming the rename trick solves the problem -- the current /cache/upload directory contents will need to be merged with the contents of the /upload directory that resides in the renamed /cache directory.

SugarCRM Troubleshooting Simplified - Part II

Expanding on my previous post covering some troubleshooting tips for SugarCRM, this article will focus on potential configuration hazards.

A number of problems relating to SugarCRM can be directly attributed to improper PHP configurations. These problems tend to manifest themselves in a variety of ways, but a few common scenarios include:

• Blank page when using Module Loader, Upgrade Wizard or Installer
• Blank page after System Check or after accepting license at install time
• Inability to upload files, i.e. Notes attachments, module or patch install packages, etc.

If you are experiencing any of the above described problems, you should first confirm that you are using a version of PHP that is supported for use with your version of SugarCRM.

Perhaps the most helpful tool for not only obtaining the version number of your PHP install, but other information as well, is to use the built-in phpinf0() function.

In order to use it, create a PHP file with the following text in it:

phpinfo();

Place the file in the root directory of your SugarCRM install and then view it in your browser. The output will display the configuration settings of your PHP installation.

A page detailing your PHP version number and configuration settings will be generated when you view the PHP file in your browser. If it does not, the likely source of the problem is that PHP is not enabled for your site or directory, which likely also explains why your SugarCRM system may not be functioning.

Once you have the version number for PHP, you can verify it is a supported one by matching it up against the official SugarCRM supported platforms matrix. Make sure that you match it up against the matrix that corresponds with your version of SugarCRM as different versions have differing requirements.

Other important PHP settings relating to SugarCRM include:

(Recommended settings specified in parantheses)

• memory_limit (64M)
• session.save_path
• upload_max_filesize (30M)

Remember, if you make a change to your PHP.INI to adjust any of these or other settings, you must restart your web service in order to apply the changes.

Hit Milestone

Just hit my 2,000th post on the SugarCRM Community Forums, joining a small group of community members sharing that same milestone.

Three thoughts immediately cross my mind:

• I may not be dedicating enough time to my running
• This might explain why my books to read queue is almost as long as my Netflix queue
• The keyboard on my laptop broke, but thankfully, it was within the warranty period

Tomorrow I'll run an extra mile to celebrate. LOL

Simple SugarCRM Logic Hook Example

Logic hooks are one of the most powerful features of SugarCRM, however, their power also introduces complexities. If you are not familiar with this feature, more detailed information on the topic can be found on the SugarCRM Developer site.

In a nutshell, their power lies in the fact that they allow a programmer to extend SugarCRM functionality with custom code which in turn can be designed do just about anything.

Some capabilities include:

• Modifying values on a record before it is saved to the database
• Updating other databases based on actions performed within SugarCRM
• Conditional sending of e-mail messages

A common task that users will want to accomplish with a logic hook is to interact with the database at some level when an action is performed within SugarCRM.

Consider the following scenario:

Workflow requirements dictate that the Assigned To value of a Case always match the Assigned To value specified on the Account record to which the Case is linked.

Thus, we must do two things:

1. Determine the assigned_user_id value of the parent Account record

and

2. Ensure that the assigned_user_id value of the Case record matches that of the parent Account

Both steps are easily accomplished via a logic hook and demonstrate the feature's ability to query the database as well as modify the current record's values.

We will use the before_save hook in this example, causing it to trigger whenever a Case record is committed to the database.

First we'll need to create a file called logic_hooks.php and put the following PHP code in it:
$hook_version = 1;
$hook_array = Array();
$hook_array['before_save'][] = Array(1, 'assignUser', 'custom/modules/Cases/autoUserAssignment.php', 'UserAssignment', 'updateCase');


Make sure to save the file to the [sugar]/custom/modules/Cases directory.


The logic_hooks.php file described above provides the parameters necessary for SugarCRM to know not only when the custom code should execute, but also where the code can be found.
Notice the reference to the file autoUserAssignment.php. That is the file that SugarCRM will examine for the custom code that needs to be executed when the appropriate conditions are met. 


Create a PHP file by that name and store it in the [sugar]/custom/modules/Cases directory.
Add the following to said file:


<?php


class UserAssignment {
      function updateCase(&$bean, $event, $arguments) 
      {
           //Query ACCOUNTS table for assigned_user_id value of parent Account
           $case_id = $bean->id;
           $acct_id = $bean->account_id;
$query = "SELECT accts.assigned_user_id FROM accounts accts ";
$query .= "WHERE accts.id = '$acct_id' LIMIT 1";
$results = $bean->db->query($query, true);
$row = $bean->db->fetchByAssoc($results);
$user_id = $row['assigned_user_id'];

           //Change assigned_user_id value on Case
           $bean->assigned_user_id = $user_id;
}
}
?>



That should do it! 


From now on, whenever a user clicks Save on a Case, SugarCRM will automatically set the Assigned To value to match that of the related Account.

SugarCRM Troubleshooting Simplified - Part I

One of the challenges about working with an open source system such as SugarCRM is the sometimes difficult process of ascertaining the exact source of a technical issue.

Some of the complexity can be attribute to the fact the application is open source, but it is not an issue of open source code being of lesser quality or more complex than code in closed source solutions.  It is rooted in the the fact that because it is open, a user/admin can modify any piece of the code they have access to, potentially impacting the system in unexpected ways.  

Troubleshooting a technical issue within SugarCRM can be a bit difficult at times, however, below are some guidelines that should help you more quickly identify the source of a problem you are facing:

Determine if the problem is system wide or module specific.

If the problem is system wide, your troubleshooting efforts should focus on PHP, Web and Database server configuration, as well as the data itself.  Take note of any modules you may have recently installed.  

Try disabling those additional modules, or uninstalling them if possible, then check if the problem persists.  Language packs have a tendency to cause system wide problems when something goes wrong with them and should be at the top of the list.

The source of module specific problems can usually be rooted out fairly quickly, but finding a solution may not be as easy.  

For module specific scenarios, try the following:

• Rename the [sugar root]/cache/modules/[your problem module] directory and try duplicating the problem
• If the problem persists, rename [sugar root]/custom/modules/[your problem module] directory and try duplicating the problem

In most cases, the problem goes away after renaming the /custom directory.  That would indicate that a customization that has been applied to the system is the source of the problem.  

Examining said folder will reveal a number of different files, depending on the type of customizations that have been applied to the system.  Using the renaming technique on those files should help you pinpoint the exact file that is causing the problem and in turn, provide some clues as to where to make the necessary adjustments to correct it. 

To elaborate, rename the [sugar]/custom/modules/[your problem module] directory to its original name and verify the problem has returned.  Now, rename the files contained in the [sugar]/custom/modules/[your problem module] directory in pairs, checking if the problem persists after each rename.  Repeat the process until the problem goes away and that should tell you which file is causing the problem, e.g. vardefs.php, layoutdefs.php, etc.