show only filter record data another filter like excel

gopiindia · June 27

Hello
This is my html page

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>demo</title>
<script type="text/javascript" src="jquery-3.2.1.min.js"></script> 
<script type="text/javascript" src="dt.js"></script>
<link rel="stylesheet" type="text/css" href=dt.css />
<link rel="stylesheet" type="text/css" href=select.css />


     <script type="text/javascript" src="select.js"></script>
      <script type="text/javascript" src="button.js"></script>
       <script type="text/javascript" src="buttona.js"></script>
        <script type="text/javascript" src="buttonab.js"></script>
     
 
   
    <script type="text/javascript">
        $(document).ready(function () {
            var thArray = [];
            $('#list > thead > tr > th').each(function () {
                thArray.push($(this).text())
            })
            var rowCount = $('table#list tbody tr').length;
            sessionStorage.setItem("rowCount", rowCount);
            // Remove the formatting to get integer data for summation
            var intVal = function (i) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '') * 1 :
                    typeof i === 'number' ?
                        i : 0;
            };
             $('#list').DataTable({
                "ordering": false,
                dom: 'Bfrtip',
                "buttons": [{
                    extend: 'excel',
                    footer: true,
                   // title: "Test",
                    title: "CHEMICAL ARRIVAL - ISSUE DETIALS",
                    className:'btn-success',
                    exportOptions: {
                       // columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
                        format: {
                            header: function (data, index, column) {
                                return thArray[index]
                            }
                        }
                    }
                }
                ],
                 
    lengthMenu: [
            [2000, 1500, 1000, 500, -1],
            [2000, 1500, 1000, 500, 'All'],
        ],
                
                initComplete: function () {
                    this.api().columns([1, 2, 3]).every(function () {
                        var title = this.header();
                        //replace spaces with dashes
                        title = $(title).html().replace(/[\W]/g, '-');
                        var column = this;
                        var select = $('<select id="' + title + '" class="select2" ></select>')
                            .appendTo($(column.header()).empty())
                            .on('change', function () {
                                //Get the "text" property from each selected data 
                                //regex escape the value and store in array
                                var data = $.map($(this).select2('data'), function (value, key) {
                                    return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                                });
                                //if no data selected use ""
                                if (data.length === 0) {
                                    data = [""];
                                }
                                //join array into string with regex or (|)
                                var val = data.join('|');
                                //search for the option(s) selected
                                column
                                    .search(val ? val : '', true, false)
                                    .draw();
                            });
                        column.data().unique().sort().each(function (d, j) {
                            select.append('<option value="' + d + '">' + d + '</option>');
                        });
                        //use column title as selector and placeholder
                        $('#' + title).select2({
                            multiple: true,
                            closeOnSelect: false,
                            width: '100%',
                            placeholder: "" + title
                        });
                        //initially clear select otherwise first option is selected
                        $('.select2').val(null).trigger('change');
                    });
                },
                footerCallback: function (tfoot, data, start, end, display) {
                    let api = this.api();
                    api.column(2).footer().innerHTML = "GRAND TOTAL";
                    // Total over all pages
                    total = api
                        .column(3, { search: 'applied' })
                        .data()
                        .reduce((a, b) => intVal(a) + intVal(b), 0);
                           
                    
                        
                        
                    // Update footer
                   api.column(3).footer().innerHTML = total.toFixed(0);
                     
                       
                }
            });
        });
    </script>
<style>

    .btn-success {
            width: 110px;
            height: 40px;
            background-image: url('images/excelnew.png');
            background-repeat: no-repeat;
            background-size: cover;
            position: relative;
            left:682px; 
            top: -34px;
            display: block;
            text-indent: -9999em;
        }
        
        .select2-results__options[aria-multiselectable="true"] li {
            padding-left: 30px;
            position: relative;           
        }
            .select2-results__options[aria-multiselectable="true"] li:before {
                position: absolute;
                left: 8px;
                opacity: .6;
                top: 6px;
                font-family: "FontAwesome";
                content: "\f0c8";
            }
            .select2-results__options[aria-multiselectable="true"] li[aria-selected="true"]:before {
                content: "\f14a";
            }
            
            table {
     margin: 0 auto;
     margin-top: 20px;
     width: 100%;
     position: relative;
     overflow: auto;
 }
 th, thead {
     position: sticky;
     top: 0;
     border: 1px solid #dddddd;
     background-color: #ABEBC6;
     text-align: center;
     table-layout: fixed;
    
     height: 25px;
 }
    </style>
         
         
      
     

