Filter table depending on certain values (including table pagination)

Darkxemnas

Hi!

I have been using datatables for a while and today I'm facing a new problem that I didn't before.

Here's the actual state:

• I have a table of tickets with several rows, more than 150. That rows have a certain column, state, giving me information of how is the ticket (Done, To Do, Backlog, etc).

• The table has pagination enabled, search and excel export system. They are working properly.

• I also have a Bootstrap btn-group created that filters the tickets depending on their state.

What is the problem?

When I select one of the buttons of the button group, let's say the 'Done' one, is filtering on the current page of the table, not in all the table, showing values that does not match with the button and others than do. If I disable the pagination part of the table and I display all the tickets in one page (without sort or similars) it works properly.

Main question:

Is there a function to filter all the values that matches on the table with the selected button, having the current functionalities? Thank you in advance.

Datatable code:

datatable = tableId.DataTable({
    "bInfo": true,
    "info": true,
    "ordering": true,
    "bAutoWidth": false,
    "pageLength": 15,
    dom: 'Bfp',
    buttons: [
        { extend: 'excelHtml5', className: 'excel-button' , text: 'Export .xsl'},
    ],
    "language": {
        "emptyTable": emptyMessage
    },
    //Cuando la tabla ha sido configurada, antes de que cargue, realizar
    //las siguientes acciones
    "fnInitComplete" : function(oSettings) {
        $('.button-wrapper').append($("#table-export_wrapper")[0]);
        $('.search-wrapper').append($('#table-export_filter')[0]);
        $('.paging-buttons').append($('#table-export_paginate'))
        // .container().appendTo('#button-wrapper');
    }
});

}

rf1234

I hope I got it right: You are filtering with your own code and or not using the Datatable functionality for this, right? That probably causes the error if you have pagination because then the data on the subsequent pages might not be available for filtering when you do the filtering using your own code.

I also do filtering but I use the datatables functionality for this. You might want to consider this code. I have up to five tables on this page and I need to check for undefined to determine which table is being filtered by data tables at any one time. If you have multiple tables on one page there is no other way to get it right. The search.push functionality will run through all tables on your page.

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex, row, counter ) {
        var ok;
        var deleted = '0';
        //we only do this for the second table
        if ( (typeof row.offer !== 'undefined') &&
             (typeof row.offer_has_rfa !== 'undefined') )   {
            if (row.offer_has_rfa.status !== null) {                  
                ok = filterShowNMonths(row.offer.update_time);
                if (ok) {
        //if the offer already has been decided upon it will be hidden like
        //a deleted inbox item (A=Approved, N=notApproved, W=Waiting for dec.)
                    if (row.offer_has_rfa.status !== 'W') {
                       deleted = '1';
                    }
                    return filterDeleted(deleted);
                } else {
                    return false;
                }
            }
        }
        if ( (typeof row.proposal !== 'undefined' ) &&
             (typeof row.proposal_has_rfa !== 'undefined') )   {  
            if (row.proposal_has_rfa.status !== null) {
                ok = filterShowNMonths(row.proposal.update_time);
                if (ok) {
                    if (row.proposal_has_rfa.status !== 'W') {
                       deleted = '1';
                    }
                    return filterDeleted(deleted);
                } else {
                    return false;
                }
            }               
        }
        if ( (typeof row.contract !== 'undefined' ) &&
             (typeof row.contract_has_rfa !== 'undefined') )   {  
            if (row.contract_has_rfa.status !== null) {
                ok = filterShowNMonths(row.contract.update_time);
                if (ok) {
                    if (row.contract_has_rfa.status !== 'W') {
                       deleted = '1';
                    }
                    return filterDeleted(deleted);
                } else {
                    return false;
                }
            }               
        } else {
            var selected = inboxRfaContractCredTable.row( {selected: true} );
            if (selected.any()) {
        //in case it is a prolongation the expired components are filtered
        //out by default (redraw upon select of contract raw and also upon push button)
                if ( selected.data().contract.prolongation > '0' ) {
                    if ( typeof row.variable !== 'undefined' )   {
                        return filterExpElements(row.variable.end_date, 'VariableExpElementsButton', showVariableExpElements);
                    } else {
                        if ( typeof row.fixed !== 'undefined' ) {
                            return filterExpElements(row.fixed.end_date, 'FixedExpElementsButton', showFixedExpElements);          
                        }    
                    }
                }
            }
        }
        return true;
    }                
);

Here is one of the functions (filterShowNMonths) that is being called (and the functions being called in that function) in the example above. As you can see I also use a custom bootstrap button with the id "monthsSelect"; its value is being used for the search.push filtering.

function nMonthsAgo(nMonths) {
    var nDays;
    var duration;
    if (nMonths < 1 && nMonths > 0) {
        //get days in current month and determine how many days back
        //for a fraction of the current month
        nDays = moment(year + '-' + month, "YYYY-MM").daysInMonth();
        nDays = nDays * nMonths;
        duration = moment.duration({'days' : nDays});
    } else {
        duration = moment.duration({'months' : nMonths});
    }
    //return the date n months ago
    return moment(currentTime).subtract(duration).format('YYYY-MM-DD');
}

function timestampToYearMonthDay(timestamp) {
    //returns YYYY-MM-DD
    var date = timestamp.substring(0, 10);
    
    if ( ( date.substring(4, 1) === '-' ) && 
         ( date.substring(7, 1) === '-' )     ) {
    //the field is already in the right format because it was taken from a timestamp
        return date;
    } else {
    //the field needs to be converted because it is in the respecive locales format
        return moment(date, 'L').format('YYYY-MM-DD');
    }
}

function filterShowNMonths(updateTime) {
    var startDate = nMonthsAgo( $('#monthsSelect').val() );
    // update time column
    
    var lastUpdate = timestampToYearMonthDay ( updateTime );
    
    if (startDate <= lastUpdate) {
        return true;
    } else {
        return false;
    }
}

Darkxemnas

Thank you for your awesome answer. I will test it soon!