Cascading Dropdown List in InfoPath 2010 with SQL Server 2008

Question

I'm starting on some of my first InfoPath forms. I have a three drop down lists (Global Function, Major Task, and Subtask) which need to query a SQL database and filter their data based on the selected value in the drop down list before it.

What I've Done So Far

1. I've already added Data Connections (for each of the tables & corresponding drop down list) which simply query the database for all the records in a table. Each connection is set to "Store a copy of the data in the form template" for Offline Mode.

   Then in the Drop-Down List Box Properties, I've set the List box choices to "Get choices from an external data source" and setup the Entries field to select the columns for the appropriate table / drop-down list combination.

   However, when I add a filter to the data and try to preview the form, the data in the "cascaded" drop down list never changes. It still has ALL of the records.

2. So then I tried to add a new Rule for one of the cascading drop downs. For example, I created a new rule for the Major Task drop down and set it up as follows:

   • Condition to Global Function is not blank
   • Rule type Action
   • Run these actions:
   • Query using a data connection: Major Tasks (which just gets ALL records)

   I don't see anywhere in the Rule / Action to filter the data.

3. EDIT I've just tried a new Data Connection which has all three tables (GlobalFunctions, MajorTasks, and Subtasks) that correctly setup the relations (i.e. GlobalFunctions.Id = MajorTasks.GlobalFunction). Then I set the drop down lists' data source to this new connection. However, this too does not filter the data at all. Their entries and values are as follows:

   • Global Function:
     • Entries: /dfs:myFields/dfs:dataFields/d_1:GlobalFunctions
     • Filter: <None>
     • Value: @Id
     • Display name: @Summary
   • Major task:
     • Entries: /dfs:myFields/dfs:dataFields/d_1:GlobalFunctions/d_1:MajorTasks
     • Filter: GlobalFunction = Id
     • Value: @Id
     • Display name: @Summary
   • Subtask:
     • Entries: /dfs:myFields/dfs:dataFields/d_1:GlobalFunctions/d_1:MajorTasks/d_1:SubTasks
     • Filter: MajorTask = Id
     • Value: @Id
     • Display name: @Summary

Restrictions

• I can't have any code behind (because it won't work in the browser right?)
• The form must be able to be filled out on the Web or on a local machine (a saved copy on a workstation)
• Should still work when there is no connection to the SQL Database

Question

How do I implement multiple cascading drop-down lists in InfoPath 2010 with data connections to a MS SQL Database that can work in the browser and using InfoPath Filler (the latter of which may not have a connection to the database).

SQL Tables

Here's what the tables look like:

CREATE TABLE GlobalFunctions (
    Id SMALLINT NOT NULL IDENTITY(1,1),
    Summary varchar(MAX) NOT NULL UNIQUE,
    PRIMARY KEY (Id)
);

CREATE TABLE MajorTasks (
    Id SMALLINT NOT NULL IDENTITY(1,1),
    Summary varchar(MAX) NOT NULL UNIQUE,
    GlobalFunction SMALLINT NOT NULL,
    PRIMARY KEY (Id),
    FOREIGN KEY (GlobalFunction) REFERENCES GlobalFunctions(Id)
);

CREATE TABLE SubTasks (
    Id SMALLINT NOT NULL IDENTITY(1,1),
    Summary varchar(MAX) NOT NULL UNIQUE,
    MajorTask SMALLINT NOT NULL,
    PRIMARY KEY (Id),
    FOREIGN KEY (MajorTask) REFERENCES SubTasks(Id)
);

Answer 1

OK, I found my solution in this article.

Basically my problem was I was not selecting the correct value (the one that was actually selected) in the previous drop down list when filtering on the current drop down list. Instead, I was selected the value of the option based on the database data.

These are the steps I took to fix my problem (reproduced from the article in case it goes down):

1. Right click the first drop down list and click "Drop-Down List Properties" and setup its data source appropriately.
2. Right click the next drop down list to be cascaded and click "Drop-Down List Properties".
3. Setup the data source appropriately.
4. Still in the second drop-down list, click the "Select XPath" button next to the "Entries" box.
5. Click "Filter Data", then Add.
6. In the first drop down box, select "Select a field or group...".
7. In the "Data Source" drop down list, select "Main".
8. Select the "Global Function" field, the first in the drop down series (may be under "myFields" then "dataFields").
9. Set your equality operator in the middle drop down list.
10. In the last drop down, select "Select a field or group...".
11. Not changing the data source, under MajorTasks select :GlobalFunction (the FK relationship to the Global Function table).
12. OK out of all dialogue boxes and repeat for the Subtask drop down list (the last in the series)