If you ever tried publishing a form to the web using infopath form services, you probably know that one compatibility issue you will have is with master-detail (also known as "cascading dropdowns") fields.
Master-detail basically means when you have two fields (usualy dropdown boxes) and you want the options in the second field to change based on the choice the user made in the first field.
For example, I have a dropdown called "States", and a dropdown calles "Cities". Now I want when the user chooses a state, to only show cities within the chosen state.
This is easy to do in infopath - just connect the dropdowns with a little configuration. But when you try to publish the form to a web form, it will tell you that the master-detail will simply not work in the web form. It's not supported.
To the rescue!
Let me use the above example, showing you how to get it to work.
First, you will need to set up two seperate data connections - one for the states, and one for the cities. I will use sharepoint lists in this example (and on the way tell you a small trick that my colluege Rai Umair showed me).
Lets create the lists:
The states list
Set up a lookup into the states list from the cities list
The cities list
Now that we have our lists set up, lets build a simple infopath form that will show them:
- Create a blank new form in infopath
- Add the connection to the "States" list:
Tools->Data Connections->Add->Receive Data->SharePoint library or list->link to the site->Select "States"->Select "Title"->click next until the wizard finishes.
- Add the connection to the "Cities" list:
ok, here is the trick from Rai. instead of adding the connection to the list as a sharepoint library connection, we will instead add it as a XML connection, and point it to a url that will allow us to filter the list. This is a nice trick and I thought you should see it. The whole idea is to set up a connection that will be able to filter based on a user selection. if you have other methods of doing that, feel free.
- First we need to find out the GUID (the id) of the cities list. To do that, open the cities list in the browser, and click on "Settings"->"List Settings". The id of the list is in the address bar - after the bit that says "?List=".
For example, if the address bar shows
"http://server/infopath/_layouts/listedit.aspx?List=%7B7DF67EB0%2D723A%2D4897%2DA461%2D09EA9CC0D90C%7D"
that means the ID is the bit painted in red.
But, if you will note, it is url-encoded. you will need to replace "%7B" with "{" and "%7D" with "}" and "%2D" with "-" so the end result is: {7DF67EB0-723A-4897-A461-09EA9CC0D90C}
- Once you have the ID, use it to construct a url like this:
http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={put the ID here}&XMLDATA=TRUE
Example: http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={7DF67EB0-723A-4897-A461-09EA9CC0D90C}&XMLDATA=TRUE
If you open it in Internet Explorer, you should see something like this:
This gets us the content of the cities list in an XML format that Infopath knows how to read.
- Go back to infopath, add a new datasource ->Receive Data->XML Document-> paste the path we constructed above->choose "Access the data from the specified location"->Give the data connection a name (Cities) and clear the "Automatically retrieve data when form is opened" (important!)
- Your data sources screen should now contain two data sources:
-
Lets add two fields into the main data source - City and State:
-
Lets add the two fields to the form as drop downs:
- Change the properties of the "State" drop down to get the values from the states data source:
-
set up the properties of the city in the same fashion:
- Ok, test the form. The states drop down should show the states, while the city dropdown should be empty since we told the datasource not to load the values on form load.
Now that our form is ready, all we need to do is make sure that when a user picks a state, the form loads the cities. Again, it would have been a simple task if we didnt want to publish the form to a web form, but since we do - we will have to do it by code.
Small note - my code is in C#, and by default Infopath is set to use VB.NET. I suggest that you change the setting for this form - open Tools->Form Options->Programming, and select C# as template code language.
While you are in the tools->options, you may as well set the compatibility to "Design a form template that can be opened in a browser or infopath"
Right click the "State" dropdown, and select "Programming"-"Changed Event":
Visual studio has opened, with an empty new event "State_Changed". Paste the following code into the module
public void State_Changed(object sender, XmlEventArgs e)
{
SetCitiesOptions();
}
private void SetCitiesOptions()
{
FileQueryConnection q = (FileQueryConnection)this.DataConnections["Cities"];
q.FileLocation = q.FileLocation + "&FilterField1=State&FilterValue1=" + GetStateValue();
q.Execute();
}
private string GetStateValue()
{
XPathNavigator nav = this.CreateNavigator();
string filterValue = (string)nav.SelectSingleNode("/my:myFields/my:State", this.NamespaceManager).ValueAs(typeof(string));
return filterValue;
}
So, what does this code do?
- The "GetStateValue" function simply uses the XPathNavigator to get the value the user selected in the "State" field. We can also get that from the "e" object of the event, but to simplify the readability of the code I used functions here.
-
The "SetCitiesOptions" changes the url of the XML file that the cities data connection is using, and adds a filter to it. the filter syntax is "FilterField1=[name of field]&FilterValue1=[Value you want to filter".
So basically, you can create whatever filter you want on the fields, and as many filters as you want (in an "And" chain).
- The changed event for the states just makes sure the form loads the city data with the appropriate filter!
this will work on the client side, but not on the server side. why? because we are doing the re-query by code, the server will not refresh the page (postback). So to force a post back you will have to add a rule to the "State" dropdown. The rule will force the page to get the new information.
To do that, right-click the "State" dropdown, and choose "Rules" from the menu. Click "Add" to create a new rule and then "Set Condition" to "State is not blank" and add an action "query using a dataconnection" from cities:
How easy is that?
Try and preview you'r form in infopath. Now when you select a state, the cities box should show options based on the selection you made:
Now publish the form to a sharepoint forms server. Since this is a long proccess, and there are enough articles about this on the net, I will leave you to it.
When you test your form on the web, it should cascade the field:
You will note that the refresh is done using AJAX, so the page does not postback, but instead just waits for a second (with a nice bar) and then populates the data.
References and thanks
- I want to thank my colleague Rai Umair for the "/_vti_bin/owssvr.dll" trick. May he resume blogging again soon.
- I want to thank Ed Torres for forcing me to take a look at this and listening to me complain as I tried to make this work.
-
While I was writing this article, a colleague (Lee Marriage) sent me a link to an article doing something very similar by Scott Heim from Microsoft's Infopath team. His solution is to use a web service to filter the list items (I like Rai's trick better. no code needed, and easier to deploy) and using a similar rule like I did to refresh the data.
_529_530.jpg){kind=small}