How We Did It – Automating Service Requests using InfoPath Forms Services

[LEFT][CODE]http://blogs.msdn.com/sharepoint/archive/2008/11/14/how-we-did-it-automating-service-requests-using-infopath-forms-services.aspx[/CODE]
[B]Overview[/B]
Today's guest blog post is from [URL="http://www.threewill.com/"]ThreeWill[/URL], a Microsoft Managed Gold Partner located in Alpharetta, Georgia that focuses on SharePoint development and integration. ThreeWill recently worked with Microsoft to build a generic Service Request Office Business Application (OBA) using InfoPath Forms Services. The application addresses the need for enterprises to have a no-code way to quickly turn around service request based SharePoint sites (i.e. sites that are using electronic forms to initiate a request and tie that request to a workflow).
Some Service Request examples are:

[LIST][*]Request for Marketing Funds[*]Request for Laptops or other equipment[*]Request for Project Site Creation[/LIST]
The solution is packaged up as a SharePoint Feature to enable deployment to a Server Farm and standard SharePoint provisioning. The application supports integration with Active Directory to pre-populate user information and provides easy access to Web Services from InfoPath using Data Connections. Finally, configuration information is stored in a SharePoint List for secure yet convenient access to Site Administrators. Over to ThreeWill on how they did it.
[I]Pej Javaheri, SharePoint Product Manager.[/I]
In Figure 1 you see the home page for the site. As you can see from in the Quick Launch navigation, users can create, edit, and view requests. This enables self service applications that can be easily configured for a multitude of purposes.
[URL="http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/workflow_infopath%20%282%29_4.jpg"][IMG]http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/workflow_infopath%20%282%29_thumb_1.jpg[/IMG][/URL]
[I]Figure 1 – Home Page for the Service Request Application[/I]
[B]Core Usability Features[/B]
One of the main goals for the project was to make some key changes to standard InfoPath Forms behavior to improve the user’s experience. For example, when you go to create a request from the “Create Request” link in the Quick Launch navigation, it brings you to the “Create/Edit Request” page with the InfoPath Form embedded as a Web Part Page. When the user saves the request, it is saved with an auto-generated name and the user is brought back to the Home page of the Service Request site. These “tweaks” to the interaction with the Form improved the overall user experience of the site.
To implement these features, the team wrapped an instance of the XML Form Viewer in a Web Part.
To apply the standard naming convention we overrode the SubmitToHost event to save the form with a naming convention based on the user name and the current time.
void _xmlFormViewControl_SubmitToHost(object sender, SubmitToHostEventArgs e)
{
const string formLibraryName = "Requests";
SPWeb currentWeb = SPContext.Current.Web;
// Get the contents of the form and put them into a byte array
XPathNavigator nav = _xmlFormViewControl.XmlForm.MainDataSource.CreateNavigator();
System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
byte[] contents = enc.GetBytes(nav.OuterXml);
//load in the xml document so we can extract out fields by name
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml(nav.OuterXml);
Dictionary<string, ArrayList> documentFields = Utility.GetNameValuePairs(xmlDocument);
//retrieve InfoPath form keys from the Configuration list
Dictionary<string, string> configuration = Utility.GetConfiguration(SPContext.Current.Web);
string [] formKeyFields = string.IsNullOrEmpty (configuration[Utility.ConstInfoPathFormKeysParameterColumn])?
new string [0]:
configuration[Utility.ConstInfoPathFormKeysParameterColumn].Split(',');
//begin to build the filename
string filename = "";
//iterate through each form key field
foreach (string formKeyField in formKeyFields)
{
//if the form key field is found in the InfoPath form, add it to the filename
if (documentFields.ContainsKey(formKeyField.Trim().ToUpper()))
{
foreach (string fieldValue in documentFields[formKeyField.Trim().ToUpper()])
{
filename += fieldValue;
filename += "_";
}
}
}
//name files using the user name and date if the infopath form keys could not be found
if (string.IsNullOrEmpty(filename))
{
filename = SPEncode.UrlEncode(currentWeb.CurrentUser.Name.Replace('\\', '-')) + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xml";
}
else
{
//trim any training underscores, add the file extension
filename = SPEncode.UrlEncode(filename.Trim(new char[] { '_' })) + ".xml";
}
// Determine the URL of the new file
string saveUrl = currentWeb.Url + "/" + formLibraryName + "/" + filename;
// open document library as a folder
SPFolder docLibFolder = currentWeb.GetFolder(formLibraryName);
// Save the form by adding its contents to the document library
if (string.IsNullOrEmpty (_xmlLocation) == false && docLibFolder.Files[_xmlLocation] != null)
docLibFolder.Files[_xmlLocation].SaveBinary(contents);
else
docLibFolder.Files.Add(saveUrl, contents);
}
The Close event is where we handled sending the user back to the home page.
void _xmlFormViewControl_Close(object sender, EventArgs e)
{
// Inspect the QueryString
if (this.Page.Request.QueryString["Source"] != null)
{
SPUtility.Redirect("", SPRedirectFlags.UseSource, this.Context);
}
else
{
SPUtility.Redirect(SPContext.Current.Web.Url + "/default.aspx", SPRedirectFlags.CheckUrl, this.Context);
}
}
Also, the “Edit My Request” link included some usability improvements. When a user clicks this link and they have only one request for the site, that request is loaded. If they have no requests, it is like creating a new request. And if they have multiple requests they are brought to the list of requests. A Service Request Redirector class was created to handle these cases.
public class ServiceRequestRedirector : LayoutsPageBase
{
private const string FORMS_LIST_NAME = "Requests";
private const string QUERY_DEF = "<Where><Or><Eq><FieldRef Name='Author'/><Value Type='User'>{0}</Value></Eq><Eq><FieldRef Name='Editor'/><Value Type='User'>{0}</Value></Eq></Or></Where>";
protected override void OnLoad(EventArgs e)
{
// get current site, web and user
SPSite siteCollection = this.Site;
SPWeb site = this.Web;
SPUser currentUser = site.CurrentUser;
//get list of fields to return "<Where><Or><Eq><FieldRef Name='Author'/><Value Type='User'>{0}</Value></Eq><Eq><FieldRef Name='Editor'/><Value Type='User'>{0}</Value></Eq></Or></Where>"
string viewFields = GetViewFields();
//build the query string for querying the Request list
//to retrieve any item created or modified by the current user
string requestQueryFields = string.Format(QUERY_DEF, currentUser.Name);
string redirectUrl = default(string);
try
{
SPListCollection lists = site.Lists;
SPList timesheetList = site.Lists[FORMS_LIST_NAME];
SPQuery requestQuery = new SPQuery();
requestQuery.ViewFields = viewFields;
requestQuery.Query = requestQueryFields;
SPListItemCollection listItems = timesheetList.GetItems(requestQuery);
if (listItems.Count == 0)
{
redirectUrl = site.Url + "/SitePages/norequests.aspx";
}
if (listItems.Count == 1)
{
string url = Request.RawUrl;
redirectUrl = site.Url + "/SitePages/editrequest.aspx?XmlLocation=" + site.Url + "/Requests/" + SPEncode.UrlEncode(listItems[0].Title) + "&Source=" + SPEncode.UrlEncode(site.Url + "/default.aspx");
}
if (listItems.Count > 1)
{
try
{
if (Request.QueryString["Title"].ToString() != "")
{
redirectUrl = site.Url + "/SitePages/editrequest.aspx?XmlLocation=" + site.Url + "/Requests/" + Request.QueryString["Title"] + "&Source=" + SPEncode.UrlEncode(site.Url + "/default.aspx");
}
}
catch (System.ArgumentOutOfRangeException)
{
//no Title in QueryString, just continue to display all items
redirectUrl = site.Url + "/Requests/Forms/MyItems.aspx";
}
catch (Exception ex)
{
//no Title in QueryString, just continue to display all items
redirectUrl = site.Url + "/Requests/Forms/MyItems.aspx";
}
}
}
catch (SPException spe)
{
System.Diagnostics.Debug.Print(spe.Message);
}
//redirect to the correct page for the number of requests
SPUtility.Redirect(redirectUrl, SPRedirectFlags.CheckUrl, HttpContext.Current);
}
[B]
Other Key Features Implemented[/B]
The solution also included the provisioning of reusable web services that are consumed through Data Connections in InfoPath and a SharePoint Designer Custom Action. Therefore, the Site Designer just needs to know how to use InfoPath to design some pretty sophisticated forms.
Some web service features are:

[LIST=1][*]Connection to AD to pre-populate user profile information into a form based on the logged in user[*]Retrieve SharePoint Group (if available) - used to simulate “role-based” functionality in a browser-enabled form.[*]Cascading Filtering of List based data – allowing users to consume list data that is related by master/child relationship (e.g. State drop down driving the automatic filtering of a City drop down)[/LIST]
SharePoint Custom Actions included a new custom action in SharePoint Designer (runtime approvers) that provides the SharePoint Approval workflow with the list of approvers at run-time.
Form is pre-populated with information from Active Directory
User can select item in drop down list and other fields are updated based on that selection
Form can show master/child relationships
[URL="http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/image11.png"][IMG]http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/image11_thumb.png[/IMG][/URL]
[I]Figure 2 - Create/Edit Service Request Example[/I]
We had some key lessons learned when simplifying these services. Kirk ran into a [URL="http://blogs.threewill.com/implementingsharepoint/archive/2008/07/01/anonymous-access-gotcha-for-custom-sharepoint-code.aspx"]gotcha when accessing a web service with anonymous access[/URL] and trying to work with the current user. We also learned more about [URL="http://blogs.threewill.com/implementingsharepoint/archive/2008/07/02/registering-a-wss-3-0-hosted-web-service-as-part-of-the-build-process.aspx"]registering a Windows SharePoint Services 3.0 Hosted Web Service[/URL] on the project and Pete followed up with a blog post to address the problem. Also, because some SharePoint web service calls do not have well defined schema for input and output parameters and/or the input/output schema is not easily consumed by tools such as InfoPath, we created a simplified Web Service class.
private SPList GetList(SPWeb site, string listName)
{
// Get the list
SPList list = null;
try
{
list = site.Lists
[listName];
}
catch (Exception ex)
{
throw new Exception(String.Format("List [{0}] does not exist within site [{1}].", listName, site.Url), ex);
}
return list;
}
private List<ListItem> ExecuteQuery(SPList list, SPQuery spQuery, string fields, bool unique)
{
// Run the query
SPListItemCollection items = null;
try
{
items = list.GetItems(spQuery);
}
catch (Exception ex)
{
throw new Exception("Unable to execute query.", ex);
}
// Turn the output into our format
List<ListItem> response = new List<ListItem>();
Dictionary<String, String> uniqueItems = new Dictionary<String, String>();
foreach (SPListItem item in items)
{
StringBuilder uniqueSb = new StringBuilder();
// Populate a response item
ListItem responseItem = new ListItem();
responseItem.ID = item.ID;
responseItem.Title = item.Title;
responseItem.Fields = new List<Field>();
// Populate the fields for the response
foreach (string field in fields.Split(','))
{
string fieldName = field.Trim();
if (!item.Fields.ContainsField(fieldName))
{
throw new Exception(String.Format("Field [{0}] does not exist within list [{1}].", fieldName, list.Title));
}
Field responseField = new Field();
responseField.Name = fieldName;
object fieldValue = item[fieldName];
responseField.Value = (fieldValue != null ? fieldValue.ToString() : String.Empty);
responseItem.Fields.Add(responseField);
// Keep track of a unique key if we care about uniqueness
if (unique)
{
uniqueSb.Append(responseField.Value).Append('\0');
}
}
// Include the list item in the responses
// But don't include it if the user requested unique values and this item is not unique
if (!unique)
{
response.Add(responseItem);
}
else
{
string uniqueKey = uniqueSb.ToString();
if (!uniqueItems.ContainsKey(uniqueKey))
{
response.Add(responseItem);
uniqueItems.Add(uniqueKey, null);
}
}
}
return response;
}
[B]List Based Configuration[/B]
To simplify administration of the sites the configuration is stored in a SharePoint list that is only accessible to the Site Administrator. The configuration parameters are stored as name/value pairs and allow the Site Administrator convenient access to configuration data through the familiar SharePoint interface.
[URL="http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/image_8.png"][IMG]http://blogs.msdn.com/blogfiles/sharepoint/WindowsLiveWriter/HowWeDidItAutomatingServiceRequestsusing_8CAF/image_thumb_3.png[/IMG][/URL]
[I]Figure 3 - Configuration of Service Request Application[/I]
[B]Summary[/B]
The benefit of this solution is an enterprise class Services Request application that requires no coding and can be configured for a number of purposes. The application has a number of key usability enhancements like auto generating form names and simplifying the process of working with forms. You can easily build the forms for the application using InfoPath and there are a number of key services that you can use like pre-populating information from Active Directory or building forms based on SharePoint list data. Configuration information for the application is stored in a SharePoint list to make it easy and secure to access.
[B]Project Team Members[/B]
The team members who worked on this project included Pete Skelly, Eric Bowden, Kirk Liemohn, Chris Edwards, and Jerry Rasmussen. We would also like to thank Donna Hodges of Microsoft for her innovative enthusiasm to drive the concepts behind this project to reality. Finally, we want to thank Kern Sutton from Microsoft who was the business liaison that helped refine the business requirements and ensured that the necessary resources from the client and Microsoft were provided to effectively solve the business problem.
[B]About the Authors[/B]
[B]Pete Skelly [/B]– Pete is a Senior Consultant with ThreeWill. Pete is certified as a MCSD using Microsoft .NET, a MCTS: SharePoint Services 3.0, Application Development and is a Certified Scrum Master. Pete would prefer to play golf for a living, but playing with his two sons and paying bills always seem to get in the way.
[B]Eric Bowden[/B] – Eric is a Senior Consultant with ThreeWill and enjoys recreational running (yes, there is such a thing), camping, outdoors with family, and optimizing commute times. Eric is certified as an MCSD as well but don’t let that fool you, he’s actually a strong coder with mad SharePoint developer skills.
[B]Danny Ryan[/B] - Danny is a co-founder of ThreeWill and enjoys chasing his two little girls around the house when he is not talking to enterprises about how SharePoint slices bread. Danny writes a monthly newsletter, called SharePoint for the Enterprise, which covers key topics about successfully building “enterprise class” SharePoint applications
[/LEFT]