Filter-search by row background color

mihalisp

1)After rowCallback-drawCallback,i have some rows with background colors red and orange.

How can i filter the Datatable so that i see only these rows ?

2)These rows with background colors are the result after executing calculations-conditions in the rowCallback-drawCallback function.
How can i use them to filter the Datatable instead of filtering by row background color?

Thanks!

colin

Hi @mihalisp ,

You could create a custom filter to do that, see some here. Probably the easiest way though, would be to have a hidden column in the table where you store values as you colour the other cells - you could then easily filter on that with column().search(),

Cheers,

Colin

mihalisp

Can you give me an example please?
In rowCallback , if a certain condition is true then the row'w background gets red.
How can i pass that info (red background color) in the rendered hidden column?

colin

Hi @mihalisp ,

You could do something like this - it's only showing the rows with "San Francisco" as the office, based on a value added in the createdRow function.

Hope that helps,

Cheers,

Colin

mihalisp

Nice,but i want a checkbox to do that job,when it is checked it will show only the red rows.
How to modify it?

Thanks.

mihalisp

Any help on this colin,please?

Thank you

kthorngren

Here is an example from a previous thread. It may have more in it as it allows for searching using multiple checkboxes. You probably can eliminate the regex part and use the default Smart search mode.
http://live.datatables.net/vipifute/1/edit

Kevin

colin

Hi @mihalisp ,

I used Kevin's example from this thread to make this example here. It seems to be doing what you're after.

Cheers,

Colin

mihalisp

Thank you very much!

mihalisp

Thanks again.

I have 2 questions please.

1)Why do you use this??Why do we need it?

  this.api().row(row).data().hidden = true;
    }
     else
    {
      this.api().row(row).data().hidden = false;
    }

2) If i replace addClass( 'red' ) and hasClass( 'red' ) with css('background-color', '#ff8080'); ,

then it doesn't work.

kthorngren

2) If i replace addClass( 'red' ) and hasClass( 'red' ) with css('background-color', '#ff8080'); ,
then it doesn't work.

Please update Colin's test case to show what you are doing so we can help.

Doesn't look like his example is doing anything with this.api().row(row).data().hidden so its probably not needed for what you are doing.

Kevin

mihalisp

you can check it now
http://live.datatables.net/vopanemi/12/edit

kthorngren

.css('background-color', '#ff8080') is a setter not a getter. This won't work to check the values in the search plugin function. A search for how to compare the CSS color yields this result:
https://stackoverflow.com/questions/11548006/jquery-comparing-background-color

Looks like you need to do a comparison like this:
if(element.css("background-color") == "rgb(0, 0, 0)")

I added a console.log statement to your example to see what '#ff8080'is in rgb and used it in this example:
http://live.datatables.net/vopanemi/14/edit

Kevin

mihalisp

Thanks again,Great school here!

But i see that it messes my other datatables in the same page.

How can i tell the search plugin to search only a specific table, eg. 'table1'?

Is if ( settings.nTable.id !== "example1" ) {return true;} suitable here ;

if yes,how to use it???

kthorngren

Here is a thread with the same question:
https://datatables.net/forums/discussion/comment/152832/#Comment_152832

Hope it helps.

Kevin

mihalisp

ok,i see.

Now in this line

    return checked && $(table.row(index).node()).css('background-color') !== 'rgb(255, 128, 128)'? false: true

i get an error ReferenceError: table is not defined

tangerine

See this line in the example:

 var table = $('#example').DataTable({

You must have initialized your DataTable with a different var name.

mihalisp

No it isn;t that the problem.I have the right name.

Does the order of these scripts play a role?the error occurs when i use return checked && $(table.row(index).node()).css('backgrou...
on the top of the script before

    $(document).ready(function() {

.Also the table doesn't draw each time i change the checkbox.

Does createdrow behave the same as rowcallback?I use the second,is it a problem?

kthorngren

on the top of the script before
$(document).ready(function() {

Its a Javascript scoping issue. The table variable is created inside the document().ready() function but you are trying to reference it outside the function. Either move the search plugin inside document().ready() or declare the table variable globally before document().ready().

Also the table doesn't draw each time i change the checkbox.

This event handler should run each time you change the checkbox:

  $('input:checkbox').on('change', function () {
    
    // Run the search plugin
    table.draw();
    
  });      

You can put a console.log statement inside the function to see if it is running. Check for errors in your browser's console.

Does createdrow behave the same as rowcallback?I use the second,is it a problem?

Essentially they are the same. createdRow runs once as when the row is first created. rowCallback runs each time the row is drawn. The first is preferred if the data doesn't change as it is more efficient. But if your data can change then rowCallback is the method to use.

If this doesn't help then please update the test case so we can see the problem.

Kevin

mihalisp

In my case i don't use serverside:true,how can .draw work here?If i replace it with ajax.reload,it doesn't work at all.

My final problem is:

1)I pick 2 dates from datepickers to filter the datatables with ajax.reload(.draw doesn't work).
2)The table loads the records and some rows of them have the red background.When i check the chkbox once it filters correctly (17 records for 1 day).
3)With the chkbox checked,If i change dates from the datepickers,the records in the table remain the same,they don't change at all.It seems not to draw.

If i uncheck the chkbox ,all the records get back as expected.If i change again dates and then check the chkbox,it returns only the 'red' rows from the first filter (only 17,but iit should be 55).

Any thoughts?

kthorngren

The datepickers are new to our discussion. Without seeing what you are doing its hard to offer suggestions. Please provide a test case replicating the issue.

Kevin

mihalisp

I can't load objects.txt,how do you do it?

Here it is http://live.datatables.net/vopanemi/17/edit

And here is the queries.php

where Alldates >= (convert (date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) + SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)+SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))  and Alldates <= (convert (date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) + SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)+ SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))    ";

$stmt = sqlsrv_prepare( $conn, $sql , array(&$_POST["startdate"],&$_POST["startdate"],&$_POST["startdate"],&$_POST["enddate"],&$_POST["enddate"],&$_POST["enddate"]));

kthorngren

The problem is not with objects.txt. The problem is you have errors on the page. Take a look at the browser's console for the errors.

Kevin

mihalisp

I don't know why it happens here,it doesn't have any errors in the real app.

an you help me on this?What kind of errors are these?Maybethe cdn links?

From what you see in my code,in your opinion what maybe the problem and the filter doesn;t work when i change the datepicker dates?The recordset reloads but the filter still returns the same records whatever dates i pick.

kthorngren

bootstrap-datepicker.el.min.js:1 Uncaught TypeError: Cannot read property 'dates' of undefined
el.js:15 Uncaught TypeError: Cannot read property 'defineLocale' of undefined
VM273:12 Uncaught ReferenceError: moment is not defined

I did a google search and it looks like you may be running into this:
https://github.com/rajit/bootstrap3-datepicker/issues/13

You have this:

      <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.9.0/locales/bootstrap-datepicker.el.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/locale/el.js"></script>

But it doesn't look like you are including the files they require. I guess these would be bootstrap3-dataicker.js and moment.js.

There is nothing obvious to me why the change event you have doesn't work.

$('#from, #to').datepicker().change(function () {
                 //table.ajax.url("queries.php"); 
                //table.ajax.reload();
                console.log('datepicker change event');   // Add this debug
                var from = $( '#from' ).val();
                var to = $( '#to' ).val();
                input ={ startdate:from, enddate: to };
              
                table.ajax.reload();
            });

I suggest putting a console.log statement in the event to see if it is being executed.

Kevin

mihalisp

I found the right links and now it throws no errors
http://live.datatables.net/vopanemi/18/edit

I have commented out my ajax script (it posts data:2 datepicker date values to the queries.php ).

Can you make it so that the 2 datepickers filter your recordset (objects.txt) and after that check the red rows by checking the chkbox.

kthorngren

See if this thread helps. it has an example for a date range filter:
https://datatables.net/forums/discussion/39894/date-range-filter#latest

Kevin

mihalisp

No,it doesn;'t help.

I noticed that :

1) When i change dates in datepickers and chkbox is already checked,it returns no records.

2)when i check the chkbox it returns records from only the 1st page of the table.

I hope these infos help you understand the problem.

mihalisp

Also,when i change dates in datepickers i use ajax.reload, .draw doesn't work here.
But in the chkbox change event i use .draw .
Is this relevant ?

  $('#from, #to').datepicker().change(function () {       

           var from = $( '#from' ).val();
           var to = $( '#to' ).val();

            table.ajax.reload();
        });  

kthorngren

Do you have an updated test case with your changes to implement a date range search? The test case you linked to above doesn't contain code for date range search. Without seeing your updated code it will be difficult to provide recommendations.

1) When i change dates in datepickers and chkbox is already checked,it returns no records.
2)when i check the chkbox it returns records from only the 1st page of the table.

Your example above seems to behave correctly for both of these. Although it doesn't do anything with searching the date range it just doesn't exhibit the behaviors you mention.

Also,when i change dates in datepickers i use ajax.reload, .draw doesn't work here.

Don't think ajax.reload() is going to help with the searches. Its just going to reload the table. Using draw() will run the search plugin.

Kevin

mihalisp

After a lot of hours debugging,i concluded that fixedHeader:true was doing all the damage.
Any thoughts?