New in Sugar 6.2: Range Search

bsoremsugar —  March 28, 2011 — 7 Comments

Editor’s Note: This articles kicks off a series of blog posts that detail what new developer features are coming in SugarCRM 6.2, now in beta. In this article, senior software engineer Collin Lee illustrates how to use a new search feature, range search.

Overview

Beginning with the 6.2 release of SugarCRM, we have made enabled range searching on the search forms for date (date and datetime) and numeric (currency, integer, decimal and float) fields.  The following table below lists the out of the box modules and the module’s fields that have range search enabled by default.

Module Field(s) Notes
Calls date_start, date_end
Campaigns start_date, end_date start_date and end_date appear in advanced search form by default
Contracts start_date, end_date, customer_signed_date, company_signed_date start_date and end_date appear in advanced search form by default
Meetings date_start, date_end
Notes date_start, date_end date_entered appears in advanced search form by default (inherited from Basic type)
Opportunities amount, date_closed date_closed appears in advanced search form by default (inherited from Basic type)
Project date_entered, date_modified, estimated_start_date, 

estimated_end_date

estimated_start_date and 

estimated_end_date appears in advanced search form by default

ProjectTask date_entered, date_modified
Quotes date_quote_expected_closed, 

original_po_date,

date_quote_closed,

date_order_shipped,

quote_num,

total_usdollar

total_us_dollar and date_quote_expected_closed appears in advanced search form by default
Tasks date_due, date_start

The phrase “enabled by default” means that the range search capability has already been enabled for the field and if the field appears on a search form layout or is added to a search form layout via Studio, then the search form will render the proper input fields to allow for range searches.  Technically speaking, the corresponding vardef entry for the field has the key enable_range_search set to true.

To enable other numeric or date fields for range search use Studio to select the target field and then locate the checkbox to enable range search.

Figure 1. Enabling range search via checkbox option

 

If you wish to revert to the previous search field paradigm for date and numeric fields, you may uncheck the Enable Range Search checkbox and save the field changes in studio.  The search form will then render the field as a textfield with a single calendar selection widget for date fields and a textfield for numeric fields.

If you wish to manually specify range search support for a field by editing files then here are the three steps to take.

1)   Locate the vardefs.php file where the field is defined.  The field must be of a valid field type (date, datetime, int, float, currency, decimal) and correspond to a column in a database table (meaning it can’t be a non-db field).

2)   Modify the field to include both the enable_range_search and options attributes.  The enable_range_search attribute should be set to true.  For the options attribute, if the field is of date or datetime type then the value should be set to date_range_search_dom; otherwise, it should be set to numeric_range_search_dom.  The following is an example of enabling range search for a vardef entry of currency field type

‘custom_currency’ =>

array (

‘name’ => ‘custom_currency’,

‘vname’ => ‘LBL_CUSTOM_CURRENCY’,

‘dbType’ => ‘decimal’,

‘type’ => ‘currency’,

‘len’ => ‘26,6’,

‘audited’=>true,

‘enable_range_search’ => true,

‘options’ => ‘numeric_range_search_dom’

),

3)   Modify the module’s SearchFields.php file to include the range search fields.  We need to create three entries here to correspond to the three different input value fields that may be sent from the search form.  For most operators, only one input value is used, but when performing a search for values between two ranges, then two input values are used.  Continuing with our example of our custom_currency field, the following block of text needs to be added to the $searchFields array variable in the SearchFields.php file.  If there’s a custom version of this file ( i.e. the file custom/modules/[module]/metadata/SearchFields.php) , you should make the changes there.

‘range_custom_currency’ => array (‘query_type’ => ‘default’, ‘enable_range_search’ => true),

‘start_range_custom_currency’ => array (‘query_type’ =>

‘default’,

‘enable_range_search’ => true),

‘end_range_custom_currency’ => array (‘query_type’ => ‘default’, ‘enable_range_search’ => true),

For range search of date and datetime fields, the above steps will also apply with the exception of an additional attribute ‘is_date_field’ with value set to true that needs to be added to the SearchFields block.   The following is an example.

‘range_date_closed’ => array (‘query_type’ => ‘default’, ‘enable_range_search’ => true, ‘is_date_field’ => true),

‘start_range_date_closed’ => array (‘query_type’ => ‘default’,  ‘enable_range_search’ => true, ‘is_date_field’ => true),

‘end_range_date_closed’ => array (‘query_type’ => ‘default’, ‘enable_range_search’ => true, ‘is_date_field’ => true),

Custom modules created with Module Builder will also have range search enabled by default for certain fields.  The following table lists the sugar object types and the object’s fields that have range search enabled by default.

Object Type Fields Notes
Basic date_entered, date_modified
Sale date_closed date_closed appears in advanced search form by default for modules based off Sale object type

 

Search Operators

The numeric and date type fields have a predefined set of range search operators available.  The operators key and labels are defined in the global language file located in the include/language directory.  Date fields use the date_range_search_dom array definition and numeric fields use the numeric_range_search_dom array definition.

