Monday, January 22, 2007

InfoPath form Services - implementing a Master-Detail on the web

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
      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)

private void SetCitiesOptions()
FileQueryConnection q = (FileQueryConnection)this.DataConnections["Cities"];
q.FileLocation = q.FileLocation + "&FilterField1=State&FilterValue1=" + GetStateValue();

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.


Anonymous said...

It's a great trick. I did not know you could do that. Well done Sagi.

Anonymous said...

Well, one thing is very strange because when infopath is doing the xml connection a error occur. "This is not a valid XML file". I guess Sharepoint doesn't create a web standard xml file.

Anonymous said...


jknight said...

Thank you, thank you, thank you. Thank you.

Anonymous said...

Thanks a lot. You saved my day

Anonymous said...

Its really good and wasy to implement...hey!can we add sort the field also.

Austin said...

Thanks for your idea! I was struggling with using an InfoPath 2007 Receive data source to get data from a SharePoint list because it wouldn't allow me to pull the data from a BDC column - but working around it by using the XML like this gives me the data I need, without a great deal of complication.

Thanks again!

Anonymous said...

Has any one done this with repeating sections in a repeating table? Is it possible?

Please post.


Chris said...

Great post. This really helped me out. Thank you very, very much.

Anonymous said...

I noticed when setting this up that if a user changes the value in the linked dropdown for 'States', the old value for 'Cities' sometimes does not change. You should add a step where the Cities data field is set to blank when the 'States' field is changed, like in the example at

Amandeep said...

Really thanks Ishai for your quick response. I am using "Implementing a Master- Detail on the web" blog for cascading drop down list as you suggested. I followed it line-by-line, but it doesn't show me values in second drop down list. While debugging i figured out that "q.filelocation" expression doesn't return any value.

q.FileLocation = q.FileLocation + "&FilterField1=ministry&FilterValue1=" + GetStateValue()

where "ministry" is my field's name from where user selects the value and "GetstateValue()" returns proper selected value, but as soon as filter get attach to "q.filelocation", it doesn't return back any result.
My file location(XML Source) is
http://servername/amandeep/_vti_bin/owssvr.dll?Cmd=Display&List={D9CB3702-08C9-4FB1-B4E1-A298C0733CDC}&XMLDATA=TRUE -----
which shows
(z:row ows_Attachments="0" ows_LinkTitle="Toronto" ows_State="1;#Ontario" /)
(z:row ows_Attachments="0" ows_LinkTitle="Brampton" ows_State="1;#Ontario" /)
(z:row ows_Attachments="0" ows_LinkTitle="Edmonton" ows_State="2;#Alberta" /)
(z:row ows_Attachments="0" ows_LinkTitle="Calgary" ows_State="2;#Alberta" /)
(z:row ows_Attachments="0" ows_LinkTitle="Vancouver" ows_State="3;#BC" /)
(z:row ows_Attachments="0" ows_LinkTitle="Surrey" ows_State="3;#BC" /)
(z:row ows_Attachments="0" ows_LinkTitle="Klowena" ows_State="3;#BC" /)
(z:row ows_Attachments="0" ows_LinkTitle="Victoria" ows_State="3;#BC" /)
(z:row ows_Attachments="0" ows_LinkTitle="Winnipeg" ows_State="4;#Manitoba" /)
(z:row ows_Attachments="0" ows_LinkTitle="Scarbrough" ows_State="1;#Ontario" /)
(z:row ows_Attachments="0" ows_LinkTitle="Kingston" ows_State="1;#Ontario" /)
---just change the tags here---
and after filter get attached to file location, it becomes

which shows
(rs:data /) --- no filtered data
Can you see any problem with it, and following is code for changed_event for "ministry" field

Public Sub ministry_Changed(ByVal sender As Object, ByVal e As XmlEventArgs)
End Sub
Private Sub SetCitiesOptions()
Dim q As FileQueryConnection = DirectCast(Me.DataConnections("Cities"), FileQueryConnection)
q.FileLocation = q.FileLocation + "&FilterField1=ministry&FilterValue1=" + GetStateValue()

End Sub
Private Function GetStateValue() As String
Dim nav As XPathNavigator = Me.CreateNavigator()
Dim filterValue As String = DirectCast(nav.SelectSingleNode("/my:myFields/my:group2/my:ministry", Me.NamespaceManager).ValueAs(GetType(String)), String)
Return filterValue
End Function

Ishai Sagi [SharePoint MVP] said...

What would cause the filelocation to be blank? I don't really know. Did you make sure the datasource is connected properly and it works?

Amandeep said...

Thanks Sagi .. Its working now... it was just, I was using dropdown list field's name instead of shareponit list's column-name in filter.. thanks for your help .. really appriciated

Maksim said...

My Change(state) event does not fire for some reason. Works fine in the client but not in web browser. Any ideas what may cause it? I already checked security.

Anonymous said...

It's great.. I've spent 2 days to sort out this problem until I find this..Thanks a lot...

