Are you a user?

Here’s a method you can use to allow a SQL table in an InfoPath form to be the source of the data in a drop-down list.  This example uses the Adventure Works database.

The result of this example is a drop-down list of employees which, when one is selected, populates the First Name and Last Name fields on the form.

Thanks to Fidel for getting me to post this!

Create the Test Form

Create a new form template in InfoPath.  On the blank template, place three controls:  a drop-down list and two text boxes.  Set the name of the drop-down list to EmpId and the names of the text boxes to LastName and FirstName.

Create a Data Connection

Select Tools | Data Connections… from the InfoPath menu.  Click the Add button and add a new data connection to Receive data.

Select Database (Microsoft SQL Server or Microsoft Office Access only).

On the next dialog, click Select Database.

From here we will create a new data source.  Click the New Source button.

Select Microsoft SQL Server.

Enter your SQL Server name and the authentication type you want to use.  You’ll have to give this some thought, as you need to insure that the person filling out the form has the necessary security to access your database.

Select the database and table you want to use.  In this example, we are using the AdventureWorksDW database and the DimEmployee table.

Provide a name for the .odc file, a description and friendly name.

You should now have a connection to the DimEmployee table.  For this example, click on the Edit SQL button.

We’re going to create a concatenated field we can use in our drop-down list that contains the LastName and FirstName fields in the form LastName, FirstName.  After the last field in the select statement add:  LastName + ‘, ‘ + FirstName as ListBoxName and click Test SQL Statement to make sure you don’t have any errors.

Click OK to dismiss the Edit SQL dialog and you’ll receive the message “The SQL statement you entered cannot be represented as a tree view.  Do you want to continue?”  Click Yes, as this is simply informing you that you’ve created a SQL statement that won’t be displayed in the in the Data Connection Wizard dialog.

Continue to click Next and close the dialogs.  Be brave and and click Yes to all the warnings!

Provide a name for the data source.  How about DimEmployee SQL Data Source?

Click Finish and you should now have a new SQL data connection.  You can close the Data Connections dialog.

Bind the Fields on the Form to the Data Source

Double-click on the EmpId drop-down to display its properties and select Look up values from an external data source.  Make sure that your DimEmployee data source is selected if it isn’t automatically selected for you.

Click the Entries button and select the d:DimEmployee node and click OK.

For the Value, select the EmployeeKey field and click OK.

For the Display name, select the ListBoxName field and click OK.

Click OK on the Drop-Down List Box Properties dialog.

Test the First Part of the Form

You can now test the first part of the form by Previewing the form.  You should see the drop-down list is populated with the last and first names of all the employees.

Close your preview and move on to the next part of the form design.

Set the Values of FirstName and LastName Fields

Double-click on the FirstName field to display its properties.  Click the data binding button on the Default Value field.  Click Insert Field or Group.

In the Select a Field or Group dialog, change the data source to be your SQL connection and then navigate to the FirstName field.  Don’t click OK yet!

Click on the Filter Data button and Add a new filter.  Select EmployeeKey as the first field and “is equal to” as the comparison.  For the comparison field click “Select a field or group” and change the data source to the Main data source.  Select EmpId as the field to compare against.  Click OK a bunch of times.  Here are the myriad of dialogs you’ll see.

Click OK to the final Text Box Properties dialog.

Perform the same binding routine for the LastName field.  Again, you are specifying a default value that comes from the SQL connection and is filtered by the EmployeeKey.

Test the Final Solution

Now you can do a preview of the form and check out how it works.  You should get the list of employees in the drop-down list and, when you select one, it should populate the FirstName and LastName fields.

You can, of course, make these fields read only so the end user can’t change them and then use them in whatever rules and other business logic you create for your form.