This post will explain how to create a
cascading dropdown within InfoPath 2010 for a list content type.
Example Situation: I am going to create a contact form which
will filter the territories based upon the region of the form and then when I
select the territory it will filter my store numbers to only show that
territory’s stores.
My Hierarchy:
Region
→Territories
→ Store
Numbers
Materials Needed:
SharePoint 2010 List Which Utilizes a Two
Lookups
A SharePoint 2010 Content Type
InfoPath 2010
First we will need to navigate to the list:
1.
Open Your List
2.
Select List Tools
a.
Select List
3.
Select List Settings
4.
Select Form Settings Under
General
5.
Select Your Content Type
6.
Select “Customize the current
form using Microsoft Infopath”
This will open InfoPath with that content
type’s fields. We are modifying the form
for this specific content type; we are not modifying the list form.
The next step is to create and style the
form to meet your needs, for this example I created this from.
My form currently is displaying all of Mountain Store ID’s when in reality I only want the ID’s shown for that specific territory.
To create the cascading dropdown:
1.
Right click your child lookup
field and select “Dropdown List box Properties…”
a. 
2.
The next step is to select Add… We will be creating a new data source in
order to implement our cascading dropdown
3. 
4. 
5.
6.
Your URL for the site should be
selected in the next screen
7.
Select the child list which your
lookup pulls from. In my example, my
Mountain Store ID comes from my Mountain Region list.
8.
Step 9 is really what makes
this entire thing work. The default data
connection only pulls the Mountain Store ID (aka Title) in the source
list. The new data connection will allow
us to query the list to obtain additional data.
For our example I will be selecting Title & Territory since I will
want to filter my ID’s down by the Territory.
9.
10.
Select Next
11.
Select Next
12.
In step 13, make sure to DESELECT
Automatically retrieve data when the form is opened.
13.
14.
Select Finish
15.
Now that we have created the
new data source, we need to switch the value to ID otherwise we will run into
validation errors later.
16. 
17.
Select OK
Now that the new data source has been
created, we can use it to create our cascading dropdown!
1.
Select your parent field, in my
case this field is territories. (All of
my store ID’s are tagged with the Territory they correspond with in the
Mountain Region List)
2.
Select the Home Tab
3.
Select manage rules
4.
Select add rule
5.
Select New
6.
Select Action
7.
Within your rule, select Add “Set
a fields value”
a.
We are going to set our
Mountain Store ID to default to blank
b.
8.
Create another Set Field Value
Action. In this action we will be
setting our form to filter our Mountain Store ID’s based upon the territory.
9.
We are going to set this action
to set our Territory to our newly created Data Sources Territory.
10.
Select the fields icon next to
the Field: dialog box
11.
A similar menu should appear
like the one below:
12. 
13.
Select the dropdown arrow and
select the data source you just created.
In my example, it
14.
is Mountain Region1
a.
b.
Open Query Fields
c.
Open q:SharePointListItem_RW
d.
Select the field which
corresponds with your parent field. In
my example, it is Territory
e.
Select OK
15.
We now need to set the value for the second parameter
of the Rule Details. Select the fx
button and select your parent field from the main data source.
16.
17.
18.
Select OK for all of the dialog
boxes after you have selected your parent field from the main data source for
the Value: input.
The last step to make this work is to query
for data within the rule. So once again,
lets select Add Action, just like we did to set the ID initially to blank and
to set the Territory = Territory.
1.
Select Add next to Run these
actions
2.
Select Query for Data
3.
Select the Data source you
created earlier from the dropdown box.
a.
Example For When I Select Denver
4.
Select OK
5.
Your final rule should have the
3 actions and resemble the below
a.
Well that’s it Folks! If you have followed the above steps
correctly, you should now have a cascading lookup field!
Example For When I Select Denver
Example for When I Select Salt Lake
Cheers!
Dan
I have one issue with it. When opening the form again, it shows the number (ID) and not the value of the title. In the list itself i see the title, and the cascade lookup is working fine.
ReplyDeleteHi Naama,
DeleteOnce I have time I will update my blog to address this error in detail, but the way to fix it is to create a rule which runs on the form load. InfoPath does not store the Display Value, but instead the ID Value of the item. In order to display the Display Value, Query your second data connection on the form load with a parameter such as "Store ID Not Null." This will make your form re-query the connection on the form load in order to show the correct Display Value and improves user experience.
Thank you again for pointing this out!
Dan
Hi,
ReplyDeletei'm facing the same issue. please be kind enough to elaborate the solution..
Hello,
DeleteIn order to get the ID's to not show up when you view the form at a later time you must add a rule to the form load which queries that data source again in order to display the display names.
InfoPath saves the ID, but not the display name so you need to navigate to:
Data Tab → Form Load → Rule Condition: None - Rule runs when form is opened → query the list / data source where the ID resides.
Hope this helps!
Dan
Hi Dan Adams,
ReplyDeleteThank you very much.. you save my day. i really appreciate your quick respond.
Regards,
Nadeera.
The tutorial is awesome. It was really helpful for me. I even recommend the article to all my friends too. I will keep visiting your blog to learn more about infopath.
ReplyDeleteinfopath signature
I have been through several tutorials. This one was the easiest to follow. I get the correct values in the child list. However, when I make a selection, it immediately jumps to the last item on the list. I then make another selection. This seams to work. But when I click off of the list, it goes to the last item again. Any clue? I actually have the same result with other methods as well.
ReplyDeleteHi Mars72,
DeleteI am not sure if I am understanding the issue, since it is hard to perceive without a visual. Do you mean that ordering of the child list is incorrect? If that is the issue you could modify the secondary data source and sort it on another column rather than title either ascending or descending.
Let me know if I am totally off base and maybe you could send me an email with screenshots outlining your issues.
Thanks Again!
Dan
Hi Dan, I hope you read this since I see it's been a long time since anyone commented on this blog.
ReplyDeleteFirst of all, great tutorial, it was very helpful and easy to follow.
However, I get the following error when trying to publish the list to my workplace's sharepoint site:
The SOAP response indicates that an error occurred on the server:
Server was unable to process request. ---> Object reference not set to an instance of an object.
Any suggestions?
Hi Zak,
DeleteThank you for the kind words and the comment. Did you create the list first and then create the InfoPath form? Also, do you know what version of SharePoint you are running? Have you been able to publish & use InfoPath forms on this site collection in the past?
Unfortunately the error you are receiving is more of the ambiguous errors, so it is very hard to troubleshoot. In order to use InfoPath Forms Services you will need to activate SharePoint Server Enterprise Collection Features, which only comes with a SharePoint Server Enterprise License. I am just spit balling at the moment, but once we have some of these questions answered we might be able to better pinpoint the issue.
Best of Luck,
Dan