Problems with Field.options

Problems with Field.options

ChrisLChrisL 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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Hi Chris,

    You need to do a left join to the contact table 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

  • ChrisLChrisL Posts: 13Questions: 4Answers: 0

    Hi Allan,

    Sorry for the late reply!

    I've amended my constructor 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))
            )
            .leftJoin("contact","contact.id","=","product.distributor_contact_id");
    
        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;
    

    Now I get the following error:

    Unhandled promise error:  [object Promise]Error: Table part of the field "id" was not found. In Editor instances that use a join, all fields must have the database table set explicitly.
    stack: Error: Table part of the field "id" was not found. In Editor instances that use a join, all fields must have the database table set explicitly.
        at Editor._prepJoin (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1549:11)
        at Editor.<anonymous> (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1621:8)
        at step (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at /home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:31:71
        at new Promise (<anonymous>)
        at __awaiter (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:27:12)
        at Editor._process (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:1319:16)
        at Editor.<anonymous> (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:857:16)
        at step (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at /home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:31:71
        at new Promise (<anonymous>)
        at __awaiter (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:27:12)
        at Editor.process (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:467:16)
        at /home/chris/datatables/Editor-NodeJS-1.9.6/controllers/product.js:46:15
    

    This is quite fustrating!

    Any more suggestions please?

    Chris.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Hi Chris,

    As the error message says:

    In Editor instances that use a join, all fields must have the database table set explicitly.

    e.g. in this case:

            new Field("product.id").setFormatter(Format.ifEmpty(null)),
            new Field("product.business_id").setFormatter(Format.ifEmpty(req.body.business_id)),
            new Field("product.distributor_contact_id")
    

    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

  • ChrisLChrisL Posts: 13Questions: 4Answers: 0
    edited February 2021

    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:

    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("product.id").setFormatter(Format.ifEmpty(null)),
            new Field("product.business_id").setFormatter(Format.ifEmpty(req.body.business_id)),
            new Field("product.distributor_contact_id")
                .options(new Options()
                    .table("contact")
                    .value("contact.id")
                    .label(["contact.forename","contact.surname"])
                    .render(row => {
                        return row.forename+" "+row.surname;
                    })
                    .where(function() {
                        this.where("contact.business_id",req.body.business_id)
                    })
                )
                .setFormatter(Format.ifEmpty(null)),
            new Field("product.product_description"),
            new Field("product.end_of_life_date")
                    .validator(Validate.dateFormat("YYYY-MM-DD"))
                .setFormatter(Format.ifEmpty(null))
            )
            .leftJoin("contact","contact.id","=","product.distributor_contact_id");
    
        console.log("product.js -> processing request for buisness_id: "+req.body.business_id);
        editor.where( function () {
            this.where( 'product.business_id', req.body.business_id );
        } );
    
        await editor.process(req.body);
        res.json(editor.data());
    });
    
    module.exports = router;
    

    But sadly the controller still seems upset:

    Unhandled promise error:  [object Promise]Error: Unknown field: product_description (index 1)
    stack: Error: Unknown field: product_description (index 1)
        at Editor._sspField (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1929:10)
        at Editor._sspSort (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:2008:10)
        at Editor.<anonymous> (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1884:8)
        at step (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at /home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:31:71
        at new Promise (<anonymous>)
        at __awaiter (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:27:12)
        at Editor._ssp (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:1660:16)
        at Editor.<anonymous> (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1205:24)
        at step (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at fulfilled (/home/chris/datatables/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:28:58)
        at process._tickCallback (internal/process/next_tick.js:68:7)
    

    Thank you with your help so far!

    If you have any further thoughts it would be appreciated!

    Chris

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Hi Chris,

    I think you might not have updated the columns.data property for the columns in the client-side Javascript for the DataTable? Do you just have:

    data: 'product_description`
    

    still? It should be:

    data: 'product.product_description'
    

    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

  • ChrisLChrisL Posts: 13Questions: 4Answers: 0

    I will double check.

    The front end client doesn't seem to get a json response from the controller though.

  • ChrisLChrisL Posts: 13Questions: 4Answers: 0

    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:

    Unhandled promise error:  [object Promise]TypeError: upload.table is not a function
    stack: TypeError: upload.table is not a function
        at Editor.<anonymous> (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1074:24)
        at step (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at /var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:31:71
        at new Promise (<anonymous>)
        at __awaiter (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:27:12)
        at Editor._fileDataFields (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:765:16)
        at Editor.<anonymous> (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1041:14)
        at step (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
        at Object.next (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:37:53)
        at /var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:31:71
        at new Promise (<anonymous>)
        at __awaiter (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:27:12)
        at Editor._fileData (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:723:16)
        at Editor.<anonymous> (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.ts:1265:31)
        at step (/var/www/html/development.alltec.com/public_html/nodejs/Editor-NodeJS-1.9.6/node_modules/datatables.net-editor-server/dist/editor.js:56:23)
    

    Thanks for your help so far. Any further thoughts please as I'm kind of stuck here!

    Thanks in anticipation!

  • ChrisLChrisL Posts: 13Questions: 4Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Those ghosts are sneaky!

This discussion has been closed.