Nested Editor select field not populating from dynamic source

rwearmouth

Debugger code (debug.datatables.net): ojeloq

I'm trying to populate a dropdown with a list of employees in a nested Editor that I'm using as a time entry sheet. The ajax call works, validation and everything, and the options are being pulled properly, but the select field doesn't seem to be getting any options data loaded into it.

I have the same information being pulled for my main Editor instance for display as a separate dropdown, and it works perfectly. Just seems to not function in the nested application. When I leave the field as a text field, it will stop me from entering a value that isn't valid according to the query (ex. entering 9 when the highest employee ID is 6 comes back invalid). Additionally, manually copying the Ajax response and running it through the field's update() function works, so there's further proof that the query is solid.

I feel like there is just something silly simple stopping the select from populating, but I can't find it. The field in question is timeEditor.field('mtrf_times.employee.id'). Help?

Nested Editor instance

var timeEditor = new $.fn.dataTable.Editor( {
    ajax: './php/times_nested.php',
    fields: [ {
        label: 'Tech',
        name: 'mtrf_times.employee_id',
        type: 'select'
    }, {
        label: 'Time In',
        name: 'mtrf_times.time_in',
        type:      'datetime',
        def:       function () { return new Date(); },
        format:    'MM-DD-YYYY h:mm A',
        opts: {
             minutesIncrement: 5
        }
    }, {
        label: 'Time Out',
        name: 'mtrf_times.time_out',
        type:      'datetime',
        format:    'MM-DD-YYYY h:mm A',
        opts: {
             minutesIncrement: 5
        }
    }, {
        label: 'Notes',
        name: 'mtrf_times.notes'
    }
    ]
} );

Main Editor instance

//initialize Editor instance
editor = new $.fn.dataTable.Editor( {
    ajax: {
        url: './php/inbox_table.php',
        type: 'POST',
        //send post variable of form ID to DB query script
        data: function (d) {
            d.form_id = 1; //editor.field('mtrf_inbox.form_id').val();
        }
    },
    table: "#inbox",
    template: "#customForm",
    fields: [ {
        label: 'Times',
        name: 'dummy_times',
        type: 'datatable',
        editor: timeEditor,
        config: {
            ajax: {
                url: './php/times_nested.php',
                type: 'POST',
                //send post variable of form ID to DB query script. Not being used yet.
                data: function (d) {
                    d.form_id = GetWorkOrderFormID();
                }
            },
            buttons: [
                { extend: 'create', editor: timeEditor },
                { extend: 'edit',   editor: timeEditor },
                { extend: 'remove', editor: timeEditor },
                {
                    extend: 'refresh',
                    editor: timeEditor,
                    attr: {
                        id: 'timeRefresh'
                    }
                }
            ],
            columns: [
                {
                    title: 'Tech',
                    data: 'mtrf_times.employee_id'
                },
                {
                    title: 'Time Logged',
                    data: 'mtrf_times.time_logged'
                },
                {
                    title: 'Notes',
                    data: 'mtrf_times.notes'
                }
            ]
       }
   } ]
} );

Nested query

<?php

session_start();
 
/*
 * Example PHP implementation used for the index.html example
 */
 
// DataTables PHP library
include( "./DataTables.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;

//if the script was sent a form ID (first open of a new record), store the value into a $_SESSION variable so that subsequent queries (edits) don't need to look for the $_POST variable
if(isset($_POST['form_id'])) {
    $_SESSION['form_id'] = $_POST['form_id'];
}

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'mtrf_times' )
    ->fields(
        Field::inst( 'mtrf_times.id' ),
        Field::inst( 'mtrf_times.work_order_id' ),
        Field::inst( 'mtrf_times.employee_id' )
            ->options( Options::inst()
                ->table( 'mtrf_employees' )
                ->value( 'id' )
                ->label( array('first_name', 'last_name') )
                ->where( function ($q) {
                    $q->where( 'status', '0', '!=' );
                } )
            )
            ->validator ( Validate::dbValues() )
            ->validator( Validate::notEmpty( ValidateOptions::inst()->message('This field is required.')) ),
        Field::inst( 'mtrf_employees.id' ),
        Field::inst( 'mtrf_times.time_in' ),
        Field::inst( 'mtrf_times.time_out' ),
        Field::inst( 'mtrf_times.time_logged' ),
        Field::inst( 'mtrf_times.truck_number' ),
        Field::inst( 'mtrf_times.start_miles' ),
        Field::inst( 'mtrf_times.end_miles' ),
        Field::inst( 'mtrf_times.miles_logged' ),
        Field::inst( 'mtrf_times.notes' ),
        Field::inst( 'mtrf_times.sort_code' )
    )
    ->leftJoin( 'mtrf_employees', 'mtrf_employees.id', '=', 'mtrf_times.employee_id' )
    //->where('mtrf_times.work_order_id', $_SESSION['form_id'])
    ->debug(true)
    ->process( $_POST )
    ->json();

Ajax preview

