Dropdown list in column with options from dynamic source

Peter.prc48

Hi,
I'm new to Datatables.net and I've scoured the forums but haven't seen an example which meets my requirement. This question: https://datatables.net/forums/discussion/28855/dropdownlist-inline-using-datatable is close but I don't have a fixed, known list of options. Basically, I'm trying to create a dropdown list for a column where the data is a foreign key to another table. Server-side, I can get an array of the values and labels required and bundle them into the opts part of the json. However, I can't seem to access this object in my datatables javascript. The render function on a column has data passed to it but it is the data for that specific row.
Has anyone implemented something similar and is my approach sound or am I heading down a wrong path here?
Thanks.

ThomD

Just so I understand, you are talking about using using Editor with a select box as the input mechanism, right?

If so, look at the last example on this page.

https://editor.datatables.net/reference/field/select

Peter.prc48

Thanks for replying.
As I understand, using this method provides a select box in the form. Whilst I will want to do this, my main objective is to provide the same select input on every row in one of the columns of the table, and provide in-line editing. To render this in the table rows - does this not require the use of some other mechanism like columns.data (with possibly nested data for the select list options) and a render function so that the select list is rendered for every row?

ThomD

Editor supports in line editing so that you do not need to load the full form. This page shows the basic way to assign an on click event that triggers in line editing.

https://editor.datatables.net/examples/inline-editing/simple.html

In my case I put a className (dt-edit-select) on a column (defined in the columns definitions) that I have a radio field. Then I use that class name to configure the on click event.

$('#myTable tbody').on('click', 'td.dt-edit-select', function(e) {
   dtEditor.inline(this, {
        submit: 'allIfChanged',
        buttons: { label: "OK", fn: function () { this.submit() } };
    } );
} );

I'm using a radio, not a select, so I need an OK button. I think you can add an onChange event as a parameter (instead of my buttons), but I'm not sure.

Peter.prc48

Appreciate your reply. I'll give it a go and let you know how it goes. Thanks.

Peter.prc48

Almost there, but not quite.
I have the select dropdown list showing in the form and I have inline editing in the table so when you edit the cell the dropdown list appears and you can choose a value but when you move off the cell, it just displays the selected value - not the select list with the chosen value selected. I know it needs the cell render function in the datatable so that it can actually render the select list but I can't seem to access the list of possible values in the datatable. In the following client-side code snippet:

render : function(data, type, row){
// the data here is for the single row
}

I can see the values and labels I need in the options array in the json sent to the client but I don't know how to access this from within the render function - anyone know?

Or if someone has a server-side equivalent example?
Thanks.

ThomD

I think somebody else was reporting a similar problem in the last week or so. Check the recent discussions.

allan

Can you give us a link to the page so we can try to debug it directly please?

It does sound like your need to use a rendering function - but I'm not sure what it would be without being able to see the code and the JSON data. Does the JSON data include the value you want to display as well as the value that is being edits?

Allan

Peter.prc48

Thanks for the offer - I'll try and arrange this but it means disabling security on the site (although it is at an early stage and data is primarily test data).
In the meantime, this is the json I get from the call to my api:

{"draw":null,"data":[{"DT_RowId":"row_1","Name":"One-Time","DivisionId":1,"Active":true,"Id":1}],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{"DivisionId":[{"value":1,"label":"Administration"},{"value":2,"label":"Irrigation"}]},"files":{},"upload":{"id":null}}

As you can see, the select list should be composed of the data in the options object. The divisionId column in the datatable should host the select list. If I knew how to access the options object from the render function within the datatable script, it would be easy.

My client-side code is shown below (currently has the render function commented out when I realised the 'data' provided was for a single row (logically) - I would eventually amend the row number in the render function):

