Left join not refreshing the update/create on datatables

ehout

Hi Allan,

The Editor extension is working great. Thanks for the great plug-in again. But one issue is there which we are struggling with for sometime now. We have a datatable implemented with Editor on one of our pages. In the pop-up for editor, we have 1 field/column which is coming from the left join. We have used the left join example from the datatables that you have provided. Everything works fine and we are able to update the back-end. But when the editor pop-up closes after update, all fields (that are coming from the main table) get refreshed in the datatables grid, except for this left join field (it shows blank). The editor popup reflects the correct value for the field from the left join table. When you reload the page, all the values show fine. We don't think that we have to use a callback to redraw it as it should come automatically, but if have to, please let us know.

Note: We have done this in Java/jsp.

The following is the code we have used to get this done. We have 3 Java objects which we are converting to get the JSON in the same format that you indicated in the example.

The Ajax load data(JSON) is as shown below:

{"data":
[
{"DT_RowId":"1767",
"fundProg":
{"active":"N",
"controlValue":"0",
"fundResource":"045",
"colinProgDesc":"PERS RECAPTURE",
"functionalArea":"0181",
"sapProgName":"",
"colinProgName":"PERS RECAPTURE",
"fundGroup1Id":"1"
},
"fundGrp1":
{"fund_source_grp_1":"BB Bond"}
},
{"DT_RowId":"1984",
"fundProg":
{"active":"N",
"controlValue":"0",
"fundResource":"045",
"colinProgDesc":"SAL-PROJ RELATED-CO-EXIST FAC",
"functionalArea":"0561",
"sapProgName":"",
"colinProgName":"SAL-PROJ RELATED-CO-EXIST FAC",
"fundGroup1Id":"1"
},
"fundGrp1":
{"fund_source_grp_1":"BB Bond"}
}]
,
"fundGrp1":
[
{"value":"1","label":"BB Bond"},
{"value":"2","label":"COPs"},
{"value":"3","label":"CRA Fund"},
{"value":"4","label":"Community Redevlopment Agency"},
{"value":"5","label":"Deferred Maintenance"},
{"value":"6","label":"Developer Fees"},
{"value":"7","label":"FEMA"},
{"value":"8","label":"FEMA Fund"},
{"value":"9","label":"General Fund"},
{"value":"10","label":"General Fund"},
{"value":"11","label":"General Fund - RRGM"},
{"value":"12","label":"General Fund - RRGM"},
{"value":"13","label":"Labor Compliance Penalties"},
{"value":"14","label":"Measure K"},
{"value":"15","label":"Measure Q"},
{"value":"16","label":"Measure R"},
{"value":"17","label":"Measure Y"},
{"value":"18","label":"Other Funds"},
{"value":"19","label":"Other State Funds"},
{"value":"20","label":"Special Reserve Fund"},
{"value":"21","label":"State Bond"},
{"value":"22","label":"Test"}
]
}

And the code for the editor and the datatables is as follows:

editor = new $.fn.dataTable.Editor( {
    "ajax": "loadFundSources.jsp",
    "table": "#FundSourceList",
    "fields": [ 
        {
            "label": "Functional Area:",
            "name": "fundProg.functionalArea",
            "fieldInfo": "Format: xxxx-xxxx-xxxxx",
            def: "0000-8500-"
        }, {
            "label": "Fund Resource:",
            "name": "fundProg.fundResource",
            "fieldInfo": "Format: xxx-xxxx"
        }, {
            "label": "SAP Prog Name:",
            "name": "fundProg.sapProgName"
        }, {
            "label": "COLIN Prog Name:",
            "name": "fundProg.colinProgName"
        }, {
            "label": "COLIN Prog Description:",
            "name": "fundProg.colinProgDesc"
        }, {
            "label": "Fund Group 1:",
            "name": "fundProg.fundGroup1Id",
            type:  "select"

        }, 
        {
            "label": "Control Value:",
            "name": "fundProg.controlValue",
            def: 0
        }, {
            "label": "Active in SAP:",
            "name": "fundProg.active",
            "fieldInfo": "Enter Y for Yes or N for No",
            def: "Y"
        }
    ],
    callback : function(sValue, x) {
        //
        oTable.fnDraw();
        oTable.fnReloadAjax();
    }
} );

// New record
 editor.on( 'preSubmit', function ( e, o, a ) {
    if ( o.data.fundProg.active === '' ) {
        this.error('fundProg.active', 'Please enter Y or N for Active in SAP');
        return false;
    }
    else if ( o.data.fundProg.controlValue === '' ) {
        this.error('fundProg.controlValue', 'Please enter a value for the control number');
        return false;
    }
    else if (( o.data.fundProg.functionalArea.length != 15 ) && (a === "create")) {
        this.error('fundProg.functionalArea', 'The functional area length must be 15 characters');
        return false;
    }
    else if (( o.data.fundProg.fundResource.length != 8 ) && (a === "create")) {
        this.error('fundProg.fundResource', 'The SAP fund resource length must be 8 characters');
        return false;
    }
} );
 editor.on('postSubmit', function ( e, json, o, a) {
        // Populate the site select list with the data available in the
        // database on load
        //editor.field( 'fundProg.fundGroup1Id' ).update( json.fundGrpList);        
        $('#FundSourceList').dataTable();
} );
$('button.new').on( 'click', function () {
    editor
        .title( 'Create New Record' )
        .buttons( { "label": "Add", "fn": function () { editor.submit() } } )
        .create();
} );

// Edit record
$('#FundSourceList').on('click', 'a.editor_edit', function (e) {
    e.preventDefault();

    editor
        .title( 'Edit record' )
        .buttons( { "label": "Update", "fn": function () { editor.submit() } } )
        .edit( $(this).closest('tr') );
} );

var oTable = $('#FundSourceList').dataTable( {
stateSave: true,
"bRetrieve":true,
"processing": true,
"ajax": "loadFundSources.jsp",
"columns": [
{ data: "fundProg.functionalArea" },
{ data: "fundProg.fundResource" },
{ data: "fundProg.sapProgName" },
{ data: "fundProg.colinProgName" },
{ data: "fundProg.colinProgDesc" },
{ data: "fundGrp1.show" },
{ data: "fundProg.controlValue", render: $.fn.dataTable.render.number( ',', '$' ) },
{ data: "fundProg.active" },
{ data: null, "bSortable": false,
defaultContent: '<a href="" class="editor_edit"><img src = "../../include/images/Edit.ico" width="16px" height="16px"></a>'}
],
"deferRender": false,
"pagingType": "full_numbers",
"pageLength": 10,
scrollCollapse: true,
"scrollY" : 580,
"language": {
"search": "Filter: "
},
"lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ],
"sDom": 'T<"clear">lfrtip',
"tableTools": {
"aButtons": [
"copy",
"print",
{
"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]
}
],
"sSwfPath": "../../include/dataTables/DataTables-1.10.2/extensions/TableTools/swf/copy_csv_xls_pdf.swf"
}
,
initComplete: function ( settings, json ) {
// Populate the site select list with the data available in the
// database on load
editor.field( 'fundProg.fundGroup1Id' ).update( json.fundGrp1);
}
} );

Could you please help us figure it out .

Thanks,
Eric.

ehout

Nevermind, figured it out. The key is to return one data set from the server rather than the whole list of records and specify "row" in the format of the JSON in the server response (it needs to match the format in the Ajax data section)