Convert the country text field in SugarCRM CE or SuiteCRM to a drop-down list

If you need to change the country text field into a drop-down list in any of the sugarCRM’s modules this post will show you how.

Step 1 Create the vardef for your country field

custom/Extension/modules/[THE_MODULE_NAME]/Ext/Vardefs/custom_primary_address_country.php

$dictionary['<MODULE_NAME>']['fields']['primary_address_country']['comments']='Country for primary address';
$dictionary['<MODULE_NAME>']['fields']['primary_address_country']['group']='primary_address';
$dictionary['<MODULE_NAME>']['fields']['primary_address_country']['options']='countries_dom';
$dictionary['<MODULE_NAME>']['fields']['primary_address_country']['type']='enum';

Step 2 Make a copy of the edit view template for the Address fields

Create a new directory here:
/custom/include/SugarFields/Fields/[CUSTOM_FIELD_TYPE_NAME]/EditView.tpl

Then copy the EditView template from the lcation below into the new directory
/include/SugarFields/Fields/Address/EditView.tpl

Step 3 Edit the copy of template in your new directory

Find this line:

<input type="text" name="{{$country}}" id="{{$country}}" size="{{$displayParams.size|default:30}}" {{if !empty($vardef.len)}}maxlength='{{$vardef.len}}'{{/if}} value='{$fields.{{$country}}.value}' tabindex="{{$tabindex}}">

and change it to the following:

<select name="{{$country}}" width="{{$displayParams.size|default:30}}" id="{{$country}}" title="{{$vardef.help}}" tabindex="{{$tabindex}}" {{if isset($displayParams.script)}}{{$displayParams.script}}{{/if}}>
{if isset($fields.{{$country}}.value) && $fields.{{$country}}.value != ''}
 {html_options options=$fields.{{$country}}.options selected=$fields.{{$country}}.value}
{else}
 {html_options options=$fields.{{$country}}.options selected=$fields.{{$country}}.default_value}
{/if}
</select>

Step 4 Change the EditView defs to display your new field type

In /custom/modules/[THE_MODULE_NAME]/metadata/editviewdefs.php change “type” to your new custom field type name.

 0 =>
      array (
        'name' => 'primary_address_street',
        'hideLabel' => true,
        'type' => '<CUSTOM_TYPE_NAME>',
        'displayParams' =>
        array (
          'key' => 'primary',
          'rows' => 2,
          'cols' => 30,
          'maxlength' => 150,
        ),
        'label' => 'LBL_PRIMARY_ADDRESS_STREET',
      ),

If /custom/modules/[THE_MODULE_NAME]/metadata/editviewdefs.php does not exist just make a change to the editview in studio and it will be automatically created.

Step 5 repeat the above steps for all modules you want to change the address fields on

The address country field exists on Accounts, Contacts, Leads and Targets by default. But in accounts for example it is billing_address_country and shipping_address_country you will just have to check the field names before makeing the changes.

The countries_dom already exists in sugar and contains the list of countries. This can be edited in the admin panel’s dropdown editior.

Posted in SugarCRM development and customisation, SugarCRM development development hints and tips

How to Change the Account Name field back to a relate field in Leads

In sugarCRM CE the relationship between Leads and Accounts has been changed so that in the Leads editview, the account name does not show as a standard relate field. If for whatever reason you would like to change this back to a standard relate field do the following:

Add the following file in custom/Extension/modules/Leads/Ext/Vardefs/vardefs_change.php

vardefs_change.php:

