DataTables Editor Table not showing Select Labels but Value instead

teuber consult + IT GmbH

Hello volks,

i've just got the editor licence and i'm struggeling to get my table showing the information i want it to.
The first screenshot shows my current table, second shows the edit select field.

I want to have the label of the selected value from the DB in the table by default not the value.
I do also have a readonly field which has options defined which is also only showing the value not label.

**Status **should display the label of the current status in the table (shows int) and select on edit (this works)
**Type **should display the label and does not need to be edited

here is my js:

var editor; // use a global for the submit and return data rendering in the examples
var table; // use global for table

$(document).ready(function() {

    editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'libs/basic/datatables/orderposition.php',
            data: {
                "collectiveinvoice": 141
            }
        },
        table: "#datatable",
        fields: [
            {
                label: "Status:",
                name: "status",
                type: "select"
            },
            {
                label: "Beschreibung:",
                name: "comment",
                type: "ckeditor",
                opts: {
                    toolbarGroups: [
                        { name: 'forms', groups: [ 'forms' ] },
                        { name: 'editing', groups: [ 'find', 'selection', 'spellchecker', 'editing' ] },
                        { name: 'document', groups: [ 'mode', 'document', 'doctools' ] },
                        { name: 'clipboard', groups: [ 'clipboard', 'undo' ] },
                        { name: 'basicstyles', groups: [ 'basicstyles', 'cleanup' ] },
                        { name: 'paragraph', groups: [ 'list', 'indent', 'blocks', 'align', 'bidi', 'paragraph' ] },
                        { name: 'links', groups: [ 'links' ] },
                        { name: 'insert', groups: [ 'insert' ] },
                        { name: 'styles', groups: [ 'styles' ] },
                        { name: 'colors', groups: [ 'colors' ] },
                        { name: 'tools', groups: [ 'tools' ] },
                        { name: 'others', groups: [ 'others' ] },
                        { name: 'about', groups: [ 'about' ] }
                    ],
                    removeButtons: 'Source,Save,Templates,NewPage,Preview,Print,Cut,Copy,Paste,PasteText,PasteFromWord,Find,SelectAll,Scayt,Replace,Form,Checkbox,TextField,Radio,Textarea,Select,Button,ImageButton,HiddenField,Subscript,Superscript,CreateDiv,BidiLtr,BidiRtl,Language,Anchor,Image,Flash,Smiley,SpecialChar,Iframe,Font,About,Maximize'
                }
            }, {
                label: "Menge:",
                name: "quantity"
            }, {
                label: "Preis:",
                name: "price"
            }, {
                label: "Steuer:",
                name: "tax",
                type: "select"
            }
        ]
    } );
    // Disable KeyTable while the main editing form is open
    editor
        .on( 'open', function () {
            table.keys.disable();
        } )
        .on( 'close', function () {
            table.keys.enable();
        } );

    // Activate an inline edit on click of a table cell
    $('#datatable').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            // Submit on leaving field
            onBlur: 'submit'
        } );
    } );


    table = $('#datatable').DataTable( {
        dom: "<'row'<'col-sm-4'l><'col-sm-4'B><'col-sm-4'f>><'row'<'col-sm-12'tr>><'row'<'col-sm-5'i><'col-sm-7'p>>",
        ajax: {
            url: 'libs/basic/datatables/orderposition.php',
            data: {
                "collectiveinvoice": 141
            }
        },
        order: [[ 1, 'asc' ]],
        columns: [
            { data: "id" },
            { data: "type" },
            { data: "status" },
            { data: "comment" },
            { data: "quantity" },
            { data: "price" },
            { data: "tax" }
        ],
        select: false,
        buttons: [
            // Export Button
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            },
        ]
    } );
} );

here is server side php:

Editor::inst( $db, 'collectiveinvoice_orderposition' )
    ->where( 'status', 0, '>' )
    ->where( 'collectiveinvoice', $_REQUEST['collectiveinvoice'] )
    ->fields(
        Field::inst( 'id' )->set(false)->validator( 'Validate::unique' )->validator( 'Validate::numeric' ),
        Field::inst( 'status' )->options( function () {
            return array(
                array( 'value' => '0', 'label' => 'gelöscht' ),
                array( 'value' => '1', 'label' => 'aktiv' ),
                array( 'value' => '2', 'label' => 'deaktiviert' ),
            );
        }),
        Field::inst( 'quantity' )
            ->validator( 'Validate::numeric' )
            ->getFormatter( 'Format::toDecimalChar' )
            ->setFormatter( 'Format::fromDecimalChar' ),
        Field::inst( 'price' )
            ->validator( 'Validate::numeric' )
            ->getFormatter( 'Format::toDecimalChar' )
            ->setFormatter( 'Format::fromDecimalChar' ),
        Field::inst( 'tax' )->options( Options::inst()
            ->table( 'taxkeys' )
            ->value( 'value' )
            ->label( 'value' )
        ),
        Field::inst( 'comment' ),
        Field::inst( 'type' )->set(false)->options( function () {
            return array(
                array( 'value' => '0', 'label' => 'Manuell' ),
                array( 'value' => '1', 'label' => 'Artikel (Kalk)' ),
                array( 'value' => '2', 'label' => 'Artikel' ),
                array( 'value' => '3', 'label' => 'Perso' ),
            );
        }),
        Field::inst( 'file_attach' )->set(false),
        Field::inst( 'perso_order' )->set(false)
    )
    ->process( $_POST )
    ->json();

In addition, i want to display buttons based on the DB values of "file_attach" and "perso_order" so that they only show and have a reference in them to the db field, how would one do that? Or in other words how do i add a custom field to the table anyways (which is readonly)?

Thanks for this great tool!

Regards

teuber consult + IT GmbH

almost forgot, i do also need to have the quantity field sometimes be input and othertimes be select, is that possible maybe with a custom function server side on a per row basis?

teuber consult + IT GmbH

i've just tried using the getFormatter combined with a switch to change the value in the table display, but that screws up the "selected" element in the select on edit:

            ->getFormatter( function ( $val, $data, $opts ) {
                switch($val){
                    case 0:
                        return 'gelöscht';
                    case 1:
                        return 'aktiv';
                    case 2:
                        return 'deaktiviert';
                }
            } )

i guess it's because DT compares the current cell value to the value of the select options and preselects the current one?

allan

Hi,

Thanks for your question and the code. The getFormatter method is the way I think would be best to go here. The problem you are running into is the fact that the label information isn't actually in the data that the DataTable sees for each row, only the value is. So the getFormatter would allow the data to be transformed to make it visible.

You say that messes up the select though. But I don't fully understand how that can be since that field is not editable. Its shown in your DataTable, but not in the Editor form, so I don't really understand why it would be a problem in the Editor form. I think I'm missing something!

Regards,
Allan

teuber consult + IT GmbH

Hey Allan,

please compare sshot-145.png and sshot-146.png
you will see that the table does show the integer values and the editor gets the currently selected element preselected.
once i switch the value shown in the table from the integer to a label, the editor is no longer preselecting the current selected value instead showing a select which nothing preselected - instead the first element of the option list is selected

what about the custom field and the quantity field type based on condition questions from above?

Regards,
Alex

allan

Hi Alex,

Could you give me a link to the page showing the issue so I can debug it please? It sounds like there is something going wrong between the labels and values, but I'm not entirely certain want and I'd need to debug the page to discover what it is.

In addition, i want to display buttons based on the DB values of "file_attach" and "perso_order" so that they only show and have a reference in them to the db field, how would one do that? Or in other words how do i add a custom field to the table anyways (which is readonly)?

I missed this part - sorry. You can use the columns.render option to default a custom renderer for data in a column. The renderers section of the manual describes how they can be used.

Allan