Dynamically generating filter for datatable

Question

I am asking this question again since i dint find any proper idea. I am trying to implement the feature to filter item based on vendor name. item column according to vendor name. I am stuck here since a week . tried many ways but not working. My requirement steps as below.

1. I get all the name of vendor from DB.
2. Use the retrieved vendor name as filter name list:<option>vendorname</option>
3. I can get the item_code for each vendor using vendor code as foreign key there will be multiple item_code with each vendor may be more than 500.
4. Now I want to create a drop down in such a way that upon selecting any vendor, corresponding item code from table should be filtered.

Any Suggestion or steps will be appreciated.

My final effort:

$('#vendor').on('change',function(){

         var selectedValue = $(this).val();
         $("#testTable").dataTable().fnFilter("^"+selectedValue+"$", 0, true); //Exact value, column, reg
     });

 <div class="optionsDiv2" style="display:inline;" >     
    Vendor Name:<select name="item" id="vendor" style="width:510px;">
        <option value=" " selected>ALL</option>
        <% try{
            String queryv = "SELECT VNAME, VCODE FROM S_VENDORS";
        Statement stmtv=conn.createStatement();
       ResultSet rsv=stmtv.executeQuery(queryv);

         while (rsv.next()) { 
           VCD = rsv.getString("VCODE"); 
           String vcd="'"+VCD+"'";
         String queryi = "SELECT ICODE FROM VENDOR_ITEM_DETAILS Where VCODE="+vcd;
        Statement stmti=conn.createStatement();
       ResultSet rsi=stmti.executeQuery(queryi);
       try{
           i=0;
        while (rsi.next()) 
           Array1[i++] = rsi.getString("ICODE").toString();
             join1 = String.join("|", Array1);
      }
        catch(Exception ex)
        {
        out.println(ex);
        }

        %>

     <option  value="<%= join1%>"><%= rsv.getString("VNAME")%></option>

    <%  Arrays.fill(Array1, null); }
 rsv.close();
stmtv.close();
}
     catch(Exception ex)
        {
        out.println(ex);
        }

%>
        </select>
</div> 

I have added working fiddle here for a datatable with id "Mine" and dropdown id "Myselect": https://jsfiddle.net/Safoora/2j6w9hqt/18/ . Same way i am trying to do for my above code.

Any new suggestions to develop from the beginning also welcome.

one among option list as below:

