Changing fields based on field in dropdown in editor

map@odenterprise.org

Hi,

I would like to change/update value fi the fields in the datatable editor based on a value selected from the dropdown. I have a table org_country_info with following fields -

country_id (Primary Key)
org_hq_country (Field in the drop down)

Dependant fields which I would like to change (same table )-

org_hq_country_income
org_hq_country_income_code
org_hq_country_locode
org_hq_country_region
org_hq_country_region_code
ISO2

Right now, if I select a field from the dropdown and press enter, the other fields are not updating according to the selection.
Hope you can help! Here is my code -

file: simple.html

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/staff.php",
        table: "#example",
        fields: [ {
                label: "profile_id:",
                name: "org_profiles.profile_id"
            },  {
                label: "org_name:",
                name: "org_profiles.org_name"
            }, {
                label: "org_description:",
                name: "org_profiles.org_description"
            }, {
                label: "org_size:",
                name: "org_profiles.org_size"
            }, {
                label: "org_profile_status:",
                name: "org_profiles.org_profile_status"
            }, 
            {
                label: "org_hq_city:",
                name: "org_profiles.location_id",
                type: "select",
                placeholder: "Select a location"
            },
            {
                label: "org_hq_st_prov:",
                name: "org_locations.org_hq_st_prov"

            },
            {
                label: "survey_contact_email:",
                name: "org_contacts.survey_contact_email"

            },
                        {
                label: "survey_contact_first:",
                name: "org_contacts.survey_contact_first"

            },
                        {
                label: "survey_contact_last:",
                name: "org_contacts.survey_contact_last"

            },
                        {
                label: "survey_contact_phone:",
                name: "org_contacts.survey_contact_phone"

            },
                        {
                label: "survey_contact_title:",
                name: "org_contacts.survey_contact_title"

            },
                        {
                label: "org_hq_country:",
                name: "org_profiles.country_id",
                type: "select",
                placeholder: "Select a country"
            },

                        {
                label: "org_hq_country_income:",
                name: "org_country_info.org_hq_country_income"

            },
                        {
                label: "org_hq_country_income_code:",
                name: "org_country_info.org_hq_country_income_code"

            },
                        {
                label: "org_hq_country_locode:",
                name: "org_country_info.org_hq_country_locode"

            },
                        {
                label: "org_hq_country_region:",
                name: "org_country_info.org_hq_country_region"

            },
                        {
                label: "org_hq_country_region_code:",
                name: "org_country_info.org_hq_country_region_code"

            },
                        {
                label: "ISO2:",
                name: "org_country_info.ISO2"

            }
        ]
    } );

    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this , {
            submit: 'allIfChanged'  //Added by Vinayak
        }
            );
    } );


    var table = $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: "../php/staff.php",
        order: [[ 1, 'asc' ]],
        "scrollY": 400,
        "scrollX": true,
        "scrollCollapse": true,
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "org_profiles.profile_id" },
            { data: "org_profiles.org_name" },
            { data: "org_profiles.org_description" },
            { data: "org_profiles.org_size" },
            /*{ data: "org_profile_status", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }*/
            { data: "org_profiles.org_profile_status"},
            { data: "org_locations.org_hq_city", editField: "org_profiles.location_id"},
            { data: "org_locations.org_hq_st_prov", editField: "org_locations.org_hq_st_prov"},
            { data: "org_contacts.survey_contact_email", editField: "org_contacts.survey_contact_email"},
            { data: "org_contacts.survey_contact_first", editField: "org_contacts.survey_contact_first"},
            { data: "org_contacts.survey_contact_last", editField: "org_contacts.survey_contact_last"},
            { data: "org_contacts.survey_contact_phone", editField: "org_contacts.survey_contact_phone"},
            { data: "org_contacts.survey_contact_title", editField: "org_contacts.survey_contact_title"},
            { data: "org_country_info.org_hq_country", editField: "org_profiles.country_id"},

            { data: "org_country_info.org_hq_country_income", editField: "org_country_info.org_hq_country_income"},
            { data: "org_country_info.org_hq_country_income_code", editField: "org_country_info.org_hq_country_income_code"},
            { data: "org_country_info.org_hq_country_locode", editField: "org_country_info.org_hq_country_locode"},
            { data: "org_country_info.org_hq_country_region", editField: "org_country_info.org_hq_country_region"},
            { data: "org_country_info.org_hq_country_region_code", editField: "org_country_info.org_hq_country_region_code"},
            { data: "org_country_info.ISO2", editField: "org_country_info.ISO2"},


        ],

file: staff.php

Editor::inst( $db, 'org_profiles' )
->pkey( 'profile_id' )
    ->fields(
        Field::inst( 'org_profiles.profile_id' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'org_profiles.org_name' ),
        Field::inst( 'org_profiles.org_description' ),
        Field::inst( 'org_profiles.org_size' ),
        Field::inst( 'org_profiles.org_profile_status' ),

        Field::inst( 'org_profiles.location_id' )
                ->options( Options::inst()
                ->table( 'org_locations' )
                ->value( 'location_id' )
                ->label( 'org_hq_city' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'org_locations.org_hq_city' ),
        Field::inst( 'org_locations.org_hq_st_prov' ),
        Field::inst( 'org_contacts.survey_contact_email' ),
        Field::inst( 'org_contacts.survey_contact_first' ),
        Field::inst( 'org_contacts.survey_contact_last' ),
        Field::inst( 'org_contacts.survey_contact_phone' ),
        Field::inst( 'org_contacts.survey_contact_title' ),
                Field::inst( 'org_profiles.country_id' )
                ->options( Options::inst()
                ->table( 'org_country_info' )
                ->value( 'country_id' )
                ->label( 'org_hq_country' )
            )
            ->validator( 'Validate::dbValues' ),
         Field::inst( 'org_country_info.org_hq_country' ),

         Field::inst( 'org_country_info.org_hq_country_income' ),
         Field::inst( 'org_country_info.org_hq_country_income_code' ),
         Field::inst( 'org_country_info.org_hq_country_locode' ),
         Field::inst( 'org_country_info.org_hq_country_region' ),
         Field::inst( 'org_country_info.org_hq_country_region_code' ),
         Field::inst( 'org_country_info.ISO2' )

    )
    ->leftJoin( 'org_locations', 'org_locations.location_id', '=', 'org_profiles.location_id' )
    ->leftJoin( 'org_contacts', 'org_contacts.profile_id', '=', 'org_profiles.profile_id' )
    ->leftJoin( 'org_country_info', 'org_country_info.country_id', '=', 'org_profiles.country_id' )
    ->process( $_POST )
    ->json();

Thanks a lot!

allan

Hi,

The dependent() method is the way to do this. It will basically add an event listener to the parent select element and trigger a call to the server to get the information for the child whenever the parent's value changes. So all you need to do is provide a server-side script that will query the database to get the options you want to display based on the parent's value.

Regards,
Allan

map@odenterprise.org

HI Allan,

Thank you for the response. I have implemented editor.dependant but I am getting this error

dataTables.editor.min.js:45 Uncaught TypeError: Cannot read property 'node' of undefined
at e.dependent (dataTables.editor.min.js:45)
at HTMLDocument.<anonymous> (simple.html:179)
at fire (jquery-1.12.4.js:3232)
at Object.fireWith [as resolveWith] (jquery-1.12.4.js:3362)
at Function.ready (jquery-1.12.4.js:3582)
at HTMLDocument.completed (jquery-1.12.4.js:3617)

Here are my code and my ajax file. Any idea about this error and is my code right, especailly the ajax file? -

simple.html

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/staff.php",
        table: "#example",
        fields: [ {
                label: "profile_id:",
                name: "org_profiles.profile_id"
            },  {
                label: "org_name:",
                name: "org_profiles.org_name"
            }, {
                label: "org_description:",
                name: "org_profiles.org_description"
            }, {
                label: "org_size:",
                name: "org_profiles.org_size"
            }, {
                label: "org_profile_status:",
                name: "org_profiles.org_profile_status"
            }, 
            {
                label: "org_hq_city:",
                name: "org_profiles.location_id",
                type: "select",
                placeholder: "Select a location"
            },
            {
                label: "org_hq_st_prov:",
                name: "org_locations.org_hq_st_prov"

            },
            {
                label: "survey_contact_email:",
                name: "org_contacts.survey_contact_email"

            },
                        {
                label: "survey_contact_first:",
                name: "org_contacts.survey_contact_first"

            },
                        {
                label: "survey_contact_last:",
                name: "org_contacts.survey_contact_last"

            },
                        {
                label: "survey_contact_phone:",
                name: "org_contacts.survey_contact_phone"

            },
                        {
                label: "survey_contact_title:",
                name: "org_contacts.survey_contact_title"

            },
                        {
                label: "org_hq_country:",
                name: "org_profiles.country_id",
                type: "select",
                placeholder: "Select a country"
            },

                        {
                label: "org_hq_country_income:",
                name: "org_country_info.org_hq_country_income"

            },
                        {
                label: "org_hq_country_income_code:",
                name: "org_country_info.org_hq_country_income_code"

            },
                        {
                label: "org_hq_country_locode:",
                name: "org_country_info.org_hq_country_locode"

            },
                        {
                label: "org_hq_country_region:",
                name: "org_country_info.org_hq_country_region"

            },
                        {
                label: "org_hq_country_region_code:",
                name: "org_country_info.org_hq_country_region_code"

            },
                        {
                label: "ISO2:",
                name: "org_country_info.ISO2"

            },
            {
                "label": "data_type:",
                "name": "org_data_use[].object_id",
                "type": "select"
            }
        ]
    } );



    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this , {
            submit: 'allIfChanged'  //Added by Vinayak
        }
            );
    } );

