Two related questions on jqGrid column header filters and the advanced filtering dialog

Question

In developing my first ASP.NET MVC 3 app using the jqGrid to display some data, I'm using the column header filters and also allowing for the advanced filter toolbar filtering to be done. Independently these things work pretty well.

First question - Has anyone a solution for communicating the current column header filter settings to the advanced filters?

As an example, a user can filter on the "Ice Cream Name" column, entering a partial name, e.g., "Chocolate", and it'll filter down to "Chocolate Explosion", "Dark Chocolate", etc. - great. What would be nice would be to open the advanced filter and have that "contains 'Chocolate'" column filter automatically populated in the advanced filter. I recognize that the other direction (where someone could AND or OR two values for the same column, e.g. 'Chocolate' OR 'Caramel') becomes problematic but in the other direction, it seems like it might be possible. Perhaps this is just a setting of the grid I'm missing. Anyone solved this?

Second question - I currently can do some filtering with the column header filters, show some result set in the grid and then go into the advanced filter dialog and set up a different filter. That will display the correct results but the column header filters are not cleared, giving the impression that the filtering is not working. How can I reset those column header filters after the use clicks the "Find" button on the dialog?

Driest

Answer 1

I find your question very interesting, so I prepared the demo which demonstrate how one can combine Advanced Searching dialog and Toolbar Searching in one grid.

One important, but simple trick is the usage of recreateFilter: true. Per default the searching dialog will be created once and then will be only hide or show. As the result the postData.filters parameter will be not refreshed. After setting recreateFilter: true the problem with filling of the advanced searching dialog with the values from the searching toolbar will be solved. I personally set the default searching options as the following

$.extend(
    $.jgrid.search,
    {
        multipleSearch: true,
        multipleGroup: true,
        recreateFilter: true,
        overlay: 0
    }
);

Now to more complex part of the solution is the function refreshSerchingToolbar which I wrote. The function is not so simple, but it's simply in use:

loadComplete: function () {
    refreshSerchingToolbar($(this), 'cn');
}

The last parameter is the same parameter which you used as defaultSearch property of the searching toolbar method filterToolbar (the default value is 'bw', but I personally prefer to use 'cn' and set jqGrid parameter ignoreCase: true).

If you fill the advanced searching dialog of the demo with the following field

and click the "Find" button, you will have the following grid:

(I marked the 'Total' column as non-searchable with respect of search: false to show only that all works correctly in the case also)

One can see that all fields of the searching toolbar excepting "Amount" are filled with the values from the searching dialog. The field are not filled because we used "grater or equal" operation instead of "equal". The function refreshSerchingToolbar fills only the elements of the searching toolbar which can be produced by the

Just as a reminder I should mention that in case of the usage of Filter Toolbar it is very important to define searchoptions.sopt options of the colModel. For all non-string column contains (dates, numbers, selects, int, currency) it is extremely important to have 'eq' as the first element of the sopt array. See here and here for details.

If you change the filter of the Advanced Dialog to the following

you will have as expected

At the end I include the code of the refreshSerchingToolbar function:

var getColumnIndex = function (grid, columnIndex) {
        var cm = grid.jqGrid('getGridParam', 'colModel'), i = 0, l = cm.length;
        for (; i < l; i += 1) {
            if ((cm[i].index || cm[i].name) === columnIndex) {
                return i; // return the colModel index
            }
        }
        return -1;
    },
    refreshSerchingToolbar = function ($grid, myDefaultSearch) {
        var postData = $grid.jqGrid('getGridParam', 'postData'), filters, i, l,
            rules, rule, iCol, cm = $grid.jqGrid('getGridParam', 'colModel'),
            cmi, control, tagName;

        for (i = 0, l = cm.length; i < l; i += 1) {
            control = $("#gs_" + $.jgrid.jqID(cm[i].name));
            if (control.length > 0) {
                tagName = control[0].tagName.toUpperCase();
                if (tagName === "SELECT") { // && cmi.stype === "select"
                    control.find("option[value='']")
                        .attr('selected', 'selected');
                } else if (tagName === "INPUT") {
                    control.val('');
                }
            }
        }

        if (typeof (postData.filters) === "string" &&
                typeof ($grid[0].ftoolbar) === "boolean" && $grid[0].ftoolbar) {

            filters = $.parseJSON(postData.filters);
            if (filters && filters.groupOp === "AND" && typeof (filters.groups) === "undefined") {
                // only in case of advance searching without grouping we import filters in the
                // searching toolbar
                rules = filters.rules;
                for (i = 0, l = rules.length; i < l; i += 1) {
                    rule = rules[i];
                    iCol = getColumnIndex($grid, rule.field);
                    cmi = cm[iCol];
                    control = $("#gs_" + $.jgrid.jqID(cmi.name));
                    if (iCol >= 0 && control.length > 0) {
                        tagName = control[0].tagName.toUpperCase();
                        if (((typeof (cmi.searchoptions) === "undefined" ||
                              typeof (cmi.searchoptions.sopt) === "undefined")
                             && rule.op === myDefaultSearch) ||
                                (typeof (cmi.searchoptions) === "object" &&
                                    $.isArray(cmi.searchoptions.sopt) &&
                                    cmi.searchoptions.sopt[0] === rule.op)) {

                            if (tagName === "SELECT") { // && cmi.stype === "select"
                                control.find("option[value='" + $.jgrid.jqID(rule.data) + "']")
                                    .attr('selected', 'selected');
                            } else if (tagName === "INPUT") {
                                control.val(rule.data);
                            }
                        }
                    }
                }
            }
        }
    };

