Setting created and modified user via SugarCRM Webservice API

A Callinize user recently reported that they were getting email notifications for each Call being created. They had assignment notifications turned on, so whenever created_by_user !== assigned_user_id, an email gets sent to the assigned user that looks like:

{The_API_User} has assigned a Call to {Assigned_To_User}.

Subject: 
Status: 
Start Date: 2014-01-03 11:44:35 UTC(+00:00)
Duration: 0h, 3m
Description: {description}

You may review this Call at:
https://your.domain.com/index.php?module=Calls&action=DetailView&record=b64a1b7b-94f5-4b5d-01fd-52c6a2a33041

So, i figured oh we’re not setting the created user… this should be an easy fix. Well, for me at least… it wasn’t. Special thanks to Jason Eggers of SugarOutfitters or I would have never figured this out…

Flags Required

In order to modify them, you must include these flags. And no, it’s not a typo… they’re named inconsistently.

    "set_created_by" => false,
    "update_modified_by" => false,

Fields Needed

Again, not a typo… they’re just inconsistent.

    "created_by" => $userCrmId,
    "modified_user_id" => $userCrmId,

Final Snippet

Putting it all together, here’s the snippet I use in my set_entry calls:

    // Sets: assigned, created, and modified user to $userCrmId
    "set_created_by" => false,
    "update_modified_by" => false,
    "assigned_user_id" => $userCrmId,
    "created_by" => $userCrmId,
    "modified_user_id" => $userCrmId,

    // NOT NEEDED, Kept because:
    // 1. In case future API makes them more consistent. (dont want to revisit)
    // 2. Doesn't break anything (Surprisingly!)
    // 3. Will help people find this posting searching on google ;-)
    "set_modified_by" => false,
    "update_created_by" => false,
    "created_by_id" => $userCrmId,
    "modified_by_id" => $userCrmId,
    "modified_by" => $userCrmId,
    "created_user_id" => $userCrmId,

Note: I tested this with Sugar v4_1 api via rest. I’ve heard it might have changed in the newer rest apis that Sugar 7 has. If anyone knows, please add a comment.

EDIT: After going through all this… It turns out there is a bug in 6.7.1 which was the real problem. I think in Sugar 6.5 and below assignment notifications are only triggered for records which are updated (not created) and the assigned_user_id changes. Please upvote this issue: http://www.sugarcrm.com/support/bugs.html?caseID=63675#issue_63675

SugarCRM Rest API Example: How to get all contacts for an account

This relies on the following PHP Wrapper Class: http://github.com/asakusuma/SugarCRM-REST-API-Wrapper-Class/

For a slightly better formatted answer see my Stackoverflow Post Here.

/**
 * returns an array of contacts that are related to the accountId passed as a param.
 * The array returned will be an array of associative arrays.
 * @param $accountId
 * @param array $contactSelectFields optional sets the different items to return, default includes id, email1, name, title, phone_work, and description
 * @return array
 *
 */
public function getAllContactsAtOrganization( $accountId, $contactSelectFields=array("id", "email1", "name", "title", "phone_work", "description")) {

    $sugar = new Sugar_REST( SUGAR_REST_URL, SUGAR_USER_NAME, SUGAR_PASSWORD);

    $fields = array( "Accounts" => array("id", "name"),
        "Contacts" =>  $contactSelectFields);
    $options = array(
        'where' => "accounts.id='$accountId'"
    );
    $apiResult = $sugar->get_with_related("Accounts", $fields, $options );


    $contacts = array();
    foreach( $apiResult['relationship_list'][0]['link_list'][0]['records'] as $almostContact) {
        $curr = array();
        foreach($contactSelectFields as $key) {
            $curr[$key] = $almostContact['link_value'][$key]['value'];
        }
        $contacts[] = $curr;
    }

    //print_r($contacts);

    return $contacts;
}

Sample Return

