The best way to build InfoPath forms

For most of the InfoPath form system, the development of forms is always easy, even if there is some coding got involved.  The real problem is about form maintenance.

Let's imagine that you, as a SharePoint administrator or a InfoPath form developer, is in charge of 500 forms. Each form, on average, needs to be changed twice a year. So you need to modify 500 * 2 / 220 (work days) = 4.5 (forms per day).

What would happen if there are 5000 forms?

Based on my experience, most of the changes don't require to change the C# code. From technical point of view, they are just cosmetic changes. However, since C# code is part of the forms, developer have to do the changes! Or, do they?

Below is how I handle this situation. It minimizes the work of SharePoint administrator (or InfoPath developer).

1. Install InfoPath program on business users' computer.
.Net framework support is needed.

2. All forms and C# code are stored on SharePoint development server

3. Move form relevant C# code into a separate project.
Normally we need a dedicated project for each InfoPath system (a SharePoint site collection).
This project is a standard SharePoint feature (solution), which shared by all relevant forms of one project.

4. Share the form folder and C# assembly folder to relevant business users.
Business users can modify forms, but can only view C# assembly folder.

5. In form project, call the methods of that shared assembly to implement functionality.

6. Business users can modify the form whenever they want, and then deploy to another shared folder.
They can change all stuff except coding, which include rules, validation, data connection, text, views, etc.

7. Once they completed the changes, they can send an request to SharePoint administrator to deploy the changed forms to development environment or test environment.

8. SharePoint administrator deploy the new forms, then ask business users to test them.

Normally through PowerShell script.

9. Once the test is passed, these forms are ready to be deployed to Production.

What do you think of this procedure? Any comments are appreciated.

Last resort to remove InfoPath template from SharePoint

Bad things do happen.

No matter how hard I try, just could not remove a InfoPath form template which was quiesced successfully from farm.

Below are the error message I got during the struggling:

"The configuration database was queried for a non-existent object with the id bcd64dfc-9b17-020b-3bcd-3e01e80bf8a1. Most commonly, this is caused by removing an SPFeatureDefinition without writing upgrade code to remove references to the feature from each site."

"This form template was deployed as part of the  feature.  This form template should be removed only by uninstalling that feature."

"Feature with Id 'bcd64dfc-9b17-020b-3bcd-3e01e80bf8a1' is not installed in this farm. The feature was not uninstalled."

In the end, I lost my patience. Since it's in Development environment, I went to the SQL Server to try my luck.

1. Search the whole database to for the feature id or form id

This can be easily done through How do I find a value anywhere in a SQL Server Database

2. Find the relevant records from table "SP_Config.dbo.Objects"

select top 100 * from SP_Config.dbo.Objects

 where [Properties] like '%urn:schemas-microsoft-com:office:infopath:IPMYFORM1:-myXSD-2013-01-06T05-53-40%'

3. Delete them.

delete from SP_Config.dbo.Objects

 where [ParentId] = 'bcd64dfc-9b17-020b-3bcd-3e01e80bf8a1'

delete from SP_Config.dbo.Objects

 where [Id] = 'bcd64dfc-9b17-020b-3bcd-3e01e80bf8a1'

4. Done.

By the way, the article Behind the Scenes of Administrator-Approved Form Templates is really good, helped me to understand the whole form deployment mechanism.   I strongly recommend to follow the suggestions from it before trying the database modification.

InfoPath form error "Can't move focus to the control because it is invisible"

Got a very weird error yesterday.

All of sudden, when opening an existing InfoPath form instance through IE8, the error below popped up.

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB7.4; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)

Timestamp: Mon, 17 Dec 2012 23:07:27 UTC

Message: Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus.

Line: 2

Char: 499033

Code: 0

URI: http://SharePointServer/_layouts/inc/Core.js?rev=D9rcLY97a1ECurcRSuOf8A%3D%3D

But, if I open the same form through IE 9+, Firefox or Chrome, then there is no problem at all.

I changed the form a little bit before the test users reported this issue, but I could not figure out what change caused it.

So I decompressed the XSN file to a folder, then compared the "manifest.xsf" and the view XSL file with the previous version, but, no luck.

So, after two days of struggling, in the end, I had to remove the fields from the form view part by part. In my case, a naughty DropDown list control field seems to be the source of pain, and I had to re-create it.

Things are resolved. I guess it's caused by a bug of InfoPath designer.

