Problems with Field.options
Problems with Field.options
ChrisL
Posts: 13Questions: 4Answers: 0
Hi all,
I'm using the latest version of Datatables along with the licenced version of Editor. Sadly my development environment is not visible on the internet so I can't link it here.
In the database table "product" there is a numeric field which contains a field called "distributor_contact_id" which contains the value of a row in a table named "contact", row name "id".
In editor when I click on the "New" button, as intended rather than getting the numeric "id" of the contact I get the "forename" then a space, and then the "surname". This is good and just as intended.
However, in the main table itself on the screen the "Contact ID" column (easier name that "distributor_contact_id") sadly displays the numeric "id" value rather than the "forename surname" combination.
Any ideas on how to fix this please? I've been messing around for hours getting nowhere!
Thanks in advance!
Chris.
My Node.JS controller file is as follows:
let db = require('../db');
let router = require('express').Router();
let moment = require('moment');
let {
Editor,
Field,
Validate,
Format,
Options
} = require("datatables.net-editor-server");
router.all('/api/product', async function(req, res) {
if (! req.body.business_id) {
res.json({data:[], err:"No selected_id selected"});
console.log('product.js -> No business_id selected.');
return;
}
let editor = new Editor(db, 'product').fields(
new Field("id").setFormatter(Format.ifEmpty(null)),
new Field("business_id").setFormatter(Format.ifEmpty(req.body.business_id)),
new Field("distributor_contact_id")
.options(new Options()
.table("contact")
.value("id")
.label(["forename","surname"])
.render(row => {
return row.forename+" "+row.surname;
})
.where(function() {
this.where("business_id",req.body.business_id)
})
)
.setFormatter(Format.ifEmpty(null)),
new Field("product_description"),
new Field("end_of_life_date")
.validator(Validate.dateFormat("YYYY-MM-DD"))
.setFormatter(Format.ifEmpty(null))
);
console.log("product.js -> processing request for buisness_id: "+req.body.business_id);
editor.where( function () {
this.where( 'business_id', req.body.business_id );
} );
await editor.process(req.body);
res.json(editor.data());
});
module.exports = router;
The JS code for the table and editor giving the problem is here:
productEditor = new $.fn.dataTable.Editor({
ajax: {
url: "../api/product",
type: "POST",
data: function ( d ) {
d.business_id = businessID
}
},
"table": "#product_table",
"fields": [
{
"type": "hidden",
"label": "Business ID",
"name": "business_id",
}, {
"label": "Product Description",
"name": "product_description"
}, {
"label": "Contact ID",
"name": "distributor_contact_id",
"type": "select",
"placeholder": "Select a contact"
},
{
"label": "End Date",
"name": "end_of_life_date",
"type": "date"
}
]
});
productTable = $('#product_table').DataTable({
ajax: {
url: "../api/product",
type: "POST",
data: function ( d ) {
d.business_id = businessID
}
},
serverSide: true,
responsive: true,
dom: 'Bfrtip',
columns: [
{data: "business_id"},
{data: "product_description"},
{data: "distributor_contact_id"},
{data: "end_of_life_date"}
],
order: [[ 1, 'asc' ]],
select: {
style: 'single'
},
buttons: [
{extend: "create", editor: productEditor},
{extend: "edit", editor: productEditor}
],
columnDefs: [
{
"targets": [ 0 ],
"visible": false,
"searchable": false
}
]
});
The data returned by the node.js controller is as follows:
{cancelled: [], data: [{DT_RowId: "row_1", id: 1, business_id: 1, distributor_contact_id: 2,…},…],…}
cancelled: []
data: [{DT_RowId: "row_1", id: 1, business_id: 1, distributor_contact_id: 2,…},…]
0: {DT_RowId: "row_1", id: 1, business_id: 1, distributor_contact_id: 2,…}
DT_RowId: "row_1"
business_id: 1
distributor_contact_id: 2
end_of_life_date: null
id: 1
product_description: "Product #100a"
1: {DT_RowId: "row_2", id: 2, business_id: 1, distributor_contact_id: null,…}
DT_RowId: "row_2"
business_id: 1
distributor_contact_id: null
end_of_life_date: null
id: 2
product_description: "Product #101a"
2: {DT_RowId: "row_3", id: 3, business_id: 1, distributor_contact_id: null,…}
DT_RowId: "row_3"
business_id: 1
distributor_contact_id: null
end_of_life_date: null
id: 3
product_description: "Product #102"
draw: 2
fieldErrors: []
files: {}
options: {distributor_contact_id: [{label: "James Smith", value: 1}, {label: "Mary Maddocks", value: 2},…]}
distributor_contact_id: [{label: "James Smith", value: 1}, {label: "Mary Maddocks", value: 2},…]
0: {label: "James Smith", value: 1}
label: "James Smith"
value: 1
1: {label: "Mary Maddocks", value: 2}
label: "Mary Maddocks"
value: 2
2: {label: "Sally Nugent", value: 3}
label: "Sally Nugent"
value: 3
recordsFiltered: 3
recordsTotal: 3
searchPanes: {options: {}}
options: {}
This question has an accepted answers - jump to answer
Answers
Hi Chris,
You need to do a left join to the
contacttable so you can load the relevant information for the product. The documentation for how to do that in our NodeJS libraries is available here.We have an example of it here (the live site uses PHP, but the Editor download package for NodeJS includes the same example, but written for Node).
Let me know how you get on with that,
Allan
Hi Allan,
Sorry for the late reply!
I've amended my constructor as follows:
Now I get the following error:
This is quite fustrating!
Any more suggestions please?
Chris.
Hi Chris,
As the error message says:
e.g. in this case:
The documentation page shows a full example of that.
Likewise on the client-side you’ll need the table prefix for the fields (DataTables columns and Editor fields).
The
product.id- is that your primary key? Add.set(false)if so - presumably you don’t want someone editing it.Allan
Ah! I see. I thought when it talked about setting the database table explicitly that it meant in the where clause!
I've amended it thus:
But sadly the controller still seems upset:
Thank you with your help so far!
If you have any further thoughts it would be appreciated!
Chris
Hi Chris,
I think you might not have updated the
columns.dataproperty for the columns in the client-side Javascript for the DataTable? Do you just have:still? It should be:
Likewise the Editor client-side code needs to reflect those names. See this example for how the client-side code might look (it is shown just below the example table).
Allan
I will double check.
The front end client doesn't seem to get a json response from the controller though.
Hi Allan,
Sorry for the late response - not had time to work on this project for a couple of weeks.
On changing the field references from fieldname to tablename.fieldname in main.js and the product.js controller we get a new error message:
Thanks for your help so far. Any further thoughts please as I'm kind of stuck here!
Thanks in anticipation!
Please ignore the last post - it appears a ghost has been pressing keys on a development PC whilst people we having lunch
Having undone the strange typing the message has gone. Will do more testing tomorrow.
Those ghosts are sneaky!