04
Aug
09

Creating a Selection Drop-down to a SQL Data Source in InfoPath

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.

image

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.

image

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

image

On the next dialog, click Select Database.

image

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

image

Select Microsoft SQL Server.

image

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.

image

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

image

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

image

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

image

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.

image

image

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.

image

image

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

image

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

image

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

image

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.

image

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

image

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

image

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

image

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.

image

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 image on the Default Value field.  Click Insert Field or Group.

image

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!

image

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.

image

image

image

image

Click OK to the final Text Box Properties dialog.

image

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.

image

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.

image

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.

Advertisements

3 Responses to “Creating a Selection Drop-down to a SQL Data Source in InfoPath”


  1. June 2, 2010 at 6:33 am

    I have an InfoPath form that uses a SQL connection in this manner exactly, except that I have a strange issue where my drop down list is populated when I view the form in “Preview” mode from InfoPath, but when I publish my drop down list is blank. Have you come across this?

    • 2 Russell Wright
      June 10, 2010 at 7:28 am

      Sounds like a security problem to me. Try giving the form full trust and see if it works. Then you’ll know you have a security issue.

  2. 3 Sebastian Atar
    June 10, 2010 at 7:52 am

    Agreed. I found that the original data connection had been converted to the Data Connection Library as a UDCX file with another user account. Once I performed these steps as the same user, I was successful in using the data connection both in preview mode and when published to the site. Strange…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

August 2009
M T W T F S S
    Sep »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Support Wikipedia

%d bloggers like this: