Populate a text field by choosing an item from a select list?

th3t1ck

So I have a user_id (Select field) and Full name (text field) in editor. I'm trying to populate the Full name based on the selected user_id.

{
                label: 'Assigned Employee ID:',
                name: 'user_id',
                type: 'select',
              }, {
                label: 'Assigned Employee Name:',
                name: 'assigned',
                type: 'text',
                readonly: true,
                fieldInfo: \"This field is read only and is created when the assigned employee id is selected.\",
                onChange: function ( data, type, set ) {
// get column indexes
// set data to assigned employee's full_name
                   return data.full_name;
                },
              }

I'm stuck trying to get the column index. Can anyone help? I read and re-read the columns().index() page but I'm just not getting it.

allan

I would suggest using the dependent() method for this. There isn't an onChange attribute for the text field (I guess it could be given in the attr object, but that's not something I've tried).

In the dependent() method it passes in the full data from the form, so you can get the user id directly from that or use field().val() to get the value.

Allan

allan

I should has said as well:

I read and re-read the columns().index() page but I'm just not getting it.

That's a DataTables API method - not Editor. Editor doesn't have any columns() methods - [its fields()]](https://editor.datatables.net/reference/api/).

Allan

th3t1ck

Thank you Allan. I'll check out dependent().

th3t1ck

So I've tried this...

      editor.dependent( 'support_requests.user_id', function ( val, data, callback ) {
         event: 'keyup change'
         return val === 'A16479' ?
         { hide: ['support_requests.assigned'] } :
         { show: ['support_requests.assigned'] };
         editor.field( 'support_requests.assigned' ).val( data.support_requests.assigned );
         editor.draw();
    } );

And also this...

<script>
var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: 'support_requests-con.php',
        table: '#support_requests',
                template: '#customForm',
        fields: [ {
                label: 'Date:',
                name: 'support_requests.date',
                type: 'datetime',
                opts: { firstDay: 0}
            },  {
                label: 'After Hours Call:',
                name: 'support_requests.after_hours',
                type: 'radio',
                def: 'No',
                options: [ 'Yes', 'No' ],
            },  {
                label: 'Category:',
                name: 'support_requests.category',
                type: 'select',
            }, {
                label: 'Requesting Employee\'s Rank :',
                name: 'support_requests.rank',
                type: 'select',
            }, {
                label: 'Division:',
                name: 'support_requests.division',
                type: 'select',
            }, {
                label: 'Summary:',
                name: 'support_requests.summary',
                type: 'textarea',
            }, {
                label: 'Assigned Employee ID:',
                name: 'support_requests.user_id',
                type: 'select',
              }, {
                label: 'Assigned Employee Name:',
                name: 'support_requests.assigned',
                type: 'select',
                fieldInfo: \"This field is read only and is created when the assigned employee id is selected.\",
            }
        ]
    } );

    editor.field( 'support_requests.user_id' ).input().on( 'change', function () {
    if ( editor.field( 'support_requests.user_id' ).val() === 'A16479' ) {
        editor.show( 'support_requests.assigned' );
    }
    else {
        editor.hide( 'support_requests.assigned' );
    }
} );
    
    var table = $('#support_requests').DataTable( {
        dom: '<\"top\"Bfl>iprt<\"bottom\">p<\"clear\">',
        ajax: 'support_requests-con.php',
        columns: [
        { data: 'support_requests.date' },
        { data: 'support_requests.after_hours' },
        { data: 'lk_categories.category' },
        { data: 'lk_chp_ranks.rank' },
        { data: 'lk_chp_divisions.division' },
        { data: 'support_requests.summary'},
        { data: 'tbl_userid.user_id'},
        { data: 'tbl_fullname.full_name'}

        ],
        fixedHeader: {
          header: true,
          footer: true
        },
         responsive: true,
         select: true,
         stateSave: true,
        buttons: [
            { extend: 'create',   editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor },
            { text: 'Reload', action: function ( e, dt, node, config ) {dt.ajax.reload();}},
            { extend: 'pdfHtml5', orientation: 'portrait', filename: 'OESA SMS - Support Requests' },
            { extend: 'copyHtml5', exportOptions: { orthogonal: 'export' } },
            { extend: 'excelHtml5', filename: 'OESA SMS - Support Requests' },
            { extend: 'csvHtml5', filename: 'OESA SMS - Support Requests' },
             'colvis', 'print'
        ],
        'aLengthMenu': [[10, 25, 50, -1], [10, 25, 50, 'All']],
        'iDisplayLength': 50
    } );   
} );
</script>
<div id=\"customForm\">
  <fieldset class=\"support_requests_details\">
    <legend><i class=\"fas fa-briefcase\"></i> SUPPORT REQUEST DETAILS</legend>
            <div data-editor-template=\"support_requests.date\"></div>
            <div data-editor-template=\"support_requests.after_hours\"></div>
            <div data-editor-template=\"support_requests.category\"></div>
            <div data-editor-template=\"support_requests.rank\"></div>
            <div data-editor-template=\"support_requests.division\"></div>
            <div data-editor-template=\"support_requests.summary\"></div>
            <div data-editor-template=\"support_requests.user_id\"></div>
            <div data-editor-template=\"support_requests.assigned\"></div>
  </fieldset>
