Populating Editor dropdown from Ajax source

htwyford

I've been using other examples on these forums and the Editor example set, but I can't seem to find what I'm doing wrong.

I am trying to populate an Editor table with some records, and a dropdown list of related records.

Here is my table:

var editor = new $.fn.dataTable.Editor({
                ajax: {
                    url: jsonUrl,
                    data: { id: id },
                    dataSrc: "Items",
                },
                table: '#schedulingTable',
                fields: [
                    {
                        "label": "User:",
                        "name": "users[].ID",
                        "type": "select",
                        "optionspair": {
                            label: "lastName",
                            value: "ID"
                        },
                        "options": {
                            "users[].lastName": "users[].ID"
                        }
                    },
                    {
                        "label": "hours:",
                        "name": "schedule.hours"
                    },
                    {
                        "label": "Week Ending:",
                        "name": "schedule.week_ending",
                        "type": "datetime",
                        "format": "YYYY-MM-DD"
                    }
                ]
            });

            $('#schedulingTable').on('click', 'tbody td:not(:first-child)', function (e) {
                editor.inline(this);
            });

            var table = $('#schedulingTable').DataTable({
                ajax: {
                    url: '@Url.Action("JsonGet", "Schedule")',
                    data: { id: id },
                    dataSrc: "Items",
                },
                columns: [
                    {
                        data: "users", render: "[].Text"
                    },
                    {
                         data: "schedule.hours"
                    },
                    {
                         data: "schedule.week_ending"
                    }
                ],
                select: true,
                lengthChange: false
            });

and a single record from my JSON. "Items" is the data source:

{"Items":
[{
"schedule":{
"ID":1,
"JobID":1,
"UserID":1,
"hours":40.00
"week_ending":"\/Date(1495771200000)\/"},

"users":[
{"ID":1,"firstMidName":"Bob","lastName":"Smith"},
{"ID":2,"firstMidName":"Darren","lastName":"Jones"},
{"ID":3,"firstMidName":"Mike","lastName":"Allan"},
{"ID":4,"firstMidName":"Rob","lastName":"Newson"},
{"ID":5,"firstMidName":"Harry","lastName":"Twyford"}
]},
...

I would simply like a table where each record is a Schedule object, with three columns: a dropdown to pick a user, where the initial selected value is the user with the same ID as schedule.UserID, an int hours column, an a DateTime week_ending column.

Instead, right now my table has five commas where the dropdown would go. What am I doing wrong?!

allan

Editor will automatically look for a parameter called options in the JSON response from the server - documentation here.

If you can't modify the JSON response you would need to use the initComplete callback from DataTables to know when the JSON has been loaded and then use the update() method of the select field type to update the options based on whatever data point you want to give it.

Allan

allan

Instead, right now my table has five commas where the dropdown would go. What am I doing wrong?!

This is actually a different issue from the options not being populated in the Editor window. The issue here is being caused by: data: "users", render: "[].Text" - there is no Text parameter in those objects. You could use firstMidName to display that parameter. If you want to display both first and last names you would need to use a function for the renderer.

Allan

htwyford

I've made some changes to my JSON. Here it is in its entirety, rather than just one record:

{
"schedules":[
{"ID":1,"JobID":1,"UserID":1,"hours":40.00,"week_ending":"\/Date(1495771200000)\/"},
{"ID":2,"JobID":1,"UserID":2,"hours":30.00,"week_ending":"\/Date(1495771200000)\/"},
{"ID":3,"JobID":1,"UserID":3,"hours":44.00,"week_ending":"\/Date(1495771200000)\/"},
{"ID":4,"JobID":1,"UserID":4,"hours":50.00,"week_ending":"\/Date(1495771200000)\/"},
{"ID":5,"JobID":1,"UserID":5,"hours":40.00,"week_ending":"\/Date(1495771200000)\/"},
],
"options":[
{"ID":1,"firstMidName":"Harry","lastName":"Name1"},
{"ID":2,"firstMidName":"Darren","lastName":"Name2"},
{"ID":3,"firstMidName":"Mike","lastName":"Name3"},
{"ID":4,"firstMidName":"Rob","lastName":"Name4"},
{"ID":5,"firstMidName":"Jay","lastName":"Name5"}
]
}

I no longer have an overarching object like "Items", and just have one array of all the schedules, and one array of all the users. Is this the right approach? How might I access options? Here's some snippets of my DataTables JS:

ajax: {
    url: '@Url.Action("JsonGet", "Schedule")',
    data: { id: id },
},
table: '#schedulingTable',
fields: [
    {
        "label": "User:",
        "name": "schedules.UserId",
        "type": "select",
        "optionspair": {
            label: "lastName",
            value: "ID"
        },

....


var table = $('#schedulingTable').DataTable({
                ajax: {
                    url: '@Url.Action("JsonGet", "Schedule")',
                    data: { id: id },
                },
                columns: [
                    {
                        data: "users.lastName"
                    },

...


initComplete: function (settings, json) {
    editor.field('users').update(json);
}

allan

If you can change the JSON, then I'd suggest you use:

"options": {
    "users[].ID": [{
            "ID": 1,
            "firstMidName": "Harry",
            "lastName": "Name1"
        },
        {
            "ID": 2,
            "firstMidName": "Darren",
            "lastName": "Name2"
        },
        {
            "ID": 3,
            "firstMidName": "Mike",
            "lastName": "Name3"
        },
        {
            "ID": 4,
            "firstMidName": "Rob",
            "lastName": "Name4"
        },
        {
            "ID": 5,
            "firstMidName": "Jay",
            "lastName": "Name5"
        }
    ]
}

Note how the field name is given in the options object. Otherwise there would be no way to populate multiple select elements if there were more than one.

Allan

htwyford

I've made similar changes but can't seem to get it. I've messaged you with a debug link with my full data.