Replies

• allan Posts: 63,695Questions: 1Answers: 10,500 Site admin

  February 2022

  Do you mean something like this? It is basically a fully editable nested table and can be configured as such.

  Allan
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  Thanks for your reply Allan. I looked at both Nested editing and Multiple selection examples in Embedded DataTable section.

  I'm probably not being clear at what my issue is. I have no problem configuring the nested table, it's that I want to control loading and saving of data not via ajax but the JSON field. In the example you provided the POST is as follows after an edit.

  

  As you can see data[row_32][users][site] is only showing the selected key value. I would need that to be all fields encoded as JSON. Conversely on record edit I would need the JSON value to populate the embedded DataTable on load.

  The only way I think I could do this is to use a hidden field for the JSON value and use that in preOpen to populate the table and then in preClose to save data() back into the hidden field.

  Maybe I'm missing something obvious, but hopefully you can point me in the right direction.
• colin Posts: 15,240Questions: 1Answers: 2,599

  February 2022

  I see you're using Editor in your question, but our accounts aren't showing that you have a license. Is the license registered to another email address? Please can let us know so we can update our records and provide support.

  Thanks,

  Colin
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022 edited February 2022

  @colin I sent you a DM with the details. Back to the issue at hand, is what I want to do possible? The existing examples all appear to be option pickers. I think this is a valid use case.
• allan Posts: 63,695Questions: 1Answers: 10,500 Site admin

  February 2022

  Ah - I think I see. You are looking for something like this, but just nested? That should work out of the box, as long as the nested Editor is able to get the options it needs (typically from its own Ajax request).

  If that isn't working, could you give me a link to your page showing the issue please?

  Allan
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  Thanks, I will give it a try since you think it's feasible. I assume that neither options and optionsPair will be necessary in this situation.

  In an similar vein, is it possible to do parent/child editing with the new datatable field type similar to Parent / child editing in child rows?
• colin Posts: 15,240Questions: 1Answers: 2,599

  February 2022 edited February 2022

  I think this example is demonstrating your second paragraph question,

  Colin
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  Thanks Colin, but that example shows editing a common lookup table not associated records with a foreign key to the current record.
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  Below is some sample code to allow editing of a JSON field. Since the data in the JSON array has no key it was necessary to append a dummy key as DT_RowId (to permit editing) before loading it into the datatable field options.

          var officeEditor = new $.fn.dataTable.Editor({
              fields: [{
                  label: "Name:",
                  name: "name",
              }, {
                  label: "Employees:",
                  name: "employees",
                  attr: {
                      type: "number"
                  }
              }]
          });
  
          var editor = new $.fn.dataTable.Editor({
              ajax: {
                  url: "/api/Save",
                  type: "POST",
                  contentType: "application/json",
                  data: function (d) {
                      var data = editor.field('offices').dt().data();
                      data.map(function (ele) {
                          return ele.DT_RowId = undefined;
                      });
                      d.data[Object.keys(d.data)[0]].offices = data.toArray();
                      return JSON.stringify(d);
                  }
              },
              table: "#example",
              idSrc: "id",
              fields: [
                  {
                      label: "Company:",
                      name: "company"
                  }, {
                      label: "Offices:",
                      name: "offices",
                      type: "datatable",
                      editor: officeEditor,
                      config: {
                          searching: false,
                          buttons: [
                              { extend: 'create', editor: officeEditor },
                              { extend: 'edit', editor: officeEditor },
                              { extend: 'remove', editor: officeEditor }
                          ],
                          columns: [
                              { title: 'Name', data: 'name' },
                              { title: 'Employees', data: 'employees' }
                          ]
                      }
                  }
              ]
          });
  
          editor
              .on('open', function (e, mode, action) {
                  if (action === 'create')
                      editor.field('offices').update([]);
                  else {
                      var data = table.row(editor.modifier()).data();
                      if (data.offices) {
                          data.offices.map(function (ele) {
                              return ele.DT_RowId = crypto.randomUUID();
                          });
                          editor.field('offices').update(data.offices || []);
                      }
                      else
                          editor.field('offices').update([]);
                  }
              });
  
          var table = $('#example').DataTable({
              "searching": false,
              "select": {
                  style: 'single',
                  info: false 
              },
              "ajax": {
                  "url": '/api/GetData',
                  "dataSrc": ''
              },
              "columns": [
                  { "data": "id", "title": "Id" },
                  { "data": "company", "title": "Company" }
              ],
              buttons: [
                  { extend: "create", editor: editor },
                  { extend: "edit", editor: editor },
                  { extend: "remove", editor: editor }
              ]
          });
  

  Then once the record is being saved that dummy key is removed before posting data to the server. It's not particularly elegant but it works. Any suggestions to improve or streamline the code would be welcome.
