SugarCRM SupportDocumentationSugar DeveloperSugar Developer Guide 7.8ArchitectureFilters

Filters

Overview

Filters are a way to predefine searches on views that render a list of records such as list views, pop-up searches, and lookups. This page explains how to implement the various types of filters for record list views. 

Filters contain the following properties:

Property Type Description
id String A unique identifier for the filter
name String  The label key for the display label of the filter
filter_definition Array The filter definition to apply to the results
editable Boolean  Determines whether the user can edit the filter.
Note: If you are creating a predefined filter for a user, this should be set to false
is_template Boolean Used with initial pop-up filters to determine if the filter is available as a template

Note: If a filter contains custom fields, those fields must be search-enabled in Studio > {Module Name} > Layouts > Search.

Operators

Operators, defined in ./clients/base/filters/operators/operators.php, are expressions used by a filter to represent query operators. They are constructed in the filter_definition to help generate the appropriate query syntax that is sent to the database for a specific field type. Operators can be defined on a global or module level. The accepted paths are listed below:

  • ./clients/base/filters/operators/operators.php
  • ./custom/clients/base/filters/operators/operators.php
  • ./modules/<module>/clients/base/filters/operators.php
  • ./custom/modules/<module>/clients/base/filters/operators.php

The list of stock operators is shown below:

Operator Label / Key Field Types Description
$contains is any of
LBL_OPERATOR_CONTAINS
multienum Matches anything that contains the value.
$not_contains is not any of
LBL_OPERATOR_NOT_CONTAINS
multienum Matches anything that does not contain the specified value.
$in is any of
LBL_OPERATOR_CONTAINS
enum, int, relate, and tag Finds anything where field matches one of the values as specified as an array.
$not_in is not any of
LBL_OPERATOR_NOT_CONTAINS
enum, relate, and tag Finds anything where the field does not match any of the values in the specified array of values.
$empty is empty
LBL_OPERATOR_EMPTY
enum and tag Matches on empty values.
$not_empty is not empty
LBL_OPERATOR_NOT_EMPTY
enum and tag Matches on non-empty values.
$equals is
LBL_OPERATOR_IS
varchar, name, email, text, textarea, currency, int, double, float, decimal, date, bool, teamset, phone, radioenum, and parent Performs an exact match on that field.
$starts starts with
LBL_OPERATOR_STARTS_WITH
varchar, name, email, text, textarea, datetime, datetimecombo, and phone Matches on anything that starts with the value.
$not_equals is not
LBL_OPERATOR_IS_NOT
currency, int, double, float, decimal, teamset, and radioenum Matches on non-matching values.
$gt after
LBL_OPERATOR_AFTER
currency, int, double, float, decimal, and date Matches when the field is greater than the value.
$lt before
LBL_OPERATOR_BEFORE
currency, int, double, float, decimal, and date Matches when the field is less than the value.
$gte after
LBL_OPERATOR_AFTER
currency, int, double, float, decimal, datetime, and datetimecombo Matches when the field is greater than or equal to the value
$lte before
LBL_OPERATOR_BEFORE
currency, int, double, float, decimal, datetime, and datetimecombo Matches when the field is less than or equal to the value.
$between is between
LBL_OPERATOR_BETWEEN
currency, int, double, float, and decimal Matches when a numerical value is between two other numerical values.
yesterday yesterday
LBL_OPERATOR_YESTERDAY
date, datetime, and datetimecombo Matches dates on yesterday relative to the current date.
today today
LBL_OPERATOR_TODAY
date, datetime, and datetimecombo Matches dates in the current date.
tomorrow tomorrow
LBL_OPERATOR_TOMORROW
date, datetime, and datetimecombo Matches dates on tomorrow relative to the current date.
last_7_days last 7 days
LBL_OPERATOR_LAST_7_DAYS
date, datetime, and datetimecombo Matches date in the last 7 days relative to the current date.
next_7_days next 7 days
LBL_OPERATOR_NEXT_7_DAYS
date, datetime, and datetimecombo Matches dates in the next 7 days relative to the current date.
last_30_days last 30 days
LBL_OPERATOR_LAST_30_DAYS
date, datetime, and datetimecombo Matches dates in the last 30 days relative to the current date.
next_30_days next 30 days
LBL_OPERATOR_NEXT_30_DAYS
date, datetime, and datetimecombo Matches dates in the next 30 days relative to the current date.
last_month last month
LBL_OPERATOR_LAST_MONTH
date, datetime, and datetimecombo Matches dates in the previous month relative to the current month.
this_month this month
LBL_OPERATOR_THIS_MONTH
date, datetime, and datetimecombo Matches dates in the current month.
next_month next month
LBL_OPERATOR_NEXT_MONTH
date, datetime, and datetimecombo Matches dates in the next month relative to the current month.
last_year last year
LBL_OPERATOR_LAST_YEAR
date, datetime, and datetimecombo Matches dates in the last year relative to the current year.
this_year this year
LBL_OPERATOR_THIS_YEAR
date, datetime, and datetimecombo Matches dates in the current year.
next_year next year
LBL_OPERATOR_NEXT_YEAR
date, datetime, and datetimecombo Matches dates in the next year relative to the current year.
$dateBetween is between
LBL_OPERATOR_BETWEEN
date, datetime, and datetimecombo Matches dates between two given dates.