</div>

<table id=\"support_requests\" class=\"table table-striped table-bordered dt-responsive no-wrap\">
        <thead>
            <tr>
                                <th>Date</th>
                                <th>After Hours</th>
                                <th>Category</th>
                                <th>Assigned Employee's Rank</th>
                                <th>Division</th>
                                <th>Summary</th>
                                <th>Assigned Employee ID</th>
                                <th>Assigned Employee Name</th>
           </tr>
        </thead>
    </table>
  </div>
</body>
</html>

But the change event does not trigger the show. I'm sure it's something simple that I'm missing or have wrong.

th3t1ck

I also have a problem with StateSave. When I use StateSave any filter that is applied stays there. Funny thing is I didn't apply a filter. Is there a setting to not save the state of certain fields?

colin

Hi @th3t1ck ,

Regarding stateSave, by default it will save everything, but you can remove (or add) other properties with stateSaveParams,

I'll leave Allan to reply to your other question

Cheers,

Colin

allan

  editor.dependent( 'support_requests.user_id', function ( val, data, callback ) {
     event: 'keyup change'
     return val === 'A16479' ?
     { hide: ['support_requests.assigned'] } :
     { show: ['support_requests.assigned'] };
     editor.field( 'support_requests.assigned' ).val( data.support_requests.assigned );
     editor.draw();
} );

I don't think that is actually valid Javascript due to the second line. Perhaps you want:

editor.dependent(
  "support_requests.user_id",
  function(val, data, callback) {
    editor
      .field("support_requests.assigned")
      .val(data.support_requests.assigned);

    return val === "A16479"
      ? { hide: ["support_requests.assigned"] }
      : { show: ["support_requests.assigned"] };
  },
  {
    event: "keyup change"
  }
);

Allan

th3t1ck

Thank you Colin. A quick scan of the documentation on StateSaveParams looks like it may be what I'm looking for.

th3t1ck

Hi Allan
I'm not so great with Javascript as you have seen. I tried the code you suggested but it still does not work. I get this constant bar graph when the form loads. Any ideas?

th3t1ck

Also can I have dependent() update a text field, readonly type?

allan

Odd. Could you try this please and let me know if it works for you (i.e. no progress indicator)?

editor.dependent(
  "support_requests.user_id",
  function(val, data, callback) {
    editor
      .field("support_requests.assigned")
      .val(data.support_requests.assigned);
 
    callback( val === "A16479"
      ? { hide: ["support_requests.assigned"] }
      : { show: ["support_requests.assigned"] }
    );
  },
  {
    event: "keyup change"
  }
);

And yes, dependent() can manipulate any field type.

Allan

th3t1ck

Progress bar sti;; shows up with the new code.

