DataTables Inline Editor - Select Options not saving editField to table

jaredgerber

I'm so close!!! I almost have this functioning 100%, lol. I have 3 days left on my trial.

I'm implementing an inline editor with a field with type=select for the "Policy Type" field with a simple list of options.

Everything about it appears to be working fine .... except that it is not saving back to the table after the selection has been submitted.

Link to application

Link to data

My code:

var editor; // use a global for the submit and return data rendering in the examples        
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: window.location.pathname,
        table: "#commissionschedule",
        bProcessing: true,
        bServerSide: true,
        fields: [ 
            {
                label: "PlanID:",
                name: "EID"
            }, {
                label: "Commission Type:",
                name: "CommissionTypeID"
            }, {
                label: "Policy Type:",
                name: "PolicyTypeName",
                editField: "PolicyTypeID",
                type: "select",
                ipOpts: [{ "label": "1-Life", "value": "1" },
                           { "label": "2-Annuity", "value": "2"}]           ,
            }, {
                label: "Commission%:",
                name: "CommPct"
            }, {
                label: "Target%:",
                name: "Target"
        }
        ]
    } );    

// Activate an inline edit on click of a table cell
    $('#commissionschedule').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            buttons: { label: '>', fn: function () { this.submit(); } }
        } );
    } );

    $('#commissionschedule').DataTable( {
        dom: "Tfrtip",
        ajax: "php/processListObj.php?list=commissionschedule&has_form=false",
        bProcessing: true,
        bServerSide: true,
        "pageLength": 25,
        "lengthMenu": [25, 50, 75, 100 ],
        columns: [
            { data: null, defaultContent: '', orderable: false },
            { data: "EID" },
            { data: "CommissionTypeID" },
            { data: "PolicyTypeName", editField: "PolicyTypeID" },
            { data: "CommPct" },
            { data: "Target" }
        ],
        order: [ 1, 'asc' ],
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        }
    } );
} );        

My server script:

<?php
    Editor::inst( $db, 'qrycommissionschedule', 'CommissionScheduleID' )
        ->fields(
            Field::inst( 'EID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'CommissionTypeID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'PolicyTypeID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'CommPct' )->validator( 'Validate::numeric' ),
            Field::inst( 'Target' )->validator( 'Validate::numeric' )
        )
        ->process( $_POST )
        ->json();

allan

Hi,

Looks close!

Editor is configured with:

name: "PolicyTypeName",

However, your PHP has:

Field::inst( 'PolicyTypeID' )

I think to fix all you need to do is have the two match. Change the Editor field descriptor to be:

{
                label: "Policy Type:",
                name: "PolicyTypeId",
                type: "select",
                ipOpts: [{ "label": "1-Life", "value": "1" },
                           { "label": "2-Annuity", "value": "2"}]           ,
            }

Note that editField is only used in the DataTables columns and columnDefs objects. It has no meaning in the Editor field descriptors.

Allan

jaredgerber

I did that, but now my dropdown doesn't work on the inline row anymore :-(

This is the error I am seeing when I click on the Policy Type data item:

 "Uncaught Unable to automatically determine field from source. Please specify the field name"

allan

Which version of Editor are you using? The editField option you are using in the DataTables columns (which is the right thing to do) is only available in 1.4+ (1.4 is currently in beta).

Allan

jaredgerber

It looks like 1.3.3 -- I downloaded it a week and a half ago -- the latest trial.

http://174.120.222.66/~opes/res/DataTables/js/dataTables.editor.min.js

Actually, if you click on the New or Edit, the dropdown works fine -- it even saves the data. But when I try to get it to work inline, it doesn't work.

Should I be working with the beta version?

This is the filename that I downloaded: DataTables-1.10.4-trial.zip

Once I get this running successfully, I'm going to convert our other report/forms with the DataTables Editor.

jaredgerber

Ok -- I updated to the Editor 1.4 and now the select (dropdown) works inline, but now it is not saving data inline or in the popup editor.

Actually, now, none of the columns are saving to the table. I may have to roll back -- unless you see an issue in the code.

    editor = new $.fn.dataTable.Editor( {
...
{
                label: "Policy Type:",
                name: "PolicyTypeID",
                type: "select",
                ipOpts: [{ "label": "1-Life",    "value": "1" },
                        { "label": "2-Annuity", "value": "2"}],
                "default": 1
}

        columns: [
            { data: null, defaultContent: '', orderable: false },
            { data: "EID" },
            { data: "CommissionTypeID" },
            { data: "PolicyTypeName", editField: "PolicyTypeID" },
            { data: "CommPct" },
            { data: "Target" }
        ],

    Editor::inst( $db, 'qrycommissionschedule', 'CommissionScheduleID' )
        ->fields(
            Field::inst( 'EID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'CommissionTypeID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'PolicyTypeID' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'CommPct' )->validator( 'Validate::numeric' ),
            Field::inst( 'Target' )->validator( 'Validate::numeric' )
        )
        ->process( $_POST )
        ->json();

allan

Interesting. I'm Afraid I don't immediately see anything wrong there. However, when I check the link above and make an Ajax request I get:

{"sError":"An error occurred while connecting to the database ''. The error reported by the server was: SQLSTATE[28000] [1045] Access denied for user ...... (using password: NO)"}

Have you setup the config.php file with the required database username and password?

Allan

jaredgerber

Oh -- wow, now everything works :-) There are so many factors, but it's all seeping into my brain -- slowly. I forgot to update the config.php when I upgraded the Editor to 1.4.

I am now much more confident in implementing this system-wide -- very excited!!

Thank you for your time.

stevehart

When I use the same approach/solution as derived above (using the editField property for the column data), in my case, I get an error popup from DataTables saying

Requested unknown parameter 'GroupName" for row 0.

My table is configured as follows:

$(document).ready(function () {
    editor = new $.fn.dataTable.Editor({
        ajax: '/api/users',
        table: "#users",
        fields: [
            {
                label: "Username",
                name: "Username"
            },
            {
                label: "Group",
                name: "GroupId",
                type: "select",
                ipOpts: [
                    { label: "Administrators", value: "1" },
                    { label: "Teachers", value: "2" },
                    { label: "Students", value: "3" }
                ],
            }
        ]
    });

    $('#users').DataTable({
        dom: "Tfrtip",
        ajax: "/api/users",
        columns: [
            { data: "Username" },
            { data: "GroupName", editField: "GroupId" }
        ]
    });
});

The server is returning an array of data as follows:

[
    { Username: 'Steve', GroupId: '1'}.
    { Username: 'Keith', GroupId: '3'}.
    { Username: 'George', GroupId: '1'},
    { Username: 'Mary', GroupId: '2'}
]

For my table I'm using exactly the same approach, using a '''select''' field type with a label and value, using the editField property for the column and am getting the error popup mentioned above. What am I missing?

In the 'PolicyTypeID' / 'PolicyTypeName' example above, I do not see that 'PolicyTypeName' is in the data being returned from the server?

In my example, when I do return 'GroupName' from the server, I don't get the error popup, the group name is displayed, but when I use inline editing to update a row, 'GroupName' is null when posted to the server and therefore the row response back to the client contains a null GroupName which blanks out the group name in the table.

jaredgerber

I posted a link to my data source above -- so you can see it live. But here is a snippet of my data source. hope it helps.

Jared

{"draw":0,"recordsTotal":244,"recordsFiltered":244,"data":[{"CommissionScheduleID":"1","DT_RowId":"row_1","EID":"2","CommissionTypeID":"2","PolicyTypeID":"1","PolicyTypeName":"Life","CommPct":"1.15","Target":"0.55"},
{"CommissionScheduleID":"2","DT_RowId":"row_2","EID":"3","CommissionTypeID":"1","PolicyTypeID":"1","PolicyTypeName":"Life","CommPct":"1.1","Target":"0.53"},
{"CommissionScheduleID":"3","DT_RowId":"row_3","EID":"66","CommissionTypeID":"1","PolicyTypeID":"1","PolicyTypeName":"Life","CommPct":"1","Target":"0.51"},
{"CommissionScheduleID":"4","DT_RowId":"row_4","EID":"75","CommissionTypeID":"1","PolicyTypeID":"1","PolicyTypeName":"Life","CommPct":"1.15","Target":"0.55"},

...

allan

@stevehart:

Requested unknown parameter 'GroupName" for row 0.

The data you show doesn't have a GroupName parameter which is why DataTables is saying it can't read it! You need to include it and the GroupId. The editField option is the correct one to use as you have it, but you need to be using Editor 1.4 (which is currently in beta) for editField to work.

Allan

stevehart

Thanks for the data source Jared. And thanks for your response Allan.

I am using Editor 1.4. I've included GroupName in the data and 'Requested unknown parameter' is no longer displayed.

Now I'm getting a problem where the 'select' DataTable cell is being blanked out after an inline edit is saved to the server. When I edit inline, and pick an option from a 'select' dropdown, it goes to the server and is successfully saved. The server returns the 'row' structure with the updated data. However, when the row is updated in the client on the DataTable, the GroupName cell is blanked out.

It seems that DataTable or the Editor is not refreshing the cell to updated the 'selected' state of the option. The DataTable column GroupName (with editField GroupId) is not having the GroupName refreshed with the newly selected option label. What I've had to do is cache on the client side the list of option pairs (e.g. var cachedGroups[GroupId] = GroupName ...) indexed by GroupId, and use the render property of the DataTable column GroupName to set the data to be cachedGroups[data].

I'm probably missing something simple.

allan

Can you link me to the page you are working on? I think I might be missing something simple as well :-)

Allan

munnsj

I'm having something very similar I think to stevehart's problem. I'm hoping I may be able to provide something useful. When I inline edit the select field, the post submits the correct headers. When I edit another field, all of a sudden the select field is null.

What may help is I also am using some code posted by Allan to only submit when a field changes. In initEdit, editor.get() returns null for the select field though the others are correct, I will also note the "data" parameter for initEdit displays the label for the select field.

In preSubmit however, both the "data" parameter and using editor.get() return null for my select field.

This is with the beta 1.4 editor.

For example where this may be helpful, the "lob" field is my select field that is being nulled when I edit anything other than the lob field.

editor = new $.fn.dataTable.Editor({
        ajax : "./LobYearDetailServlet",
        table : "#example",
        idSrc : "rowID",
        fields : [ {
            label : "Description:",
            name : "description",
            type : "selectize",
            options : [  { "label" : "Fixed Fee", "value" : "Fixed Fee" },
                        { "label" : "OOP Expense Amount", "value" : "OOP Expense Amount" },
                        { "label" : "Intellectual Capital", "value" : "Intellectual Capital" },
                        { "label" : "Other Project and Product Revenue", "value" : "Other Project and Product Revenue" },
                        { "label" : "Non-Billable Expenses", "value" : "NonBillable Expenses" }
                     ]       
        }, {
            label : "Year",
            name : "year",
            type : "selectize",
            options : [  { "label" : "1", "value" : "1" },
                        { "label" : "2", "value" : "2" },
                        { "label" : "3", "value" : "3" },
                        { "label" : "4", "value" : "4" },
                        { "label" : "5", "value" : "5" },
                        { "label" : "6", "value" : "6" },
                        { "label" : "7", "value" : "7" },
                        { "label" : "8", "value" : "8" },
                        { "label" : "9", "value" : "9" },
                        { "label" : "10", "value" : "10" }
                     ]
        }, {
            label : "LOB",
            name : "lob",
            type : "selectize",
            "options" : getLobList(),
            "opts" : {
                placeholder : 'Enter a search',
                labelField : 'label',
                searchField : 'label'
             }
        }, {
            label : "Amount",
            name : "amount"
        }, {
            label : "ParentQuote",
            name : "parentQuote",
            type : "hidden"
        }]
    });
    
    editor
    .on( 'open', function ( e, type ) {
        if ( type === 'inline' ) {
            // Listen for a tab key event when inline editing
            $(document).on( 'keydown.editor', function ( e ) {
                if ( e.keyCode === 9 ) {
                    e.preventDefault();

                    // Find the cell that is currently being edited
                    var cell = $('div.DTE').parent();

                    if ( e.shiftKey && cell.prev().length && cell.prev().index() !== 0 ) {
                        // One cell to the left (skipping the first column)
                        cell.prev().click();
                    }
                    else if ( e.shiftKey ) {
                        // Up to the previous row
                        cell.parent().prev().children().last(0).click();
                    }
                    else if ( cell.next().length ) {
                        // One cell to the right
                        cell.next().click();
                    }
                    else {
                        // Down to the next row
                        cell.parent().next().children().eq(1).click();
                    }
                }
            } );
        }
    } )
    .on( 'close', function () {
        $(document).off( 'keydown.editor' );
    } )
    .on ('initEdit', function (e, node, data) {
       rowData = JSON.stringify( editor.get() );
        //rowData = JSON.stringify(data);
    })
    .on ('preSubmit', function (e, data, action) {
        var x = editor.get();
       if ( rowData === JSON.stringify( editor.get() ) ) 
        { 
           editor.close();
           return false;
        }
    });

    // Activate an inline edit on click of a table cell
    $('#example').on('click', 'tbody td:not(:first-child)', function(e) {
        editor.inline( this, {
            submitOnBlur: true
        } );
    });
    
    $('#example').DataTable({
        dom : "Tfrtlip",
        ajax : "./LobYearDetailServlet?method=getLobYearDetails&quote=" + quote,
        "lengthMenu": [[25, 50, -1], [25, 50, "All"]],
        columns : [ 
        { 
            data: null, 
            defaultContent: '',
            orderable: false 
        }, 
        {
            data : "description",
            editField: 'description'
        }, {
            data : "year"
        }, {
            data : "lob",
            editField: 'lob'
        }, {
            data : "amount"
        }],
        tableTools : {
            sRowSelect : "os",
            aButtons : [ {
                sExtends : 'select_single',
                sButtonClass : 'marginLeft',
                sButtonText : 'Add Row',
                fnClick : function() {
                    var table = $('#example').DataTable();
                    table.row.add({
                          "description": "",
                          "year": 1,
                          "lob": "0",
                          "amount": 0,
                          "rowID": "0",
                          "parentQuote" : quote
                        }).draw();
                    //editor.create(true, {}).submit();
                }
            },
            {
                sExtends : "editor_remove",
                editor : editor
            }]
        }
    });

munnsj

Allan any guidance here? Should I open up a new discussion to continue this?

allan

Hi,

Sorry I missed your post. Can you link me to your page, as I asked stevehart above please? That will hoepfully let me debug it directly and discover what is going wrong.

Allan

munnsj

They are internal applications to our company, I tried to include the code where I could. Is there guidance on recreating them in a sandbox?

allan

You could use http://live.datatables.net to do so. or even just to dump your code into if that might help.

Allan