How To: Populate Items from a Secondary Data Source

When designing a form within InfoPath a form designer may want to populate additional controls on a form based on a selected value in a drop down list. When a value is selected in a drop down, this value is then re-used as part of a database call (or Web service) to return additional data used to load other controls within the form. Using InfoPath this can easily be done using a secondary data source within the current form.

For an example, let’s create a company lookup form that is based on a single Access database table. The company name is used to populate a drop down list box control. The Access table contains the following columns.

Within the InfoPath form, define a data source that contains the following string based values.

Drag the CompanyAddress field onto the form and change it to a drop down list box. This will change the appearance to the following.

Drag the rest of the InfoPath data source values onto the form into a table as shown below.

Drop down list boxes can easily be populated using an InfoPath secondary data source from either a Web Service or database. For this example, we will use the Access table that was created. Create a secondary data source called “CompanyName” that points to the CompanyName drop down list box.

Using the following steps design the rest of the form. When a user selects a company name from the drop down list box in the CompanyName, this value is then appended to a database query and the return values are then used to populate the remaining fields in the form.

1. Create a new secondary data source called CompanyLookup that points to the Company table as shown below.

2. Create a global variable to catch the SQL statement

var originalSQLQuery1 = "";

3. In the OnAfterChange event of the Company drop down place the following code. This code is responsible for capturing the selected values and then updating the underlying nodes and populating the rest of the form based on a SQL “where” statement.

// 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("CompanyLookup").QueryAdapter.Command;

}

//Get the current value of the resource dropdown.

var resourceValue = eventObj.Source.text

//Construct a new command for the second dropdown listbox using a

//Where clause...

XDocument.DataObjects("CompanyLookup").QueryAdapter.Command =

originalSQLQuery1 +" WHERE CompanyName='" + resourceValue + "'"

//Requery the data source

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

//Set the "SelectNamespaces" property so we can do selectNodes

//calls on the resources DOM

XDocument.DataObjects("CompanyLookup").DOM.setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" ' );

//Get a reference to the list of resources

var objCompanyInfo = XDocument.DataObjects("CompanyLookup").DOM.selectNodes("/dfs:myFields/dfs:dataFields/d:Company" );

for (i=0; i < objCompanyInfo.length; i++)

{

//update the other values

var companyName = objCompanyInfo.item(i).selectSingleNode( "@CompanyName" ).text;

var companyAddress = objCompanyInfo.item(i).selectSingleNode( "@CompanyAddress" ).text;

var companyCity= objCompanyInfo.item(i).selectSingleNode( "@CompanyCity" ).text;

var companyState= objCompanyInfo.item(i).selectSingleNode( "@CompanyState" ).text;

var companyZip= objCompanyInfo.item(i).selectSingleNode( "@CompanyZip" ).text;

}

// update data fields

XDocument.DOM.selectSingleNode("/my:myFields/my:CompanyName").text = companyName

XDocument.DOM.selectSingleNode("/my:myFields/my:CompanyAddress").text = companyAddress

XDocument.DOM.selectSingleNode("/my:myFields/my:CompanyCity").text = companyCity

XDocument.DOM.selectSingleNode("/my:myFields/my:CompanyState").text = companyState

XDocument.DOM.selectSingleNode("/my:myFields/my:CompanyZip").text = companyZip

//Force the view to refresh

XDocument.View.ForceUpdate();

Secondary data sources provide some great feature and functionality that you can use within your applications. Just as I used a local Access table, this could just as easily have been a Web service. I have made this sample available for download so that you can take a look at it.