jqGrid Filter Toolbar and Date and Select filters

Question

I have a jqGrid:

$('#jqgFileInfoList').jqGrid({
    url: '@Url.Action("GetFiles", "File")',
    datatype: 'json',
    mtype: 'POST',
    colNames: ['Id', 'Name', 'Interface', 'Amount', 'Type', 'Created', 'Status'],
    colModel: [
        { jsonmap: 'Id', name: 'Id', formatter: 'integer', align: 'right', hidden: true },
        { jsonmap: 'Name', name: 'Name', align: 'right', hidden: true },
        { jsonmap: 'InterfaceName', name: 'InterfaceName', align: 'left', width: '100%', sorttype: 'text', frozen: true, search: true },
        { jsonmap: 'Amount', name: 'Amount', formatter: 'currency', align: 'right', width: '100%', sorttype: 'number', search: true },
        { jsonmap: 'Type', name: 'Type', align: 'right', width: '100%', sorttype: 'text', search: true },
        { jsonmap: 'RunDate', name: 'RunDate', formatter: 'date', align: 'right', width: '100%', sorttype: 'date', search: true },
        { jsonmap: 'Status', name: 'Status', align: 'right', width: '100%', sorttype: 'text', formatter: formatStatus, search: true }
    ],
    sortname: 'RunDate',
    sortorder: 'desc',
    pager: $('#jqgPagerFileInfoList'),
    rowNum: 5,
    viewrecords: true,
    height: '100%',
    autowidth: true,
    refresh: true,
    ignoreCase: true,
    jsonReader: {
        repeatitems: false,
        root: "rows"
    },
    altRows: true,
    altclass: 'jqGridAltRow',
    loadComplete: function () {
        $("tr.jqgrow:odd").addClass('jqGridAltRow');
    }
});

$('#jqgFileInfoList').jqGrid('filterToolbar', { searchOnEnter: false, enableClear: true, stringResult: true });

Filtering is working, except for a couple columns I want to modify.

The Created/RunDate column I would like to filter a range somehow. Picking a single date is not useful.

The Interface, Type and Status columns I would like to use drop downs. What's the best way to do this?

I find the jqGrid documentation VERY difficult to follow, there are just so many options. It took me an hour to figure out that I needed stringResult: true to get my filter options into the GridSettings.Where property in the controller.

And if it matters, it's a .Net 4.0, MVC app.

EDIT: Bonus Question: How do I make it case insensitive.

Answer 1

I agree that the official documentation can be used only more as reference and not as the introduction for developer who start to use it, especially not for .NET developer.

To your questions:

1) There are no simple way to implement searching by range of dates. One could make some tricks like the trick described here. The usage of beforeSearch callback of filterToolbar or onSearch callback of advanced searching allows to "pre-process" searching filter before it will be applied, but all together is very tricky. In case of usage server side sorting you can implement the same on the server side directly.

2) There are many ways to implement drop downs in jqGrid. The main alternatives are the usage of value or dataUrl properties of searchoptions. The best choice depend on your exact requirements.

The usage of value is very practical if the column have some small set of values which are more static as dynamic. For example if you use formatter: "checkbox" in the grid to display boolean data as checkboxes you would like sure to use drop downs in the filter for the column. In the case the usage of settings like below

stype: "select", searchoptions: { sopt: ["eq", "ne"], value: ":All;true:Yes;false:No" }

will be very practical. By the way you can use value in the object form

value: {"": "All", true:"Yes", false: "No"}

In other cases if you have small number of values you can use value to implement dropdowns in the searching filter.

The usage of dataUrl one should use if the list of the values you get dynamically from the database. I can address you to the answer which explains the main idea of the corresponding implementation.

The answer shows simple pure JavaScript code which demonstrate how one can implements selects and autocomplete on the client side only. In case of server side paging and filtering one should choose another implementation way. The answer shows how one can implement jQuery UI Autocomplete in ASP.NET MVC solution.

I think that jQuery UI Autocomplete could be good alternative to select in some cases, especially if the number of possible options is very large.

UPDATED: There are many ways to make searching case insensitive. In case of usage local searching (usage datatype other as "json" or xml" or usage of loadonce: true) one should just use ignoreCase: true option of jqGrid. In case of server side searching you should make searching insensitive on the server side. I use personally just COLLATE during filtering. For example instead of constructs like

WHERE Name LIKE ('%' + @s + '%')

I use

WHERE Name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE ('%' + @s + '%')

One more small remarks about the code which you posted:

• use always gridview: true option. See the answer for more details.
• specify the pager always in the form pager: "#jqgPagerFileInfoList" instead of the form pager: $("#jqgPagerFileInfoList").
• don't use non-existing options like refresh: true
• I recommend you to use autoencode: true option to force interpret the data returned from the server as text and not as HTML fragments. Without the options you could have problems to display textes having <, & etc.
• I recommend you examine default values of colModel properties (see the documentation). you will see that you can remove from your code align: 'left', sorttype: 'text', search: true etc.
• You should use permitted vales for properties of colModel. For example width: '100%' is incorrect and will be interpreted as default value width: 150.
• all values which you use for jsonmap are the same as the value of name property. So you can remove the values.
• I recommend you remove hidden column name: 'Id'. jqGrid use id attribute of <tr> which represent rows of grid. You use repeatitems: false inside of jsonReader. You can add in jsonReader the property id: "Id" to specify that jqGrid should use "Id" property of items from the row data for rowids.