Question regarding cell data replacement and global or column searches

cachiap

Allan,

I have run into a difficulty when searching and I need to know if there is a solution I have overlooked.

Here is my problem.

My MySQL database table that I am searching contains a "date" column that uses a bogus date "9999-12-13" to indicate that the entry is OBSOLETE. I am trying to find a way to present that date information as the text "OBSOLETE" for the client.

I am using serverside processing.

I have tried both "mRender" and "cellCreated" to change the bogus date to "OBSOLETE" but global and column searches only recongnise the date and not the text. I realise now that this is because they are operating on the serverside where only the date is present.

Is there some work around that I have not found in the manual to help with this? I need to work on the serverside to make these client tables load quickly as some clients have tables with several thousand rows of data.

Am I stuck with this situation?

Thanks

Paul

allan

Hi Paul,

Can you show me how you were using the mRender option (columns.render in 1.10 notation)? That should work okay - createdCell will not work for the search data since that modifies the cell, not the DataTables cached data, but columns.render should work correctly).

Allan

cachiap

Allan,

Thanks for pointing me to the relevant areas in your manual.

I have read up on Orthogonal Data and columns.render and I can get the desired display I want ... replacing the date "9999-12-31" with the word "OBSOLETE" but the global and column specific searches still only recognize the date format and not the text.

I have included the script below from my programme. The table is constructed from a join employing data from three databases.... 'location','product_name', and 'pdflocation'. The date I am trying to manipulate belongs to the 'product_name' database

        var table = $('#ClientInventoryJoin').DataTable( { 
            
            
                "scrollY": "400px",
                
                "scrollCollapse": true,
                
        "dom": '<"top"lfi>rt<"bottom"p><"clear">',
  
                "lengthMenu": [100, 25, 50], 
                
        columns: [ 

                        { data: "location.locname", target: 0 },
                        { data: "product_name.pdfprodname", target: 1 },
                        { data: "product_name.pdfmanufacturer", target: 2 },                        

            { data: "pdflocation.prodidentifier", target: 3 },
                        { data: "pdflocation.prodtype", target: 4 },
                        { data: "pdflocation.ref3", target: 5 },
                        { data: "pdflocation.ref4", target: 6 },
                        { data: "product_name.pdfexpiredate", target: 7 },
                        { data: "product_name.pdfcontrolled", target: 8 }

                        

        ],
                
        order: [[ 2, 'asc' ], [ 1, 'asc' ]],
            
                "serverSide": true,
                
                "processing": true,
            
        ajax: {
                    
                        "type": 'POST',
                        "url": '../php/serverSideClientInventory1_join.php',
                        
                        
                        
                        "data": {
                            
                            "clientid": '<?php echo $ID ?>',
                            
                            
                        },
                
                       },
           
        columns: [ 

                        { data: "location.locname", target: 0 },
                        { data: "product_name.pdfprodname", target: 1 },
                        { data: "product_name.pdfmanufacturer", target: 2 },                        

            { data: "pdflocation.prodidentifier", target: 3 },
                        { data: "pdflocation.prodtype", target: 4 },
                        { data: "pdflocation.ref3", target: 5 },
                        { data: "pdflocation.ref4", target: 6 },
                        { data: "product_name.pdfexpiredate", target: 7 },
                        { data: "product_name.pdfcontrolled", target: 8 }

                        

        ],                   
                
                                
                
                "columnDefs": [ 
                
// This block sets up the links in the Product Name column.
                
                {
                "targets": [ 1 ], // Column to target
                "render": function ( data, type, full ) {
                 // 'full' is the row's data object, and 'data' is this column's data
                 // e.g. 'full[0]' is the comic id, and 'data' is the comic title
                 return '<a href="Http://www.paulandkirk.com/Debolt/data/PDFFiles/'+ full.product_name.pdffilename +'">'+ data +'</a>';
//                 return '<a href="Http://localhost/PhpTut/data/PDFFiles/'+ full.product_name.pdffilename +' ">'+ data +'</a>';
               }
             },
             
             
// Set up a style element on columns 3,4,5,6,and 7             
             
             {
                "targets": [3, 4, 5, 6], 
                
                "createdCell": function (td, cellData, rowData, row, col) {
                    
                        $(td).css('text-align', 'center');
                    
                }
    
             },
             
             {
                 "targets": [7],
                 
                 "data": "product_name.pdfexpiredate",
                
                 "render": function (data, type, row){
                     
                     if (data === "9999-12-31" && type === 'display' || type === 'filter){
                         
                      return "OBSOLETE";   
                         
                     }
                     
                         
                       return data;
                         
                       },
            
             },
             
             
             
             
            {
                "targets": [7], 
                
                "createdCell": function (td, cellData, rowData, row, col) {
                    
                    if ( cellData === "9999-12-31") {
                        
                        $(td).css('text-align', 'center');
                        $(td).css('color', 'red');
                        $(td).css('font-weight', 'bold');
                        
                     }   

                    
                }
    
            },

             
             
// Further define styles on column 8.             
             
             {
                "targets": [8], 

                    
               "createdCell": function (td, cellData, rowData, row, col) {
                
                       if ( cellData === "Y") {
                            
                          
                       $(td).css('color', 'red');
                       $(td).css('font-weight', 'bold');
                       $(td).css('text-align', 'center');
                       
                    }
                    
                       if ( cellData === "N"){
                           
                          $(td).css('color', 'green'); 
                          $(td).css('font-weight', 'bold');
                          $(td).css('text-align', 'center');
                    }
                    
                     if ( cellData === "?"){
                         
                          $(td).css('color', 'black'); 
                          $(td).css('font-weight', 'bold');
                          $(td).css('text-align', 'center'); 
                    }
                }   
                      

                }
             
           ],
                
               
    } );     


        
        
        
       // Apply the search (to apply to footer use "table.column( colIdx ).footer()")
                                
                                
    table.columns().eq( 0 ).each( function ( colIdx ) {
        $( 'input', table.column( colIdx ).footer() ).on( 'keyup change', function () {
            table
                .column( colIdx )
                .search( this.value )
                .draw();
        } );
    } );
        
        $('#ClientInventoryJoin').css('display');
        table.columns.adjust().draw();
        
        
        
} );

allan

if (data === "9999-12-31" && type === 'display' || type === 'filter){

Could you try:

if (data === "9999-12-31" && (type === 'display' || type === 'filter') ){

If that doesn't work, could you give me a link to the page or a debug trace?

Thanks,
Allan

cachiap

Allan,

I tried the change you suggested. But it did not resolve the problem.

{

"targets": [7],

"data": "product_name.pdfexpiredate",

"render": function (data, type, row) {

if ( data === "9999-12-31" && ( type === 'display' || type === 'filter')){

            return "OBSOLETE";

            }

            return data;

            }

  },

Here is my debug code: erajij

In addition here is a link to the active site as you requested:

http://www.paulandkirk.com/Debolt/data/ClientPages/DataTables/DataTables-1.10.7/extensions/Editor-PHP-1.4.2/examples/inline-editing/ClientInventory1_join.php

I have set this up so it opens to the Demonstration Company. There are several OBSOLETE records here as well as other active records. The global search works on the active dates but fails to find OBSOLETE records. Note that there are 13 "null" values at the start of this data set but if you scroll down you will find the rest of the data.

Thanks for looking at this.

Paul

allan

Hi Paul,

"serverSide": true,

That's the bit of information I was missing - thanks for the link and debug code.

The suggestions I've given above are all client-side, but since you are using server-side processing the filtering is of course being done at the server-side. You would need to modify whatever server-side script you are using to perform the filtering you want - possibly some nasty SQL statement :-).

Alternatively, use client-side processing - server-side should only be needed for tens of thousands or more of records.

Allan

cachiap

Allan,

Sorry for the delay in answering your last comment. You are right as usual. I made a change to the SQL and the search is working fine now :-)
Thanks again for your great advice.

Cheers,

Paul