Dynamically populate a form field based on a previous user entry

Question

I have an application that is using Flask and wtforms and part of the functionality is to take user input for the first two fields and populate the remainder of the form fields which are of type SelectMultipleField (I'm going to refer to these as select fields) with choices from a database based on the first two fields (I'm going to refer to these as entry fields).

My issue right now is getting the select fields to dynamically populate. I found a solution here and this seems to be exactly what I need. It instantiates the select fields to all possible choices and then when it detects a JQuery "onchange" event in the entry fields, filters the select fields to choices based on the user entry for the entry fields. An example would be a user entering a specific company into the form and the select fields populating with "locations" only for that company.

However, in adapting this solution to my problem, I have not been able to get the code to run and I have researched far and wide and unable to resolve this. I'm new to both JQuery and Stack Overflow so any help would be greatly appreciated. Below is my code. Note that I am only focusing on one of the entry fields and dynamically populating just one of the select fields until I get this to work. Test_table is the entry field and test_join_key is the select field.

Here's the form with relevant fields-

class QaForm(FlaskForm):
test_table_in = StringField('Test Table', validators=[DataRequired()], id= 'test_table')
test_join_key = SelectMultipleField("Select Test Fields to Join on", choices=[], coerce=str, id = 'select_test_join_key')

Flask view to instantiate all the select fields -

@app.route('/', methods = ['GET', 'POST'])
@app.route('/home', methods = ['GET', 'POST'])
def home():
    form = QaForm()

    fields_query = f"""select column_name AS Fields from information_schema.columns group by 1;"""
    conn.execute(fields_query)
    result = conn.fetchall()
    select_choices = [(column, column) for column in result]

    form.test_join_key.choices = select_choices

Flask view to get choices for select fields based on user input for entry field -

@app.route('/_get_fields/<table>')
def _get_fields(table):
    table = request.args.get(table, type=str)
    fields_query = f"""select column_name AS Fields from information_schema.columns WHERE table_name = '{table}' group by 1;"""
    conn.execute(fields_query)
    result = conn.fetchall()
    select_choices = [(column, column) for column in result]
return jsonify(select_choices)

JQuery to detect input in entry field and filter choices for select field (injected in HTML file)-

<script charset="utf-8" type="text/javascript">
         $function() {
            var dropdown = {
                test_table: $('#test_table')
                test_join_key: $('#select_test_join_key')
            }
            updateFields();

            function updateFields() {
                var send = {
                    test_table: dropdown.test_table.val()
                };
                dropdown.test_join_key.attr('disabled', 'disabled');
                dropdown.test_join_key.empty();
                $.getJSON("{{url_for('_get_fields') }}", send, function(data) {
                    data.forEach(function(item) {
                        dropdown.test_join_key.append(
                            $('<option>', {
                                value: item[0],
                                text: item[1]
                            })
                        );
                    });
                    dropdown.test_join_key.removeAttr('disabled');
                });
            }
            dropdown.test_table.on('change', function() {
                updateFields();
            });
         });
     </script>

EDIT: Using @Ibsn suggestions, I was able to get the JQuery snippet to run for one form field. However, updating it to perform the same actions for multiple fields using parameters for the function again results in the code not running. I've checked to make sure my syntax is correct based on the tutorial on W3 schools as well as other Stack Overflow questions but still unable to get it to run. Here's the updated Jquery to detect input in entry fields and filter choices for select fields -

<script charset="utf-8" type="text/javascript">
         $(function() {
            var tables = {
                test_table: $('#test_table'),
                prod_table: $('#prod_table')
            };

            var fields = {
                test_join_key: $('#select_test_join_key'),
                prod_join_key: $('#select_prod_join_key'),
                test_dimensions: $('#select_test_dimensions'),
                prod_dimensions: $('#select_prod_dimensions'),
                test_measures: $('#select_test_measures'),
                prod_measures: $('#select_prod_measures')
            };
            updateFields(table, field);

            function updateFields(table, field) {
                var send = {
                    table: tables.table.val()
                };
                fields.field.attr('disabled', 'disabled');
                fields.field.empty();
                $.getJSON("{{url_for('_get_fields') }}", send, function(data) {
                    data.forEach(function(item) {
                        fields.field.append(
                            $('<option>', {
                                value: item[1],
                                text: item[0]
                            })
                        );
                    });
                    fields.field.removeAttr('disabled');
                });
            }
            tables.test_table.on('change', function() {
                updateFields(tables.test_table, fields.test_join_key);
                updateFields(tables.test_table, fields.test_dimensions);
                updateFields(tables.test_table, fields.test_measures);
            });
            tables.prod_table.on('change', function() {
                updateFields(tables.prod_table, fields.prod_join_key);
                updateFields(tables.prod_table, fields.prod_dimensions);
                updateFields(tables.prod_table, fields.prod_measures);
            });
         });

