Date range filter and Editor not working - no results when choosing date

Date range filter and Editor not working - no results when choosing date

asleasle Posts: 96Questions: 28Answers: 0
edited September 2022 in Free community support

I am trying to get the date range filter example to work with Editor. When I choose a valid date I get no results. I wonder where I can look to troubleshoot this. I moved away from serverSide so now I get the complete json and can filter locally. There are only fewer than 5 thousand. records so it should be no problem speedwise.

I have my columns set up like this:

    columns: [ 
        { data: "garantinr" },
        { data: "forhandler" },
        { data: "kunde", render: function ( data, type, row ) {
            // Combine the first and last names into a single table field
            return row.kunde+' '+row.kunde_enavn;
        } },
        { data: "kunde_postnr"},            
        { data: "kunde_sted" },
        { data: "pumpemod" },
        { data: "varenummer" },
        { data: "mont_id" },
        { data: "reg_dato" },  //  ## this is the date field to sort ##
        { data: "ordrenummer", render: function ( data, type, row ) {
            if (row.ordrenummer == null) {return ''} else {
            return '<a href="https://spor.bring.no/sporing/' + 
            row.ordrenummer + '" target="–blank">' + 
            row.ordrenummer + '</a>'; 
            }
        } }, 
        { data: "kunde_enavn" },
    ],

columnDefs: [
        {
            targets: [8],
            render: $.fn.dataTable.render.moment('DD-MM-YYYY' ) // ## Show reg_dato in local date format ##
        }, 

I am not using serverSide since I need the smart search.
When I use the example function with the row containing date, I get "Uncaught ReferenceError: data8 is not defined". So I changed the function from function( settings, data, dataIndex ) { .... var date = new Date( data[8] ); to this below:

// Custom filtering function which will search data in column eight between two values
$.fn.dataTable.ext.search.push(
    function( data, type, row ) {
        var min = minDate.val();
        var max = maxDate.val();
        var date = new Date( row.reg_dato );
        if (
            ( min === null && max === null ) ||
            ( min === null && date <= max ) ||
            ( min <= date   && max === null ) ||
            ( min <= date   && date <= max )
        ) {
            return true;
        }
        return false;
    }
);

I create the date inputs with local date formatting but have also tried mySQL format YYYY-MM-DD

    // Create date inputs
    minDate = new DateTime($('#min'), {
        format: 'DD-MM-YYYY'
        //format: 'YYYY-MM-DD'
    });
    maxDate = new DateTime($('#max'), {
        format: 'DD-MM-YYYY'
        //format: 'YYYY-MM-DD'
    }); 
    // Refilter the table
    $('#min, #max').on('change', function ( settings, data, dataIndex ) {
        table.draw();   console.log($('#min').val());
   });

When I click in the startDate field and enter a date the table is redrawn but I get no records returned!

Here is some of my json

 "data": [
    {
      "DT_RowId": "row_3441",
      "id": "3441",
      "garantinr": "XXXXX-22-05-1",
      "ordrenummer": "618222",
      "kunde": "XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 39",
      "kunde_postnr": "4027",
      "kunde_sted": "Stavanger",
      "kunde_tel": "XXXXX",
      "pumpemod": "Lofoten Powerful",
      "varenummer": "601700-601701",
      "forhandler": "COOP OBS BYGG Madla",
      "ekstern_kom": "Ta kontakt med kunde..",
      "ekstern_reg": "1",
      "mont_id": "803548",
      "montdato": "2022-06-01",
      "rorlengde": "3",
      "testrun": "1",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3442",
      "id": "3442",
      "garantinr": "801214-22-05-1",
      "ordrenummer": "618276",
      "kunde": "Svein \XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 66",
      "kunde_postnr": "1927",
      "kunde_sted": "RXXXXX",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG XXXXX",
      "ekstern_kom": "Kundes telefon nummer: XXXXX\r\nmail",
      "ekstern_reg": "1",
      "mont_id": "801214",
      "montdato": "2022-06-13",
      "rorlengde": "6",
      "testrun": "ja",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3443",
      "id": "3443",
      "garantinr": "801214-22-05-2",
      "ordrenummer": "618276",
      "kunde": "Jon XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 68",
      "kunde_postnr": "1927",
      "kunde_sted": "XXXXX",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG XXXXX",
      "ekstern_kom": "Kunde har ikke oppgitt mail.",
      "ekstern_reg": "1",
      "mont_id": "801214",
      "montdato": "2022-06-13",
      "rorlengde": "5",
      "testrun": "ja",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3446",
      "id": "3446",
      "garantinr": "803548-22-05-2",
      "ordrenummer": "618223",
      "kunde": "XXXXX ",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "\XXXXX 106",
      "kunde_postnr": "4330",
      "kunde_sted": "\XXXX\t",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG",
      "ekstern_kom": null,
      "ekstern_reg": "1",
      "mont_id": "803548",
      "montdato": "2022-06-01",
      "rorlengde": "2.7",
      "testrun": "1",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-19",
      "ferdig_status": "1"
    },

I am sorry I can not show a working example since this is on a closed site. I can of course make a sample outside the site if necesarry.

Answers

  • asleasle Posts: 96Questions: 28Answers: 0

    Hi, has anyone implemented the date range filter with Editor or is it only used in DataTables?

  • kthorngrenkthorngren Posts: 21,468Questions: 26Answers: 4,977

    The Date Range filter works with Datatables whether you are using Editor or not. Datatables is in control of the table display while Editor is an add-on (for lack of a better word) to allow for editing the table.

    The place to start is to do some debugging to find out what the result of var date = new Date( row.reg_dato ); is. Set a breakpoint and use the browser's debugger or use console.log(date);. My guess is you will find the same issue as this thread.

    If you still need help please provide a test case so we can help debug.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • asleasle Posts: 96Questions: 28Answers: 0

    Thanks I read the thread you referred to. But none of the test example pages work. I get "ERR_TOO_MANY_REDIRECTS" on all pages for "http://live.datatables.net/"

    When I log the result of console.log(date) I get Invalid Date.
    If I use the original example with the function function( settings, data, dataIndex ) { - I get a 500 server error. I know my field is data[8] but when I do console.log(row.reg_dato) I get undefined
    It seems datatables can not find the correct column. This is strange. I can access the column with this code and it formats the date correctly:

    columnDefs: [
            {
                targets: [8],  // this is the reg_dato field
                render: $.fn.dataTable.render.moment('DD-MM-YYYY' )
            }, 
    
  • kthorngrenkthorngren Posts: 21,468Questions: 26Answers: 4,977

    I get "ERR_TOO_MANY_REDIRECTS" on all pages for "http://live.datatables.net/"

    Hmm, strange, it works for me. Anyway here is the code from the working search plugin. Notise on line 6 its using moment.js to format the date to an ISO format. You will likely need to do the same be changing DD/MM/YYYY to the format you are using.

     // Custom filtering function which will search data in column four between two values
     $.fn.dataTable.ext.search.push(
         function( settings, data, dataIndex ) {
             var min = minDate.val();
             var max = maxDate.val();
             var date = new Date( moment(data[0], 'DD/MM/YYYY').format('YYYY-MM-DD') );
           
      
             if (
                 ( min === null && max === null ) ||
                 ( min === null && date <= max ) ||
                 ( min <= date   && max === null ) ||
                 ( min <= date   && date <= max )
             ) {
                 return true;
             }
             return false;
         }
     );
    

    Kevin

Sign In or Register to comment.