Adding new record to leftJoin()ed field within primary New Record modal without page refresh

shawngibson

Hi All, I'm thankfully back at it after a few rough weeks. Hope you're all well:)

To facilitate my inability to describe my current issue with any brevity, please consider the following screenshot, which is the "New Record" modal for my products table:

Within the above modal, you will see four searchable fields, namely Metacat, Category, Subcat and, finally, Manufacturer:

I have been trying, unsuccessfully, to add code within the table.products.js file which would allow me to create new records in these four leftJoin()ed fields while adding a new product, e.g. when a new manufacturer is met, or a new metacat most appropriately suits the current product.

As it is, I must exit my new product data entry session, go to the manufacturer or metacat table, add the new record, and then refresh my products_crud.html page so it reflects the new entry in the `leftoin()ed table. This makes data entry sessions very cumbersome for me (or my hypothetical clients/staff).

I'm sure this is not a unique problem, so I suppose my question is: How might I add a "New Manufacturer" or "New Metacat" subroutine within my table.products.js code which allows me to continue within my current "New Record" modal after updating the manufacturer or metacat database and choosing the new Manufacturer/Metacat without having to exit my current modal?

(To be clear, I did successfully add a "New Record" button beside the above-mentioned leftJoin()ed tables and open another modal to enter the data, but when done the new entry was not reflected until I refreshed my underlying page, which of course destroys the current new product data entry. So I know at least that part is possible...just not feasible as the entire page needs to be refreshed to show the new records within the Products modal.)

Any thoughts?

As always, Merci:)
Shawn

rf1234

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

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

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

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

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

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

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".