Cant get an id column
Cant get an id column
minobu
Posts: 23Questions: 8Answers: 2
All other columns work except for the id column. Column name is correct. Its an int column from mysql database, other int cols seem to work fine just not this one. I get this JS error then the rest of the cols load however the id is empty.
DataTables warning: table id=lease_units - Requested unknown parameter 'id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4
Code is posted below.
Editor::inst( $db, 'lease_units', 'id' )
->fields(
Field::inst( 'id' ),
Field::inst( 'site' ),
Field::inst( 'lease' ),
Field::inst( 'unit_type' ),
Field::inst( 'batch_id' ),
Field::inst( 'grade' ),
Field::inst( 'total_units' ),
Field::inst( 'extra1' ),
Field::inst( 'extra2' ),
Field::inst( 'attrition_oysters' ),
Field::inst( 'avg' ),
Field::inst( 'bio_mass' ),
Field::inst( 'date_arrived' )
->validator( Validate::dateFormat( 'd-m-y' ) )
->getFormatter( Format::dateSqlToFormat( 'd-m-y' ) )
->setFormatter( Format::dateFormatToSql( 'd-m-y' ) ),
Field::inst( 'fy_saleable' )
)
->process( $_POST )
->json();
<thead>
<tr>
<th>Id</th>
<th>Site</th>
<th>Lease</th>
<th>Unit</th>
<th>Batch</th>
<th>Grade</th>
<th>Units</th>
<th>Value</th>
<th>Oysters</th>
<th>Post</th>
<th>Density</th>
<th>Bio mass</th>
<th>Deployment</th>
<th>Harvest Fy</th>
</tr>
</thead>
<tfoot>
<tr>
<th style="text-align:center">Totals:</th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr>
</tfoot>
</table>
var table = $('#lease_units').DataTable( {
dom: 'fBQPrtip',
ajax: 'table.lease_units.php',
columns: [
{
data: "id" //0
},
{
data: "site" //1
},
{
"data": "lease" //2
},
{
"data": "unit_type"//3
},
{
"data": "batch_id"//4
},
{
"data": "grade"//5
},
{
"data": "total_units",//6
"type": "num-fmt"
},
{
"data": "extra1",//7
"type": "num-fmt"
},
{
"data": "extra2",//8
"type": "num-fmt"
},
{
"data": "attrition_oysters",//9
"type": "num-fmt"
},
{
"data": "avg",//10
"type": "num-fmt"
},
{
"data": "bio_mass",//11
"type": "num-fmt"
},
{
"data": "date_arrived"//12
},
{
"data": "fy_saleable"//13
},
],
select: true,
lengthChange: false,
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor },
{
extend: 'excelHtml5',
autoFilter: true,
footer: true,
filename: 'Deployed units_',
sheetName: 'Deployed units'
},
],
searchPanes: {
cascadePanes: true,
controls: true,
collapse: false,
layout: 'columns-1'
},
columnDefs:[
{
searchPanes:{
show: false,
},
targets: [0,6,7,8,9,10,11,12],
},
{
targets: [6,8,9,10],
render: $.fn.dataTable.render.number(',', '.', 0, '')
},
{
targets: [7],
render: $.fn.dataTable.render.number(',', '.', 2, '$')
},
{
targets: [11],
render: $.fn.dataTable.render.number(',', '.', 2, '')
},
],
scrollY:"60vh",
scrollX:1400,
scroller:true,
select: true,
fixedHeader: true,
"language": {
"search": "",
"clearMessage": "✖",
},
"order": [[0, 'desc']],
"footerCallback": function ( row, data, start, end, display ) {
var api = this.api(), data;
// Remove the formatting to get integer data for summation
var intVal = function ( i ) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '')*1 :
typeof i === 'number' ?
i : 0;
};
// Total over all pages TOTAL UNITS
total6 = api
.column( 6,{ search:'applied'} )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Total over all pages OYSTERS PRE
total7 = api
.column( 7,{ search:'applied'} )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Total over all pages OYSTERS POST
total8 = api
.column( 8,{ search:'applied'} )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Total over all pages
total9 = api
.column( 9,{ search:'applied'} )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Total over all pages
total11 = api
.column( 11,{ search:'applied'} )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Update footer
total6 = numeral(total6).format('0,0');
$( api.column( 6).footer() ).html(
''+ total6 +''
);
total7 = numeral(total7).format('0,0');
$( api.column( 7).footer() ).html(
''+ total7 +''
);
total8 = numeral(total8).format('0,0');
$( api.column( 8).footer() ).html(
''+ total8 +''
);
total9 = numeral(total9).format('0,0');
$( api.column( 9).footer() ).html(
''+ total9 +''
);
total11 = numeral(total11).format('0,0');
$( api.column( 11).footer() ).html(
''+ total11 +''
);
},
This question has an accepted answers - jump to answer
Answers
Did you follow the troubleshooting steps at the link in the error?
http://datatables.net/tn/4
The error suggests that Datatables can't find the
idobject in the returned rows. By default it will expect to find this structure:See the Ajax docs for details. Use the browser's network inspector tool to see the JSON response. If you still need help post a snippet of the JSON response so we can see your data structure.
Kevin
Thanks, Yes i did follow that guide, I spent quite a few hours trying to figure it out before coming here. JSON response is below. Not sure why its returning "DT_RowId":"row_17029" the 17029 is correct but not sure why its prefixing row_ to it or what DT_RowId is. column in the database is "id".
{"data":[{"DT_RowId":"row_17029","site":"Cromarty Bay","lease":"Cromarty Bay","unit_type":"8mm Tooltech","batch_id":"2021 Forster","grade":"25mm","total_units":"5","extra2":"2000","extra1":"1200","attrition_oysters":"1860","date_arrived":"22-06-23","avg":"400","bio_mass":"0","fy_saleable":"24"},{"DT_RowId":"row_16162","site":"Wallis Lakes","lease":"Sciaccas Paddock","unit_type":"Tray - Wooden 9ft","batch_id":"2020 Forster","grade":"20mm","total_units":"61","extra2":"48800","extra1":"24400","attrition_oysters":"45384","date_arrived":"28-04-23","avg":"800","bio_mass":"0","fy_saleable":"23"}
This was my bad. I had an old version of JS file and i was still pointing to that. JSON response put me on the right track. Thanks.