Answers

• rf1234 Posts: 2,996Questions: 87Answers: 421

  February 2023

  I think there are two ways to do this, Shawn.

  1. Use field type "datatable" to create new records on the fly and then select them as the selected option: While I have done the first I haven't done the latter myself. Not sure how this would work because you would need both features at the same time. I can post some code for parent-child editing. But here is something pretty good from @allan. You would need a blend of parent - child editing and single select.
     https://editor.datatables.net/examples/datatables/parentChild.html
     https://editor.datatables.net/examples/datatables/select.html

  2. Use selectize and create options on the fly. I think you don't really need those tables in the Editor popup, do you? Just having selectize drop downs would do, I guess. And it would reduce the size of the popup.

  Using selecize I built something hat allows you to select an option (by its id) or enter a new option on the fly (and send the value of the option to the server instead of the id; the server code regognizes that it's not an existing option that is being sent, but a new option should be created).

  I you are interested in learning more about 2. I can post some code. For 1.: Good luck, but maybe somebody knows how to mix parent - child editing and single select using field type "datatable".
• rf1234 Posts: 2,996Questions: 87Answers: 421

  February 2023 edited February 2023

  Here is the code for the selectize example:

  table: "#tblContractGov",
  fields: [ {
          label: lang === 'de' ? 'Kontrahent:' : 'Counterparty:',
          name:  "contract.gov_manual_creditor_id", //render creditor name
          type: "selectize", 
          opts: {
              create: true,
              createFilter: function(val) {
                return ( isNaN(val) || val.indexOf('.') > -1 ) ? val : false;
              },
              maxItems: 1,
              openOnFocus: false,
              allowEmptyOption: false,
              placeholder: lang === 'de' ? 
                  "Bitte Kontrahenten wählen oder hinzufügen" : 
                  "Please select a Counterparty or add one",                
              render: {
                  option_create: function(data, escape) {
                      var add = lang === 'de' ? "Neu: " : "Add ";      
                      return '<div class="create">' + add + '<strong>'
                             + escape(data.input) + '</strong>&hellip;</div>';
                  }
                }
              },
      },
  

  "createFilter" makes sure that only values that are not numeric or contain a period are allowed to be created. Id fields that are integer cannot be created (false).
  "option_create" defines how this is being shown either as "Neu" (German) or "Add" (English)

  And the server script:

  Editor::inst( $db, 'contract' )
  ->field( 
      Field::inst( 'contract.gov_manual_creditor_id' )
          ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
          ->options( Options::inst()
              ->table('gov_manual_creditor')
              ->value( 'id' )
              ->label( array('name', 'govdept_id') )    
  //                    ->label( 'name' )
              ->render( function ( $row ) { 
                  if ($row['govdept_id'] > 0) {
                      return 'Interne Abteilung: ' . $row["name"];
                  }
                  return $row["name"];
              } )
              ->order( 'govdept_id desc, name asc' )
              //where clause MUST be a closure function in Options!!!
              ->where( function($q) {
                  $q ->where('gov_id', $_SESSION['gov_id']);
                  $q ->where( function($r) {
                      $r ->where('govdept_id', $_SESSION['govdept_id'], '!=');
                      $r ->or_where('govdept_id', null);
                  });
              } )
          ),
  ....
  ->leftJoin( 'gov_manual_creditor', 'contract.gov_manual_creditor_id', '=', 'gov_manual_creditor.id')
  ....
  ->on( 'writeCreate', function ( $editor, $id, $values ) {
      $manualId = $values['contract']['gov_manual_creditor_id'];
      if ( ( ! is_numeric( $manualId ) ) || //we don't have a valid id => new manual counterparty entered on the fly
           ( count( explode('.', $manualId) ) > 1 ) ) { //strpos didn't work with a period!!
          if ( isset($values['contract']['counterparty']) ) {
              processNewManualCreditor( $editor->db(), $id, $manualId, $values['contract']['counterparty'] );
          }
      }
  } )
  

  On "writeCreate" and also on "writeEdit" I check whether a new counterparty has been entered by the user and do the db processing for it.

  You will find the selectize plugin here:
  https://editor.datatables.net/plug-ins/field-type/editor.selectize

  I would use a newer version of "selectize" e.g. 0.12.6 or newer
  https://github.com/selectize/selectize.js

  And this is what is looks like:

  

  
• shawngibson Posts: 32Questions: 9Answers: 0

  February 2023

  Hi rf, I thought I responded to this shortly after you wrote it, but it seems not. Your second suggestion would be perfect for me, as it appears in your screenshot.

  Your code is very complex to me, so I need to study it to alter it appropriately to match my table/field etc. names. Am I correct that:

  From your JS File:

  tblContractGov is your source table (like my Products table above) and contract.gov_manual_creditor_id is a field in that table which will be leftJoin()ed via the 'real' field in another table, which here is named Counterparty?

  From your PHP File:

  contract is your leftJoin()ed table (like my Manufacturer or MetaCat tables) and within contract the field contract.gov_manual_creditor_id is the id and that this table has the other fields where the actual records are stored?

  Let me do some reading from your links and do a mockup to come back with for your perusal before I try implementing any changes to my live code...back soon:)

  Shawn
• rf1234 Posts: 2,996Questions: 87Answers: 421

  February 2023

  Regarding JS:
  Yes the field is called "Counterparty" in the Editor window. The id of the counterparty is saved as a foreign key in the "contract" database table: contract.gov_manual_creditor_id. To populate the dropdown for "Counterparty" there is some rendering server side but eventually just the foreign key in the "contract" table is being saved. (And in case of a new counterparty added on the fly the respective database entry in table "gov_manual_creditor" is saved which is done in function "processNewManualCreditor".)

  Regarding PHP:

  contract is your leftJoin()ed table (like my Manufacturer or MetaCat tables) and within contract the field contract.gov_manual_creditor_id is the id and that this table has the other fields where the actual records are stored?

  No, "contract" is the base table. "gov_manual_creditor" is the left joined table. In terms of "parent - child": "gov_manual_creditor" is the parent and "contract" is the child because one counterparty can have many contracts, but one contract can only have one counterparty. (Actually it is even more complex because there are various types of counterparties, but that is not helpful in this context.)
• shawngibson Posts: 32Questions: 9Answers: 0

  February 2023

  Thanks for the update, rf. I spent almost 7 hours on this yesterday, starting with your example, then eventually moving over to Allan's "Nested Editing" example, which I tried to get working verbatuum (with appropriate fields changed). I was unable to get it working. I kept getting errors in the "Continent" bits, which in my case was a "Comments" field.

  Hopefully, by sharing my working code (as it is in the above screenshots in my first post), and explaining what I did, maybe you can see what I need to do here. I basically replaced Allan's Users with my Products (parent) and Allan's Sites with my Subcats (child).

  My Products PHP:

  <?php
  
  /*
   * Editor server script for DB table products
   * Created by http://editor.datatables.net/generator
   */
  
  // DataTables PHP library and database connection
  include("lib/DataTables.products.php");
  
  // Alias Editor classes so they are easy to use
  use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions;
  
  
  // Build our Editor instance and process the data coming from _POST
  Editor::inst($db, 'products', 'id')
      ->fields(
          Field::inst('products.product')
              ->validator(Validate::notEmpty()),
          Field::inst('products.img1'),
          Field::inst('products.img2'),
          Field::inst('products.img3'),
          Field::inst('products.img4'),
          Field::inst('products.prodshot'),
          Field::inst('products.description'),
          Field::inst('products.restit1'),
          Field::inst('products.reslnk1'),
          Field::inst('products.restit2'),
          Field::inst('products.reslnk2'),
          Field::inst('products.restit3'),
          Field::inst('products.reslnk3'),
          Field::inst('products.restit4'),
          Field::inst('products.reslnk4'),
          Field::inst('products.comment'),
          Field::inst('products.keywords'),
  //      Field::inst('products.manufacturer'),
          Field::inst('products.mfrpn'),
          Field::inst('products.mfrlnk'),
          Field::inst('products.rgbid'),
  
          Field::inst('products.universe')
              ->options(
                  Options::inst()
                      ->table('categories.universes')
                      ->value('id')
                      ->label('universe')
              )
              ->validator(Validate::dbValues()),
          Field::inst('universes.universe'),
  
  
          Field::inst('products.metacat')
              ->options(
                  Options::inst()
                      ->table('categories.metacats')
                      ->value('id')
                      ->label('metacat')
              )
              ->validator(Validate::dbValues()),
          Field::inst('metacats.metacat'),
  
  
          Field::inst('products.category')
              ->options(
                  Options::inst()
                      ->table('categories.categories')
                      ->value('id')
                      ->label('category')
              )
              ->validator(Validate::dbValues()),
          Field::inst('categories.category'),
  
  
          Field::inst('products.subcat')
              ->options(
                  Options::inst()
                      ->table('categories.subcats')
                      ->value('id')
                      ->label('subcat')
              )
              ->validator(Validate::dbValues()),
          Field::inst('subcats.subcat'),
          
          Field::inst('products.manufacturer')
              ->options(
                  Options::inst()
                      ->table('contacts.contacts')
                      ->value('first_name')
                      ->label('first_name')
              )
              ->validator(Validate::dbValues()),
              
          Field::inst('contacts.first_name')
  
  
      )
      ->leftJoin('categories.universes', 'categories.universes.id', '=', 'products.universe')
      ->leftJoin('categories.metacats', 'categories.metacats.id', '=', 'products.metacat')
      ->leftJoin('categories.categories', 'categories.categories.id', '=', 'products.category')
      ->leftJoin('categories.subcats', 'categories.subcats.id', '=', 'products.subcat')
      ->leftJoin('contacts.contacts', 'contacts.contacts.first_name', '=', 'products.manufacturer')
      ->process($_POST)
      ->json();
  

  My Subcats PHP:

  <?php
  
  /*
   * Editor server script for DB table universes
   * Created by http://editor.datatables.net/generator
   */
  
  // DataTables PHP library and database connection
  include( "lib/DataTables.subcats.php" );
  
  // Alias Editor classes so they are easy to use
  use
      DataTables\Editor,
      DataTables\Editor\Field,
      DataTables\Editor\Format,
      DataTables\Editor\Mjoin,
      DataTables\Editor\Options,
      DataTables\Editor\Upload,
      DataTables\Editor\Validate,
      DataTables\Editor\ValidateOptions;
  
  
  
  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'subcats', 'id' )
      ->fields(
          Field::inst( 'subcat' )
              ->validator( Validate::notEmpty() )
              ->validator( Validate::unique() ),
          Field::inst( 'comments' )
  
      )
      ->process( $_POST )
      ->json();
  

  The Subcats table is rooted at CATEGORIES database, which the Products table is rooted at INVNEW database, hence all the confounding "dot syntax". Products and Subcats go to their own Bootstrap.products.php/Bootstrap.subcats.php files where their respective config.products.php/config.subcats.php files point to their appropriate credentials. Works a charm right now.

  I'll have to add more later as I have an appointment. On another, quick, note; how do I get the forum here to code my code bits according to the language they are written in, i.e. PHP/JS, the way you have it above, and in the Examples?
• shawngibson Posts: 32Questions: 9Answers: 0

  February 2023

  My Products JS (ignore the function format() it's just for the child rendering):

  var editor; // use a global for the submit and return data rendering in the examples
  
  (function ($) {
    //wrap everything
  
    function format(d) {
      //HTML rendering of the child/parent dropdown
      // `d` is the original data object for the row
      return (
  // rendering code
      );
      }
      
    //initialize everything
      $(document).ready(function () {
        // build the Editor New Record modal
        editor = new $.fn.dataTable.Editor({
          ajax: "php/table.products1.php",
          table: "#products",
          fields: [
       //etc.
            {
              label: "Metacat:",
              name: "products.metacat",
              type: "datatable",
              config: {
                columns: [
                  {
                    title: "Value",
                    data: "label",
                  },
                ],
              },
            },
            {
              label: "Category:",
              name: "products.category",
              type: "datatable",
              config: {
                columns: [
                  {
                    title: "Value",
                    data: "label",
                  },
                ],
              },
            },
            {
              label: "Subcat:",
              name: "products.subcat",
              type: "datatable",
              config: {
                columns: [
                  {
                    title: "Value",
                    data: "label",
                  },
                ],
              },
            },
            {
              label: "Manufacturer:",
              name: "products.manufacturer",
              type: "datatable",
              config: {
                columns: [
                  {
                    title: "Value",
                    data: "label",
                  },
                ],
              },
            },
  
  // etc.
  
          ],
        });
  
        // hide|show visible columns COLS button
        // HTML: <!-- +Toggle Table Columns-->
        $("a.toggle-vis").on("click", function (e) {
          e.preventDefault();
          // Get the column API object
          var column = table.column($(this).attr("data-column"));
          // Toggle the visibility
          column.visible(!column.visible());
        });
  
        // new record button link
        // HTML: "<a aria-label="label" class="editor - create" href="#" style="margin - right: 5px; ">New</a>"
        $("a.editor-create").on("click", function (e) {
          e.preventDefault();
          editor.create({
            title: "Create new record",
            buttons: "Add",
          });
        });
  
        // Edit record (pencil icon)
        $("#products").on("click", "td.editor-edit", function (e) {
          e.preventDefault();
  
          editor.edit($(this).closest("tr"), {
            title: "Edit record",
            buttons: "Update",
          });
        });
  
        // Delete a record (trash icon)
        $("#products").on("click", "td.editor-delete", function (e) {
          e.preventDefault();
  
          editor.remove($(this).closest("tr"), {
            title: "Delete record",
            message: "Are you sure you wish to remove this record?",
            buttons: "Delete",
          });
        });
  
        //The DataTable
        var table = $("#products").DataTable({
          dom: '<"top"frt<"clear">>rt<"bottom"Bilp<"clear">>',
          scrollToTop: "true",
          fixedHeader: {
            header: false,
            footer: true,
          },
          lengthMenu: [
            [10, 25, 50, 100, -1],
            [10, 25, 50, 100, "All"],
          ],
          tableTools: {
            sSwfPath: "../../dt/swf/copy_csv_xls_pdf.swf",
            aButtons: [
              "copy",
              "print",
              {
                sExtends: "collection",
                sButtonText: "Save",
                aButtons: ["csv", "xls", "pdf"],
              },
            ],
          },
          ajax: {
            url: "php/table.products1.php",
            type: "POST",
          },
          deferRender: true,
          columnDefs: [
            {
              targets: [5, 7, 8, 9, 10, 11, 12], //hides these columns but they remain
              visible: false, //searchable and viewable via "Cols" button
            },
          ],
          columns: [
            {
              className: "dt-control",
              orderable: false,
              data: null,
              defaultContent: "",
            }, // +/- icon to exspand child cells
            {
              data: null,
              className: "dt-center editor-edit",
              defaultContent: '<span class="icon icon-edit fs2" ></span>',
              orderable: false,
            },
            {
              data: null,
              className: "dt-center editor-delete",
              defaultContent: '<span class="icon icon-trash3 fs2" ></span>',
              orderable: false,
            },
            {
              data: "products.prodshot",
              render: function (data, type, row, meta) {
                return (
                  '<div id="js-lightbox-animation" class="" uk-grid="" uk-lightbox=""><a class="uk-inline uk-panel uk-link-muted uk-text-center" href="' +
                  data +
                  '" caption=""><figure><img src="' +
                  data +
                  '" width="150" height="150" alt=""></figure></a></div>'
                );
              },
            },
            {
              data: "products.product",
            },
            {
              data: "contacts.first_name",
            },
            {
              data: "products.mfrlnk",
              render: function (data, type, row, meta) {
                return (
                  '<a target="_blank" href="' +
                  data +
                  '" uk-tooltip="title:' +
                  data +
                  ';pos: top">link</a>'
                );
              },
            },
            {
              data: "products.comment",
            },
            {
              data: "products.keywords",
            },
            {
              data: "universes.universe",
            },
            {
              data: "metacats.metacat",
            },
            {
              data: "categories.category",
            },
  
            {
              data: "subcats.subcat",
            },
            {
              data: "products.rgbid",
            },
          ],
          //add # of rows dropdown i.e. (10-100)
          order: [1, "asc"],
          select: {
            style: "os",
            selector: "td.select-checkbox",
          },
          select: true,
          buttons: [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            {
              extend: "selected",
              text: "Duplicate",
              action: function (e, dt, node, config) {
                // Start in edit mode, and then change to create
                editor
                  .edit(table.rows({ selected: true }).indexes(), {
                    title: "Duplicate record",
                    buttons: "Create from existing",
                  })
                  .mode("create");
              },
            },
            { extend: "remove", editor: editor },
          ],
        });
  
        table
          .buttons()
          .container()
          .appendTo($(".col-sm-6:eq(0)", table.table().container()));
  
        // Add event listener for opening and closing details
        $("#products tbody").on("click", "td.dt-control", function () {
          var tr = $(this).closest("tr");
          var row = table.row(tr);
  
          if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass("shown");
          } else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass("shown");
          }
        });
      });
  })(jQuery);
  
  
• rf1234 Posts: 2,996Questions: 87Answers: 421

  February 2023

  You might find something on using multiple databases in the forum if you search for it. I don't know because I only use one database at a time.

  Here you'll find something about the various languages using Markdown
  https://datatables.net/manual/tech-notes/8
  Just search for "Code blocks with syntax highlighting".