Editor with Datatable select on subset of records

parcival

I have a weird table I'm trying to setup with editing, where I'm presenting the data through the server side handler using a SQL view. I'm able to use the Datatable select type to present the data to the user, and this works great where new adds are allowed. In the example below, App1 and App3 will be presented through the select UI and for those records (ID's 1,2 and 4) I can edit the version.

My problem is that when I try to edit the version for App2, I get an error AllowListId: Input not valid. This is also reflected in the payload where it's blank.

On top of that I'm also not able to see any of the original data in the preSubmit data object. This makese sense since this entry's AllowListId is not included in the select set.

Is there any way around this?

The SQL view return something like this where the columns with an asterisk cannot be changed as they're from another table:

| Id | AllowListId | Version | Name* | AllowNew* |
=================================================
| 1  | 101         | 1.0     | App1  | TRUE      |
| 2  | 101         | 2.0     | App1  | TRUE      |
| 3  | 102         | 5.1     | App2  | FALSE     |
| 4  | 103         | 3.1     | App3  | TRUE      |

This is the relevant javascript config:

editor = new $.fn.dataTable.Editor({
          ajax: "/api/applications/data/",
          table: "#data-table",
          fields: [
          {
              label: "Application Name",
              name: "AllowListId",
              type: "datatable"
          },
          {
              label: "Version:",
              name: "Version"
          }
          ]
      });

     editor
          .on('open', function (e, type, action) {
              if (action === "edit") {
                  editor.hide(["AllowListId"])
              } else {
                  editor.show(["AllowListId"])
              }
              openVals = editor.get();
          })
          .on('preSubmit', function (e, data, action) {
              if (action === 'edit' || action === 'create') {
                  if (this.inError()) {
                      return false;
                  }
              }
          });

This is the setting on the server side (DotNet Core) that builds the data for the Datatable select:

Field(new Field("AllowListId")
    .Options(new Options()
        .Table("AllowList")
        .Where(q => q.Where("AllowNew", 1, "=", true))
        .Value("Id")
        .Label("Name")
    )
    .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
)

parcival

Well, I may just have solved it, but still would like some feedback--perhaps there is a better way

I added a hidden field in the editor with a different name AllowListHolder, using the original field as the data.
Then I iterate through the edited entries and checking whether the AllowListId is empty. If it is found empty, I take the value from the placeholder field and set it. If both are empty the server side validation will still kick back.

editor = new $.fn.dataTable.Editor({
          ajax: "/api/applications/data/",
          table: "#data-table",
          fields: [
          {
              label: "Application Name",
              name: "AllowListId",
              type: "datatable"
          }, {
              type: "hidden",
              data: "AllowListId",
              name: "AllowListIdHolder"
          },
          {
              label: "Version:",
              name: "Version"
          }
          ]
      });

     editor
          .on('open', function (e, type, action) {
              if (action === "edit") {
                  editor.hide(["AllowListId"])
              } else {
                  editor.show(["AllowListId"])
              }
              openVals = editor.get();
          })
          .on('preSubmit', function (e, data, action) {
              if (action === 'edit' || action === 'create') {
                  if (this.inError()) {
                      return false;
                  }
                  $.each(data.data, function (key, values) {
                      if (data.data[key].AllowListId === "") {
                          data.data[key].AllowListId = data.data[key].AllowListIdHolder;
                      }
                  });
              }
          });

allan

I think what you need to do to get data from the datatable field is set the optionsPair option to tell it where to get the value from. e.g.:

          {
              label: "Application Name",
              name: "AllowListId",
              type: "datatable"
              optionsPair: {value: 'Id'}
          }

Regards,
Allan

parcival

When the edited application is in the select list everything works fine, so I don't think it's the ID. It's more that when the edit UI loads, it tries to match the current value with something in the select list, and for App2 it cannot be found. This causes the AllowListId to be reset to a blank value. The switcheroo I setup with the secondary field solved the issue.