Using the Form Designer › How to Use User Input to Pre-Populate a Select Box

How to Use User Input to Pre-Populate a Select Box

You can use input provided by the user in one or more fields in the form to determine the values in a select box. This technique is useful when you want to provide the user with two sets of multiple valid choices from your data source but do not want to enable the user to specify a custom selection. This option thus helps enforce the standardization and validity of users' selections while still typically providing users with multiple options from which to choose.

A common application of this technique occurs on a form with select boxes for both country and state. Using the reportobjid and reportobjvars attributes, you can configure the state select box to display only the states for the country selected by the user.

To use this technique of pre-populating a select box based on a user's selection in an earlier select box, follow this process. This process uses the country-state scenario as a running example.

1. In the Report Builder, create a data object that queries the data source (such as the MDB) for the data you want in the first select box. The query must return the list of all possible values and populate the combo box with this list. This step is part of the process of pre-populating a combo box based on a report data object.

   In our running example, create a report data object to retrieve the list of countries from the database.

   When the form appears, the report data object runs and populates the combo box with the resulting data.

   Record the ID of the data object for later reference.

   Consider the following sample query for a data object for the MDB. This example returns the list of available countries for the service or service option to which the form is attached

   SELECT country_id,country_name from my_country_table
   

   This query does the following:

   • "SELECT " specifies which values to return. In this example, it returns the ID and the name of the country in the database.
   • "FROM my_country_table" specifies the name of the database table.
2. On the form, add the first select box; it is one of the elements of a form.
   1. For the _id attribute, specify a meaningful value, and save the form. Record the value for later reference when you specify the reportobjvars attribute.
   2. For the value of the HTML Attribute named reportobjid, specify the id of the data object, and save the form.

   Note: When you use a data object to populate a combo box, do not add any options to the select box, because they are ignored (not used) when the user opens the form. Any options for the select box are ignored, only the data object "matters."

   1. For the multiple attribute of the select box, specify False so that users can select only one option in the combo box. Save the form.
   2. For the title attribute (tool tip text), optionally specify instructional text, such as "Click the arrow and scroll to select a country." Save the form.
   3. Optionally rename the default display text from the select group from "Select" to a more meaningful name, by selecting the element in the Component Tree and clicking the Rename icon at the top of the tree.

      Note: You can optionally localize the tool tip text or the name of the select box if users from different locales use this form.

   4. Leave the value attribute empty. This value is populated with the first result returned by the query.
3. Create a second report data object to retrieve a list of values from the database, based on the user's selection in the first select box. In this report data object, specify some report variables in the query that are to be filled using the input provided by user in other fields in the form

   In our running example, create a second report data object to retrieve the states from the database, based on the country that the user selected. Follow the guidelines in Step 1 to create the second report data object.

4. On the form, add the second select box. This box is to be pre-populated, based on the user's selection for the first select box. Follow the guidelines in Step 2 to create the second select box, but account for the following considerations:
   • The second query contains a report variable and is similar to the following:

         select state_id,state_name from my_state_table where country_id=%selected_country%
     

   • The running example in this topic uses only one report variable (%selected_country%); however, your query may contain multiple report variables.
   • Record the names of all report variables for use when you specify the reportobjvars attribute for the second select box.
5. In the second select box, specify the reportobjvars attribute using the following format: $({'reportvar':value}).

   A valid value is one of the following:

   • A constant, such as 12, as in $({'reportvar':12})
   • A string, such as abc, as in $({'reportvar':'abc'})

     Note: Enclose strings in single quotation marks, as shown. Within a quoted string, if needed, use a backslash (\) as an escape character to specify a literal single quote or apostrophe. For example: $({‘what\’s the status?’})

   • A JavaScript expression, such as the user's last name, as in $({'reportvar':_.user.lastName})
   • A JavaScript function of the following form: $({'reportvar':foo()})
   • Combinations of the previous values, delimited with commas, using the format shown in the following example:

     $({'reportvar':_.user.lastName,’reportvar1’:’abc’,’reportvar2’:12,’reportvar3’:foo()})
     

   In our running example, create a second select box to be populated with the states from the database, based on the country that the user selected. For the reportobjvars attribute of the state select box, specify the pre-defined JavaScript function named ca_fdGetSelectedOptionValues, as follows:

   $({'selected_country':ca_fdGetSelectedOptionValues ('<form _id> ','country' )[0]})
   

   form _id

   Specifies the value of the _id attribute of the form containing the first select box. You reference this form in Step 2.

   country

   Specifies the value of the _id attribute of the first select box. You create and record this value in Step 2.

6. In the first select box, set the onchange attribute to retrieve the data for the second select box as soon as the user makes a selection in the first select box. For the onchange attribute, specify the pre-defined JavaScript function named ca_fdFetchSelectData, using the following format:

   ca_fdFetchSelectData('<form _id>','<field _id>'); 
   

   form _id

   Specifies the value of the _id attribute of the form containing the second select box. You reference this form in Step 4.

   field_id

   Specifies the value of _id attribute of the form containing the second select box. You create and record this value in Step 4.

   In our running example, set the onchange attribute for the country select box to the following:

   ca_fdFetchSelectData('<form _id>','<state field _id>'); 
   

7. Test the form to verify that it works as you intended.

As a best practice, test this form with a service in a test environment before you use them in a production environment.