Array
(
    [0] => Array
        (
            [id] => 47e1376c-3029-fc42-5ae2-51aeead1041b
            [email1] => johndoe@gmail.com
            [name] => Blake Robertson
            [title] => CTO
            [phone_work] => 8881112222 
            [description] => Opinionated developer that hates SugarCRM's REST API with a passion!
        )

    [1] => Array
        (
            [id] => 4c8e3fcf-8e69-ed7d-e239-51a8efa4f530
            [email1] => csmith@mailinator.com
            [name] => Carolyn Smith
            [title] => Director of Something
            [phone_work] => 832-211-2222
            [description] => She's a smooth operator...
        )
)

For Reference Purposes

Here’s the “rest-data” (nicely formatted)

Used print_r of the php array

Array
(
    [session] => 9j7fm4268l0aqm25kvf9v567t3
    [module_name] => Accounts
    [query] => accounts.id='e583715b-7168-5d61-5fb1-513510b39705'
    [order_by] => 
    [offset] => 0
    [select_fields] => Array
        (
            [0] => id
            [1] => name
        )

    [link_name_to_fields_array] => Array
        (
            [0] => Array
                (
                    [name] => contacts
                    [value] => Array
                        (
                            [0] => id
                            [1] => email1
                            [2] => name
                            [3] => title
                            [4] => phone_work
                            [5] => description
                        )

                )

        )

    [max_results] => 20
    [deleted] => FALSE
)

Post Body

method=getentrylist&inputtype=JSON&responsetype=JSON&restdata={“session”:”iov5a257lk5acsg9l3ll6kuej3”,”modulename”:”Accounts”,”query”:”accounts.id=’e583715b-7168-5d61-5fb1-513510b39705’”,”orderby”:null,”offset”:0,”selectfields”:[“id”,”name”],”linknametofieldsarray”:[{“name”:”contacts”,”value”:[“id”,”email1”,”name”,”title”,”phonework”,”description”]}],”maxresults”:20,”deleted”:”FALSE”}method=logout&inputtype=JSON&responsetype=JSON&rest_data={“session”:”iov5a257lk5acsg9l3ll6kuej3”}

SugarCRM - Update Calculated Fields Nightly

SugarCRM has some powerful calculated fields called Sugar Logic. One limitation though is they are only calculated when a field is saved. I wanted a simple way to display the number of days since X occurred.

So, I created a very simple script which can be run from the Scheduler every night. It basically just iterates through all the records for a user configurable module type and forces the calculation to update. Best of all it does this without changing the modified date, modified by or creating a tracker entry.

This script could also be used one time to just initially seed all the calculated values for a module. The suggestion from the admin guide is to do a mass update but that’ll cause modified dates, etc to change.

The code is hosted on github here: https://github.com/blak3r/sugarcrm-update-calculated-fields

Potential Applications:

1. Days since lead was last contacted.
2. Days since order added to system.
3. Days since a support case was entered.
4. (There’s lots)

My original application was to keep track of how long it was taking our company to ship orders once we had all the information from a customer.

1. I created a workflow rule such that when orderstage == Production Ready, I would set the date field “productionready_date”.
2. I then created a calculated field called “dayssinceproduction_ready” and set the formula to something like abs(daysSince($production_ready_date))
3. Installed the script on my github site and created a scheduler job to run at 3am every night.

We now use the field as a dashlet on our order status tab and it’s used in some reports to be able to look back over the past year and see how well the production team is doing at getting orders out and to be able to do analysis on the orders that took a long time to identify ways to speed them up.

Alternate Strategies

An alternative to using custom fields would be to create a nightly task which just updates a fields. I prefer this approach for the following reasons…

1. I can add new calculated fields using Studio and not have to update code at all on the server. You can also have multiple calculated fields per module.
2. Since Sugar Logic is also applied in Javascript fields, if a date field is changed manually (instead of via a workflow rule), the calculated field updates in realtime while in the edit view and will be accurate on the detail view as well. Otherwise you’d need to create a on save logic hook to do that if nightly synchronization wasn’t enough.

Other alternative is to not calculate the days and just have a date field and design reports to only show records that are within a certain time range. This was the initial approach I used to take, I find that this makes creating reports a LOT harder. Also, if you want to calculate something like the average time an order was fulfilled you can’t do that with Sugar’s Reporting at this time. Plus it’s just easier in my opinion to look at an integer such as 14 vs. 2013-02-18. It also simplifies custom formatting of rows in my dashlets. It’s fairly easy to write some jquery code to highlight based on a number… having to parse dates and calculate them would be slower and more of a pain.