Example

The example below defines a filter where the type field must contain the value Customer and the name field must start with the letter A.

$filters = array(
    array(
        'type' => array(
            '$in' => array(
                'Customer',
            ),
        ),
    ),
    array(
        'name' => array(
            '$starts' => 'A',
        ),
    ),
);

Sub-Expressions

Sub-expressions group filter expressions into groupings. By default, all expressions are bound by an $and expression grouping. 

Sub-Expression Description
$and Joins the filters in an "and" expression
$or Joins the filters in an "or" expression

Note: Sub-Expressions are only applicable to predefined filters and cannot be used for initial filters. 

The example below defines a filter where the name field must being with the letters A or C.

$filters = array(
    '$or' => array (
        array(
            'name' => array(
                '$starts' => 'A',
            ),
        ),
        array(
            'name' => array(
                '$starts' => 'C',
            ),
        ),
    )
);

Module Expressions

Module expressions operate on modules instead of specific fields. The current module can be specified by either using the module name _this or by leaving the module name as a blank string.

Module Expression Description
$favorite Filters the records by the current users favorited items.
$owner Filters the records by the assigned user.

The example below defines a filter where records must be favorited items.

$filters = array(
    array(
        '$favorite' => '_this'
    ),
);

Filter Examples

Adding Predefined Filters to the List View Filter List

To add a predefined filter to the module's list view, create a new filter definition extension, which will append the filter to the module's viewdefs.

Filters

The following example will demonstrate how to add a predefined filter on the Accounts module to return all records with an account type of "Customer" and industry of "Other".

To create a predefined filter, create a display label extension in  ./custom/Extension/modules/<module>/Ext/Language/. For this example, we will create:

./custom/Extension/modules/Accounts/Ext/Language/en_us.filterAccountByTypeAndIndustry.php

<?php

$mod_strings['LBL_FILTER_ACCOUNT_BY_TYPE_AND_INDUSTRY'] = 'Customer/Other Accounts';

Next, create a custom filter extension in  ./custom/Extension/modules/<module>/Ext/clients/base/filters/basic/.

For this example, we will create:

./custom/Extension/modules/Accounts/Ext/clients/base/filters/basic/filterAccountByTypeAndIndustry.php

<?php

$viewdefs['Accounts']['base']['filter']['basic']['filters'][] = array(
    'id' => 'filterAccountByTypeAndIndustry',
    'name' => 'LBL_FILTER_ACCOUNT_BY_TYPE_AND_INDUSTRY',
    'filter_definition' => array(
        array(
            'account_type' => array(
                '$in' => array(
                    'Customer',
                ),
            ),
        ),
        array(
            'industry' => array(
                '$in' => array(
                    'Other',
                ),
            ),
        ),
    ),
    'editable' => false,
    'is_template' => false,
);

You should notice that the editable and is_template options have been set to "false". If editable is not set to "false", the filter will not be displayed in the list view filter's list.

Finally, navigate to Admin > Repair and click "Quick Repair and Rebuild" to rebuild the extensions and make the predefined filter available for users.

Adding Initial Filters to Lookup Searches

To add initial filters to record lookups and type-ahead searches, define a filter template. This will allow you to filter results for users when looking up a parent related record. The following example will demonstrate how to add an initial filter for the Account lookup on the Contacts module. This initial filter will limit records to having an account type of "Customer" and a dynamically assigned user value determined by the contact's assigned user.

To add an initial filter to the Contacts record view, create a display label for the filter in ./custom/Extension/modules/<module>/Ext/Language/. For this example , we will create:

./custom/Extension/modules/Accounts/Ext/Language/en_us.filterAccountTemplate.php

<?php

$mod_strings['LBL_FILTER_ACCOUNT_TEMPLATE'] = 'Customer Accounts By A Dynamic User';

Next, create a custom template filter extension in  ./custom/Extension/modules/<module>/Ext/clients/base/filters/basic/. For this example, create:

./custom/Extension/modules/Accounts/Ext/clients/base/filters/basic/filterAccountTemplate.php