{data: [{DT_RowId: "row_2",…}], options: {,…}, files: [], debug: [{,…}, {,…}]}
data: [{DT_RowId: "row_2",…}]
0: {DT_RowId: "row_2",…}
DT_RowId: "row_2"
mtrf_employees: {id: "6"}
id: "6"
mtrf_times: {id: "2", work_order_id: "1", employee_id: "6", time_in: "2021-04-28 00:00:00",…}
employee_id: "6"
end_miles: null
id: "2"
miles_logged: null
notes: ""
sort_code: null
start_miles: null
time_in: "2021-04-28 00:00:00"
time_logged: null
time_out: "0000-00-00 00:00:00"
truck_number: null
work_order_id: "1"
debug: [{,…}, {,…}]
0: {,…}
bindings: []
query: "SELECT  `mtrf_times`.`id` as 'mtrf_times.id', `mtrf_times`.`work_order_id` as 'mtrf_times.work_order_id', `mtrf_times`.`employee_id` as 'mtrf_times.employee_id', `mtrf_employees`.`id` as 'mtrf_employees.id', `mtrf_times`.`time_in` as 'mtrf_times.time_in', `mtrf_times`.`time_out` as 'mtrf_times.time_out', `mtrf_times`.`time_logged` as 'mtrf_times.time_logged', `mtrf_times`.`truck_number` as 'mtrf_times.truck_number', `mtrf_times`.`start_miles` as 'mtrf_times.start_miles', `mtrf_times`.`end_miles` as 'mtrf_times.end_miles', `mtrf_times`.`miles_logged` as 'mtrf_times.miles_logged', `mtrf_times`.`notes` as 'mtrf_times.notes', `mtrf_times`.`sort_code` as 'mtrf_times.sort_code' FROM  `mtrf_times` LEFT JOIN `mtrf_employees` ON `mtrf_employees`.`id` = `mtrf_times`.`employee_id` "
1: {,…}
bindings: [{name: ":where_1", value: "0", type: null}]
query: "SELECT DISTINCT  `id` as 'id', `first_name` as 'first_name', `last_name` as 'last_name' FROM  `mtrf_employees` WHERE (`status` != :where_1 )"
files: []
options: {,…}
mtrf_times.employee_id: [{label: "Brayden Hagemeier", value: "4"}, {label: "Employee Pending", value: "6"},…]
0: {label: "Brayden Hagemeier", value: "4"}
1: {label: "Employee Pending", value: "6"}
2: {label: "Finn Durkin", value: "5"}
3: {label: "Marv Wearmouth", value: "1"}
4: {label: "Matt Tessman", value: "3"}
5: {label: "Ryan Wearmouth", value: "2"}

Ajax raw response

{"data":[{"DT_RowId":"row_2","mtrf_times":{"id":"2","work_order_id":"1","employee_id":"6","time_in":"2021-04-28 00:00:00","time_out":"0000-00-00 00:00:00","time_logged":null,"truck_number":null,"start_miles":null,"end_miles":null,"miles_logged":null,"notes":"","sort_code":null},"mtrf_employees":{"id":"6"}}],"options":{"mtrf_times.employee_id":[{"label":"Brayden Hagemeier","value":"4"},{"label":"Employee Pending","value":"6"},{"label":"Finn Durkin","value":"5"},{"label":"Marv Wearmouth","value":"1"},{"label":"Matt Tessman","value":"3"},{"label":"Ryan Wearmouth","value":"2"}]},"files":[],"debug":[{"query":"SELECT  `mtrf_times`.`id` as 'mtrf_times.id', `mtrf_times`.`work_order_id` as 'mtrf_times.work_order_id', `mtrf_times`.`employee_id` as 'mtrf_times.employee_id', `mtrf_employees`.`id` as 'mtrf_employees.id', `mtrf_times`.`time_in` as 'mtrf_times.time_in', `mtrf_times`.`time_out` as 'mtrf_times.time_out', `mtrf_times`.`time_logged` as 'mtrf_times.time_logged', `mtrf_times`.`truck_number` as 'mtrf_times.truck_number', `mtrf_times`.`start_miles` as 'mtrf_times.start_miles', `mtrf_times`.`end_miles` as 'mtrf_times.end_miles', `mtrf_times`.`miles_logged` as 'mtrf_times.miles_logged', `mtrf_times`.`notes` as 'mtrf_times.notes', `mtrf_times`.`sort_code` as 'mtrf_times.sort_code' FROM  `mtrf_times` LEFT JOIN `mtrf_employees` ON `mtrf_employees`.`id` = `mtrf_times`.`employee_id` ","bindings":[]},{"query":"SELECT DISTINCT  `id` as 'id', `first_name` as 'first_name', `last_name` as 'last_name' FROM  `mtrf_employees` WHERE (`status` != :where_1 )","bindings":[{"name":":where_1","value":"0","type":null}]}]}

allan

I've got a feeling I know what is causing this - Editor listens for the xhr.dt event on the document which DataTables triggers whenever it does an Ajax load. But for that to happen, the table needs to be in the document, which it might not be in this case.

Could you do me a quick favour as add:

editor.on('open', function () {
  editor.field('dummy_times').dt().ajax.reload();
});

That will trigger an Ajax reload when the nested DataTable is in the document and the nested Editor will see that xhr.dt event.

I'll give it a try locally here tomorrow as well.

Thanks,
Allan

rwearmouth

No change there. Though my intent all along was to use Select2, and the ajax capabilities there provided me with a solid work around. So my particular issue is solved. Loving this system though, really great work.