How To: Dynamically populating a drop down control

Many times during the development of a form it may be necessary to dynamically populate a drop down control based on a selected value. Dynamically loading a drop down based on a selected value takes advantage of the Secondary Data Sources function of InfoPath. For this example, we are creating a form that provides the user with a list of states. Once they select a specific state, a second drop down of cities within that state is loaded.

If we start with a form that contains the following two text fields:

As text fields these can be changed into drop down lists and then placed in a form that looks like the following.

Once the graphical portion of the form is created we can then start to wire up the data. This is done in two steps.

Populating State Dropdown

1. Within the properties page of the States drop down create a secondary data source (called States) that contains the follows SQL statement.

select distinct [StateName] from [States] as [States]

Note: Ignore any errors that you may receive during the creation of the data source. These errors are merely warnings about data representation. The ADO Adapter is unable to adequately represent the schema within an XSD.

2. Populate the data source for the States as follows:

Populating the Cities Lookup

1. Create a Secondary data source for the Cities field (called Cities) that contains the following SQL Statement

select [StateID],[CityName] from [States] as [States]

2. When creating the Data source make sure that you select not to load the data into the drop down on form load as shown below. This will ensure that the user has to select a value from the States field before these values are populated.

3. Within the OnAfterChange Event of the States field enter the following code.

var originalSQLQuery1 = "";

function msoxd_my_States::OnAfterChange(eventObj)

{

// Write code here to restore the global state.

if (eventObj.IsUndoRedo)

{

// An undo or redo operation has occurred and the DOM is read-only.

return;

}

// A field change has occurred and the DOM is writable. Write code here to respond to the changes.

//First, check to make sure we've cached the original query. We'll need it if the user

//changes the listbox selection later

if (originalSQLQuery1 == "")

{

originalSQLQuery1 = XDocument.DataObjects("Cities").QueryAdapter.Command;

}

//Get the current value of the resource dropdown.

var StateValue = eventObj.Source.text

//Construct a new command for the second dropdown listbox using a Where clause...

XDocument.DataObjects("Cities").QueryAdapter.Command = originalSQLQuery1 +

" WHERE StateName='" + StateValue + "'"

//Requery the data source

XDocument.DataObjects("Cities").Query();

//Force the view to refresh

XDocument.View.ForceUpdate();

}

As you test the form you will see that during the form load the user is given a list of States in the States drop down. Once they select a particular state the OnAfterChange event is run. This event, requeries the Cities data source with the selected State value. Once the query is run the returned City values are then populated into the Cities drop down. This sample is available for download from here.