Ryan McIntyre said...

I utilized this method successfully, but had to convert to 2003 compatible code. I thought it may be useful for others: 2003 Compatible Code.

Thanks for the very helpful post!

Amandeep said...

Dear Sagi,
I have implemented master detail on my browser enabled form and it worked perfectly. Now i have some updates in my data sources(sharepoint lists) and when i changed the XML document data sources, i can't see any changes in dropdown list(those were made in sharepoint list)

Gordon Messmer said...

I just noticed a bug in the code you're using, and have updated my previous template. When you modify the value of q.FileLocation, you're modifying an attribute of a persistent object. If you have multiple form elements using the same data connection and each uses a different number of filters, you may not get your expected results. This is because each time you modify FileLocation, you're simply appending filters to the FileLocation that you modified some time previously in the session. It's important, then, to create a copy of the FileLocation and restore it at the end of the function:

public void State_Changed(object sender, XmlEventArgs e)

private void SetCitiesOptions(XmlEventArgs e)
FileQueryConnection q = (FileQueryConnection)this.DataConnections["Cities"];
string OriginalFileLocation = string.Copy(q.FileLocation);
q.FileLocation = q.FileLocation + "&FilterField1=State&FilterValue1=" + e.NewValue;
q.FileLocation = OriginalFileLocation;

Stephan said...

Hi Sagi,

Thanks for your sample. I replicated your example and it works great. However, when I tried it in my application, my detail list returns the value prefixed by the number value, semicolon, and number sign (e.g., "15;#Canberra"). Any obvious ways to remove the number, semicolon and number sign?

Anonymous said...

Great Post,

But i want to filter the dropdown list items based on username() function.

My list having the assigned to coloumn.

Waiting for replies....

Anonymous said...

I have also some difficulties to remove the ;# information when the field is actually a sharepoint look up. I have tried to use rules but for some reason they do not get fired when the data is refreshed...

Azhar said...

Indeed a great post Sagi, but I am getting an exception
"Request Failed" the exception is
"SecurityException unhandled by user code",
this exception is thrown on executing the following line
FileQueryConnection q = (FileQueryConnection)this.DataConnections["Cities"];
please help me..

Stephan said...

For Anonymous:

Until Sagi replies with the magic touch, here's an imperfect workaround:
1) create a new view
2) copy and paste relevant fields into the new view
3) for the field that returns the "32;#" value preding the data you want, have a helper field pull in that data and use a "substring-after" command with the field and "#" as parameters. This returns only the values you want.
4) insert a control button on the first view that will toggle to the second view when the button is pressed.

So, with this workaround, users will still see the "32;#" momentarily, but the button makes it vanish.

Anonymous said...

For a few weeks, I would get this error on a production server when I would use the cascading drop down code, but not on my dev server:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

I realized this is because I had to sign my infopath form.

Form Options->Security and Trust->Form Template Signature

Choose to sign it, and if you dont have a certificate, create one.

Ries said...

this is a BIG help.
i'll give you a BIG BIG BIG thanx.

Gokhiel said...

Great Post!! Thanks Sagi..:)

Jesper G. Simonsen said...

Thanks for this tip.
We successfully used it until a recent security update made it fail. The solution can be found at and you can read more about our troubleshooting at

Alana said...

Hey Sagi,

Thanks for this.
Hoping you or someone else can help me with one little thing. I have done this same thing for a list called 'Organisations' and another list called 'Client Contacts'. When the user selects the organisation then the client contact field is filter to show only contacts from that organisation.
However, because my 'client contacts' list has a space in it, it is causing problems and my code is not working.

Could you please advise in C# how I cater for spaces in the document library name?


Anonymous said...


Try filtering on "Client_x0020_Contacts" instead of "Client Contacts"

Frederik Van Lierde said...

Have a look at the following link, it is code you can reuse to create Cascading Dropdown Out of The Box in InfoPath and WebForms

You can use the code to create 2,3,4,... cascading dropdowns


Dipti said...

thanks for this link as this articles has solved my problem that has taken more time to implement

thank you very much
Dipti Chhatrapati

Anonymous said...

Thanks, code saved me, though
I had some problems creating the URL for the XML. I did find an easy way to create-get the URL.

Use Actions->Export to Spreadsheet.
Save the owssvr.iqy file it gives you and open with NotePad or similar.

The URL can be found in this file.

Wai-man said...

Thanks tremendously for the post. I have been looking for a solution the whole day and your description of the problem is the best! Go Aussie

Anonymous said...

Thanks for your post - I was recieving an error after first using it - the issue was related to xpath for the filter. I had the cities field in a section, which needs to be decalred. Null error. Thanks again

Charisse Pineda said...

Hi, I just want to ask.

I'd like to filter document through infopath form. The Selected SUBJECT on the INFOPATH FORM should be the reflected filtered document. The connection is SUBJECT to TITLE. I've used CONNECTIONS>SEND DATA TO but it doesn't work. Do i miss something? Please help. Thanks