I want to have range filter in columns (not in templates filters which is ther in jqgrid) . Can anyone help on this

Question

as u see in screenshot ,in existing dropdown I want to add an option of inbetween ,where either user can give two values for range selection in textbox or it can be a editabe dropdown.

jQGRID DATA - IF user enter 34:09:0;90:08:8 he should get all values which are >=34:09:0 and <90:08:8 in duration filter

$(function() {
  "use strict";
  var mydata = [{
    id: "1",
    invdate: "720:0:0",
    name: "test",
    note: "note",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "2",
    invdate: "34:09:0",
    name: "test2",
    note: "note2",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "3",
    invdate: "0:0:0",
    name: "test3",
    note: "note3",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "4",
    invdate: "90:08:8",
    name: "test4",
    note: "note4",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "5",
    invdate: "09:34:2",
    name: "test5",
    note: "note5",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "6",
    invdate: "80:12:02",
    name: "test6",
    note: "note6",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "7",
    invdate: "80:12:01",
    name: "test7",
    note: "note7",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "8",
    invdate: "112:23:6",
    name: "test8",
    note: "note8",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "9",
    invdate: "80:12:1 ",
    name: "test9",
    note: "note9",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "10",
    invdate: "112:34:2",
    name: "test10",
    note: "note10",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "11",
    invdate: "114:23:2",
    name: "test11",
    note: "note11",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "13",
    invdate: "80:12:4 ",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "14",
    invdate: "0:1:0",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "15",
    invdate: "80:12:3",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }];


  $("#grid").jqGrid({
    data: mydata,
    colNames: ['Inv No', 'Duration', 'Client', 'Amount', 'Tax', 'Total', 'Notes'],
    colModel: [{
      name: 'id',
      index: 'id',
      search: false,
      width: 65,
      sorttype: 'int'
    }, {
      name: 'invdate',
      index: 'invdate',
      width: 120,
      align: 'center',
      sorttype: 'datetime',
      datefmt: 'H:i:s',
      searchoptions: {
        sopt: ['inbetween', 'eq', 'lt', 'le', 'gt', 'ge']
      }
    }, {
      name: 'name',
      index: 'name',
      width: 90,
      search: false,
    }, {
      name: 'amount',
      index: 'amount',
      width: 70,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'tax',
      index: 'tax',
      width: 60,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'total',
      index: 'total',
      width: 60,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'note',
      index: 'note',
      width: 100,
      search: false,
    }],
    pager: true,
    autoencode: true,
    viewrecords: true,
    rowNum: 10,
    rowList: [5, 10, 20, "10000:All"],
    caption: "Demonstration custom searching operation (Duration Range)",
    customSortOperations: {

      inbetween: {
        operand: "inbetween",
        text: "in between",
        filter: function(options) {}
      }
    },
    searching: {
      searchOperators: true
     
    }
  }).jqGrid("filterToolbar");
});

<style> html,
body {
  font-size: 75%;
}
</style>

<html lang="en">

<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/themes/redmond/jquery-ui.css">
  <link rel="stylesheet" href="http://netdna.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css">
  <!--<link rel="stylesheet" href="../jqGrid/css/ui.jqgrid.css">-->
  <link rel="stylesheet" href="http://www.ok-soft-gmbh.com/jqGrid/OK/ui.jqgrid.css">

  <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/jquery-ui.min.js"></script>
  <script src="http://www.ok-soft-gmbh.com/jqGrid/OK/i18n/grid.locale-en.js"></script>

  <script src="http://www.ok-soft-gmbh.com/jqGrid/OK/jquery.jqGrid.src.js"></script>
</head>

<body>
  <div id="outerDiv" style="margin:5px;">
    <table id="grid"></table>
  </div>
</body>

</html>

The implementation depends on the version of jqGrid which you use (can use) and from fork of jqGrid which you use ([free jqGrid](https://github.com/free-jqgrid/jqGrid), commercial [Guriddo jqGrid JS](http://guriddo.net/?page_id=103334) or an old jqGrid in version <=4.7). I develop free jqGrid fork and implemented [**custom filtering operations**](https://github.com/free-jqgrid/jqGrid/wiki/Custom-filtering-searching-Operation), where you can define any custom filter operation. See [the answer](http://stackoverflow.com/a/28615923/315935), [this one](http://stackoverflow.com/a/29676941/315935) — Oleg, Nov 08 '16 at 09:45
added code @Oleg .pls help.so when user enters for Example:34:09:00;90:08:08 in textbox and selects inbewteen - jqgrid shud display all records which are greater thanequal to 34:09:00 but less than 90:08:08 – — Priya, Nov 08 '16 at 17:32
See **UPDATED** part of my answer. Click the button "Run code snippet" to try the demo. I use "-" as the separater between the values. For example `12:00:03-80:13:50` or in short form like `12-80:13` — Oleg, Nov 08 '16 at 18:14
@Oleg- Didnot noticed you are using jqgrid .I am using * jqGrid 4.6.0 - jQuery Grid * Copyright (c) 2008, Tony Tomov, tony@trirand.com. Is there any solution avaiable for my problem in the version of jqgrid I am using Because If I use your jqgrid library its affecting my existing code.Can you provide solution with jqgrid 4.6.0 — Priya, Nov 09 '16 at 08:36
@Oleg- http://stackoverflow.com/questions/8951523/jqgrid-toolbar-searching-search-for-multiple-words-for-a-column/8953934#8953934 I saw this .What would be the code if I want to modify an filter to work like inBetween in my case. — Priya, Nov 09 '16 at 08:59
Sorry, but I can't do all your work myself. I provided the demo for old jqGrid more as 4.5 year ago. You can use it and modify to your purpose. I implemented custom filtering operations, which I develop since the end of 2014. You demo used `http://www.ok-soft-gmbh.com/jqGrid/OK/jquery.jqGrid.src.js`, which is already free jqGrid. I wrote for you the implementation of `inbetween.filter`, like you asked. Now you write that you don't want to use it. I have my main job, which I have to do and try to help other in my free time, which is restricted. — Oleg, Nov 09 '16 at 11:47

score 0 · Answer 1 · edited May 23 '17 at 12:07

The implementation depends on the version of jqGrid which you use (can use) and from fork of jqGrid which you use (free jqGrid, commercial Guriddo jqGrid JS or an old jqGrid in version <=4.7). I develop free jqGrid fork and implemented custom filtering operations, where you can define any custom filter operation. See the answer, this one.

If you can't use free jqGrid, then you you can use beforeSearch callback of filterToolbar to change the filter like I described in the old answer. You will get more long and more complicated code, which works in the filter toolbar, but not in Searching Dialog, where you will have to use onSearch callback. In any way it's possible too, but the implementation is more sophisticated.

UPDATED: The fix of your code could be for example the following:

$(function() {
  "use strict";
  var mydata = [{
    id: "1",
    invdate: "720:0:0",
    name: "test",
    note: "note",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "2",
    invdate: "34:09:0",
    name: "test2",
    note: "note2",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "3",
    invdate: "0:0:0",
    name: "test3",
    note: "note3",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "4",
    invdate: "90:08:8",
    name: "test4",
    note: "note4",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "5",
    invdate: "09:34:2",
    name: "test5",
    note: "note5",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "6",
    invdate: "80:12:02",
    name: "test6",
    note: "note6",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "7",
    invdate: "80:12:01",
    name: "test7",
    note: "note7",
    amount: "200.00",
    tax: "10.00",
    total: "210.00"
  }, {
    id: "8",
    invdate: "112:23:6",
    name: "test8",
    note: "note8",
    amount: "300.00",
    tax: "20.00",
    total: "320.00"
  }, {
    id: "9",
    invdate: "80:12:1 ",
    name: "test9",
    note: "note9",
    amount: "400.00",
    tax: "30.00",
    total: "430.00"
  }, {
    id: "10",
    invdate: "112:34:2",
    name: "test10",
    note: "note10",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "11",
    invdate: "114:23:2",
    name: "test11",
    note: "note11",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "13",
    invdate: "80:12:4 ",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "14",
    invdate: "0:1:0",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }, {
    id: "15",
    invdate: "80:12:3",
    name: "test11",
    note: "note12",
    amount: "500.00",
    tax: "30.00",
    total: "530.00"
  }];


  $("#grid").jqGrid({
    data: mydata,
    colNames: ['Inv No', 'Duration', 'Client', 'Amount', 'Tax', 'Total', 'Notes'],
    colModel: [{
      name: 'id',
      index: 'id',
      search: false,
      width: 65,
      sorttype: 'int'
    }, {
      name: 'invdate',
      index: 'invdate',
      width: 200,
      align: 'center',
      sorttype: 'datetime',
      datefmt: 'H:i:s',
      searchoptions: {
        sopt: ['inbetween', 'eq', 'lt', 'le', 'gt', 'ge']
      }
    }, {
      name: 'name',
      index: 'name',
      width: 90,
      search: false,
    }, {
      name: 'amount',
      index: 'amount',
      width: 70,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'tax',
      index: 'tax',
      width: 60,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'total',
      index: 'total',
      width: 60,
      formatter: 'number',
      align: "right",
      search: false,
    }, {
      name: 'note',
      index: 'note',
      width: 100,
      search: false,
    }],
    pager: true,
    autoencode: true,
    viewrecords: true,
    rowNum: 10,
    rowList: [5, 10, 20, "10000:All"],
    caption: "Demonstration custom searching operation (Duration Range)",
    customSortOperations: {
      inbetween: {
        operand: "inbetween",
        text: "in between",
        filter: function(options) {
           var convertToSeconds = function (value) {
                   // every value should have 3 integer parts
                   var parts = value.split(":");
                   if (parts.length < 2) { parts.push(0); }
                   if (parts.length < 3) { parts.push(0); }
                   // now parts has 3 elements: h:m:s
                   return parseInt(parts[2], 10) +
                       parseInt(parts[1], 10) * 60 +
                       parseInt(parts[0], 10) * 3600;

               };
           var partsOfFilter = options.searchValue.split("-");
           if (partsOfFilter.length < 2) {
               // wrong filter
               return false;
           }

           // options.item[options.cmName] - the data from the column
           return convertToSeconds(options.item[options.cmName]) >=
                  convertToSeconds(partsOfFilter[0]) &&
                  convertToSeconds(options.item[options.cmName]) <=
                  convertToSeconds(partsOfFilter[1])
        }
      }
    },
    searching: {
      searchOperators: true
     
    }
  }).jqGrid("filterToolbar");
});

<style> html,
body {
  font-size: 75%;
}
</style>

<html lang="en">

<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/themes/redmond/jquery-ui.min.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/free-jqgrid/4.13.5/css/ui.jqgrid.min.css">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/free-jqgrid/4.13.5/js/jquery.jqgrid.min.js"></script>
</head>

<body>
  <div id="outerDiv" style="margin:5px;">
    <table id="grid"></table>
  </div>
</body>

</html>

Hi Oleg - I am following ur ok-soft-gmbh.com/jqGrid/OK/customFilterOperartion.htm to add inbetween filter in my code. can you help me with the code for ---- filter: function (options) {},my column is duration {name:'DURATION',index:'DURATION',width:120,sortable: true,sorttype:'datetime', datefmt: "H:i:s",searchoptions: { sopt:['nIN','eq','lt','le','gt','ge'] }}, and on textbox of search user will enter data in format of hh:mm:ss, for eg- on selection of inbetween 12:00:03;23:02:13- i shud get all the values inbetween timestamp 12:00:03 to 23:02:13- http://stackoverflow.com/users/315935/oleg — Priya, Nov 08 '16 at 13:40
@Priya: You should append the code to the text (click on "edit" link below of the text) of your question and post me comment to inform about it. The best if you would create the demo in JSFiddle. I could just modify it and post you the new URL. — Oleg, Nov 08 '16 at 14:25
@Priya: I wrote you already that custom filtering operations feature (`customSortOperations`) exists **only in free jqGrid fork** and it's not exist of cause in the old jqGrid 4.6.0. I wrote you it at the beginning and referenced [the old answer](http://stackoverflow.com/a/8953934/315935) which can be used in case of usage old jqGrid. Free jqGrid 4.13.5 is full compatible with old jqGrid 4.6 and it can be used under the same license agreement completely free of charge. You should just update to it. — Oleg, Nov 09 '16 at 11:39
@Oleg- I am new to jqgrid. I did not noticed you already mentioned about customSortOperations. I have tried to write code as per your old answer onSearch and modifying the 'contain' filter option. Its not working . SO I need your help over there. — Priya, Nov 09 '16 at 12:32
@Priya: You wrote that you are new in jqGrid. Why you then use the old jqGrid 4.6.0, which is more as 2.5 years old? — Oleg, Nov 09 '16 at 12:47
@Oleg- Its not allowing me to chat .Saying have 6 reputation not enough to chat .I will edit my existing code as per version4.6. and will seek your help . Pls help me on that — Priya, Nov 09 '16 at 12:48
@Priya: What you want to ask me? The version 4.6 is dead. There are now two main forks: free jqGrid and commercial Guriddo jqGrid JS. Why you don't want to update to the latest free jqGrid 4.13.5? — Oleg, Nov 09 '16 at 12:49
@Oleg- All aplication is build on 4.6 if I am updating it my other drop downs is affected and there is some other issues ..... — Priya, Nov 09 '16 at 12:52
@Priya: Free jqGrid should be full compatible with 4.6. Which exactly compatibility problems you have? Can you demonstrate the problem? — Oleg, Nov 09 '16 at 12:55
@Priya: Sorry, but I don't understand what you mean in your last comment. What is your current question to me? — Oleg, Nov 09 '16 at 15:31
@Oleg-https://i.stack.imgur.com/qwIEb.png I had many dynamic dropdown which shows Unique values . Its gets disturbed If I try to upgrade — Priya, Nov 09 '16 at 18:02
@Priya: You should post the demo (or at least the code fragments with the test data) and not only the picture, which displays wrong results. You can for example create the demo in https://jsfiddle.net/. The Echo service (/echo/json/) allows easy simulate loading data via Ajax calls. — Oleg, Nov 09 '16 at 18:09
@Oleg- Itsnot possible for me. as its a huge project and restrictions from project. Can upls help me if I update my existing snippet with 4.6 version.. which I am trying to code by seeing ur old example — Priya, Nov 09 '16 at 18:26
@Priya: Sorry, but I can't help you if I see only the picture. I don't have any interst to debug your code. It beaks the rules of stackoverflow if you would post your long code. You should post questions which have some values **for other people** and not only for you. Thus you can create small demo with one dropdown, which you fill with the data loaded from static file (or you get from JSFiddle Echo service). You can post all in new question and I'll try to modify your demo to make it working with the latest free jqGrid. — Oleg, Nov 09 '16 at 18:34
@Oleg-http://stackoverflow.com/questions/40514989/while-upgrading-from-jqrid4-6-to-4-13-5-facing-issue-in-creating-a-dynamic-dropd — Priya, Nov 09 '16 at 20:02

I want to have range filter in columns (not in templates filters which is ther in jqgrid) . Can anyone help on this

1 Answers1

Linked