<option value="401 1|401 1.1/2|401 1.1/4|401 1/2|401 2|401 2.1/2|401 3|401 3/4|401 4|AD CAN|BB SS 3|BBH BU 2.1/2X1.1/2|BBH BU 2.1/2X2|BBH BU 3X1|BBH BU 3X1.1/2|BBH BU 3X2|BBH BU 3X2.1/2|BBH BU 4X2|BBH BU 4X3|BBH CP 2.1/2|BBH CR 3|BBH EL 45 3|BBH EL 45 4|BBH EL 90 2.1/2|BBH EL 90 3|BBH EL 90 4|BBH NI 2.1/2|BBH NI 3|BBH NI 4|BBH RS 2.1/2X1.1/2|BBH RS 2.1/2X2|BBH RS 3X2|BBH RT 2.1/2X1|BBH RT 2.1/2X1.1/2|BBH RT 2.1/2X2|BBH RT 3X1.1/2|BBH RT 3X2|BBH RT 4X2|BBH SO 2.1/2|BBH SO 3|BBH SO 4|BBH TE 2.1/2|BBH TE 3|BBH TE 4|BBH UN 2.1/2|BBH UN 3|BD 150 12|BD 150 3|BIB 1/2|BIB 3/4|BN BU 1.1/2X1|BN BU 1.1/2X1.1/4|BN BU 1.1/2X1/2|BN BU 1.1/4X1|BN BU 1.1/4X1/2|BN BU 1.1/4X3/4|BN BU 1X1/2|BN BU 1X3/4|BN BU 2.1/2X1.1/2|BN BU 2X1|BN BU 2X1.1/2|BN BU 2X1.1/4|BN BU 2X1/2|BN BU 2X3/4|BN BU 3/4X1/2|BN CR 1|BN CR 1.1/2|BN CR 1.1/4|BN CR 2|BN EL 45 1|BN EL 45 1.1/2|BN EL 45 1.1/4|BN EL 45 2|BN EL 45 3/4|BN EL 90 1|BN EL 90 1.1/2|BN EL 90 1.1/4|BN EL 90 1/2|BN EL 90 2|BN EL 90 3/4|BN NI 1|BN NI 1.1/2|BN NI 1.1/4|BN NI 1/2|BN NI 2|BN NI 3/4|BN PG 1|BN PG 1.1/2|BN PG 1.1/4|BN PG 1/2|BN PG 3/4|BN RL 1.1/2X1|BN RL 1.1/2X1.1/4|BN RL 1.1/4X1|BN RL 1.1/4X1/2|BN RL 1X1/2|BN RL 2X1.1/2|BN RS 1.1/2X1|BN RS 1.1/2X1.1/4|BN RS 1.1/2X1/2|BN RS 1.1/2X3/4|BN RS 1.1/4X1|BN RS 1.1/4X3/4|BN RS 1X1/2|BN RS 1X3/4|BN RS 2X1.1/2|BN RS 2X1.1/4|BN RS 3/4X1/2|BN RT 1.1/2X1|BN RT 1.1/2X1.1/4|BN RT 1.1/2X1/2|BN RT 1.1/2X3/4|BN RT 1.1/4X1|BN RT 1.1/4X1/2|BN RT 1.1/4X3/4|BN RT 1X1/2|BN RT 1X3/4|BN RT 2X1|BN RT 2X1.1/2|BN RT 2X1.1/4|BN RT 2X1/2|BN RT 2X3/4|BN SL 1|BN SL 1.1/2|BN SL 1.1/4|BN SL 1/2|BN SL 3/4|BN SO 1|BN SO 1.1/2|BN SO 1.1/4|BN SO 1/2|BN SO 2|BN SO 3/4|BN SOM 1/2|BN TE 1|BN TE 1.1/2|BN TE 1.1/4|BN TE 1/2|BN TE 2|BN TE 3/4|BN UN 1|BN UN 1.1/2|BN UN 1.1/4|BN UN 1/2|BN UN 2|BN UN 3/4|BPS40L 1|BR BL 1|BR BL 1.1/2|BR BL 1.1/4|BR BL 1/2|BR BL 2|BR BL 2.1/2|BR BL 3|BR BL 3/4|BR BL 4|BR FT 1|BR FT 1.1/2|BR FT 1.1/4|BR FT 2|BR FT 2.1/2|BR FT 3|BR FT 4|BR GT 2.1/2|BR HN 1/2|BRP GT 1.1/2|BRP GT 2|BRP GT 2.1/2|BRP GT 3|BRP GT 3/4|BRP GT 4|BSP40 1 N|BSP40 1.1/4 N|BSP40 3/4 N|BT|BWL CP 6|BWL CP 8|BWL EL 45 5|BWL EL 90 1|BWL EL 90 1.1/2|BWL EL 90 10|BWL EL 90 2|BWL EL 90 5|BWL RC 2X1.1/4|BWL RE 10X4|BWL RE 8X4|BWL RT 10X5|BWL RT 10X8|BWL RT 12X10|BWL RT 2.1/2X1.1/2|BWL RT 2.1/2X1.1/4|BWL RT 3X1|BWL RT 3X1.1/2|BWL RT 3X1.1/4|BWL RT 4X1.1/2|BWL RT 4X1.1/4|BWL RT 4X3|BWL RT 6X4|BWL RT 8X6|BWL TE 5|CB CP 3|CB CP 4|CB CP 6|CB EL 45 2.1/2|CB EL 45 3|CB EL 45 4|CB EL 45 6|CB EL 45 8|CB EL 90 10|CB EL 90 2.1/2|CB EL 90 3|CB EL 90 4|CB EL 90 5|CB EL 90 6|CB FL 2.1/2|CB FL 3|CB FL 4|CB FL 6|CB FL 8|CB MT 2.1/2X1|CB MT 2.1/2X1.1/2|CB MT 3X1|CB MT 3X1.1/2|CB MT 3X2|CB MT 4X1|CB MT 4X1.1/2|CB MT 4X1.1/4|CB MT 4X2|CB MT 4X2.1/2|CB MT 4X3|CB MT 6X1.1/2|CB MT 6X1.1/4|CB MT 6X2|CB MT 6X2.1/2|CB MT 6X3|CB RC 2.1/2X2|CB RC 2X1.1/2|CB RC 3X2|CB RC 3X2.1/2|CB RC 4X2|CB RC 4X2.1/2|CB RC 4X3|CB RC 6X3|CB RC 6X4|CB RS GS 2.1/2X2|CB RT 2.1/2X2|CB RT 3X1.1/2|CB RT 3X2|CB RT 3X2.1/2|CB RT 4X2|CB RT 4X3|CB RT 5X3|CB RT 6X2.1/2|CB RT 6X3|CB RT 6X4|CB SO 10|CB SO 12|CB SO 2|CB SO 2.1/2|CB SO 3|CB SO 4|CB SO 5|CB SO 6|CB SO 8|CB TE 2.1/2|CB TE 3|CB TE 4|CB TE 5|CB TE 6|CB TE 8|CU AD CF 1/2X3/8 CH|FN BU 1X1/2|FN BU 1X3/4|FN BU 3/4X1/2|FN EL 90 1|FN EL 90 1/2|FN EL 90 3/4|FN NI 1|FN NI 1/2|FN NI 3/4|FN SO 1|FN SO 1/2|FN SO 3/4|FN TE 1|FN TE 1/2|FN TE 3/4|FN UN 1|FN UN 1/2|FN UN 3/4|GAZ 1/2|GAZM 1/2|GB ST FL 2.1/2|GB ST FL 3|GB ST FL 4|GBH BU 2.1/2X1|GBH BU 2.1/2X1.1/2|GBH BU 2.1/2X2|GBH BU 3X2.1/2|GBH BU 4X1.1/2|GBH BU 4X2|GBH BU 4X3|GBH EL 45 3|GBH EL 45 4|GBH EL 90 2.1/2|GBH EL 90 3|GBH EL 90 4|GBH NI 2.1/2|GBH NI 3|GBH NI 4|GBH PG 3|GBH RT 2.1/2X2|GBH RT 3X1|GBH RT 3X1.1/2|GBH RT 3X2|GBH RT 4X2|GBH RT 4X3|GBH SO 2.1/2|GBH SO 3|GBH SO 4|GBH TE 2.1/2|GBH TE 4|GBH UN 2.1/2|GBH UN 3|GBH UN 4|GCB RC 3X1.1/2|GN BU 1.1/2X1|GN BU 1.1/2X1.1/4|GN BU 1.1/2X1/2|GN BU 1.1/2X3/4|GN BU 1.1/4X1|GN BU 1.1/4X1 R|GN BU 1X1/2|GN BU 1X3/4|GN BU 2X1|GN BU 2X1 R|GN BU 2X1.1/2|GN BU 2X1.1/2 R|GN BU 2X1.1/4|GN BU 2X1.1/4 R|GN BU 2X1/2|GN BU 2X3/4|GN BU 3/4X1/2|GN CP 1|GN CP 1.1/2|GN CP 1.1/4|GN CP 2|GN CR 2|GN EL 45 1|GN EL 45 1.1/2|GN EL 45 1.1/4|GN EL 45 2|GN EL 90 1|GN EL 90 1.1/2|GN EL 90 1.1/2 R|GN EL 90 1.1/4|GN EL 90 1/2|GN EL 90 2|GN EL 90 3/4|GN NI 1|GN NI 1.1/2|GN NI 1.1/4|GN NI 1/2|GN NI 2|GN NI 3/4|GN PG 1|GN PG 1.1/2|GN PG 1.1/4|GN PG 1/2|GN PG 2|GN PG 3/4|GN RL 1.1/4X1|GN RL 1X1/2|GN RL 2X1.1/2|GN RS 1.1/2X1|GN RS 1.1/2X1.1/4|GN RS 1.1/2X1/2|GN RS 1.1/4X1|GN RS 1X1/2|GN RS 1X3/4|GN RS 2X1|GN RS 2X1.1/2|GN RS 2X1.1/4|GN RT 1.1/2X1|GN RT 1.1/2X1.1/4|GN RT 1.1/2X1/2|GN RT 1.1/4X1|GN RT 1.1/4X1/2 R|GN RT 1X1/2|GN RT 2X1|GN RT 2X1.1/2|GN RT 2X1.1/2 R|GN RT 2X1.1/4|GN RT 2X1.1/4 R|GN RT 2X1/2|GN RT 2X3/4|GN RT 3X1|GN SL 1/2|GN SO 1|GN SO 1.1/2|GN SO 1.1/4|GN SO 1/2|GN SO 2|GN SO 3/4|GN SOM 1.1/2|GN SOM 1/2|GN TE 1|GN TE 1.1/2|GN TE 1.1/4|GN TE 1/2|GN TE 2|GN TE 3/4|GN UN 1|GN UN 1.1/2|GN UN 1.1/4|GN UN 2|GN UN 3/4|HEMP|HM WT 15 1/2 PPP|PERMATEX|PIPE|PTFE|PTFE 3|PTFE 6|SO 150 2|SO 150 2.1/2|SO 150 3|SO 150 4|SO 150 5|SO 150 6|SO 16 2|SO 16 2.1/2|SO 16 3|SO 16 4|SO 16 5|SPW40 2 316L|SS UN 1|SSO 16 3|SSO 16 4|TP IN|TP INR|WN 150 2|WN 150 3|WN 150 4|WN 150 5|WN 150 8|WN 16 4|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null">ABDULAZIZ A . ISMAIL EST</option>