The following table lists the operators available for date fields as well as the corresponding English language labels.  Some of the date operators will create a search value based off on the current date.  For example, a date operator to search values of “This Month” run in the month of January will automatically generate a search with values between and inclusive of January 1 to January 31 for the specified field.  These fields which have values generated automatically are denoted as macro fields in the table below (i.e. the user will not be prompted to enter any date values and a textual substitute will be passed).

Key Label Notes
= Equals
not_equal Not On
greater_than After
less_than Before
last_7_days Last 7 Days Macro
next_7_days Next 7 Days Macro
last_30_days Last 30 Days Macro
next_30_days Next 30 Days Macro
last_month Last Month Macro
this_month This Month Macro
next_month Next Month Macro
last_year Last Year Macro
this_year This Year Macro
next_year Next Year Macro
between Is Between

Here are examples of the search widget for date fields depending on the operator chosen.

Figure 2. Example of date field search widget to search for a value after specified date

Figure 3. Example of date field search widget with macro value “This Month”

Figure 4. Example of date field search widget with “Is Between” operator

The operators for numeric fields are listed in the following table.  Note that there are no operators that will trigger a macro value.

Key Label Notes
= Equals
not_equal Does Not Equal
greater_than Greater Than
greater_than_equals Greater Than or Equal To
less_than Less Than
less_than_equals Less Than or Equal To
between Is Between

The user should may enter thousands separator characters for large numbers and the search code will remove the thousands separator (ex: “1,000,000” is the equivalent of “1000000” for one million).  Currency symbols may also be entered for range searches involving currency fields (ex: “$100” is the equivalent of “100” for one hundred dollars).  There is no validation done prior to submission to ensure that a numeric value is indeed a numeric value.

Date values submitted for searching are assumed to be in the user’s date preference format.  Selecting a date with the calendar widget will ensure a date value in the user’s date preference.  For example, if the user’s date preferences is set to m/d/Y (month/date/year) then selecting a date from the calendar widget will return 07/04/2011 for July 4, 2011.  This date value will be translated to the database format equivalent (Y-m-d) when performing the search against the database or in this case, 2011-07-04.  Therefore, it is imperative for the user to provide a date value that corresponds to their date preference format if they choose to manually enter date values; otherwise, the search code will not be able to correctly translate the value to the proper database format.  This is also no validation done prior to submission to ensure that a date value is a valid date.

Saved Searches

Existing pre-6.2 saved searches for date and numeric fields that are now range search enabled will continue to work as they did prior to 6.2.  That is, the range search field will default to use the equals (=) operator for the saved search field value.  An important thing to note though is that for saved searches created before 6.2, the date value used to populate the search form will be whatever value last specified by the user and stored in the saved_search table.  Normally this should not be a cause for concern except in the case where the user created a saved search with a date value specified in a date preference format different from the date preference format when the saved search is called up again.  For example, assume that a user created a saved search in SugarCRM 6.1 on a date value 07/30/2011 with date preference m/d/Y.  Then after an upgrade, this date value is range search enabled and the user date preference has switched to m-d-Y.  The search form will be populated with continue to be populated with value 07/03/2011 and not the expected 07-03-2011 value.  This is because there was no way for us to know what the prior user date preference format was set to.  The user should take note of this discrepancy and adjust the search value to 07-03-2011 and re-save the saved search to resolve this problem.  This error is present even in pre 6.2 versions and the transformation of date values to a database format in 6.2 resolves this problem going forward.

You will be able to create saved searches using all operators and macro values as well as multiple values for the between operators.  New saved searches created in 6.2 will now have date values converted to the database format before being stored in the saved_search table.  This will allow for saved searches to continue to work and populate the search form with whatever the current user’s date preferences are set to.

Range Search Customizations

Should the need arise to customize the search fields rendered for range searches or to add some custom javascript validation to filter acceptable numbers or dates just override the Smarty template files that render the range search widgets.  For numeric fields, copy the contents of include/SugarFields/Fields/Int/RangeSearchForm.tpl to custom/include/SugarFields/Fields/Int/RangeSearchForm.tpl.  For the date fields, copy the include/SugarFields/Fields/Datetimecombo/RangeSearchForm.tpl to custom/include/SugarFields/Fields/Datetimecombo/RangeSearchForm.tpl.

The following is a simple example of changing the single input field in a numeric range search to be a dropdown list of five possible values.  All you’d have to do is create the file custom/include/SugarFields/Fields/Int/RangeSearchForm.tpl and change the div contents of the section with id {$id}_range_div to:

<div id=”{$id}_range_div” style=”{if $starting_choice==’between’}display:none;{else}display:”;{/if}”>
{if empty($smarty.request.{{$id_range}}) && !empty($smarty.request.{{$original_id}})}
{assign var=”dropdown_value” value=$smarty.request.{{$original_id}}}
{else}
{assign var=”dropdown_value” value=$smarty.request.{{$id_range}}}
{/if}
<select name=’range_{$id}’ id=’range_{$id}’ tabindex='{{$tabindex}}’ size=’1′>
<option value=”-10″ {if $dropdown_value==-10}SELECTED{/if}>-10</option>
<option value=”-5″ {if $dropdown_value==-5}SELECTED{/if}>-5</option>
<option value=”-0″ {if $dropdown_value==0}SELECTED{/if}>0</option>
<option value=”5″ {if $dropdown_value==5}SELECTED{/if}>5</option>
<option value=”10″ {if $dropdown_value==10}SELECTED{/if}>10</option>
</select>
</div>

Figure 5. Numeric range search with custom dropdown for input values

As of version 6.2, we do not yet allow for a simple upgrade safe customization of adding additional operators to the drop down lists (e.g. “This Quarter”, “Last Quarter”, “Next Quarter”).  If you must add additional operators, then here are the following steps that show one way to do it.

1) Customize the dropdown array lists in the global language file(s) (date_range_search_dom for dates and numeric_range_search_dom for numbers).  For example, if we wanted to add a First Quarter operator, we can add to the file custom/include/language/en_us.lang.php:

$app_list_strings[‘date_range_search_dom’] =
array(
‘=’ => ‘Equals’,
‘not_equal’ => ‘Not On’,
‘greater_than’ => ‘After’,
‘less_than’ => ‘Before’,
‘last_7_days’ => ‘Last 7 Days’,
‘next_7_days’ => ‘Next 7 Days’,
‘last_30_days’ => ‘Last 30 Days’,
‘next_30_days’ => ‘Next 30 Days’,
‘last_month’ => ‘Last Month’,
‘this_month’ => ‘This Month’,
‘next_month’ => ‘Next Month’,
‘first_quarter’ => ‘First Quarter’, //Added “First Quarter” operator
‘last_year’ => ‘Last Year’,
‘this_year’ => ‘This Year’,
‘next_year’ => ‘Next Year’,
‘between’ => ‘Is Between’,
);

There should now be a First Quarter choice in the dropdown list of operators for date range searches.  Selecting First Quarter will result in a macro field being created.  In our example, because we have defined the key as first_quarter actual value submitted to the SearchForm code will be [first_quarter].

2.  Alter the include/MVC/View/views/view.list.php.  This step is not upgrade safe, but necessary since there are a lot of files that reference this file.  The code to change is in the prepareSearchForm function.  Instead of loading include/SearchForm/SearchForm2.php, you should load your custom SearchForm2.php implementation (say custom/include/SearchForm/SearchForm2.php).

function prepareSearchForm() {

$this->use_old_search = false;

require_once(‘custom/include/SearchForm/SearchForm2.php’);

}

3.  Write the code to handle your custom operator in the generateSearchWhere method of SearchForm2.php.  This is already a method that is doing a lot of work.  In the future, when we can decouple the query generation from the various search operators, this method will have a much smaller footprint.  For now, locate the area in the code that falls between the switch statements to handle next_30_days and this_year.  We may now insert our custom operator for first_quarter handling here.  In our example, we assume first_quarter to be the dates between January 1 and March 31, inclusive.  Also, for brevity, this example covers mysql queries only.

case ‘this_quarter':
if ($GLOBALS[‘db’]->dbType == ‘mysql’) {
//Get the current year
global $timedate;
$current_year = $timedate->getNow(true)->year;
$where .= “{$db_field} >= ‘{$current_year}-01-01′ AND {$db_field} <= ‘{$current_year}-03-31′”;
}
break;

7 responses to New in Sugar 6.2: Range Search

  1. 

    This is great stuff! Will this be for all editions?

    • 

      Yes, this will be enabled for all editions. The above mentioned module support will depend on the version though, but the functionality will be there.

  2. 

    Great
    Thanks

  3. 

    Range Search doesn’t appear to work in Pop-up searches. For example when selecting targets into specific target list the range functionality is not present, while works fine in the advanced search for targets in general.

    Could you please give some guidance on how to add such functionality into Pop-Up searches?

    Thank you!

  4. 

     Thank you SugarCRM! This makes advanced search so much more valuable.

  5. 

    Is Between not working
    my code
    searchdefs.php
    ‘order_amount’ =>
    array(
    ‘name’ => ‘order_amount’,
    ‘vname’ => ‘LBL_ORDER_AMOUNT’,
    ‘dbType’ => ‘decimal’,
    ‘type’ => ‘float’,
    ‘audited’ => true,
    ‘len’ => ‘26,6’,
    ‘enable_range_search’ => true,
    ‘options’ => ‘numeric_range_search_dom’
    ),
    SearchFields.php
    ‘range_order_amount’ => array (‘query_type’ => ‘default’, ‘enable_range_search’ => true),
    ‘start_order_amount’ => array(‘query_type’ =>’default’,’enable_range_search’ => true),
    ‘end_range_order_amount’ => array(‘query_type’ => ‘default’, ‘enable_range_search’ => true),

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s