Answers

• ThomD Posts: 334Questions: 11Answers: 43

  October 2015 edited October 2015

  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 Posts: 22Questions: 6Answers: 1

  October 2015

  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 Posts: 334Questions: 11Answers: 43

  October 2015

  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 Posts: 22Questions: 6Answers: 1

  October 2015

  Appreciate your reply. I'll give it a go and let you know how it goes. Thanks.
• Peter.prc48 Posts: 22Questions: 6Answers: 1

  October 2015

  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 Posts: 334Questions: 11Answers: 43

  October 2015

  I think somebody else was reporting a similar problem in the last week or so. Check the recent discussions.
• allan Posts: 63,353Questions: 1Answers: 10,444 Site admin

  October 2015

  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 Posts: 22Questions: 6Answers: 1

  October 2015

  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 Posts: 334Questions: 11Answers: 43

  October 2015

  In the On click that brings up the select box, could insert some jQuery that add values to the select box?
• allan Posts: 63,353Questions: 1Answers: 10,444 Site admin

  October 2015 Answer ✓

  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 Posts: 22Questions: 6Answers: 1

  October 2015

  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 Posts: 8Questions: 2Answers: 0

  January 2016

  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 Posts: 22Questions: 6Answers: 1

  January 2016 edited January 2016

  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 Posts: 8Questions: 2Answers: 0

  January 2016

  Thanks!