UPDATED: The above code is no more needed in case of usage free jqGrid 4.13.1 or higher. It contains the new default option loadFilterDefaults: true of the filterToolbar, which refreshes the values of the filter toolbar and the filter operations (if searchOperators: true option of filterToolbar is ised) if postData.filters and search: true are set (the filter is applied). Free jqGrid refreshes the filter toolbar on jqGridAfterLoadComplete (if loadFilterDefaults: true are set) or if the event jqGridRefreshFilterValues are explicitly triggered.

Unrestraint

@itsmatt: Is it what you need? – Unrestraint

That's pretty thorough, Oleg - I appreciate the depth of your response! – Driest

One of the complications I have with date filtering is that I might want to filter down the month level and have so far solved this by using GT (greater than) as my operator, so if I wanted to see June's data I might filter to GT June 1 2011 and while not ideal it gives me all of June's data (along with, as a side effect, July's data too). That's probably just a setting in the date picker though - need to look at that, then EQ would work well, I assume. – Driest

Also, thanks for showing the non-filtered Total column - that's a good thing to show too. – Driest

@itsmatt: You are welcome! You can try to set "ge" instead of 'eq' as the first option on the sopt for the column with the data. You can of course increase the list of options in the sopt from ['eq', 'ne'] in my example to the ['eq', 'ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni'] for example. Important is which option you are use as the first - the option will be used in the searching toolbar. – Unrestraint

@Unrestraint : Thanks for such a nice answer. Its work me too... keep supporting like this. – Predicant

@Rahul: You are welcome! I uses myself very close implementation and find it very practical. The main enhancement which I use is setting "ui-state-highlight" on the column headers of filtered columns. Even if the filter from postData.filters (set by by Advanced Searching dialog) are too complex I highlight column headers. In the way the user see columns by which the grid are filtered. – Unrestraint

@FrankFajardo: Thank you! I used the code in one project (with the name Testportal) where some common functions are defined as global. So it was cut and paste error. I fixed the code now. – Unrestraint

@Unrestraint Is this code included in latest free jqgrid ? In this case it is no more required. – Diluvium

@Andrus: Yes one don't need to use the code in case of usage free jqGrid 4.13.1 or higher. It contains the new default option loadFilterDefaults: true of the filterToolbar, which refresh the value of the filter toolbar and the filter operations if postData.filters and search: true are set (the filter is applied). Free jqGrid refreshes the filter toolbar on jqGridAfterLoadComplete (if loadFilterDefaults: true are set) or if the event jqGridRefreshFilterValues are explicitly triggered. – Unrestraint

@Unrestraint In today github version if filter condition is removed in advanced search dialog by pressing - button, filter value is still shown in search toolbar – Diluvium

@Unrestraint I posted it in #36252098 – Diluvium

Answer 2

I know it's an old post - but if you have multiple grids on the same page the above code can add the filter text to the wrong grid.

Changing this in the first loop in refreshSearchingToolbar, from

control = $("#gs_" + $.jgrid.jqID(cm[i].name));

to

control = $("#gview_"+$grid.attr('id')+" #gs_" + $.jgrid.jqID(cm[i].name));

and this in the second loop from

control = $("#gs_" + $.jgrid.jqID(cmi.name));

to

control = $("#gview_"+$grid.attr('id')+" #gs_" + $.jgrid.jqID(cmi.name));

should do the trick.

Kudos to Oleg

Ixia