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

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

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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.

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
      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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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?

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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'
      }
    );
    
  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    But it won't work, still.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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?

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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?
    ?>
    
  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    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.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

This discussion has been closed.