Miss the Red X for Closing Tasks in SugarCRM v6.5?

Within two days of upgrading from Sugar 6.4 -> SugarCRM 6.5, I had two people come up to me and say how they missed the red X there used to be for closing tasks in subpanels.  In Sugar 6.5 they replaced all the little icons with an action dropdown widget.  As a result, it’s now two clicks to close a task, one to click the triangle icon to show the other actions and another to click close.

Fortunately, there is a simple workaround.  Open up /modules/Tasks/Metadata/Subpanels/ForActivities.php and swap the order in which the ‘close_button’ and the ‘edit_button’ appear in the file.   The result is close will be the default and you’ll have to click the triangle to get to edit.   If for some reason you like to edit tasks alot (aka a procrastinator), you’ll hate this change. But, if you’re a task master you’ll love having close be the default.

I suspect you can copy this ForActivities file to the custom folder and make this upgrade safe.  I’m personally banking on the red X making a comeback in Sugar 6.6 so I wasn’t so worried about it for now. 

Note: this process works for pretty much any module.  Just go to the appropriate module/metadata/subpanels folder and open the appropriate file.  For Tasks there are different layouts for “Activities”, “History” and the default view.

Enabling Spellcheck in SugarCRM's WYSIWYG Editor (TinyMCE)

Recently I started noticing a lot of spelling mistakes and typos in our KB Articles. To my surprise I found that the built in spellchecking support that nearly every modern browser has wasn’t working inside the WYSIWYG Editor (the editor used when composing emails/KB Articles). Here’s a guide on how I installed the spellchecker plugin for TinyMCE. It takes about 5 minutes to do. I did this with version 6.4.1 pro but should work in others.

