We’re replacing one of our critical systems, and one of the system analysts was looking for a way to capture key data entities in the existing system, and every system/form/report that used each entity. Someone suggested SharePoint and I got myself roped into prototyping a solution.
Because of the many-to-one relationship being captured (e.g. one entity may map to fields in multiple systems), a straight out SharePoint list didn’t make sense. I have yet to see a great way to do parent/child relationships in SharePoint lists. So, I proposed an InfoPath form.
I started by building up SharePoint lists of reference data. For instance, I have one list with all the various impacted systems, another with the screens for a given system (using a lookup field to the first list), and another with tabs that are present on a given screen (with a lookup field to the second list). In my InfoPath form, I’d like to pick a system, auto-populate a list of screens in that system, and if you pick a screen, show all the tabs.
Using the InfoPath rich client, one can utilize the “filter” feature and create cascading drown downs by filtering the data source results based on a previously selected value. However for InfoPath Form Services enabled forms, you see this instead:
Son of a! The suggestions I found to get around this included either (a) write custom code to filter the result set, or (b) use a web service. I know that InfoPath Form Services is a limited version of the rich client, but I hate that the response to every missing feature is “write a web service.” However, that’s still a better option than putting code in the form because I don’t want to deal with “administrator approved” forms in my environment.
So, I wrote a freakin’ web service. I have operations that take in a value (e.g. system), and uses the out-of-the-box SharePoint web services to return the results I want. The code looks like this …
Notice that I’m using the GetListItems method on the SharePoint WSDL. I pass in a CAML statement to filter the results returned from my “system screens” SharePoint list. Since I don’t like to complain about EVERYTHING, it is pretty cool that even though my operation returns a generic XMLDocument, InfoPath was smart enough to figure out the return schema when I added a data connection to the service.
What next? Well, I have a drop down list bound to this web service data connection, but chose to NOT retrieve the information when the form opened. It’s data is conditional based on which system was selected, so calling this web service is dependant on choosing a system. So, on my “systems” drop down list, I have a rule that fires if the user actually selected a system. The rule action first sets the input parameter of the web service schema to the value in the “systems” drop down list. Next, it performs the “Query Using A Data Connection” function to call the custom web service.
So what do I have? I’ve got a nice form that gets all its data from external SharePoint lists, and cascades its drop downs like a mad man.
Of course after I deployed this, I was asked about reporting/filtering on this data. The tricky thing is, the list of system mappings is obviously a repeating field. So when publishing this form to SharePoint, and asked to promote columns, I have to choose whether to pick the first, last, count or merge of system fields.
I chose merge, because I want the data surfaced on a column. However, the column type that gets created in the SharePoint list is a “multiple lines of text”, which cannot be sorted or filtered.
So how to see a filtered view of this data? What if the business person wants to see all entities that touch system “X”? I considered about 72 different options (views, custom columns updated by WF on the list, connected web parts, data sheet view, etc) before deciding to build a new InfoPath form and new web service that could give me the filtered results. My web service takes in all possible filter criteria (system name, system screen, system tab) and based on which values came into the operation, builds up the appropriate CAML statement. Then, in my new form, I have all the search criteria in drop down lists (reusing my custom web service from above to cascade them), and puts the query results in a repeating table. One table column is a hyperlink that takes the user to the InfoPath form containing the chosen entity. Had to figure out that the hyperlink control’s data source had be specially formatted so that I could have a dynamic link:
concat(http://sharepointsite/sites/IS/division/Program/IntakeDist/Safety%20Entity%20Definition%20List/, @ows_LinkFilename)
This takes my static URL, and appends the InfoPath XML file name. Now I have another form that can be opened up and used to query and investigate the data entities.
That was a fun exercise. I’m sure there’s probably a better way to do some of the things I did, so if you have suggestions, let me know. I do really like InfoPath Form Services, but once you really start trying to meet very specific requirements, you have to start getting creative to work around the limitations.
Technorati Tags: SharePoint, InfoPath
Richard Seroter's Architecture Musings 
Brilliant! Thanks so much for this post!
Well written! While reading this I swear I started to feel your pain.
Did you ever figure out a better way of doing some of these things since then?
Have not yet come across a more efficient way …
I wrote an alternative to this solution, to have Cascading Dropdown list boxes, used in a InfoPath Webform and without using Webservices, I use only InfoPath and the InfoPath Object Model
Please read the article on http://blueinfopath.blogspot.com/
Frederik Van Lierde
http://www.SilverSandsAssociates.com
http://blueinfopath.blogspot.com
Richard, great article, could you please share the code that you used to write the web service I can’t see the screen capture.
Thanks
Hi Mark,
I take it that you’re talking about the part that does the filter-builder. It’s really just a big conditional statement that constructs an aggregate query.
Hi Richard, like Mark mentioned i’d love to see the webservice code. I’d really appreciate it if you could it paste in the comments :).
Mark, here you go…
—————
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml;
namespace KarthikSPWebServices
{
///
/// Summary description for Service1
///
[WebService(Namespace = “http://spwebservice.karthik.com/”)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class KarthikSPWebService : System.Web.Services.WebService
{
[WebMethod]
public XmlDocument GetFilteredListItems(string filterName, string listName)
{
SPListServiceReference.Lists listService = new SPListServiceReference.Lists();
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
XmlDocument queryDoc = new XmlDocument();
XmlElement queryNode = queryDoc.CreateElement(“Query”);
queryNode.InnerXml = “” +
“” +
“” + filterName + “” +
“” +
“”;
XmlNode listResults = listService.GetListItems(listName, “”, queryNode, null, “0″, null, “”);
XmlDocument resultDoc = new XmlDocument();
resultDoc.LoadXml(listResults.OuterXml);
return resultDoc;
}
}
}
————
Don’t forget to add a Web Reference to the web service within your visual studi project and naming it “SPListServiceReference”.
You Rock!!
Richard here you go…
—————
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml;
namespace KarthikSPWebServices
{
///
/// Summary description for Service1
///
[WebService(Namespace = “http://spwebservice.karthik.com/”)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class KarthikSPWebService : System.Web.Services.WebService
{
[WebMethod]
public XmlDocument GetFilteredListItems(string filterName, string listName)
{
SPListServiceReference.Lists listService = new SPListServiceReference.Lists();
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
XmlDocument queryDoc = new XmlDocument();
XmlElement queryNode = queryDoc.CreateElement(“Query”);
queryNode.InnerXml = “” +
“” +
“” + filterName + “” +
“” +
“”;
XmlNode listResults = listService.GetListItems(listName, “”, queryNode, null, “0”, null, “”);
XmlDocument resultDoc = new XmlDocument();
resultDoc.LoadXml(listResults.OuterXml);
return resultDoc;
}
}
}
————
Don’t forget to add a Web Reference to the web service within your visual studi project and naming it “SPListServiceReference”.
I am new to this field. Could you explain how can I add a Web Reference to the web service within your visual studi project and naming it “SPListServiceReference”.
Hi, are you looking for how to get the URL to a SharePoint site’s web service endpoint? The “Lists” web service is detailed here (http://msdn.microsoft.com/en-us/library/lists(office.12).aspx).
I tried building the same web service…build failed getting an error @
SPListServiceReference.Lists listService = new SPListServiceReference.Lists()
error is “The type or namespace name ‘SPListServiceReference’ could not be found (are you missing a using directive or an assembly reference?)”
here is code
—————–
using System;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Xml;
namespace SPWebServices
{
///
/// Summary description for Service1
///
[WebService(Namespace = “http://tempuri.org/”)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class SPWebService : System.Web.Services.WebService
{
[WebMethod]
public XmlDocument GetFilteredListItems(string LocationZip, string DestinationLocation)
{
SPListServiceReference.Lists listService = new SPListServiceReference.Lists();
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
XmlDocument queryDoc = new XmlDocument();
XmlElement queryNode = queryDoc.CreateElement(“Query”);
queryNode.InnerXml = “” +
“” +
“” + LocationZip + “” +
“” +
“”;
XmlNode listResults = listService.GetListItems(DestinationLocation, “”, queryNode, null, “0”, null, “”);
XmlDocument resultDoc = new XmlDocument();
resultDoc.LoadXml(listResults.OuterXml);
return resultDoc;
}
}
}
This is probably a very old response but I wanted to let you know that the error you get stating:
Filtering is not enabled in browser based templates is bogus.
You can simply disable it, perform your filtering work, then re-enable the browser based template.
To my knowledge this only applies to Infopath 2007.
James
Hey it is still showing remove filters on right side of the form on error section! I dont think James your idea will work on filtering option in InfoPath 2007?