Hopefully this can save you some time. :-)

General solution to create Unique ID in InfoPath

How to allow business users to build InfoPath forms?   That's what developers need to think about. As what I can see, application developers should put more and more effort to make business users to build and change the system,  all by themselves.

Here, the best choice is web service. So users can create data connections easily, call them through rules, and bound them to controls.

Below is what I wrote recently, which can give users 5 different way to get a unique id.

Please be careful about the method "GenerateNextListItemID()".   If two users open a new form at the same time, they will get the same ID.   To avoid conflict, we need to submit the form through rules, and re-generate the ID before submitting the form.  But that need further control to avoid creating new form when user trying to update existing form.


        [WebMethod]
        public string GenerateGUID()
        {
            string strId = string.Empty;


            strId = Guid.NewGuid().ToString();
            return strId;
        }


        [WebMethod]
        public string GenerateShortGUID()
        {   //http://madskristensen.net/post/Generate-unique-strings-and-numbers-in-C.aspx
            string strId = string.Empty;


            byte[] buffer = Guid.NewGuid().ToByteArray();
            long lUniqueId = BitConverter.ToInt64(buffer, 0);


            StringBuilder sb = new StringBuilder(7);
            while (lUniqueId > 0)
            {
                int mod = (int)(lUniqueId % 62);
                if (mod < 10)
                {
                    sb.Append(mod);
                }
                else if (mod < 36)
                {
                    mod += 87;
                    sb.Append((char)mod);
                }
                else
                {
                    mod += 29;
                    sb.Append((char)mod);
                }
                lUniqueId = lUniqueId / 62;
            }


            strId = sb.ToString();


            return strId;
        }


        [WebMethod]
        public string GenerateNowCPUTicks()
        {
            return DateTime.UtcNow.Ticks.ToString();
        }


        [WebMethod]
        public string GenerateNowTimeString()
        {
            return DateTime.Now.ToString("yyyyMMddHHmmssFFF");
        }


        [WebMethod]
        public string GenerateNextListItemID(string strListUrl)
        {
            string strId = string.Empty;
            int iLastID = int.MinValue;


            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    SPWSShared.sysWriteAppEntry(string.Format("strListUrl={0}", strListUrl));


                    using (SPSite objSPSite = new SPSite(strListUrl))
                    {
                        using (SPWeb objSPWeb = objSPSite.OpenWeb())
                        {
                            SPList objSPList = objSPWeb.GetList(strListUrl);
                            SPQuery objSPQuery = new SPQuery();
                            objSPQuery.Query = @"";
                            objSPQuery.ViewFields = @"";
                            objSPQuery.ViewFieldsOnly = true;
                            objSPQuery.RowLimit = 1;
                            SPListItemCollection objSPListItemCollection = objSPList.GetItems(objSPQuery);
                            if (objSPListItemCollection.Count > 0)
                            {
                                iLastID = objSPListItemCollection[0].ID + 1;
                            }
                            else
                            {
                                iLastID = 1;
                            }


                            strId = iLastID.ToString();
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                SPWSShared.sysWriteAppEntry(string.Format("ex.Message={0}", ex.Message));
                SPWSShared.sysWriteAppEntry(string.Format("ex.StackTrace={0}", ex.StackTrace));
            }
            return strId;
        }

General solution to retrieve data from SharePoint list view

It's not easy to retrieve data from SharePoint List View in InfoPath, especially for business users.  A link like below do the job, however, it asks for GUID of the list, and the internal name of filter field, which is not so easy to implement and maintain.

http://contoso/sites/sales/_vti_bin/owssvr.dll?Cmd=Display&List={115BC7B7-0A82-403E-9327-F3C73E6D37F3}&XMLDATA=TRUE&noredirect=true&FilterField1=xd__x007b_52AE1EF8_x002d_28E7_x002d_4CE4_x002d_AE23_x002d_54E23E80DDB5_x007d_&FilterValue1=Approved


Normally we can build a web service for that InfoPath data connection.


Let's say users want to retrieve data from a list view through Data Connection, and want to bind the data to Dropdown control.

The web service could be like below:

    public class ListData : System.Web.Services.WebService

    {

        public class ReferenceData

        {

            public ReferenceData() { }

            public int ID;

            public string Title;

        }

        [WebMethod]

        public ReferenceData[] getReferenceDataFromListView(string strListViewUrl)

        {  //parameter:   http://contoso/sites/sales/Lists/products/AllItems.aspx

            StackTrace objStackTrace = new StackTrace();

            string strLogMethodPrefix = objStackTrace.GetFrame(1).GetMethod().Name + "() - ";

            ReferenceData[] resultList = null;

            try

            {

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite site = new SPSite(strListViewUrl))

                    {

                        using (SPWeb web = site.OpenWeb())

                        {

                            SPView objSPView = web.GetViewFromUrl(strListViewUrl);

                            SPList currentList = objSPView.ParentList;

                            SPListItemCollection oSPListItemCollection = currentList.GetItems(objSPView);

                            resultList = new ReferenceData[oSPListItemCollection.Count];

                            int iIndex = 0;

                            foreach (SPListItem currentListItem in oSPListItemCollection)

                            {

                                resultList[iIndex] = new ReferenceData();

                                resultList[iIndex].ID = currentListItem.ID;

                                resultList[iIndex].Title = currentListItem.Title;

                                iIndex++;

                            }

                        } // using spweb web = site.openweb()

                    }

                });

            }

            catch (Exception ex)

            {

                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex, strListViewUrl={1}", strLogMethodPrefix, strListViewUrl));

                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.Message={1}", strLogMethodPrefix, ex.Message));

                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.StackTrace={1}", strLogMethodPrefix, ex.StackTrace));

                throw;

            }

            return resultList;

        }

    }