Answer 1

$(this).val() refers to the value attribute of your <option> which contains the ICODE, which is going to be passed to the fnFilter.

For example, if the html output is like this:

<option value="CL 1/2|CL 3/4|CU EL 45 1.1">AL- KASER FOR A/C EST.</option>

If it is selected, with your current code, the constructed regex would be ^CL 1/2|CL 3/4|CU EL 45 1.1$, which means it will match table rows where the first column contains text like CL 1/2xxxx, xxxxxCL 3/4xxxx, and xxxxxCU EL 45 1.1 (note the extra x chars). It will also match xxxxxCU EL 45 1x1 because in regex, the dot char could match any characters.

If you want to match exactly CL 1/2, CL 3/4, and CU EL 45 1.1, then the pattern needs to be enclosed with parantheses ( ) between the ^ and $, and all the special regex characters must be escaped, i.e. ^(CL 1\/2|CL 3\/4|CU EL 45 1\.1)$. Moreover, fnFilter doesn't recognize space so that we need to use \s instead of literal space. The final regex would be ^(CL\s1\/2|CL\s3\/4|CU\sEL\s45 1\.1)$.

Looking at your current code (where the second parameter to fnFilter is 0, i.e. the first column), I assume that you will display the ICODE in the first column and perhaps the vendor name in another column. See the illustration below.

