How to link data lookup dropdowns

[Smonczka]

I have created an employee information form that allows HR to input
information about a new employee or look up information on existing
employees. The data is bound to a SQL back end. At the top of the
form I have two dropdown list boxes. These are linked to the First
Name and Last Name columns in my tables. So to look up an employee's
information you just pull the First Name and then the Last Name from
the dropdown list and hit Query.

What I would like to do is link the data in the dropdown lists so the
first name is linked to the last name. So if you look up the first
name from dropdown list one, say Bob, then the information listed in
dropdown list two (Last Name) would only be for those employees first
name is Bob. At the moment the way I have it set up it returns a value
of All Employees last names.

Is this at all possible?

Thanks,
Steve Monczka
(e-mail address removed)

 

[Scott L. Heim [MSFT]]

Hi Steve,

Here are some sample steps you can test using the Northwind sample database
on SQL Server:

1) Create a new, blank InfoPath form
2) Add 2 drop-down boxes to the form
3) From the Tools menu, choose Data Connections and then add a new
connection to the Employees table in the Northwind database
4) Right-click on the first drop-down box, choose Drop-down Properties and
set the following:

- Data Connection: Employees
- Entries: click the builder button and select the Employees node
- Value: FirstName
- Display Name: FirstName

5) While still on the Properties box, click the Rules button, click Add,
click Add Action and complete the following:

- Action: Set a Fields Value
- Field: Click the builder button and choose the 2nd drop-down box (the
one for the Last Name)
- Value: Leave this empty
- Click OK 3 times to get back to Properties and then close out of the
Properties window

6) Right-click on the second drop-down box, choose Drop-down Properties and
set the following:

- Data Connection: Employees
- Entries: Click the builder button, choose Employees and then click the
"Filter Data" button
- Click Add
- First drop-down box: click Select a Field or Group, select the Employees
secondary data source and select FirstName
- Second drop-down box: leave this as "is equal to"
- Third drop-down box: click Select a Field or Group, select your Main
data source and select the first drop-down box from your form
- Click OK until you are back to the Properties window
- Value: LastName
- Display: LastName

Preview the form and test - in this sample case, each time you select a
first name there should only be one last name in the second box!

I hope this helps!
Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Smonczka]

Scott,

Thanks time you took to send me the detailed step by step. It did what
i wanted but left me with another problem. The second dropdown list
(Last Name), the data in it does not refresh when I go back to the
first dropdown list and select another first name.

From the first drop down list I pull the FName. After going through

the steps you outlined I now see only the LName in the second dropdown
list. I go back to the first box and pull down a new FName. The
dropdown for the second box now has the new LName in it but ALSO
contains the last LName that I looked up.

Did I do something wrong or do you have to refresh the page first?

Thanks very much for your help.

Steve Monczka
(e-mail address removed)

 

[Scott L. Heim [MSFT]]

Hi Steve,

Sorry for the delay as I have been out of the office.

Please forgive me for being so "granular" with the question but did you
complete step #5 in the steps I provided? This is the one that should clear
out the 2nd drop-down list.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.