• allan Posts: 63,695Questions: 1Answers: 10,500 Site admin

  February 2022

  Nice one - thanks for sharing this with us! I think this is probably as good as it gets at the moment for something like this. We'll take it onboard and look at options for how we can integrate that more in Editor.

  If anyone else reads this and is interested in direct JSON editing of the value, please let us know!

  Regards,
  Allan
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  Thanks for reviewing and letting me know that I'm on the right track. I noticed that there is internal code to autogenerate a value for DT_RowId when a new record is created, is that something that is exposed via the library or can you share a code snipped on how that is generated?
• mguinness Posts: 85Questions: 12Answers: 1

  February 2022

  I was able to find the code to autogenerate a key value, it basically uses the Date() constructor returning a milliseconds value. I've also moved code from the ajax function to the preSubmit event instead, as it seems a cleaner approach.

  editor
      .on('open', function (e, mode, action) {
          if (action === 'create')
              editor.field('offices').update([]);
          else {
              var data = table.row(editor.modifier()).data();
              if (data.offices) {
                  data.offices.map(function (ele, idx) {
                      return ele.DT_RowId = +new Date() + idx.toString();
                  });
                  editor.field('offices').update(data.offices || []);
              }
              else
                  editor.field('offices').update([]);
          }
      })
      .on('preSubmit', function (e, data, action) {
          var items = editor.field('offices').dt().data();
          items.map(function (ele) {
              return ele.DT_RowId = undefined;
          });
          data.data[Object.keys(data.data)[0]].offices = items.toArray();
      });
  
• colin Posts: 15,240Questions: 1Answers: 2,599

  February 2022

  Nice, thanks for posting back,

  Colin
• Loren Maxwell Posts: 406Questions: 99Answers: 10

  July 2022

  @allan -- "If anyone else reads this and is interested in direct JSON editing of the value, please let us know!"

  I second the request for an out-of-the-box Datatable control option to load/save JSON data!

  Another example might be a running series of notes pertaining to a specific record.

  Would be nice to be able to make a new entry with the date, the note, the id of the person making the note, etc., and store them all in a JSON field without a separate "Notes" table in the database.

  A Datatable control with that option seems to be the best solution.
• rf1234 Posts: 3,021Questions: 88Answers: 421

  July 2022 edited July 2022

  @allan - I see that other people also want to use field type "datatable" for parent child editing. Not just me. I have a work around that requires deleting the "datatable" field of the parent table, but apart from that my solution works as it should. Maybe you could add parent - child editing to the Editor "standard" if you know what I mean.
  https://datatables.net/forums/discussion/comment/210205#Comment_210205

  As @mguinness noted you are not covering real parent - chidl editing right now:

  Thanks Colin, but that example shows editing a common lookup table not associated records with a foreign key to the current record.

  There has been so much confusion about parent - child editing in this forum. @mguinness definition "associated records with a foreign key to the current record" is precisely what I mean.

  Implementing parent - child editing with field type "datatable" would be the first prerequisite to get JSON field editing running.

  It is pretty simple actually:
  - Using Editor at the back end you split the JSON field into individual fields that you return to the server.
  - On editing in field type "datatable" you just use one of the PHP events to put the individual fields back into the required JSON structure and update the database
  - all of this would be done in the Editor instance of field type "datatable".
• rf1234 Posts: 3,021Questions: 88Answers: 421

  July 2022

  Here is a post that highlights how to tear the JSON field apart on the server in order to return the individual fields to the client:
  https://datatables.net/forums/discussion/comment/168181/
• Loren Maxwell Posts: 406Questions: 99Answers: 10

  August 2022

  I'm on my way out the door -- otherwise I would try to make a test case. I can try later this evening but I wanted to see if anyone has any quick answers :-)

  Anyway, I'm using @mguinness last code snippet above to also store values from a Datatable input in a JSON database field.

  Everything works perfectly (i.e., I can add, edit, and delete the entries and they are saved in the JSON field correctly) EXCEPT in the special case of deleting all the entries for the JSON field, leaving an empty array [] as the correct entry into the database. In this case, no update is passed along, leaving the JSON database field untouched.

  In other words, I cannot delete all the JSON entries to leave an empty array [].

  The presubmit seems to work correctly:

  editor
      .on('open', function (e, mode, action) {
          if (action === 'create')
              editor.field('offices').update([]);
          else {
              var data = table.row(editor.modifier()).data();
              if (data.offices) {
                  data.offices.map(function (ele, idx) {
                      return ele.DT_RowId = +new Date() + idx.toString();
                  });
                  editor.field('offices').update(data.offices || []);
              }
              else
                  editor.field('offices').update([]);
          }
      })
      .on('preSubmit', function (e, data, action) {
          var items = editor.field('offices').dt().data();
          items.map(function (ele) {
              return ele.DT_RowId = undefined;
          });
          data.data[Object.keys(data.data)[0]].offices = items.toArray();
  console.log(data.data[Object.keys(data.data)[0]].offices ) // Correctly shows [] when all entries have been deleted from the Datatable input
      });
  

  But in the payload sent to the server, offices field is missing (again, only in the special case of an empty array [], otherwise it's correct).

  My guess is that there is something after the presubmit and before it is sent to the server that is removing the offices field if it contains only an empty array [].

  Is presubmit the correct event? Or is there something else I should be doing to update the offices field to show an empty array []?
• allan Posts: 63,695Questions: 1Answers: 10,500 Site admin

  August 2022

  Yes, preSubmit is the correct place to manipulate the data that is being sent t the server.

  The thing with an empty array is that when being sent by http parameters (which Editor does by default) it basically means that there is no parameter to send - which is exactly what you are seeing. e.g. you can't send office: since that would mean that there is an entry! This is the exact reason that Editor sends a special -many parameter for array fields (if you've seen that before).

  So ideally, you'd send a parameter with the length of the offices array. Its a pain, but that's a workaround for using http parameter.

  The alternative is to use JSON - e.g. data.json = JSON.stringify(data) and then parse that at the server. That has the benefit of preserving typing and array information.

  Allan
• Loren Maxwell Posts: 406Questions: 99Answers: 10

  August 2022

  Thanks, @allen -- I used the JSON.stringify(data) and parse at the server approach.