1. Download the spellchecker plugin from: http://www.tinymce.com/download/download.php (at time of writing it was v2.0.6)
2. Extract the contents to  /sugarcrm/include/javascript/tiny_mce/plugins/spellchecker. Probably a good idea to backup the existing files first.
3. Open /include/SugarTinyMCE.php and modify:

   // Put "spellchecker" in the buttons configs.  Note: putting it at the end of list didn't work on the email ui (no idea why/didn't care to find out why) so I put it before styleselect (and also added a seperator)
   var $buttonConfigs = array(
   	'default' => array(
   				'buttonConfig' => "code,help,separator,bold,italic,underline,strikethrough,separator,justifyleft,justifycenter,justifyright,
   									justifyfull,separator,forecolor,backcolor,separator,styleselect,formatselect,fontselect,fontsizeselect,", 
   				'buttonConfig2' => "cut,copy,paste,pastetext,pasteword,selectall,separator,search,replace,separator,bullist,numlist,separator,outdent,
   									indent,separator,ltr,rtl,separator,undo,redo,separator, link,unlink,anchor,image,separator,sub,sup,separator,charmap,
   									visualaid", 
   				'buttonConfig3' => "tablecontrols,separator,advhr,hr,removeformat,separator,insertdate,inserttime,separator,preview,spellchecker"),
   	'email_compose' => array(
   				'buttonConfig' => "code,help,separator,bold,italic,underline,strikethrough,separator,bullist,numlist,separator,justifyleft,justifycenter,justifyright,
   									justifyfull,separator,forecolor,backcolor,separator,spellchecker,seperator,styleselect,formatselect,fontselect,fontsizeselect", 
   				'buttonConfig2' => "", 
   				'buttonConfig3' => ""),
   	'email_compose_light' => array(
   				'buttonConfig' => "code,help,separator,bold,italic,underline,strikethrough,separator,bullist,numlist,separator,justifyleft,justifycenter,justifyright,
   									justifyfull,separator,forecolor,backcolor,separator,spellchecker,seperator,styleselect,formatselect,fontselect,fontsizeselect", 
   				'buttonConfig2' => "", 
   				'buttonConfig3' => ""),
   );
   // Add spellchecker to list of plugins
   var $pluginsConfig = array(
   	'email_compose_light' => 'insertdatetime,paste,directionality,safari,spellchecker',
   	'email_compose' => 'advhr,insertdatetime,table,preview,paste,searchreplace,directionality,fullpage,spellchecker',
   );	
   // Add spellchecker to list of plugins
   var $defaultConfig = array(
   		//... other elements of array not shown
   	'plugins' => 'advhr,insertdatetime,table,preview,paste,searchreplace,directionality,spellchecker',
   

4. At this point, you should now see a spellcheck icon in your editor. If you click on it you will probably get a 403 error. I got around this by making a modification to the .htaccess file in the root of my sugarcrm folder. You must allow access to: /include/javascript/tiny_mce/plugins/spellchecker/rpc.php

   # Replace the line below...
   RedirectMatch 403 /+(soap|cache|xtemplate|data|examples|include|log4php|metadata|modules)/+.*\.(php|tpl)
   # With this version:
   RedirectMatch 403 /+(soap|cache|xtemplate|data|examples|include|log4php|metadata|modules)/+((?!rpc).)*\.(php|tpl)
   

   If you don’t have this file or use IIS, I’m not sure what the equivalent method for restricting access is.

Here’s the result:

Unfortunately, you do have to press the spellcheck button.  You don’t get automatic red highlighting like you do in wordprocessors or gmail :/

Please note that this change is NOT UPGRADE SAFE so you may have to redo these changes after you upgrade. I just now noticed that there appears to be a way to make it upgrade safe by adding files to ‘custom/include/tinyButtonConfig.php’ and ‘custom/include/tinyMCEDefaultConfig.php’; If you try it out and it works, please add a comment.

UPDATE: Revised code 3/21/2012: Original post didn’t add the buttons to Email UI.

Sugar Online Preview -- Allows testing latest beta without having to install!

Want to test the latest version of sugar without having to install it?

Use SugarCRM Online Preview: http://www.sugarcrm.com/crm/SugarOnlinePreview and you’ll get a “cloud” instance to play around with for 7 days.  It takes less than 30 seconds!

This is a great way to test out the new features or see if your bug has been fixed without having to download it, allocate a database, go through the install wizard, etc.  

This preview mode doesn’t seem to be well publicized.  There really should be a link to it on the download page for customers.  I found this link one time before and couldn’t find it again even after signifigant searching around on the sugarcrm.com/forums.   So, bookmark it!

SugarCRM Server Performance Optimization / How to Benchmark Yourself

In an effort to improve the response time of my sugarcrm installation.  I developed a simple performance testing benchmark tool that allowed me to measure the impact each optimization had on the response time.  In this blog post I will a) present what optimizations worked the best for me b) explain how you can do the same and c) provide specific steps and recommendations for users who used the sugarcrm “FastStack” installers as their Apache+Mysql+PHP stack.

Test Methodology

To isolate the performance gain of each optimization, I’m changing one thing at a time and then running a performance benchmark script I wrote using the Apache Jmeter.  Jmeter is a free open source tool for load testing and performance measurement.  At the bottom of this article I provide a brief tutorial if you want to try testing the results yourself.

My test plan mimics the webbrowser.  It logs and fetches about 10 different module list view pages and loops 3 times.  My load test script does not fetch embedded resources in the php. It just calls index.php and passes relevant params such as module=Account, action=ListView.  So, none of the image files, js files, or css files are downloaded.  In this article i’m focusing purely on server side optimizations.  Perhaps I’ll dive into client side optimizations in a seperate article.

One thing that’s important to point out is that all these tests were done to measure the response time of the server when only one user was active.  And while there is generally a correlation between response time and amount of My tests focused on the average response time so just because the optimzations listed didn’t impact response time doesn’t mean that they do not decrease the amount of processing required or be asome of the tweaks I tested that didn’t have an improvement in terms of response time could very well reduce the amount of processing needed which in turn could provide a much greater impact when a lot of users are active.

Test Server Details

