Date range fields under the advanced search panel

[mathewp]

I need to know how to put 2 input text fields under the advanced search panel in the Leads page. One will be called Start Date and the other one will be called End Date. All records from the lead table whose date_entered field fall in the range between Start Date and End Date will be displayed under results.

Thanks in advance,

Mathew P

[genius786]

Hi Mathew,

You have to use Studio which exists in admin panel. After opening Studio go to the leads folder then click on Layouts -> Search and select advance search where you put the field you want either the field is custom created field or already created field.



Best regards,

[mathewp]

Thanks for your help... Putting the fields in place isn't the problem. I need to find a way to get date from start_date and end_date fields and get all the records that fall in that range.
I want mysql to run the following query:

SELECT * from sugarcrm.leads
where date_entered
BETWEEN start_date AND end_date;

[andopes]

Thanks for your help... Putting the fields in place isn't the problem. I need to find a way to get date from start_date and end_date fields and get all the records that fall in that range.
I want mysql to run the following query:

SELECT * from sugarcrm.leads
where date_entered
BETWEEN start_date AND end_date;

Hi Mathew

You need to create a custom controller for your module (custom/<ModuleName>/controller.php) containing the method action_listview.
Inside this method you need to override the $this->bean as an instance of a new class which extends the default module class.
Inside that module class you need to implement the method create_new_list_query.
This method is defined in the data/SugarBean.php.

Take a look at this two piece of code:

controller:

 <?PHP
require_once('include/MVC/Controller/SugarController.php');
require_once('modules/Opportunities/OpportunityInListView.php');
 
class OpportunitiesController extends SugarController {
    function action_listview() {
        $this->view_object_map['bean'] = $this->bean;
        $this->view = 'list';
        $GLOBALS['view'] = $this->view;
        $this->bean = new OpportunityInListView();
    }
}
?>  

new class:

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
class OpportunityInListView extends Opportunity {
 
    function Opportunity() {
        parent::Opportunity();
    }
    
    function create_new_list_query($order_by, $where,$filter=array(),$params=array(), $show_deleted = 0,$join_type='', $return_array = false,$parentbean, $singleSelect = false){
        $ret_array = parent::create_new_list_query($order_by, $where,$filter,$params, $show_deleted,$join_type, $return_array,$parentbean, $singleSelect);
        $ret_array['select'] .= ", accounts.name account_name, jtl0.account_id account_id, jtl1.name agency_name, jtl2.name advertiser_name ";
        $ret_array['from'] .= "
LEFT JOIN  accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id  AND jtl0.deleted=0 
LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 
LEFT JOIN  accounts jtl1 ON opportunities.agency_id=jtl1.id AND jtl1.deleted=0 
LEFT JOIN  accounts jtl2 ON opportunities.advertiser_id=jtl2.id AND jtl2.deleted=0";
 
        $ret_array['where'] = str_replace("opportunities.agency_name", "jtl1.name", $ret_array['where']);
        $ret_array['where'] = str_replace("opportunities.advertiser_name", "jtl2.name", $ret_array['where']);
 
        return $ret_array;
    }
}
?>  

Kind regards

[malcolmh]

Hi,

Maybe this is what you are looking for http://www.sugarforge.org/projects/enhancedsearch/

[rhoerbe]

I installed enhanced searchin my CE-installation, and tried advanced search. However, when I endered date ranges it did not retrieve any data, although it should. OR and NOT did not restrict the query in any way.

[rhoerbe]

The demo seems to be limited to the user interface, need commercial version.

[kenshiro]

The demo seems to be limited to the user interface, need commercial version.

No, the DEMO is not limitied to the user interface.
The difference between the DEMO and the FULL is that the usage of some operators is not extended to all search type fields.
The reason of this problem is a bug related to the php error level configuration, that will be fixed in the next Enhanced Search release.