Alternative Solution #1 Filtering based on ICODE

$(document).ready(function() {
  var table = $('#mine').DataTable();

  $('#mySelect').on('change', function() {
    var selectedValue = $(this).val();
    var patt = selectedValue.replace(/[\[\]\/\{\}\(\)\*\+\?\.\\\^\$-]/g, "\\$&"); //escape regex chars except "|"
    patt = patt.replace(/ /g, "\\\s");  //change space to "\s" to make fnFilter works
    if (patt) patt = "^(" + patt + ")$";  //enclose with parentheses only when pattern is not empty

    console.log(patt);

    $("#mine").dataTable().fnFilter(patt, 0, true); //Exact value, column, reg
  });
});

<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

<select id="mySelect">
  <option value="">All</option>                                        <!--     -->
  <option value="CL 1/2|CU EL 45 1.1">AL- KASER FOR A/C EST.</option>  <!-- ^(CL\s1\/2|CU\sEL\s45\s1\.1)$  -->
  <option value="BOLT 3/4X4|CH 1.1/2">AL-ALWANI TRADING C0</option>    <!-- ^(BOLT\s3\/4X4|CH\s1\.1\/2)$   -->
</select>


<table id="mine" border="1" cellpadding="1" cellspacing="0">
  <thead>
    <tr>                            <!-- header row -->
      <th>ICODE</th>                    <!-- column index 0 -->
      <th>VENDOR</th>                   <!-- column index 1 -->
      <th>DESC</th>                     <!-- column index 2 -->
    </tr>
  </thead>
  <tfoot>

  </tfoot>
  <tbody>
    <tr>                            <!-- 1st row -->
      <td>CL 1/2</td>                   <!-- column index 0 -->
      <td>AL- KASER FOR A/C EST.</td>   <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 2nd row -->
      <td>CU EL 45 1.1</td>             <!-- column index 0 -->
      <td>AL- KASER FOR A/C EST.</td>   <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 3rd row -->
      <td>BOLT 3/4X4</td>               <!-- column index 0 -->
      <td>AL-ALWANI TRADING C0</td>     <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 4th row -->
      <td>CH 1.1/2</td>                 <!-- column index 0 -->
      <td>AL-ALWANI TRADING C0</td>     <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
  </tbody>
</table>

IMPORTANT that you should not have nulls in your ICODE (as you shown in the chat conversation in the comment below). If you have multiple option values that have null, then if any of those option selected, all rows which have ICODE equals to null will be shown. If null is unavoidable, the second alternative below may be better to achieve the same result.

---

Alternative Solution #2 Filtering based on vendor name

The code is almost identical. The only difference is that we are using the vendor name for the value of the <option>s. And because of that, the second parameter to the fnFilter() is changed to 1 (which refers to the second column that is showing the vendor names).

$(document).ready(function() {
  var table = $('#mine').DataTable();

  $('#mySelect').on('change', function() {
    var selectedValue = $(this).val();
    var patt = selectedValue.replace(/[\[\]\/\{\}\(\)\*\+\?\.\\\^\$-]/g, "\\$&"); //escape regex chars except "|"
    patt = patt.replace(/ /g, "\\\s");  //change space to "\s" to make fnFilter works
    if (patt) patt = "^(" + patt + ")$";  //enclose with parentheses only when pattern is not empty

    console.log(patt);

    $("#mine").dataTable().fnFilter(patt, 1, true); //Exact value, column, reg
  });
});

<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

<select id="mySelect">
  <option value="">All</option>                                            <!--     -->
  <option value="AL- KASER FOR A/C EST.">AL- KASER FOR A/C EST.</option>   <!-- ^(AL\-\sKASER\sFOR\sA\/C\sEST\.)$  -->
  <option value="AL-ALWANI TRADING C0">AL-ALWANI TRADING C0</option>       <!-- ^(AL\-ALWANI\sTRADING\sC0)$   -->
</select>


<table id="mine" border="1" cellpadding="1" cellspacing="0">
  <thead>
    <tr>                            <!-- header row -->
      <th>ICODE</th>                    <!-- column index 0 -->
      <th>VENDOR</th>                   <!-- column index 1 -->
      <th>DESC</th>                     <!-- column index 2 -->
    </tr>
  </thead>
  <tfoot>

  </tfoot>
  <tbody>
    <tr>                            <!-- 1st row -->
      <td>CL 1/2</td>                   <!-- column index 0 -->
      <td>AL- KASER FOR A/C EST.</td>   <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 2nd row -->
      <td>CU EL 45 1.1</td>             <!-- column index 0 -->
      <td>AL- KASER FOR A/C EST.</td>   <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 3rd row -->
      <td>BOLT 3/4X4</td>               <!-- column index 0 -->
      <td>AL-ALWANI TRADING C0</td>     <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
    <tr>                            <!-- 4th row -->
      <td>CH 1.1/2</td>                 <!-- column index 0 -->
      <td>AL-ALWANI TRADING C0</td>     <!-- column index 1 -->
      <td>3rd column</td>               <!-- column index 2 -->
    </tr>
  </tbody>
</table>