Windows Server 2003
Intel Core i7 2 cores @2.9Ghz
2GB Ram
250 Megs of diskspace free.

* All testing was done on SugarCRM Pro Edition v6.4.0 (unless otherwise specified)
* My Database is ~200mb, 1500 Accounts.
* We have ~20 users.  Only 1 active user when I did testing.
* To account for network delays, most results I ran test about 5 times, removed the worst and best and averaged the remaining 3.  

• I was unable to isolate the reason for why Apache 2.2 / PHP 5.3 had such a major performance benefit.  I posted to the sugarcrm forums about it here: http://www.sugarcrm.com/forums/f6/need-help-identifying-cause-speedup-blog-article-its-not-apc-mysql-query-78499/
• The performance improvements with APC opcode cache seem to make my webserver a little unstable.  So, I ended up disabling it.  
• The tests marked with low confidence are ones that I’m not extremely confident on.  I did less tests on them and since the performance impact was so small all of them are kind of within the margin of error.
• Mod Deflate was somewhat of a surprise.  Keep in mind that this test is only retrieving the .php file (none of the resources).  Since mod_deflate compresses the response, it makes sense that this would take more server processing time.  This slight increase in processing time is typically more then made up for by the shortend download time.  In my case, when running the test I was getting average bytes around 250k when deflate was off versus 44k when it was on.  So, I was compressing the html being returned a lot.  I’m still going to keep mod deflate on in production but it’s something to consider if you’re CRM is on your LAN as it might speed things up slightly.

Recommendations For FastStack Users

if you’re like me, I assumed when I installed the FastStack provided by SugarCRM that it would already be pre-optimized for sugarCRM.  This isn’t the case.  After going through this process for my own server, I suspect this was intentional to consume a low memory footprint and generally minimize potential for issuess such as needing to disable opcode cache before upgrading sugar.

First recommendation, install the latest stack.  I don’t know what changes were made in each version of the stack (I’ve looked everywhere for Release Notes and haven’t been able to find them…).  But, I know that between faststack version 6.0 and faststack version 6.4 there were signifigant improvements made.  Apache got upgraded from 2.0 to 2.2, PHP 5.2 —> 5.3, the php version installed with stack 6.4 comes with extension DLLs like php_apc, and php_gd which are compatible… so no need to go through the headache of finding the compatible binaries…  The GD extension is needed for the UI enhancements in 6.4 which make use of sprites.  Note: run repair after switching stacks.

Here’s a bulletted list of changes you should make:

1. Enabled PHP opcode caching (maybe…) — After posting this I found that it was causing my Apache to crash.  I didn’t notice that much of an impact.
2. Enabled Mysql Query Caching / Increase table size
3. Enable mod_expires / mod_deflate 
4. Make sure ajax ui is enabled.

I was planning on posting how to do 1-4 but got kinda of bored by the time I finished this article.  The links I provide below provide good guidance on it.  Or you can leave some comments and I’ll make a new post.

How to Test Against Your Own Tutorial

1. Download jmeter: Apache JMeter - Downloads
2. Run bin/jmeter.(bat|sh)
3. Download my Test Plan from here: http://www.blakerobertson.com/files/…e-Clean-v2.jmx
4. In Jmeter open the test plan (File->Open)
5. Edit variables & run test as shown above.

I have created other more complex TestPlans.  The one I posted is sort of universal since it didnt’ depend on specific records to be around etc.  I have created more advanced scripts which also go to specific records.  You can have it download the CSS/JS files.  You can measure the server CPU / disk usage so you can compare the response time vs. server load.  There is a lot you can do.  If you’re interested in any of these things then please leave me a comment and I’ll see about writing more tutorials.

Useful SugarCRM articles 

All the tweaks I made in this article I got the idea from these articles.  I’d highly recommend reading them.

* Some Quick Pointers on Improving SugarCRM Performance - great overview all around.  Specifically this article has good information on mysql tuning and sugarcrm specific tweaks.

* Improving SugarCRM client-side performance: The server - Another good all around, specifically has great Apache snippets.  

* The developer blog as a ‘performance’ tag.  So, check here for the most up to date: Performance