Below is the returned data.

As we can see, in the code, we need to define a internal class for the structure of the retrieved data.  It's possibly OK for small system, but in real world, we may need to create and maintain hundreds of classes, which is not acceptable.


A better solution is to build a general web service for all list views.



        [WebMethod]
        public XmlDocument getDataFromListView(string strListViewUrl)
        {   //parameter:   http://contoso/sites/sales/Lists/products/AllItems.aspx
            StackTrace objStackTrace = new StackTrace();
            string strLogMethodPrefix = objStackTrace.GetFrame(1).GetMethod().Name + "() - ";


            System.Data.DataTable objDataTable = null;
            XmlDocument xmlDoc = new XmlDocument();
            string strReturn = @"";


            if (string.IsNullOrEmpty(strListViewUrl))
            {
                strReturn += @"";
                xmlDoc.LoadXml(strReturn);
                return xmlDoc;
            }


            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite(strListViewUrl))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPView objSPView = web.GetViewFromUrl(strListViewUrl);
                            if (objSPView != null)
                            {
                                SPList currentList = objSPView.ParentList;
                                SPListItemCollection oSPListItemCollection = currentList.GetItems(objSPView);
                                //SPWSShared.sysWriteAppEntry(string.Format(@"{0} oSPListItemCollection.Count={1}", strLogMethodPrefix, oSPListItemCollection.Count));


                                objDataTable = oSPListItemCollection.GetDataTable();


                                string strItem = string.Empty;


                                strReturn += @"";
                                foreach (System.Data.DataRow currentItem in objDataTable.Rows)
                                {
                                    strItem = string.Empty;
                                    foreach (System.Data.DataColumn currentColumn in objDataTable.Columns)
                                    {
                                        strItem += string.Format("<{0}>{1}", currentColumn.ColumnName, currentItem[currentColumn.ColumnName]);
                                    }
                                    strReturn += string.Format("{0}", strItem);
                                }


                                strReturn += @"";
                            }
                            else
                            {
                                SPWSShared.sysWriteAppEntry(string.Format(@"{0} List view URL ({1}) is invalid!", strLogMethodPrefix, strListViewUrl));
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex, List view URL ({1}) is invalid!", strLogMethodPrefix, strListViewUrl));
                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.Message={1}", strLogMethodPrefix, ex.Message));
                SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.StackTrace={1}", strLogMethodPrefix, ex.StackTrace));


                throw;
            }


            if (objDataTable != null)
            {
                xmlDoc.LoadXml(strReturn);
            }


            return xmlDoc;
        }

Below is the returned data.

Done.


Below are some screen shots regarding how to configure it in InfoPath.

Infopath - Unique form number/name generation?

This problem is always there when building InfoPath web forms.   As I know, there are many options:

• Use "user login name" + "current time" as unique id;

Need to create a field for "unique id", and if there are millions of users, it may cause some potential conflict problem.
The "unique id" can be used as part of the file name.
The "unique id" could be quite long.
No coding needed.