lbsn · Accepted Answer · 2021-05-07T08:15:44.387

There are a couple of syntax errors in your code.

$function() {} should be $(function(){}). And you're missing the comma between properties on var dropdown = {}

This is the updated version:

<script charset="utf-8" type="text/javascript">
     $(function(){
         var dropdown = {
             test_table: $('#test_table'),
             test_join_key: $('#select_test_join_key')
         }
         updateFields();
    
         function updateFields() {
             var send = {
                test_table: dropdown.test_table.val()
             };
             dropdown.test_join_key.attr('disabled', 'disabled');
             dropdown.test_join_key.empty();
             $.getJSON("{{url_for('_get_fields') }}", send, function(data) {
                  data.forEach(function(item) {
                     dropdown.test_join_key.append(
                         $('<option>', {
                             value: item[0],
                             text: item[1]
                          })
                      );
                  });
                  dropdown.test_join_key.removeAttr('disabled');
             });
         }
         dropdown.test_table.on('change', function() {
             updateFields();
         });
   });

The OP updated the question with new requirements

If I understand correctly, you're trying to update all the test_ fields when test_table changes and all the prod_ fields when prod_table changes.

So this code should do that:

$(function () {
    var tables = {
        test_table: $('#test_table'),
        prod_table: $('#prod_table')
    };

    // I'm organizing fields in two arrays, test and prod, for simplyfing iterate over each group
    var fields = {
        test: [$('#select_test_join_key'), $('#select_test_dimensions'), $('#select_test_measures')],
        prod: [$('#select_prod_join_key'), $('#select_prod_dimensions'), $('#select_prod_measures')]
    };

    // This is for updating fields the first time
    fields.test.forEach(item => updateFields(tables.test_table, item));
    fields.prod.forEach(item => updateFields(tables.prod_table, item));

    function updateFields(table, field) {
        var send = {
            table: table.val()
        };
        field.attr('disabled', 'disabled');
        field.empty();
        $.getJSON("{{url_for('_get_fields') }}", send, function (data) {
            data.forEach(function (item) {
                field.append(
                    $('<option>', {
                        value: item[0],
                        text: item[1]
                    })
                );
            });
            field.removeAttr('disabled');
        });
    }

    // Test fields and prod fields are two arrays now, so I can simply iterate through them
    tables.test_table.on('change', function () {
        fields.test.forEach(item => updateFields(tables.test_table, item));
    });
    tables.prod_table.on('change', function () {
        fields.prod.forEach(item => updateFields(tables.prod_table, item));
    });
});

@Ibsn Thanks so much for this! It actually got my code to run. However, when I update the JQuery bit to handle multiple fields instead of just one, it fails again. I've updated the function to have parameters and then I pass in the fields to be updated as arguments to the function. I've edited the questions with this new code. Any thoughts? — user5394117, May 06 '21 at 15:47
It is not completely clear to me what's your new requirement. How `tables` and `fields` relate to each other? I strongly suggest you post a new question about this, possibly with a simple working stackblitz. — lbsn, May 07 '21 at 07:48
Anyway, your syntax is again incorrect. You're defining two parameters on `updateFields()`: `table` and `field`. But you're not using them at all in the function body. And the first time you're calling the function like this: `updateFields(table, filed)`. Where are those two params defined that you're trying to pass to the function? — lbsn, May 07 '21 at 07:52
@Ibsn, yes that is what I was trying to do. Thanks much! Follow up question- does the first call to updateFields() update the fields with nothing since the user has not yet entered anything into the form? — user5394117, May 11 '21 at 07:06
It will invoke `$.getJSON()` passing the `send` parameter as empty string. Then it will update the fields with whatever the response is. Since the `send` param gets appended as a query string to URL, the result depends on how your API handles the case of empty query string. — lbsn, May 11 '21 at 12:28
@Ibsn I have a Flask view that supposed to the take the table name passed from JQuery and retrieve data from the database for that table. I use the request.args.get() function to get the table name from JQuery. Do you know how the variables from JQuery should be called since "tables" has two subvariables? It's failing to pick up anything when I call just "tables". This is the bit in the Flask view to handle it : table = request.args.get(table, type=str) — user5394117, May 11 '21 at 21:12
Sorry, I don't have any experience with Flask. But I don't think the issue here is how to name the variable coming from jQuey. The first time `updateFields()` gets called that variable will be empty, no matter how it's named, because the user has not yet selected anything. The point is how to handle this case. An option would be to provide a default value for `send` directly in `updateFields()`. Quite sure you can do the same in your Flask view. I suggest you accept the answer here and post a new question for this specific issue. — lbsn, May 12 '21 at 07:21

Dynamically populate a form field based on a previous user entry

1 Answers1