Can I associate value with dropdown selection for Editor update?

boicenet

I'm using Editor and a join to display data from 3 related tables - Jobs, Machines, Job_Assignments. During an Edit in the DataTable (job_assignments table) I'd like to have saved to my job_assignments table the machines.machine_id based on the associated machines.machine_name value when changed to a different machine using the more user-friendly machine name. I plan to provide a dropdown list for the user to choose a different machine. Is this possible using Editor?

// DataTables PHP library
include( "AssignmentTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'job_assignments', 'assignment_id' )
    ->fields(

        Field::inst( 'jobs.job_number' ),
        Field::inst( 'jobs.job_name' ),
        Field::inst( 'job_assignments.production_date' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd."
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),

        Field::inst( 'machines.machine_name' )
    )

    ->leftJoin( 'jobs', 'jobs.job_id', '=', 'job_assignments.job_id' )
    ->leftJoin( 'machines', 'machines.machine_id', '=', 'job_assignments.machine_id' )


    ->process( $_POST )
    ->json();

allan

Just to confirm my understanding, the key thing here is that you want to be able to have Editor write to two different database tables with a single "Submit" action? If so, then yes, that is possible - the key thing you need to do is make sure that you include the primary key of the joined table (presumably machines.machine_id in this case) in the Field::inst() list and also as a hidden field type in the Editor form. That allows the PHP libraries to identify the machines row to be edited uniquely.

Allan

boicenet

Hi Allan,

Actually, I want to write only to a single link table (job_assignments). Main job info is written to the jobs table. A separate machines table contains a list of machines (ID, Name, etc.). I'm using the job_assignments table to store the actual machine assignment(s) of various components associated with the job entry. For example, a single job in the jobs table may have a front and back each needing to be assigned to a separate machine. I then write to and use the job_assignments table for displaying the job "components" assigned to each machine. I want to be able to update the job_assignments table with a potentially new production date (which now works), and/or a new machine assignment (my challenge). I use a machine_id in the job_assignments table, but want the user to be able to select a new machine by name (more user-friendly), but have the machine_id in the job_assignments table updated.

Thank you.

allan

Okay - think I've got it. So the key thing is that you want the interface the user sees to show the machine name, while the actual value is the machine id.

That is basically exactly what is happening in this example. The Location column comes from a separate table - the DataTable and Editor will show the label to the end user, but the value submitted to the server is the id.

Are you using the PHP or .NET libraries that are provided for Editor (edit you are using the PHP libraries - sorry!)? If so, have a look over their left join information which details how that works in Editor.

Allan

edit: I should qualify my last sentence! You are using the left join aspect already, but the key take away from the documentation should be how the Field->options() method can be used to get the list of options from the database and have that automatically populate the select list

boicenet

I am using the code below and the user does see the machine name substituted for the actual id data; however, whenever the Edit button is selected, the user then sees the id data and the name is not displayed. So, I've accomplished part of what I need. I'm now wanting the user to be able to select during the Edit of the record the machine name (defined in a separate table) and then save back to the db the corresponding machine id. I can give you access to the page if you'd like.

<script type="text/javascript" language="javascript" class="init">

        var editor; //

        $(document).ready(function() {

            editor = new $.fn.dataTable.Editor( {
                ajax: "assignments.php",
                table: "#assignment_table",
                fields: [
                     {
                        label: "Job Number",
                        name: "jobs.job_number"
                    }, {
                        label: "Job Name",
                        name: "jobs.job_name"
                    }, {
                        label: "JA Production Date",
                        name: "job_assignments.production_date",
                        type: "datetime"
                    }, {
                        label: "JA Machine ID",
                        name: "job_assignments.machine_id"
                    }, {
                        label: "Machine",
                        name: "machines.machine_name",
                        type: "select"
                    }
                ],


                i18n: {
                    create: {

                        title:  "Add New Assignment"
                    },
                    edit: {

                        title:  "Edit Assignment Details"
                    },
                    remove: {

                        title:  "Delete Selected Assignment?"

                    }
                }

            } );


            $('#assignment_table').DataTable( {
                "iDisplayLength": 12,
                dom: "Bfrtip",
                ajax: "assignments.php",
                columns: [
                    { data: "jobs.job_number" },
                    { data: "jobs.job_name" },
                    { data: "job_assignments.production_date" },
                    { data: "job_assignments.machine_id" },
                    { data: "machines.machine_name" }
                ],


                columnDefs: [
                    {
                        // The `data` parameter refers to the data for the cell (defined by the
                        // `data` option, which defaults to the column being worked with, in
                        // this case `data: 0`.
                        "render": function ( data, type, row ) {
//                          return data +' - ('+ row.machines.machine_name+')';
                            return ''+ row.machines.machine_name+'';
                        },
                        "targets": 3
                    },
                    { "visible": true,  "targets": [ 4 ] }
                ],


                select: true,
                buttons: [
                    { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    {
                        extend: "remove",
                        editor: editor,
                        formMessage: function ( e, dt ) {
                            var rows = dt.rows( e.modifier() ).data().pluck('job_id');
                            return 'Are you sure you want to permanently delete this assigned job? <ul><li>'+rows.join('</li><li>')+'</li></ul>';
                        }
                    }
                ]
            } );

        } );

    </script>

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "AssignmentTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'job_assignments', 'assignment_id' )
    ->fields(
        Field::inst( 'jobs.job_number' ),
        Field::inst( 'jobs.job_name' ),
        Field::inst( 'job_assignments.production_date' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd."
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),

        Field::inst( 'job_assignments.machine_id' ),

        Field::inst( 'machines.machine_name' )
//            ->options( 'machines', 'machine_id', 'machine_name' )
//            ->validator( 'Validate::dbValues' )

    )

    ->leftJoin( 'jobs', 'jobs.job_id', '=', 'job_assignments.job_id' )
    ->leftJoin( 'machines', 'machines.machine_id', '=', 'job_assignments.machine_id' )


    ->process( $_POST )
    ->json();

allan

In your Editor form you have:

name: "machines.machine_name",

I don't think you want that. That would be used to edit a value on the machines table.

What I thik you do want is the Field->options() method that you have commented out, but move it to the Field::inst( 'job_assignments.machine_id' ), field. That is the value you want to edit (if I understand correctly) and that one you want to have a list of options for.

Regards,
Allan

boicenet

Allan,

I'm not 100% clear with your suggestion. I believe that I just tried what you suggested, but ran into a situation where my machine_name column was being overwritten in the db. I moved the commented Field->options() method under the Field::inst( 'job_assignments.machine_id' ) and commented out the name: "machines.machine_name".

boicenet

Allan,

I have it working now. Thank you for all of your assistance!

Bob

Davidnguyen

Thank you for sharing the interesting information. As I hoped, these info will be useful to many people. I am very pleased with our association; we have been able to help one another often. Do not hesitate to call again if I can help.

allan

Hi Bob,

Good to hear you have it working now - thanks for the update.

Regards,
Allan