Search in Editor - any "null" value in column returns Unknown field: (index 1) and no returned json

asle

When I use data:null in a column I can display the table but when I filter the table the search returns this error "DataTables warning: table id=my_table value in column returns Unknown field: (index 1)". No json is returned. I get no error in PHP and nothing in network tab in Chrome developer. So I am lost where to find out what is wrong.

If I change the null value for column 1 get the same error but then on (index 2). I have to completely remove all "null" values to get rid of the unknown field error.

I found this post with the same error but moving the column with null (like in the post) does not help. I have to completely remove all traces of null. Here are the columns with null values and render function:

columns: [
            { data: "garantinr"}, 
            { data: null, render: function ( data, type, row ) {
                if (data.ordrenummer == null) {return ''} else {
                return '<a href="https://bri.no/sporing/' + 
                data.ordrenummer + '" target="–blank">' + 
                data.ordrenummer + '</a>'; 
                }
            } }, 
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.kunde+' '+data.kunde_enavn;
            } },
            { data: "kunde_adr"},
            { data: "kunde_postnr"},

Here is my server script:

<?php
// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions; 

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'fes.garantibasen' )
    ->fields( 
        Field::inst( 'id' ),
        Field::inst( 'garantinr' ),
        Field::inst( 'ordrenummer' ),
        Field::inst( 'kunde' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'Skriv inn fornavn' )  
            ) ),
        Field::inst( 'kunde_enavn' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'Skriv inn etternavn' )    
            ) ),
        Field::inst( 'kunde_adr' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'Skriv inn adresse' )  
            ) ),
        Field::inst( 'kunde_postnr' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( 'kunde_sted' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'Skriv inn sted' ) 
            ) ),
        Field::inst( 'kunde_tel' ),
        Field::inst( 'pumpemod' ),
        Field::inst( 'forhandler' ),
        Field::inst( 'ekstern_kom' ),
        Field::inst( 'ekstern_reg' ),
        Field::inst( 'mont_id' ),
        Field::inst( 'montdato' ),
        Field::inst( 'testrun' ),
        Field::inst( 'trykktest_nitro' ),
        Field::inst( 'garanti_ok' ),
        Field::inst( 'reg_dato' )
    )

    ->where( 'mont_id', $_POST['mont_id'])
    ->where( 'ekstern_reg', 1)
    ->where( 'garanti_ok', 1, '!=') 
} )
    ->process( $_POST )
    ->debug(true)
    ->json();

asle

An update ## From this post Allan wrote that > "The issue is that you are using server-side processing, but column index 7 is a client-side generated column, so the server-side knows nowthing about it."
So my server-side does not know about these fields? I am combining first+last name. I set this code and I get no error. But I need to have the 2 column searchable!

columnDefs: [
{ "orderable": false, "searchable": false, "targets": 1 },

Is there a way for format the fields in the server script for Editor? The first field can be empty and then is a null value. Why is that a problem?

update ## - so I understand that since I am using

serverSide: true,
then the server script is searching and not my client script. When I remove serverSide: true I do not get any errors and the search works fine.

But I need to use serverSide since I have so many records. How can I fix this and still use serverSide: true?

rf1234

Why is that a problem?

Using server side you are searching for database values on the server not rendered values on the client. Any client rendering is being ignored for the search. You might still get sufficiently reasonable search results but you mustn't use data: null any longer.

You can replace

{ data: null, render: function ( data, type, row ) {
                if (data.ordrenummer == null) {return ''} else {
                return '<a href="https://bri.no/sporing/' +
                data.ordrenummer + '" target="–blank">' +
                data.ordrenummer + '</a>';
                }
            } },

with

{ data: "ordrenummer", render: function ( data, type, row ) {
                if (data == null) {return ''} else {
                return '<a href="https://bri.no/sporing/' +
                data + '" target="–blank">' +
                data + '</a>';
                }
            } },

but your search might still not be accurate. The error will disappear though. Even if you use multiple database fields to render just one client side field this will work. Just avoid "data: null" and you will be fine regarding the error.

There are a couple of ways to fix the issue if your search isn't accurate enough after using the fix above:
- Get rid of server side
- Manipulate the search values sent to the server to match database content more accurately (cumbersome)
- Use complex views to emulate client rendering on the server (very cumbersome)

Good luck!

rf1234

Here is an example of search value manipulation sent to the server to make search work with server side. This is for dates and numbers in English (UK) and German (Germany) formats.

Database content: YYYY-MM-DD HH:MM etc.

User will rather enter something like this:
['D/M/YYYY', 'DD/MM/YYYY', 'D/M/YY', 'DD/MM/YY', 'D/M', 'DD/MM', 'D/M/', 'DD/MM/']

This is the "data" part of "ajax.data":

        data: function ( d ) {
    //allow searching for dates with server side processing
            var dFs = ['D/M/YYYY', 'DD/MM/YYYY', 'D/M/YY', 'DD/MM/YY', 'D/M', 'DD/MM', 'D/M/', 'DD/MM/'];
            var toBeFormat = ''; var sepCounter = 0;
    //No commas allowed as date separator; if English: no period either!
            if ( ( ! ( d.search.value.indexOf(',') >= 0 ) ) &&
                 ( ! ( lang !== 'de' && d.search.value.indexOf('.') >= 0 ) )    )   {
                if ( moment(d.search.value, dFs).isValid() ) {
                    toBeFormat = 'MM-DD';
                    for (i=0; i < d.search.value.length; i++) {
                        //counting the separators in the search string
                        if ( isNaN (d.search.value.substr(i, 1)) ) {
                            sepCounter++;
                    //if we find two separators and the second one is not at the
                    //end of the string we have a long date otherwise only a short one
                            if ( sepCounter === 2 && i < (d.search.value.length-1) ) {
                                toBeFormat = 'YYYY-MM-DD';
                            }
                        }                        
                    }
                    if (sepCounter > 0) { //we need to have found at least one separator
                        d.search.value = moment(d.search.value, dFs).format(toBeFormat);
                    } else {
                        toBeFormat = '';
                    }
                }
            }
    //not a date: we check for a number
            if (toBeFormat <= '') {
                var number;
                if (lang == 'de') {
                    number = d.search.value.toString().replace( /[\.]/g, "" );
                    number = d.search.value.toString().replace( /[\,]/g, "." );
                } else {
                    number = d.search.value.toString().replace( /[\,]/g, "" );
                }
                if ( ! isNaN( parseFloat(number) ) ) {             
                    d.search.value = number;
                }
            }
        }

asle

Thanks @rf1234 I am learning thanks to you guys. Here is how I solved the field where I combine first and last name. Note that "data.kunde" gave undefined while "row.kunde" works. What is the difference?

I removed null and wrote the name field like this:

{ data: "kunde", render: function ( data, type, row ) {
   // Combine the first and last names into a single table field
   return row.kunde+' '+row.kunde_enavn;
    } },

Then I add the last name field at the end of the field definitions:

{ data: "kunde_enavn"}

I hide this field but it is searchable. I am able to search on both first and last name!

{ visible: false, orderable: false, searchable: true, targets: 10 },

I still am working on your date example!

rf1234

Cool solution!

Note that "data.kunde" gave undefined while "row.kunde" works. What is the difference?

"row" are all fields read from the server, while "data" is just the current field read. Hence "data" doesn't work with "data: null" and it doesn't work as "data.kunde" either.