SharePoint Project Series.

This is a series of blogs I am writing on SharePoint projects:

Part 1 - Design and Planning Part 2 - Implementation (Stage 1) Part 3 - Implementation (Stage 2)
Part 4 - Training Part 5 - Continued Support Part 6 - Suggested Continued Improvement

Sunday, May 30, 2010

Cascading Drop Downs with InfoPath 2010 Browser Forms

SharePoint 2010 InfoPath forms services in combination with InfoPath 2010 browser forms have enabled end users and developers to create cascading drop downs within browser forms without even thinking about a line of code!

So if you want to keep it simple or are not terribly technical follow these steps to create cascading drop downs within SharePoint 2010.

The first thing you will need is a SharePoint 2010 list to populate the drop downs. Basically it works like this: Each Item and it's respective columns will filter the items in the following drop downs. For this example I have created a 'location' list called 'suburbs'.

There are two other metadata columns that define the suburb: State and Country.

The way the cascading drop downs will work in InfoPath is that the first selection will be 'Country' which will then filter the second drop down, 'State' which in turn once slected, will give the user filtered option in the third drop down; Suburb.

The List

Custom list created with two additional metadata choice columns (Country and State).


List with some sample data:

The Form

To create the cascading drop downs in the form you will first need to create 3 empty datafields.


Add the three drop downs of the fields to the form. To populate the form with the columns from the list create a new SharePoint list data connection, which can be found under the 'data' tab.

Select the SharePoint server and then select the list. Select the 3 columns (title plus the two columns you created earlier).

You can check the box 'Store a copy of the data in the form' if you would like to view the form in offline mode. Finish the wizard.

Now right click on the first drop down and go to Drop Down List Box properties. Select 'Get Choices from an External Datasource'. Select 'Suburb' (this will be automatically selected if it is your only external datasource). For the first 'Country' drop down select 'Country' as the value and display name and also check the box 'Show only entries with unique display names' (this will avoid duplication).


Now right click the 'State' drop down, follow the same procedure as before except this time select 'State' for value and display. However when you select the 'State' option, you will need to click on the 'Select XPath' from next to the 'Entries' box. This will allow you to filter the 'States' based on the selected Country.

Click 'Add' to add a new filter. In the first drop down box you will need to select the value of the InfoPath field for 'Country' and then in the third drop down box you will need to select the corresponding data field from the SharePoint list data connection. We are basically saying here, filter the drop down of state when the Country drop down is equal to the country in the list.

Select the 'Title' drop down and then click on 'Select a field or group'.

Change the datasource to 'Main', then select the field where 'country' is stored.

Now the middle drop down can remain as 'Equals', click on the third drop down and click on 'Select a field or group'. This time from the '(Suburbs) Secondary Datasource' expand the list and select the 'Country' field.

Click ok on all the dialogue boxes to make the changes, don't forget to click on the unique names check box. You can now text the form to see the first drop down filters the second. One thing you will notice is that if you select an option in the 'State' drop down it will remain even if you change the 'First' one, however the rest of the options will respect the filtering. This can be rectified using a rule on the first 'State' option.

However, first follow the procedure above to change the third drop down to display 'Suburb' fields and filter them agaisnt the field selected in the 'State' drop down box.

Now to create the rule that resets the other two drop downs when you reselect the first 'Country' one.

Right click on the 'Country' drop down and select 'Rules' > 'Manage Rules'. A new side panel will appear on the right which allows you to manage the rules of this particular field element.

On the Rules Tab select 'New > Action' to create a new action rule. Name the rule something like 'resetDropDowns'. Click on the 'Condition' area to create a rule condition, change this to something like: 'Country is not blank'. Then click ok.

Click on 'Add > Set a field's value'. Set each of the other two drop downs to 'nothing'. Do this once for each of the other drop down fields.

The rule should look something like this:

There you have it, you can now test your form's cascading drop downs.












23 comments:

bmcworldcitizen said...

Thanks! Your explanation was the only one I could follow:-)

Bruce Harrison said...

Hi Koobar - thanks for that, this approach was nice and easy to follow, but when I publish InfoPath indicates the filtering isn't compatible with web browser. Waht else do i need to do to ensure this works in the web browser, not just the InfoPath filler? :)

Steve said...

I see that there has been no replies from the author on Bruce's comment. I am having the same issue, it give me errors saying that filtering is not available in browser based forms.

Lilupa said...

Thanks for your valuable article. I have slightly different requirement. I have two lists (country and states; not within the same list like your post) linked with a column. I applied your logic to my requirement but it's not filtering. Any idea?
Thanks.

Agus Suhanto said...

Filtering is NOT supported on browser-based InfoPath forms... so this is not the solution for browser based InfoPath forms...

crazy said...

Amazing BOSS just i was searching for this ............thank u very Much...........? Keep on Good work

crazy said...

Amazing Boss thats Helped gREAT FOR MEE.............

crazy said...

Amazing Boss .....its helped alot for me..............keep on good work.....

suresh.pydi said...
This comment has been removed by the author.
suresh.pydi said...
This comment has been removed by the author.
suresh.pydi said...

Thanks! You saved my Time and Hair;-)

0f0045a2-166a-11e1-aa5d-000bcdcb8a73 said...

my question is that, once the 1st, 2nd and 3rd dropdown are selected..and if we go back and change the 1st dropdown to other value..the previous values of 2nd and 3rd dropdown for previous 1st dropdown value are stored and if we save it..those values will be stored..Can we get any pop up message or any warning message to change the value ?

Akalanka said...

Really Helpful...Thanks.....

Akalanka said...
This comment has been removed by the author.
Dean Morgan said...

I think that the use of the word "Metadata" has confused the issue. The fields he created in the list are just text columns in the list, not true metadata. That being said, the solution does work with the text fields.

Nitin Gupta said...

I tried the solution and it worked. Be sure that you are designing a 2010 Infopath form template and not a 2007 InfoPath form template.

Majella said...

Koobar - thank you so much. This works beautifully.

Gennady Vanin said...

"Filtering is NOT supported on browser-based InfoPath forms... " is wrong. It works in browser-based forms. I checked it few times

hawk1821 said...

very helpful. thank you!!

Yuri Nayder said...

This worked great for me in one instnace, but in a second implmentation I'm having a wierd issue. For the second use of it I added two people picker fields and and have two fields in the InfoPath form to use the value of that list item in those fields. When a new form is opened, if the user does not have Full Control of the list the form uses as a data connection, the dropdowns are all blank. As soon as I give the user Full Control, the user is able to see the filtered dropdown. Anyone have any ideas?

Career Dreamer said...

Thank you thank you thank you! This is the best explained lesson I've come across. Thanks so much!

Akila R said...

but in this scenario , if suppose i want to fetch the second drop down list from the lookup field. how it poosible.? i tired it but it shown only the id vales , not a text value of particular drop down. can u help me..

Saeid & Elizabeth said...
This comment has been removed by the author.