<?php

$viewdefs['Accounts']['base']['filter']['basic']['filters'][] = array(
    'id' => 'filterAccountTemplate',
    'name' => 'LBL_FILTER_ACCOUNT_TEMPLATE',
    'filter_definition' => array(
        array(
            'account_type' => array(
                '$in' => array(),
            ),
        ),
        array(
            'assigned_user_id' => ''
        )
    ),
    'editable' => true,
    'is_template' => true,
);

As you can see, the filter_definition contains arrays for account_type and assigned_user_id. These filter definitions will receive their values from the contact record view's metadata. You should also note that this filter has is_template and editable set to "true". This is required for initial filters.

Once the filter template is in place, modify the contact record view's metadata. To accomplish this, edit ./custom/modules/Contacts/clients/base/views/record/record.php to adjust the account_name field. If this file does not exist in your local Sugar installation, navigate to Admin > Studio > Contacts > Layouts > Record View and click "Save & Deploy" to generate it. In this file, identify the panel_body array as shown below:

1 => 
array (
    'name' => 'panel_body',
    'label' => 'LBL_RECORD_BODY',
    'columns' => 2,
    'labelsOnTop' => true,
    'placeholders' => true,
    'newTab' => false,
    'panelDefault' => 'expanded',
    'fields' => 
    array (
        0 => 'title',
        1 => 'phone_mobile',
        2 => 'department',
        3 => 'do_not_call',
        4 => 'account_name',
        5 => 'email',
    ),
),

Next, modify the account_name field to contain the initial filter parameters. 

1 =>
array (
    'name' => 'panel_body',
    'label' => 'LBL_RECORD_BODY',
    'columns' => 2,
    'labelsOnTop' => true,
    'placeholders' => true,
    'newTab' => false,
    'panelDefault' => 'expanded',
    'fields' =>
    array (
        0 => 'title',
        1 => 'phone_mobile',
        2 => 'department',
        3 => 'do_not_call',
        4 => array (
            //field name
            'name' => 'account_name',

            //the name of the filter template
            'initial_filter' => 'filterAccountTemplate',

            //the display label for users
            'initial_filter_label' => 'LBL_FILTER_ACCOUNT_TEMPLATE',

            //the hardcoded filters to pass to the templates filter definition
            'filter_populate' => array(
                'account_type' => array('Customer')
            ),

            //the dynamic filters to pass to the templates filter definition
            //please note the index of the array will be for the field the data is being pulled from
            'filter_relate' => array(
                //'field_to_pull_data_from' => 'field_to_populate_data_to'
                'assigned_user_id' => 'assigned_user_id',
            )
        ),
        5 => 'email',
    ),
),

Finally, navigate to Admin > Repair and click "Quick Repair and Rebuild". This will rebuild the extensions and make the initial filter available for users when selecting a parent account for a contact.

Adding Initial Filters to Drawers from a Controller

When creating your own views, you may need to filter a drawer called from within your custom controller. Using an initial filter, as described in the Adding Initial Filters to Lookup Searches section, we can filter a drawer with predefined values by creating a filter object and populating the config.filter_populate property as shown below:

//create filter
var filterOptions = new app.utils.FilterOptions()
    .config({
        'initial_filter': 'filterAccountTemplate',
        'initial_filter_label': 'LBL_FILTER_ACCOUNT_TEMPLATE',
        'filter_populate': {
            'account_type': ['Customer'],
            'assigned_user_id': 'seed_sally_id'
        }
    })
    .format();

//open drawer
app.drawer.open({
    layout: 'selection-list',
    context: {
        module: 'Accounts',
        filterOptions: filterOptions,
        parent: this.context
    }
});

To create a filtered drawer with dynamic values, create a filter object and populate the config.filter_relate property using the populateRelate method as shown below:

//record to filter related fields by
var contact = app.data.createBean('Contacts', {
    'first_name': 'John',
    'last_name': 'Smith',		
    'assigned_user_id': 'seed_sally_id'
});

//create filter
var filterOptions = new app.utils.FilterOptions()
    .config({
        'initial_filter': 'filterAccountTemplate',
        'initial_filter_label': 'LBL_FILTER_ACCOUNT_TEMPLATE',
        'filter_populate': {
            'account_type': ['Customer'],
        },
        'filter_relate': {
            'assigned_user_id': 'assigned_user_id'
        }
    })
    .populateRelate(contact)
    .format();

//open drawer
app.drawer.open({
    layout: 'selection-list',
    context: {
        module: 'Accounts',
        filterOptions: filterOptions,
        parent: this.context
    }
});

Last modified: 10/14/2016 04:12pm

Back to top Contents