Adding an "older than x days" filter

Dear all,

I'm curious if any of you ever came across a customer needing to filter his records based on an "older than x days" condition and how you solved it? 

Typical example: Opportunities with close dates older than 7 days, last interaction older than 30 days etc. 

The operator would be used in list views, dashlets and reports. 

I've found the blog posts indicating how to build filters, but I suspect this will not work with reports. 

Maybe a calculated field that would flag the record? 

Parents
  • Hello Demien,

    Straight forward and simple way : You can do filter based on Date Create or Date Modified for all the modules by default.

    Now if you want to tract any perticular event happen and when it was happen and then wants to do filering on that . Then you need to capture the date when the event happen.
    Like , whenever the Opportunity is set to close won we will capture the date and save into additional field . And from that additional field you can perform search . And this will be date field so it will have all the necessary filter that you are looking for ( last X days or so.) . Also it will work for reports module .

    Here is one example link that how you can capture the date event on any action using SugarCRM Advanced Workflow.

    http://support.sugarcrm.com/Knowledge_Base/Advanced_Workflow/Capturing_the_Sales_Stage_When_an_Opportunity_Closes/ 

    If you are using the CE edition of sugarcrm then you need to write logic hook to achieve this.

    Thanks
    Prashant
    Email : prashant13290@gmail.com

    WhatsAPP : +91 9016739026

  • Hello Damien Pochon

    You can follow the KB about "how to create a new type of field" to extend the Date field and add your new operator in the sugar widget. For the second step, to solve the report case, you can deal with that with a logic hook "after_ui_frame" which use SUGAR.util.doWhen to add the operator in the filter_defs['date'] = filter_defs['date'].concat(additionnalFilters) when it is define.

    That's the "main" idea :-)

  • I've added exactly this filter myself, and one to get exactly N days ago.  Here is a step by step guide.

    Warning - this changes a couple of core files so you may need to make the changes again after any upgrade.

    1. First add the language string to the reports module.

    custom/extension/modules/Reports/ext/lang/en_us.lang.php

    $mod_strings['LBL_BEFORE_N_DAYS'] = 'Before last # Days';
    $mod_strings['LBL_N_DAYS_AGO'] = '# Days Ago';

    2. In modules/Reports/templates/templates_modules_def_js.php add these lines

    qualifiers[qualifiers.length] = {name:'tp_before_n_days',value:'<?php echo $mod_strings['LBL_BEFORE_N_DAYS']; ?>'};
    qualifiers[qualifiers.length] = {name:'tp_n_days_ago',value:'<?php echo $mod_strings['LBL_N_DAYS_AGO']; ?>'};

    3. Copy include/generic/SugarWidgets/SugarWidgetFielddatetime.php to custom/include/generic/SugarWidgets/SugarWidgetFielddatetime.php

    Add these 2 functions to your new file - custom/include/generic/SugarWidgets/SugarWidgetFielddatetime.php

    function queryFilterTP_before_n_days($layout_def)
    {
        $days = $layout_def['input_name0'] - 1;

        $begin = $this->now()->get("-10 years")->get_day_begin();
        $end = $this->now()->get("-$days days")->get_day_begin();

        return $this->get_start_end_date_filter($layout_def, $begin, $end);
    }

    function queryFilterTP_n_days_ago($layout_def)
    {
        $days = $layout_def['input_name0'];

        return $this->queryDay($layout_def, $this->now()->get("-$days day"));
    }

    4. Repair and rebuild.

Reply
  • I've added exactly this filter myself, and one to get exactly N days ago.  Here is a step by step guide.

    Warning - this changes a couple of core files so you may need to make the changes again after any upgrade.

    1. First add the language string to the reports module.

    custom/extension/modules/Reports/ext/lang/en_us.lang.php

    $mod_strings['LBL_BEFORE_N_DAYS'] = 'Before last # Days';
    $mod_strings['LBL_N_DAYS_AGO'] = '# Days Ago';

    2. In modules/Reports/templates/templates_modules_def_js.php add these lines

    qualifiers[qualifiers.length] = {name:'tp_before_n_days',value:'<?php echo $mod_strings['LBL_BEFORE_N_DAYS']; ?>'};
    qualifiers[qualifiers.length] = {name:'tp_n_days_ago',value:'<?php echo $mod_strings['LBL_N_DAYS_AGO']; ?>'};

    3. Copy include/generic/SugarWidgets/SugarWidgetFielddatetime.php to custom/include/generic/SugarWidgets/SugarWidgetFielddatetime.php

    Add these 2 functions to your new file - custom/include/generic/SugarWidgets/SugarWidgetFielddatetime.php

    function queryFilterTP_before_n_days($layout_def)
    {
        $days = $layout_def['input_name0'] - 1;

        $begin = $this->now()->get("-10 years")->get_day_begin();
        $end = $this->now()->get("-$days days")->get_day_begin();

        return $this->get_start_end_date_filter($layout_def, $begin, $end);
    }

    function queryFilterTP_n_days_ago($layout_def)
    {
        $days = $layout_def['input_name0'];

        return $this->queryDay($layout_def, $this->now()->get("-$days day"));
    }

    4. Repair and rebuild.

Children