If you want to populate controls in an infopath form based on a selection in a control that looks up a record in an external source, then read on.

I was recently asked by a client to:

1. allow users to select from a drop down a record from an external source (database, sharepoint list, web service)

2. When the item is selected, then other fields in the same row in the grid would need to be populated with information from the master list. See picture below. In this instance, selecting the prod code drop down then populates the description and the cost columns.

so, how do you do this?

The steps in general are (in addition to creating the main data source):

1. Create a secondary source to get the lookup list values

2. Create a rule on the prod code field to populate the description and cost fields.

Creating the secondary source and set the drop down values

In my scenario, I used a SharePoint list to make it easier but it can be a web service or a database.

a. Go to the drop down field and right-click Properties.

b. Click on Add... then SElect "Create a new connection to" and select "Receive data"

c. Select "Sharepoint library or list"

d. Specify the URL of the list where the items are stored.

e. Set the Value field to the UNIQUE identifier of the list and the Display Name to another field (usually a Friendly name, description or title of a row)

Set the field rules

Once this is done, then you need to set the rules of the field to populate the adjacent ones.

1. Right click on the ProdCode field and select Rules (alternatively from the properties dialog, click Rules...)

2. Click on Add Action...

3. Set Condition...

4. Click on Add Action..., then select "Set a field's value. Select the @ProdDescription from the Main source

5. Click on the Value button, Select "Insert Field or Group", from the data source drop down select the secondary source. then select the field that you want to RETRIEVE from the selected record.

6. this next step is to make sure that we get the right value, otherwise you will be retrieving the first item in the list everytime. This is how we lookup the row. So click on "Filter Data...", then Add...

Select the UNIQUE identifier from the left drop down (in my case ItemNo), then is equal to and then "Select a field or group" from the last drop down

In this dialog you need to select the value from the "Main" datasource that corresponds to the lookup. In my case, it is the ProdCode item in a repeating group.

When you click ok you should see in the Existing filters dialog: ItemNo=current(). and in the final action dialog the followig values:

When you save the form and then publish it you will see how the fields are populated based on the selection from a drop down.