</head>
<body>
    <form id="form1" runat="server">
    <div>
       <div id="c1" runat="server">
        <table cellspacing="0" class="myClass" id="list" style="width: 1400px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black; z-index: 102;">
            <thead>
                <tr>
                
                  <th style="width:60px;text-align:center;">F1</th>
                    <th style="width:110px;text-align:center;">F2</th>
                    <th style="width:150px;text-align:center;">F3</th> 
                    <th style="width:40px;text-align:center;">F4</th>
                    
                     
                    
                </tr>
            </thead>
            <tbody style="border-collapse: collapse; border: 1px solid black;">
                
<tr>
<td>APPLE</td>
<td>G1</td>
<td>K1</td>
<td>11</td>
</tr>

<tr>
<td>MANGO</td>
<td>G3</td>
<td>Q2</td>
<td>10</td>
</tr>

<tr>
<td>BANANA</td>
<td>V1</td>
<td>G4</td>
<td>40</td>
</tr>


<tr>
<td>Kiwi</td>
<td>R4</td>
<td>G4</td>
<td>30</td>
</tr>


<tr>
<td>Papaya</td>
<td>E3</td>
<td>W4</td>
<td>10</td>
</tr> 

<tr>
<td>Watermelon</td>
<td>S1</td>
<td>Q1</td>
<td>4</td>
</tr>
 
            </tbody>
            <tfoot>
                <tr>
                                
             <td style="background: sandybrown; border: 1px solid black;"></td> 
                             
             <td style="background: sandybrown; border: 1px solid black;"></td>
                         
             <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td>
                         
             <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td> 
             
              
              
               
                          
               </tr>
            </tfoot>
        </table>
    </div>
    </div>
    </form>
</body>
</html>

I have 4 cloumn F1,F2,F3,F4 I have applied multi filter for F2,F3,F4 all working fine only one problem for example i filter in f2 G1,R4 it has to show In F3 Filter data K1,G4 And In F4 Filter data 11,30 but its shwoing in F3 and F4 all data in filter how to solve this in my above code

allan · June 27

I've tried to get your example to run here ( https://live.datatables.net/togijizi/1/edit ) so I can understand your question a bit better, but I think I'm missing a few parts. Can you modify it to run as you would expect?

I'm finding it difficult to follow your question and a running test case would really help.

Allan

gopiindia · June 27

Hello

I have updated the code it working fine now https://live.datatables.net/togijizi/14

what i need is

Example 1 on page load user comes and do multi filter in f2 column as G3,V1 In f3 and f4 its showing all data not filtered data we want only in f3 as Q2,G4 and F4 10,40

Example 2: on page load user comes and do multi filter in f3 column as K1,W4 In f2 and f4 its showing all data not filtered data we want only in f2 as G1,E3 and F4 11,10

Example 3 on page load user comes and do multi filter in f4 column as 30,4 In f2 and f3 its showing all data not filtered data we want only in f2 as R4,S1 and f3 G4,Q1

how to acheive this using my above code
thanking you in advance please help

gopiindia · June 27

Here Is The Complete HTML code

</head>
<body>
<form id="form1" runat="server">
<div>
<div id="c1" runat="server">
<table cellspacing="0" class="myClass" id="list" style="width: 1400px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black; z-index: 102;">
<thead>
<tr>

              <th style="width:60px;text-align:center;">F1</th>
                <th style="width:110px;text-align:center;">F2</th>
                <th style="width:150px;text-align:center;">F3</th> 
                <th style="width:40px;text-align:center;">F4</th>



            </tr>
        </thead>
        <tbody style="border-collapse: collapse; border: 1px solid black;">

<tr>
<td>APPLE</td>
<td>G1</td>
<td>K1</td>
<td>11</td>
</tr>

<tr>
<td>MANGO</td>
<td>G3</td>
<td>Q2</td>
<td>10</td>
</tr>

<tr>
<td>BANANA</td>
<td>V1</td>
<td>G4</td>
<td>40</td>
</tr>

<tr>
<td>Papaya</td>
<td>E3</td>
<td>W4</td>
<td>10</td>
</tr>

<tr>
<td>Watermelon</td>
<td>S1</td>
<td>Q1</td>
<td>4</td>
</tr>

        </tbody>
        <tfoot>
            <tr>

         <td style="background: sandybrown; border: 1px solid black;"></td> 

         <td style="background: sandybrown; border: 1px solid black;"></td>

         <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td>

         <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td> 





           </tr>
        </tfoot>
    </table>
</div>
</div>
</form>

</body>
</html>

kthorngren · June 27

Souds like you want to update the select options based on the applied search. See this example from this thread.

Basically move your initComplete code into the buildSelect() function. You will need. to use statement to allow updating the select options based on the applied search:

var column = table.column( this, {search: 'applied'} );

Kevin

gopiindia · June 27

Can you modified the below code im a fresher so little confues where to add

<script type="text/javascript">
    $(document).ready(function () {
        var thArray = [];
        $('#list > thead > tr > th').each(function () {
            thArray.push($(this).text())
        })
        var rowCount = $('table#list tbody tr').length;
        sessionStorage.setItem("rowCount", rowCount);
        // Remove the formatting to get integer data for summation
        var intVal = function (i) {
            return typeof i === 'string' ?
                i.replace(/[\$,]/g, '') * 1 :
                typeof i === 'number' ?
                    i : 0;
        };
         $('#list').DataTable({
            "ordering": false,
            dom: 'Bfrtip',
            "buttons": [{
                extend: 'excel',
                footer: true,
               // title: "Test",
                title: "CHEMICAL ARRIVAL - ISSUE DETIALS",
                className:'btn-success',
                exportOptions: {
                   // columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
                    format: {
                        header: function (data, index, column) {
                            return thArray[index]
                        }
                    }
                }
            }
            ],

lengthMenu: [
        [2000, 1500, 1000, 500, -1],
        [2000, 1500, 1000, 500, 'All'],
    ],

            initComplete: function () {
                this.api().columns([1, 2, 6, 7]).every(function () {
                    var title = this.header();
                    //replace spaces with dashes
                    title = $(title).html().replace(/[\W]/g, '-');
                    var column = this;
                    var select = $('<select id="' + title + '" class="select2" ></select>')
                        .appendTo($(column.header()).empty())
                        .on('change', function () {
                            //Get the "text" property from each selected data 
                            //regex escape the value and store in array
                            var data = $.map($(this).select2('data'), function (value, key) {
                                return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                            });
                            //if no data selected use ""
                            if (data.length === 0) {
                                data = [""];
                            }
                            //join array into string with regex or (|)
                            var val = data.join('|');
                            //search for the option(s) selected

                            //var column = table.column( this, {search: 'applied'} );
                            column
                                .search(val ? val : '', true, false)
                                .draw();
                        });
                    column.data().unique().sort().each(function (d, j) {
                        select.append('<option value="' + d + '">' + d + '</option>');
                    });
                    //use column title as selector and placeholder
                    $('#' + title).select2({
                        multiple: true,
                        closeOnSelect: false,
                        width: '100%',
                        cumulative_filtering: false, 
                        placeholder: "" + title
                    });
                    //initially clear select otherwise first option is selected
                    $('.select2').val(null).trigger('change');
                });
            },
            footerCallback: function (tfoot, data, start, end, display) {
                let api = this.api();
                api.column(2).footer().innerHTML = "GRAND TOTAL";
                // Total over all pages
                total = api
                    .column(3, { search: 'applied' })
                    .data()
                    .reduce((a, b) => intVal(a) + intVal(b), 0);

                       // next 
                  total1 = api
                    .column(5, { search: 'applied' })
                    .data()
                    .reduce((a, b) => intVal(a) + intVal(b), 0);   



                // Update footer
               api.column(3).footer().innerHTML = total.toFixed(0);
                  api.column(5).footer().innerHTML = total1.toFixed(0);

            }
        });
    });
</script>

gopiindia · June 27

what i need is

Example 1 on page load user comes and do multi filter in f2 column as G3,V1 In f3 and f4 its showing all data not filtered data we want only in f3 as Q2,G4 and F4 10,40

Example 2: on page load user comes and do multi filter in f3 column as K1,W4 In f2 and f4 its showing all data not filtered data we want only in f2 as G1,E3 and F4 11,10

Example 3 on page load user comes and do multi filter in f4 column as 30,4 In f2 and f3 its showing all data not filtered data we want only in f2 as R4,S1 and f3 G4,Q1

how to acheive this using my above code
thanking you in advance please help

gopiindia · June 28

please help me to solve this issues

gopiindia · June 28

exactly this https://stackoverflow.com/questions/62863691/how-to-update-select-options-based-on-previous-selected-values-in-jquery-datatab

I want the other selects to be updated based on the previous selected options.

So in my example if I select office London and New York, I want to see in name only people who are in London and New York, not all options.

Please help me where to change/add in my code it would be needful if someone help me change my code and update here full code to understand

show only filter record data another filter like excel

show only filter record data another filter like excel

Answers

Howdy, Stranger!

Categories

DataTables

show only filter record data another filter like excel

show only filter record data another filter like excel

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables