Adding a custom search filter to the ListView

I was given a task that involved adding a filter to the ListView of an Events module so that (by default) only future events are displayed….but with some way of listing them all.

Two obvious solutions came to mind:

    • filter on date and have a check-box ticked on basic search that says “Display future events”
    • filter on event status (planning or confirmed)

I preferred the first option because the second involves two possible status codes..

Step 1:

The first thing I had to was add a custom non-db field to the Events module:

custom/Extension/modules/Events/Ext/Vardefs/future_only.php

$dictionary['Events']['fields']['open_only_future_events'] = array(
'name' => 'open_only_future_events',
'type' => 'bool',
'source'=> 'non-db',
'studio' => 'visible',
'query_type' => 'default',
'vname'=> 'LBL_EVENT_FUTURE_ONLY',
);

Then I added the field to the basic search layout in the Events module (this can also be done via studio):

array (
'layout' =>
array (
'basic_search' =>
array (
'name' =>
array (
'name' => 'name',
'label' => 'LBL_NAME',
'default' => true,
'width' => '10%',
),
'open_only_future_events' =>
array (
'type' => 'bool',
'studio' => 'visible',
'label' => 'LBL_EVENT_FUTURE_ONLY',
'width' => '10%',
'name' => 'open_only_future_events',
),
'current_user_only' =>
array (
'name' => 'current_user_only',
'label' => 'LBL_CURRENT_USER_FILTER',
'type' => 'bool',
'default' => true,
'width' => '10%',
),
),

Normally when you add a check-box field to the basic search it displays it as a drop-down rather than a check-box. This was an annoying issue as I wanted a check-box to be displayed. You will notice that the My Items check-box is displayed correctly in the basic search layout so there must be a hard coded check somewhere that looks for current_user_only or Open Items and makes them stay a check-box.

After a bit of searching I found the code in include/SugarFields/Fields/Bool/SugarFieldBool.php line 55. It appears that all I had to do was pre-pend my field name with open_only and it would remain a check-box rather than a drop-down field.

Don’t forget to at the label to the language file in: custom/Extension/modules/Events/Ext/Language/en_us.Events.php

$mod_strings ["LBL_EVENT_FUTURE_ONLY"] = "Display future events";

Step 2:

Now that the check-box is displaying correctly in the basic search layout at the top of the Events ListView I had to actually make the search filter work by only displaying upcoming future events in the ListView when it was checked. After a little research and  a lot of pouring over the code this turned out to be simpler than I thought.

All I had to do was add a simple subquery to the SearchFields.php file like so:

custom/modules/Events/metadata/SearchFields.php

$searchFields['Events'] = array (
'name' =>
array (
'query_type' => 'default',
),
'current_user_only' =>
array (
'query_type' => 'default',
'db_field' =>
array (
0 => 'assigned_user_id',
),
'my_items' => true,
'vname' => 'LBL_CURRENT_USER_FILTER',
'type' => 'bool',
),
'open_only_future_events' =>
array (
'query_type' => 'format',
'operator' => 'subquery',
'subquery' => 'SELECT events.id FROM events LEFT JOIN events_cstm ON events.id = events_cstm.id_c WHERE event_date_time_c > NOW() OR NOT {0}',
'db_field' =>
array (
0 => 'id',
),
'type' => 'bool',
),
'assigned_user_id' =>
array (
'query_type' => 'default',
),
'range_date_entered' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_date_entered' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_date_entered' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'range_date_modified' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_date_modified' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_date_modified' =>
array (
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
);

The main part of the above code to look at is :

'open_only_future_events' =>
array (
'query_type' => 'format',
'operator' => 'subquery',
'subquery' => 'SELECT events.id FROM events LEFT JOIN events_cstm ON events.id = events_cstm.id_c WHERE event_date_time_c > NOW() OR NOT {0}',
'db_field' =>
array (
0 => 'id',
),
'type' => 'bool',
),

By setting the query_type to format and adding OR NOT {0} to the end of the query you are telling the filter to use the open_only_future_events check-box value in the {} and to ignore it if its {0} (not checked).

That’s pretty much all that was involved in getting this working however it took quite a bit of effort to figure this out hence the post.

Andy
About

Software Developer from Falkirk, Central Scotland.

Posted in SugarCRM development and customisation, SugarCRM development development hints and tips, SugarCRM development tutorials
5 comments on “Adding a custom search filter to the ListView
  1. Nice article Andy. I wish you would write more blogs.

  2. Guss says:

    Thank you for this neat solution!

  3. Vasik says:

    Thanks, this was really helpful – this was the only place where I discovered the trick with dropdown vs checkbox. Well done!

  4. Erwin Verbruggen says:

    Hi Andy, your solution seems great, but somewhat different from the requirement. I too would support a way to do second and third (and fourth and …) sorting order, much like a spreadsheet allows you to.

  5. darius says:

    “By setting the query_type to format and adding OR NOT {0} to the end of the query you are telling the filter to use the open_only_future_events check-box value in the {} and to ignore it if its {0} (not checked).”

    Hi, how is it exactly work ‘{}’ ?
    I wrote some query and it doesnt work.
    For example: WHERE (a NOT IN (some expression) AND b.deleted = 0) AND c > d OR NOT {0}.
    The first part should be static (a NOT IN (some expression) AND b.deleted = 0) and the second one should be dynamic use ‘c > d’ only if checkbox is checked, if not then only static part should be run.
    Is it possible to write that kind of query?

    And one more question whether we can put the value from our checkbox with expression {} in few places in a query? For example WHERE (a>b AND 0 = {0}) OR (c=d AND 1={0}). The query should work in depends on checkbox value.
    I hope you understand what I’m trying to do.

    I would appreciate any help.

Leave a Reply to Guss Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>