//       editor.dependent( 'org_country_info.country_id', '../php/dependant/country.php' );

        editor.dependent('org_country_info.org_hq_country', function (val, data, callback) {
                $.ajax({
          //          url: '/Admin/GetActivities?projectId=' + val,
                    url: '../php/dependant/country.php',
                    data: data,
                    dataType: 'json',
                    success: function (json) {
                        callback(json);
                    }
                });
            });



    var table = $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: "../php/staff.php",
        order: [[ 1, 'asc' ]],
        "scrollY": 400,
        "scrollX": true,
        "scrollCollapse": true,
/*        "paging":         false,*/
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "org_profiles.profile_id" },
            { data: "org_profiles.org_name" },
            { data: "org_profiles.org_description" },
            { data: "org_profiles.org_size" },
            /*{ data: "org_profile_status", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }*/
            { data: "org_profiles.org_profile_status"},
            { data: "org_locations.org_hq_city", editField: "org_profiles.location_id"},
            { data: "org_locations.org_hq_st_prov", editField: "org_locations.org_hq_st_prov"},
            { data: "org_contacts.survey_contact_email", editField: "org_contacts.survey_contact_email"},
            { data: "org_contacts.survey_contact_first", editField: "org_contacts.survey_contact_first"},
            { data: "org_contacts.survey_contact_last", editField: "org_contacts.survey_contact_last"},
            { data: "org_contacts.survey_contact_phone", editField: "org_contacts.survey_contact_phone"},
            { data: "org_contacts.survey_contact_title", editField: "org_contacts.survey_contact_title"},
            { data: "org_country_info.org_hq_country", editField: "org_profiles.country_id"},

            { data: "org_country_info.org_hq_country_income", editField: "org_country_info.org_hq_country_income"},
            { data: "org_country_info.org_hq_country_income_code", editField: "org_country_info.org_hq_country_income_code"},
            { data: "org_country_info.org_hq_country_locode", editField: "org_country_info.org_hq_country_locode"},
            { data: "org_country_info.org_hq_country_region", editField: "org_country_info.org_hq_country_region"},
            { data: "org_country_info.org_hq_country_region_code", editField: "org_country_info.org_hq_country_region_code"},
            { data: "org_country_info.ISO2", editField: "org_country_info.ISO2"},

            { data: "org_data_use", render: "[, ].data_type"}

        ],
        keys: {
            columns: ':not(:first-child)',
            editor:  editor
        },

        select: {
            style:    'os',
            selector: 'td:first-child'
        },

country.php

```
<?php

$data = [
'options' => [
'org_country_info.org_hq_country' => [
"Kosovo",
"Liberia"
]
],

'values': [
    'org_country_info.org_hq_country' => "Kosovo",
    'org_country_info.org_hq_country_income' => "Higher Test"
]

];

echo json_encode($data);

<?php > ``` ?>

Thanks so much for all the help!

allan

Hi,

There doesn't appear to be a field in your Editor form called org_country_info.org_hq_country. THere are various fields with that name and something else postfixed - such as org_country_info.org_hq_country_income, but not that name specifically, which is why it is giving an error.

Perhaps it should be org_profiles.country_id?

Allan