JQGrid Filter Toolbar not filtering rows when using a Formatter on a column

Question

So in a current app, I have to use a custom Formatter on a couple rows in my jqGrid. All these do is take a few fields from my ajax call, concat them into one, and place that into a row.

EG ( data.toStreet + data.toCity + data.toState + data.toZip ) comes back as "Street City, State Zip" into the "To Address" column. This works fine and the data displays correctly, but when using the filtering toolbar, the filter is only based on the first val (data.street). below is a super simplified version of the pieces of code in question.

$('#grid').jqGrid({
...
colNames:["AddressTo", "AddressFrom"],
colModel:[
     {name:"toStreet" formatter: ToAddressFormatter},
     {name:"fromStreet" formatter: FromAddressFormatter}
],
...
}),
 $('#grid').jqGrid('filterToolbar',
    {
        stringResult:true,
        searchOnenter: true,
        defaultSearch: 'cn'
    }
 });
ToAddressFormatter = function(el, opt, rowObj){
    var address = rowObj.toStreet+ " " + rowObj.toCity + ", " + rowObj.toState + " " + rowObj.toZip;
    return address;
},
FromAddressFormatter = function(el, opt, rowObj){
    var address = rowObj.fromStreet+ " " + rowObj.fromCity + ", " + rowObj.fromState + "  " + rowObj.fromZip;
    return address;
}

So if the value in the cel says "123 fake st, springfield, Va 22344" after being formatted, the filter toolbar can only search on "123 fake st" and nothing else. Does anybody have any clue on how to remedy this, or possibly why it's happening and a good workaround??

EDIT: I have included the beginning of my grid. Also, the property Address of result.d is created in the code below, and not returned from the webservice. My column is mapped to "Address" which displays the formatting properly, but still does not search as intended.

 $('#grdDisasters').jqGrid({
                datatype: function(postdata) {
                    var obj = { "showActive": $('#btnFilterActive.pressed').length > 0 ? true : false, "showInactive": $('#btnFilterActive.pressed').length > 0 ? true : false,
                        'page': postdata.page, 'rows': postdata.rows, 'sortIndex': postdata.sidx, 'sortDirection': postdata.sord, 'search': postdata._search,
                        'filters': postdata.filters || ''
                    };
                    $.ajax({
                        url: "/GetGrid",
                        data: JSON.stringify(obj),
                        success: function(result) {
                            for (var i = 0, il = result.d.rows.length; i < il; i++) {
                                LoadedDisasters[i] = result.d.rows[i];
                                result.d.rows[i].cells.Address = result.d.rows[i].cells.Street + " " + result.d.rows[i].cells.City + ", "+ result.d.rows[i].cells.State+ " "+ result.d.rows[i].cells.Zip;
                            }
                            result.d = NET.format(result.d);//just correctly format dates
                            UpdateJQGridData($('#grdDisasters'), result.d);
                        },
                        error: function(result) {
                            //alert("Test failed");
                        }
                    });

score 2 · Answer 1 · edited Apr 11 '12 at 20:53

jqGrid has a problem filtering rows when data is formatted using custom/predefined formatter.

You will have to filter rows on the server-side.

Add 2 more request parameter in your controller to handle jqgrid search request:

When jqGrid requests for filtered raws it will add a parameter: _search with value: true and all the search parameter like col1=abc&col4=123 meaning user wanted to filter using column named col1 and column named col4 with values respectively: abc and 123

Use those values and query the database with like operation something as follows:

select id, concat(street1, street2, city, state, zip) as address 
 where address like "%abc%" and id like "%123%"

return these rows as json to jqGrid and display those in the current page. So basically you will have to have a jqGrid with server-side paging, sorting and searching. You can not use client-side paging, sorting and searching features. Also, make sure you don't have loadonce: true set.

score 2 · Answer 2 · edited May 23 '17 at 10:27

I think that you fill the grid in the wrong way. If your source data has toStreet, toCity, toState, toZip, fromStreet, fromCity, fromState, fromZip properties and you need to have composed addressTo and addressFrom you should do this in another way. Your problem is that toStreet and fromStreet will be saved locally in the internal data parameter in the original format like you get it from the server. The local searching uses the data parameter, so the toStreet and fromStreet like you get there from the server will be used.

You don't posted more full code of jqGrid which you use. So I suppose that you use datatype: 'json', datatype: 'jsonp' or datatype: 'xml' in combination with loadonce: true. You should define colModel

$('#grid').jqGrid({
    ...
    colNames:["AddressTo", "AddressFrom"],
    colModel:[
        {name: "addressTo", ...},
        {name: "addressFrom", ...}
    ],
    beforeProcessing: function (data) {
        var i, rows = data.rows, l = rows.length, item;
        for (i = 0; i < l; i++) {
            item = rows[i];
            item.addressTo = item.toStreet + " " + item.toCity + ", " +
                item.toState + " " + item.toZip;
            item.addressFrom = item.fromStreet+ " " + item.fromCity + ", " +
                item.fromState + "  " + item.fromZip;
        }
    }
    ...
});

The exact code depend on the format of the input data. The advantage of the usage of beforeProcessing is that it will be called before the data will be processed by jqGrid. So you can do any modification in the data or like in the above.

UPDATED: The code of datatype can be easy implemented in another way using standard jqGrid options. So I suggest to use the following settings:

datatype: "json",
url: "/GetGrid",
postData: {
    // add and to the list of parameters sent to the web service
    showActive: function () {
        return $('#btnFilterActive.pressed').length > 0;
    },
    showInactive: function () {
        return $('#btnFilterActive.pressed').length > 0;
    }
},
prmNames: {
    // rename some parameters sent to the web service
    sort: "sortIndex",
    order: "sortDirection",
    search: "search",
    // don't send nd parameter to the server
    nd: null
    // you leave the nd is you don't set any "Cache-Control" HTTP header
    // I would recommend you to set "Cache-Control: private, max-age=0"
    // For example
    // HttpContext.Current.Response.Cache.SetMaxAge (new TimeSpan(0));
},
serializeGridData: function (postData) {
    // control modification of the the data (parameters) which will be sent
    // to the web method
    if (typeof postData.filters === "undefined") {
        postData.filters = "";
    }
    return JSON.stringify(postData);
},
ajaxGridOptions: { contentType: "application/json" },
jsonReader: {
    root: "d.rows",
    page: function (obj) { return obj.d.page; },
    total: function (obj) { return obj.d.total; },
    records: function (obj) { return obj.d.rows.length; },
    repeatitems: false
},
loadError: function (jqXHR, textStatus, errorThrown) {
    // see an implementation example in the answers
    // https://stackoverflow.com/a/6969114/315935
    // and
    // https://stackoverflow.com/a/5501644/315935
},
colNames:["AddressTo", "AddressFrom"],
colModel:[
    {name: "addressTo", ...},
    {name: "addressFrom", ...}
],
beforeProcessing: function (data) {
    var i, rows, l, item;

    data.d = NET.format(data.d); // just correctly format dates
    rows = data.d.rows;
    l = rows.length;
    for (i = 0; i < l; i++) {
        item = rows[i];
        LoadedDisasters[i] = item;
        item.addressTo = item.toStreet + " " + item.toCity + ", " +
            item.toState + " " + item.toZip;
        item.addressFrom = item.fromStreet+ " " + item.fromCity + ", " +
            item.fromState + "  " + item.fromZip;
    }
}
...

The usage of nd: null with setting of "Cache-Control: private, max-age=0" I described in the answer. You can download the corresponding demo project which use this. In general one needs just include one additional line where you call SetMaxAge

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
public MyGridData GetGrid(...) {
    HttpContext.Current.Response.Cache.SetMaxAge (new TimeSpan(0));
    ...
}

See more about caching control you can read here.

Sorry my code snippet was limited, but the datatype: is actually a function, and inside of the function there are several ajax calls made that use the WS to fill the grid. I attempted the beforeProcessing, but that did not work. Any other suggestions? Also Oleg, if you get a chance, could you check out my other jqGrid question? Frozen cols have become quite the thorn in my side.. — spacebed, Apr 17 '12 at 14:37
@imaxcowboyx: You can do the same inside of your datatype function. — Oleg, Apr 17 '12 at 14:40
I've tried that before to no avail. I'll play around with it some more and update my original post later today with my results. Also, ignore the previous comment about my other jqGrid question. Just needed to update jquery to 1.7.2 and frozen cols works quite nicely with the filter toolbar. Success! — spacebed, Apr 17 '12 at 15:29
@imaxcowboyx: What I mean that you can produce new `addressTo` and `addressFrom` property from the data returned from the server. Inside of `datatype` inside of `success` callback of `$.ajax` you can do the same what I suggested to do inside of `beforeProcessing` callback. — Oleg, Apr 17 '12 at 15:45
I understand, and had previously tried that before making this question, with no success. My guess is that the filter data is still searching for a value in a column on the db, but i could be wrong. — spacebed, Apr 17 '12 at 18:02
@imaxcowboyx: First of all I don't recommend to use `datatype` as function. If you do use it and want to implement the behavior which you ask *you should include the code of `datatype` function which you use currently*. — Oleg, Apr 17 '12 at 18:19
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10230/discussion-between-imaxcowboyx-and-oleg) — spacebed, Apr 18 '12 at 13:27
Oleg, I have added the datatype function to the original post. — spacebed, Apr 18 '12 at 13:34
@imaxcowboyx: I updated my answer. I recommend you to use *standard* jqGrid settings. In the case you can include any other parameters in jqGrid like `loadonce: true` for example and your code will still work. — Oleg, Apr 18 '12 at 15:24

JQGrid Filter Toolbar not filtering rows when using a Formatter on a column

2 Answers2

Linked