• Generate the unique ID through workflow after the form is submitted

Need to create a field for "unique id", and  need to promote this field to SharePoint column;  this column needs to be editable.
This id normally is based on the "Item ID" of the form file.
Normally cannot use this id as file name.
Can only generate the "unique id" after the form is submitted.
No coding needed

• Generate the unique ID through coding

Need to create a field for "unique id".
The "unique id" can be used as part of the file name.


The last solution is my favorite. It's very flexible and easy to maintain.  Once the source code is ready, not hard to re-use it.

Below is the code which generate a GUID then convert it into a 11 characters unique ID string.

        public void setFileNameField()
        {
            string strElementPath = string.Empty;

            strElementPath = @"/my:myFields/my:FileName";
            XPathNavigator objFormFileName = MainDataSource.CreateNavigator().SelectSingleNode(strElementPath, NamespaceManager);
            string strFormFileName = objFormFileName.Value;
            if (string.IsNullOrEmpty(strFormFileName))
            {
                strFormFileName = string.Format("{0}", GenerateUniqueId());
                objFormFileName.SetValue(strFormFileName);
            }
        }

        public string GenerateUniqueId()
        {   //http://madskristensen.net/post/Generate-unique-strings-and-numbers-in-C.aspx
            string strId = string.Empty;

            byte[] buffer = Guid.NewGuid().ToByteArray();
            long lUniqueId = BitConverter.ToInt64(buffer, 0);

            StringBuilder sb = new StringBuilder(7);
            while (lUniqueId > 0)
            {
                int mod = (int)(lUniqueId % 62);
                if (mod < 10)
                {
                    sb.Append(mod);
                }
                else if (mod < 36)
                {
                    mod += 87;
                    sb.Append((char)mod);
                }
                else
                {
                    mod += 29;
                    sb.Append((char)mod);
                }
                lUniqueId = lUniqueId / 62;
            }

            strId = sb.ToString();

            return strId;
        }

        public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {
            setFileNameField();
        }

"Page break" missing when converting InfoPath 2007 form to PDF

With the help of 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS, we can easily convert InfoPath 2007 form to PDF.  However, it doesn't support "Page break".   All "page break" is missing after converting to PDF file.   Is there a simple way to resolve this problem?  Yes!

There are many free "pdf printer" we can utilize such as Bullzip PDF Printer and PDFCreator.  After installing one of them on client side computer, we can print the form to a PDF file easily. Unfortunately, we still need to install InfoPath 2007 program on client computer.

Below are some relevant screenshots:

Open the InfoPath from SharePoint site with InfoPath client program, then click "Print"

Choose the "pdf printer", then click "Save" button.

Specify file name:

The file is generated.

PS1: We can also generate PDF file from web browser (without InfoPath client program), but will lose "Header" and "Footer"

PS2: InfoPath 2010 doesn't have this "Page Break" issue, so we can publish the form to PDF or XPS file directly.

How to get user information in InfoPath web form programmatically?

Normally we can get the user information from SPUser object. There is only one issue: we need to add that user into at least one user group of the current site.

                SPUser objSPUser = objSPWeb.EnsureUser(strAccountId);

                string strUserName = objSPUser.Name;

What should we do if we only want to add "domain\domain users" into "site members" sharepoint user group?  In that case, we need to get the user information from User Profiles store.

First, define "GetUserProfileByName" data connection based on the SharePoint web service

Then, run the data connection as showed below:

                XPathNavigator xGetUserProfileByName = this.DataSources[@"GetUserProfileByName"].CreateNavigator();

                XPathNavigator xQueryAccountName = xGetUserProfileByName.SelectSingleNode(@"/dfs:myFields/dfs:queryFields/tns:GetUserProfileByName/tns:AccountName", NamespaceManager);

                xQueryAccountName.SetValue(strAccountId);

                WebServiceConnection dcGetUserProfileByName = (WebServiceConnection)this.DataConnections[@"GetUserProfileByName"];

                dcGetUserProfileByName.Execute();

                XPathNavigator xnAcctWorkEmail = xGetUserProfileByName.SelectSingleNode(@"/dfs:myFields/dfs:dataFields/tns:GetUserProfileByNameResponse/tns:GetUserProfileByNameResult/tns:PropertyData/tns:Values[../tns:Name = ""WorkEmail""]", NamespaceManager);

                XPathNavigator xnAcctDepartment = xGetUserProfileByName.SelectSingleNode(@"/dfs:myFields/dfs:dataFields/tns:GetUserProfileByNameResponse/tns:GetUserProfileByNameResult/tns:PropertyData/tns:Values[../tns:Name = ""Department""]", NamespaceManager);

                XPathNavigator xnAcctWorkPhone = xGetUserProfileByName.SelectSingleNode(@"/dfs:myFields/dfs:dataFields/tns:GetUserProfileByNameResponse/tns:GetUserProfileByNameResult/tns:PropertyData/tns:Values[../tns:Name = ""WorkPhone""]", NamespaceManager);

