Populate second dropdown based on the value selected in the first dropdown in flask using ajax and jQuery

Question

I am not getting anywhere on this. Any help regarding this would be greatly appreciated!

I have two dropdowns for Managers and Employees.

Managers dropdown contains a list of Managers by default.

I want to populate Employees dropdown with the employees name by querying a SQL Server database with the Manager name a user selects in Managers dropdown.

So for e.g. if a person selects Tom as the Manager in the Managers dropdown then the Employees dropdown should populate with the Employees name where Manager = Tom.

So far I have the following code:

Route (which I am using to query SQL Server database to get employees list based on a manager name):

@app.route ('/getEmployees', methods=['GET'])
def getEmployees():
    engine = create_engine('mssql+pyodbc://<server name>/<DB name>?driver=SQL+Server+Native+Client+11.0')
    Base = declarative_base(engine)

    class Bookmarks(Base):
        __tablename__ = '<table name>'
        Employee = Column(String(50))
        __table_args__ = {'autoload': True}

        def __repr__(self):
            return '{} '.format(self.Employee)

    def loadSession():
        metadata = Base.metadata
        Session = sessionmaker(bind=engine)
        session = Session()
        return session
    if __name__ == "__main__":
        session = loadSession()
        return session.query(Bookmarks).filter_by(Manager='<I want to populate this with the manager name a user selects in the Manager dropdown>')

Managers dropdown in index.html

<div class="form-group">
  <select class="form-control" id="next" name="division" data-error="Required!" required>
    <option value="default" disabled selected>Select something</option>
    <option value="option1">Tom</option>
    <option value="option2">Bob</option>
  </select>  
</div>

Employees dropdown in app.py

Employees = QuerySelectField('Employees', query_factory=getEmployees, allow_blank=True)

In index.html

<div class="form-group" id="sel_user">
    {{ render_field(form.Employees,class="form-control", required="required") }}
</div>

I am using jQuery with ajax to get the Manager name a user selects in the manager dropdown and then do something with it..

$(document).ready(function(){

    $("#next").change(function() {

        var Manager=($('#next option:selected').text());
        console.log(Manager);

        $.ajax({
            url: "/getEmployees",
            type: 'GET',
            contentType: 'application/json',
            dataType: 'json',
            // not sure what to do next..?
        });
    });
});

Can anyone please help as what I should do next once I have the value a user selects in Managers dropdown?

Answer 1

Using AJAX/Jquery

You can do something like this,

You have two dropdown selects, one as the manager select and the second is the employee select , the employee select dropdown values depends on the manager select

1st select dropdown,

<select id="manager_select">
</select>

<select id="employee_select">
</select>

/* This will populate your managerselect */

$.ajax({
    url: 'url that will display all list'
    dataType: 'json',
    type: 'get',
    success: function(data){
      var output = "";
       $.each(data, function(a,b){
          output += "<option value='"+b.id+"'>"+b.manager_select_name+"  </option>"
        $("#manager_select").append(output);
       });
    }
})

/* This will populate your employee select upon changing the values of your manager*/

$(document).on("change", "#manager_select", function(){ var manager_id = $(this).attr("id");

 $.ajax({
     url: 'url that will display employee data where manager_id = manager_id ',
     type: 'POST',
     dataType: 'json',
     data: { 'manager_id' : manager_id } /* you are passing manager_id*/
     success: function(data){
          $("#employee_select").empty();
          var output = "";
          $.each(data, function(a,b){
              output += "<option id="+b.employee_id+">"+b.employee_desc+"</option>";
          })
          $("#employee_select").append(output);
     }
 })

})

Answer 2

I think you're almost there. I don't know python or flask, but I can give you the main idea.

1. When you select the manager, you get the manager name and you have to send that name in the ajax call, so you can get it in your Route code and use it to filter the array. You can send that value using the data parameter of the ajax call...

   $(document).ready(function(){
   
       $("select#next").change(function() {
   
           var managerName = $(this).find('option:selected').text();
   
           $.ajax({
               type: 'GET',
               url: "/getEmployees",
               data: { manager: managerName }
               contentType: 'application/json',
               dataType: 'json'
           });
       });
   });
   

2. In your ajax call, create a success callback function that will be called when you receive a successful response. Something like this...

   $(document).ready(function(){
   
       $("select#next").change(function() {
   
           var managerName = $(this).find('option:selected').text();
   
           $.ajax({
               type: 'GET',
               url: "/getEmployees",,
               data: { manager: managerName }
               contentType: 'application/json',
               dataType: 'json',
               success: function(response) {
               }
           });
       });
   });
   

3. You could also add a check to verify if you've selected manager or unselected. In case you unselect manager, you have can empty the employee select, disable it, show all employees, or whatever you want.

   $(document).ready(function(){
   
       $("select#next").change(function() {
   
           if ($(this).val() != 'default') {
   
               var managerName = $(this).find('option:selected').text();
   
               $.ajax({
                   type: 'GET',
                   url: "/getEmployees",,
                   data: { manager: managerName }
                   contentType: 'application/json',
                   dataType: 'json',
                   success: function(response) {
                   }
               });
           }
           else {  // No manager selected.
               // Do what you wnat when there's no manager selected.
               // For example, if you'd want to empty and disable the employees select...
               $('select#sel_user').html('').prop('disabled',true);
           }
       });
   });
   

Now is where I have problems to help you, because of my lack of knowledge of python/flask:

• In your Route code, you have to read the manager parameter sended with the GET ajax call. I don't know how you do that in python/flask but it has to be easy. In php would be just $_GET['manager']. With a quick search, it looks like it could be something like request.GET['username'], but you'll know it way better than me. You have to get that parameter and put that value in the last return line, something like...

  return session.query(Bookmarks).filter_by(Manager=request.GET['username'])
  

• Is not clear to me the format of this response, so I don't know how to extract the info to create the employees select. Looking at your ajax call, you say the response is in JSON format, but I would need to see an example of that response to shw you the exact code. The idea is that you have to get that info and create the options in the success function of the ajax call, and then put that options in the employees select. Something like...

  // Imagine that your response is an array of objects similar to this
  // [{"name": "Tommy", "other": "value10"},{"name": "Jim", "other": "value32"},...]
  
  success: function(response) {
  
      var options = [];
      for (var i=0, l=response.length; i<l; i++)
          options.push('<options value="'+response[i].other+'">'+response[i].name+'<options>');
  
      $('select#sel_user').html(options.join(''));
  }
  

I think with all this you can have an idea of how to proceed, adapting it to your specific needs. I hope it helps.