$dictionary['Lead']['fields']['account_id']['name'] = 'account_id';
//$dictionary['Lead']['fields']['account_id']['rname'] = 'id';
//$dictionary['Lead']['fields']['account_id']['id_name'] = 'account_id';
//$dictionary['Lead']['fields']['account_id']['group'] = 'account_name';
$dictionary['Lead']['fields']['account_id']['vname'] = 'LBL_ACCOUNT_ID';
$dictionary['Lead']['fields']['account_id']['type'] = 'id';
//$dictionary['Lead']['fields']['account_id']['table'] = 'accounts';
//$dictionary['Lead']['fields']['account_id']['isnull'] = 'true';
//$dictionary['Lead']['fields']['account_id']['module'] = 'Accounts';
//$dictionary['Lead']['fields']['account_id']['dbType'] = 'id';
$dictionary['Lead']['fields']['account_id']['reportable'] = false;
//$dictionary['Lead']['fields']['account_id']['source'] = 'non-db';
//$dictionary['Lead']['fields']['account_id']['massupdate'] = false;
//$dictionary['Lead']['fields']['account_id']['duplicate_merge'] = 'disabled';
//$dictionary['Lead']['fields']['account_id']['hideacl'] = true;

$dictionary['Lead']['fields']['account_name']['name'] = 'account_name';
$dictionary['Lead']['fields']['account_name']['rname'] = 'name';
$dictionary['Lead']['fields']['account_name']['id_name'] = 'account_id';
//$dictionary['Lead']['fields']['account_name']['id_name'] = 'id';
$dictionary['Lead']['fields']['account_name']['vname'] = 'LBL_ACCOUNT_NAME';
$dictionary['Lead']['fields']['account_name']['join_name'] = 'accounts';
//$dictionary['Lead']['fields']['account_name']['group'] = 'account_name';
$dictionary['Lead']['fields']['account_name']['type'] = 'relate';
$dictionary['Lead']['fields']['account_name']['link'] = 'accounts';
$dictionary['Lead']['fields']['account_name']['table'] = 'accounts';
//$dictionary['Lead']['fields']['account_name']['isnull'] = 'true';
$dictionary['Lead']['fields']['account_name']['module'] = 'Accounts';
//$dictionary['Lead']['fields']['account_name']['dbType'] = 'varchar';
$dictionary['Lead']['fields']['account_name']['len'] = '255';
$dictionary['Lead']['fields']['account_name']['source'] = 'non-db';
//$dictionary['Lead']['fields']['account_name']['unified_search'] = true;
$dictionary['Lead']['fields']['account_name']['reportable'] = false;
$dictionary['Lead']['fields']['account_name']['save'] = false

Then in custom/modules/Leads/metadata/editviewdefs.php

Change the account_name definition as follows:

array (
0 =>
array (
'name' => 'account_name',
'type' => 'relate',
// 'validateDependency' => false,
//'customCode' =&gt; '<input id="EditView_account_name" type="text" disabled="disabled" maxlength="255" name="account_name" size="30" value="{$fields.account_name.value}" />',
),
1 => 'phone_fax',
),

And in the form hidden fields array comment out the account_id field as this field will interfere with the Javascript call back that populates the id for relate field.