string strEmail = xnAcctWorkEmail.Value;

"xsf2" namespace issue with K2 and infopath 2007

Recently when trying to upgrade an InfoPath form from 2003 and 2007, then integrate it with K2 workflow, the error "Namespace prefix 'xsf2' is not defined" popped up.

Thanks for the hints from Infopath Edit The Manifest (by SLADESCROSS), this problem is easy to fix:

1. Change the “.xsn” file to “.cab” file;

2.  Uncompress it to a separate folder;

3.  Edit the “.xsf” file through notepad or any other plain text editor;

4.  Add the attribute below to the root node “xsf:xDocumentClass” (after the “xmlns:xsf” attribute, to keep consistent with normal InfoPath 2007 form), then save it.

xmlns:xsf2=http://schemas.microsoft.com/office/infopath/2006/solutionDefinition/extensions

5.  Open the infopath 2007 program in design mode, then open that modified “.xsf” file

6.  Save the form to a new “.xsn” file

After that, we can integrate the new form into K2   :-)

Update: Using an InfoPath form from a K2.net 2003 process in BlackPearl  mentioned similar solution to upgrade K2.net 2003 InfoPath form.

Cannot open .xsf file from Visual Studio

When trying to build InfoPath 2007 forms from visual studio 2008, sometimes I got error message "the operation could not be completed" and could not open the form.  This is frustrating, because no further details of that error.

After fighting with this issue many times in the past three years, I guess I found the reason.


There are some bugs in Visual Studio (I believe).


1. When we change the location of the InfoPath project, such as moved to another computer, moved to another folder, checked into TFS server, then been opened by another developer from another computer, etc. This problem might pop up.


2. There are several xml files in the folder "InfoPath Form Template" as below.  The smallest inconsistence in these files would also cause this problem.

3. Visual Studio has its own cache in memory and file system. Sometimes the old version xml file in cache may be checked into TFS.


So, how to fix this issue?

• Make sure there is enough ram on you development workstation

• Reboot Visual Studio

If it's your lucky day, this action may fix the problem.

• Be careful with the file "manifest.xsf". 

Make sure all URL and Folder path appeared in this file are valid in you current environment.   I would recommend to remove the attribute "Publishurl" from the node "xsf:xDocumentClass" if it is there (By Notepad).

• Keep a separate backup manually once the form is published successfully. TFS is not enough.

If you don't have the backup, don't worry. Take the latest .xsn file (maybe, from SharePoint site), change the file extension to ".cab", then decompress it to a folder. Then you can get most of the files from there.

• If there are more than one form in a solution (one project per form), only keep one project "active".  Unload other projects.

• Always check out the whole project before editing anything in it.

If cannot figure out the reason, or don't want to spend hours on trouble shooting, roll back to the previous backup.  I know it's not a good idea, but maybe it's the best way.


If anyone get any thoughts, please let me know.

An easy way to implement "Append-only comments"

We all know that SharePoint has built-in support for "Append-only comments". However, it needs to turn on the versioning feature, which could waste disk space.

Another normal choice is to purchase or to build custom field type to get that functionality. It would cost more time and money, and it brings more trouble in SharePoint 2010. SharePoint 2010 built-in InfoPath forms don't support custom field type!

The last choice is my favourite: implement "Append-only" functionality through one extra column and a list workflow. Below is the example. If we want, we can change the field "Comment History" to Read-Only, or just customize the edit form and view form through InfoPath.

Here are some relevant screenshots:

Update (26/07/2010): If you want to get the user full name instead of user login account, please check Useful Sharepoint Designer Custom Workflow Activities.  The user full name property is "LinkTitle", and the user email address property is "E-Mail". For SharePoint 2010, we need to install this solution manually through "stsadm" command or powershell.