Cascading Dropdown - Three Level

Teddybeardog — Mon, 04 May 2015 15:42:39 GMT

I'm new to InfoPath so feel free to impart knowledge where I'm obviously missing something.

My environment:
InfoPath Designer 2013
Internet Explorer 9
SharePoint 2010

I am modifying a SharePoint form/new InfoPath form (Within SP click on Customize Form). I need 3 cascading dropdowns - Network, Category, Issue. Once you select a network, then the appropriate values for category should populate. Then once you select category, the appropriate issues should validate.

Because the values may change in the future and management would want to use historical data to support budget increases, etc, I set up 3 lists as such:

Network (Title)	Deactivated	Active
N/W A	No	N/W A
N/W B		N/W B
N/W C		N/W C
Test	Yes

Active is a calculated column (Data Type:Single Line of Text) --> =IF(Deactivated,"",Network).
Deactivated is a Yes/No column checkbox.
Network is a lookup column to the "Network" list, "Network (Title)" column.

Category (Title)	Network	Deactivated	Active
Accounts	N/W A		Accounts
Network	N/W A		Network
Workstation/Printer	N/W A		Workstation/Printer
Accounts	N/W B		Accounts
Server	N/W B		Server
Workstation/Printer	N/W B		Workstation/Printer
Test	N/W B	Yes

Network is a lookup column to the "Network" list, "Network (Title)" column.
Category is a lookup column to the "Category" list, "Category (Title)" column.

Issue(Title)	Category	Network	Deactivated	Active
Add User	Accounts	N/W A		Add User
Create Group	Accounts	N/W A		Create Group
Add Drop	Workstation/Printer	N/W A		Add Drop
Install	Workstation/Printer	N/W A		Install
Add User	Accounts	N/W B		Add User
Create Group	Accounts	N/W B		Create Group
Add Drop	Workstation/Printer	N/W B		Add Drop
Install	Workstation/Printer	N/W B		Install
Test 1	Workstation/Printer	N/W A	Yes
Test 2	Workstation/Printer	N/W B	Yes

So I created three data connections - NetworkLookup2, CategoryLookup2, and IssueLookup2. Did this because I needed to filter on the Active column and the original data connection for the list didn't have the Active field. The first dropdown is set up under Dropdown List Box Properties as follows:

Data Source: NetworkLookup2
Filter: Active is not blank
Value: ID
Display Name: Active
THIS WORKS!

The second dropdown is set up as follows:
Data Source: CategoryLookup2 Filter: Network (Secondary Data Connection) = Network (Main), Active is not blank
THIS WORKS! NOTE: That the values for some of the category are identical between N/W A and N/W B

The third dropdown is setup as follows:
Data Source: IssueLookup2 Filter: Network (Secondary Data Connection) = Network (Main), Category (Sec. DS) = Category (Main), Active is not blank
THIS DOESN'T WORK! N/W A's issues work but I get no values for N/W B's when the category are Accounts or Workstation/Printer (I do get values for N/W B's Server category). If I take out the Category filter, it works somewhat in that I get the all the issues for a network - not filtered by the selected category NOTE: That the values for some of the category are identical between N/W A and N/W B

I realize from reading various things and just reading the infopath info that I should NOT have secondary data sources that are set up to retrieve data when the form is open but since the lists are small, I wanted to proceed this way because I spent too much time trying to use rules to filter my second and third dropdowns and I could never get it to work.

Thank you in advance for your help!

Browser Forms

Cascading Dropdown - Three Level