form' => 
      array (
        'hidden' => 
        array (
          0 => '<input type="hidden" name="prospect_id" value="{if isset($smarty.request.prospect_id)}{$smarty.request.prospect_id}{else}{$bean->prospect_id}{/if}">',
          //1 => '<input type="hidden" name="account_id" value="{if isset($smarty.request.account_id)}{$smarty.request.account_id}{else}{$bean->account_id}{/if}">',
          1 => '<input type="hidden" name="contact_id" value="{if isset($smarty.request.contact_id)}{$smarty.request.contact_id}{else}{$bean->contact_id}{/if}">',
          2 => '<input type="hidden" name="opportunity_id" value="{if isset($smarty.request.opportunity_id)}{$smarty.request.opportunity_id}{else}{$bean->opportunity_id}{/if}">',
        ),

Now go to admin then repair and do a quick repair and rebuild and that should be it.

Posted in SugarCRM development and customisation

Working out percentages in a Mysql query for reporting tool zuckerreports

Although not strictly Sugarcrm related I had requirement the needed me to work out the percentage of opportunities which had the sale_stage field set to ‘Closed Lost’ and ‘Closed Won’. This would be displayed as part of sugar via Zuckerreports, effectively showing a report with the percentage of all Opportunities that were Won and Lost.

As it turned out this was easy:

SELECT
   sum(sales_stage="Closed Won")/count(*) * 100 AS won_pct,
   sum(sales_stage="Closed Lost")/count(*) * 100 AS lost_pct
FROM opportunities

The second part of this report was much more complicated as follows:

For our purposes the opportunities table contains the following fields: id and sales_stage. The opportunities_cstm table contains the fields id_c and sales_stage_before_closed_c. id_c is what relates the two tables.

sales_stage contains the values from 1 to 10 and also either ‘Closed Lost’ or ‘Closed Won’. In the actual SugarCRM instance 1 to 10 represents percentage bands from 0-9% to 90-99% and closed lost is 0% and closed won is 100%.

sales_stage_before_closed_c is the percentage band that the opportunity was at before it was closed.

So in my actual query I needed to display a percentage for each sales_stage on how many opportunities reached this stage and resulted in a won opportunity and how many reached this stage and resulted in a lost opportunity.

After much brain strain and help from a college:

SELECT opportunities_c_top.sales_stage_before_closed_c AS 'Sales Stage',
FORMAT(
( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join 
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Won' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Won',
FORMAT(
 ( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join 
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Lost' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Lost'

FROM `opportunities_cstm` opportunities_c_top join 
 `opportunities` opportunities_top
 on opportunities_top.id = opportunities_c_top.id_c
WHERE (opportunities_top.`sales_stage` = 'Closed Won' OR opportunities_top.`sales_stage` = 'Closed Lost') 
GROUP BY opportunities_c_top.sales_stage_before_closed_c

An sql fiddle showing a working example: Fiddle

Posted in General Programming

Logging custom error messages in SugarCRM

Depending on what your Log Level is set to in the system settings you can log errors to the sugarcrm.log located in the root folder with the following code snippets

$GLOBALS['log']->fatal("My fatal message");

$GLOBALS['log']->debug("My debug message");

$GLOBALS['log']->info("My log message");
Posted in SugarCRM development development hints and tips

Redirect with a custom error message in SugarCRM

If your looking to redirect with a custom error message in Sugarcrm the following code snippet shows you how to do it. This could be used within a controller action for example if a certain condition is not met then redirect back to the module and display an error.

//Append error msg 
SugarApplication::appendErrorMessage('Action Prohibited: Cannot Create or Edit Products.');
        //set params
        $params = array(
            'module'=> 'RL_SalesHistory', //the module you want to redirect to
            'action'=>'DetailView', //the view within that module
            'record' => $_REQUEST['record'], //the record id
        );
//redirect       
SugarApplication::redirect('index.php?' . http_build_query($params))

Posted in SugarCRM development development hints and tips

Adding custom JavaScript to a view in SugarCRM

If you need to add custom JavaScript to a view in SugarCRM the first thing you need to do is create your script file and place it in the custom folder of the module you want to add the client side functionality to.

Example:

custom/modules/YOURMODULE/customscript.js

Then add your script file to includes part of the viewdefs file. In this example I’m adding it to the edit view of the Opportunities module.

custom/modules/Opportunities/metadata/editviewdefs.php

/Add javascript to editviewdefs
$viewdefs ['Opportunities'] = 
array (
  'EditView' => 
  array (
    'templateMeta' => 
    array (
      'maxColumns' => '2',
      'widths' => 
      array (
        0 => 
        array (
          'label' => '10',
          'field' => '30',
        ),
        1 => 
        array (
          'label' => '10',
          'field' => '30',
        ),
      ),
      'includes' => 
      array (
        0 => 
        array (
          'file' => 'custom/modules/Opportunities/lost_opportunity.js', //call for the js file
        ),
      ), 


An alternative method of adding the JavaScript would be to create a new view for the particular module and then echo out the link to the script file or you could even echo out the JavaScript code directly in the view.

In the example below I create a new edit view in the calls module and then add the JavaScript. I dont want to alter anything so I just call the parent display method and add the script afterwards.

custom/modules/Calls/views/view.edit.php


class CallsViewEdit extends ViewEdit
{

     public function display(){

        parent::display();
        
        echo '<script type="text/javascript">
             var current_status = document.getElementById("status");
                if(current_status.value == "Held"){
                    //do something
                }
             </script>';

     }

}
Posted in SugarCRM development development hints and tips