th3t1ck

I'm getting the following error, "Uncaught TypeError: Cannot read property 'assigned' of undefined"

At this line...
.val(data.support_requests.assigned);

colin

Hi @th3t1ck ,

That one means that data.support_requests is undefined, so can't be deferenced. Try printing it out in console.log() message,

Cheers,

Colin

th3t1ck

So I used the simple example from
https://editor.datatables.net/reference/api/dependent()
and it worked fine.

        editor.dependent( 'support_requests.user_id', function ( val, data, callback ) {
    return val === '8' ?
        { hide: 'support_requests.assigned' } :
        { show: 'support_requests.assigned' };
} );

I also found the the return val was actually the index/id of the user_id not the user_id itself. I still get the progress bar and the hide is not working nor is the assigned field being populated. Here is the modified code.

editor.dependent(
  'support_requests.user_id',
  function(val, data, callback) {
    editor
      .field('support_requests.assigned')
      .val(data.support_requests.assigned);
 
    return val === '8'
      ? { hide: ['support_requests.assigned'] }
      : { show: ['support_requests.assigned'] };
  },
  {
    event: 'keyup change'
  }
);

th3t1ck

But it won't work, still.

th3t1ck

Thanks for the suggestion Colin. I get the same info in the Chrome debugger's console as I received in the Network portion. I think I'm going to need to use ajax to query the database for the user_id and return the full username. I just don't know how the ajax should look like and what I should have in the php page that is called upon. I know how to query the database with php but I'm not sure how to pass the user_id to the php page and pass back the query value to the original page.

allan

Let's aim for that then

editor.dependent(
  'support_requests.user_id',
  function( val, data, callback ) {
    $.ajax( {
      url: '/api/to/lookup/fullname',
      dataType: 'json',
      data: {
        user_id: data.values.support_requests.assign
      },
      success: function ( json ) {
        callback( json );
      }
  }
);

Where json is the data from the server-side in the structure described in dependent() to update the form.

I think the key thing I missed out before (sorry!) was the .values from the data object. Forgot about that part...

Allan

th3t1ck

No problem Allan. Would the ajax file that will do the SQL query need any thing special such as json_decode, json_encode, json stringify, etc.? Do you have an example?

th3t1ck

Allan, Colin and Kevin, Happy Holidays. Thank you for your support and patience. I greatly appreciate your help and datatables.

th3t1ck

So I did a little work on the file called from ajax and it is looking up the user_id and getting the id (assigned field) as I am expecting. I don't know what is being passed to the find_fullname_id.php file and how to return the value that was found in the sql query back to the page calling on the ajax. What value am I doing the json_decode on?
```
<?php

//$user_id = json_decode(); // What variable goes here?
$user_id='A16479'; // manually setting variable for testing

include('../db_connect.php');

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$result= $dbConn->query("SELECT id FROM oesa_users WHERE user_id='A16479'");
$row_cnt = $result->num_rows;

while($row=$result->fetch_assoc()) {
$fullname_id=$row['id'];
// print'"'.$fullname_id.'", ';
}

$id = json_encode($fullname_id); // how is this value returned? What variable name should I use?

<?php > ``` ?>

th3t1ck

So I have it working the way I need it to. I found that the user_id's value was actually the row id. With that I could populate the full name in the table. Here is my code...

editor.dependent(
  'support_requests.user_id',
  function(val, data, callback) {
    editor
    .field( 'support_requests.assigned' ).set( editor.field('support_requests.user_id').val() );
  },
  {
    event: 'keyup change'
  }
);

Thanks for your help guys.

th3t1ck

No need for ajax or a database query since the data is already in the original ajax data returned. So simple.

allan

Excellent - great to hear that you've got it working the way you need. Just to answer this question, if you were to use Ajax:

// how is this value returned? What variable name should I use?

echo it back - e.g.

echo json_encode( ... );

Allan

th3t1ck

Thank you Allan. I'll probably need to call on ajax at some point and this will be good to know.