$("#serviceTypesDataTable").DataTable(
    {
        dom: "<'row'<'col-md-4'B><'col-md-4'l><'col-md-4'f>>" + "<'row'<'col-sm-12'tr>>" + "<'row'<'col-sm-5'i><'col-sm-7'p>>",
        ajax: "/api/servicetypes",
        columns: [
            {
                data: null,
                defaultContent: "",
                className: "select-checkbox",
                width: "5px",
                orderable: false
            },
            { data: "Name" },
            {
                data: "DivisionId"
                //render: function (data, type, row) {
                //    var ddl = "<select size='1' id='row-1-division' name='row-1-division'>";
                //    for (var i = 0; i < data.length; i++) {
                //        ddl = ddl + "<option value='{0}'>{0}</option>".replace("{0}", data.division);
                //    }
                //    ddl += "</select>";
                //    return ddl;
                //}
            },
            {
                data: "Active",
                width: "45px",
                render: function (data, type) {
                    if (type === "display") {
                        return "<input type='checkbox' class='editor-active'>";
                    }
                    return data;
                },
                className: "dt-body-center"
            }
        ],
        select: {
            style: "os",
            selector: "td:first-child"
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
        rowCallback: function (row, data) {
            // Set the checked state of the checkbox in the table.
            $("input.editor-active", row).prop("checked", data.Active == 1);
        }
    });

    $("#serviceTypesDataTable").on("change", "input.editor-active", function () {
        editor.edit($(this).closest("tr"), false)
            .set("active", $(this).prop("checked") ? 1 : 0)
            .submit();
    });

ThomD

In the On click that brings up the select box, could insert some jQuery that add values to the select box?

allan

If I knew how to access the options object from the render function within the datatable script, it would be easy.

Assign it to a variable in ajax.dataSrc - a variable that your rendering function can access (i.e. define it at the same level you create your DataTable).

var divisions;
var table = $(...).DataTable( {
  ajax: {
    url: ...,
    dataSrc: function ( json ) {
      divisions = json.options
      return json.data;
    }
  },
  columns: [
     { data: 'DivisionId', render: function ( data ) {
        return divisions[ data ]; // or similar
     },
     ...
  ]
} );

Another option - and the way that I do it in the Editor examples is to simply include the label in the row's data.

Allan

Peter.prc48

Thanks Allan. I twigged, eventually to the adding the label - so use a 'join' model, include the Division.Name, still use the DivisionId for the basis of the select list but render the Division.Name in the row. That way, the name is displayed and when I click the row, the editor takes over and displays the full dropdown list. Cool.

jay113

Hi Peter! I am trying to accomplish the same thing and would be interested in seeing the code that you used to make this work. Thanks ☺

Peter.prc48

Hi Jay113,
I'm a beginner at Datatables.Net but I'll do my best to explain my solution. I wanted a dropdown list of Divisions in each row of my ServiceType table, so that I could choose the Division for the ServiceType.
So the first part is the model I'm using is as follows:

public class ServiceTypeJoinModel
    {
        public class ServiceType
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public bool Active { get; set; }
            public int DivisionId { get; set; }
            public int CompanyId { get; set; }
        }

        public class Division
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    }

The Id and the Name within the Division class will be used as the key/values for the dropdown list.
In my controller, the Process method is as follows:

private const string Table = "ServiceType";

private IHttpActionResult Process(IEnumerable<KeyValuePair<string, string>> data)
        {
            var settings = Settings.Default;

            var user = User as IUser;
            if (user == null)
                return null;

            int companyId = user.CompanyId;
            string divisionsSql = string.Format("SELECT Id AS value, Name AS label FROM Division WHERE CompanyId = {0} ORDER BY NAME", companyId);

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, Table, "Id")
                   .Model<ServiceTypeJoinModel>()
                   .Field(new Field("ServiceType.Name").Validator(Validation.NotEmpty(new ValidationOpts { Message = "A Name is required." })))
                   .Field(new Field("ServiceType.DivisionId")
                        .Options(() => db
                        .Sql(divisionsSql)
                        .FetchAll()))
                   .Field(new Field("ServiceType.Active"))
                   .LeftJoin("Division", "Division.id", "=", "ServiceType.DivisionId")
                   .Process(data)
                   .Data();
                
                return Json(response);
            }
        }

Here, the options for the Field "ServiceType.DivisionId" uses the sql query to get all the Divisions.
Then, client-side, in the javascript, my datatable contains the following:

$("#serviceTypesDataTable").DataTable(
    {
        dom: "<'row'<'col-md-4'B><'col-md-4'l><'col-md-4'f>>" + "<'row'<'col-sm-12'tr>>" + "<'row'<'col-sm-5'i><'col-sm-7'p>>",
        ajax: {
            url: "/blah/api/servicetypes",
            dataSrc: function(json) {
                divisions = json.options;
                return json.data;
            }
        },
        columns: [
            {
                data: null,
                defaultContent: "",
                className: "select-checkbox",
                width: "5px",
                orderable: false
            },
            { data: "ServiceType.Name" },
            {
                data: "ServiceType.DivisionId",
                render: function (data, type, row) {
                    if (type === "display") {
                        return row.Division.Name;
                    }
                    return data;
                }
            },

Here, the "ServiceType.DivisionId" is using the render function to display the name of the chosen Division, instead of its Id and when you click in the cell and have inline-editing enabled:

// Activate an inline edit on click of a table cell
    $("#serviceTypesDataTable").on("click", "tbody td:not(:first-child)", function (e) {
        editor.inline( this );
    } );

Then the editor displays the dropdown list, I think because the editor as been set up as follows:

editor = new $.fn.dataTable.Editor({
        processing: "true",
        serverSide: "true",
        ajax: "/blah/api/servicetypes",
        table: "#serviceTypesDataTable",
        fields: [
        {
            label: "Services",
            name: "ServiceType.Name"
        },
        {
            label: "Division",
            name: "ServiceType.DivisionId",
            type: "select"
        },
        {
            label: "Active",
            name: "ServiceType.Active",
            type: "checkbox",
            separator: "|",
            options: [{ label: "", value: 1 }]
        }]
    });

Here you can see the type "select" on the editors "ServiceType.DivisionId".

Hope this helps.

jay113

Thanks!