Avoid corrupted site columns due feature re-activation

One of our clients reported an issue that provisioned site columns get corrupted after deinstall of the provisioning feature. The feature deinstallation is a step within the repair of erroneous situation in which sandbox solution per accident is also deployed as global farm-based solution. This incorrect deployment requires a fixture because an effect is that features now appear as duplicate entries in the list of site(collection) features: one installed via the sandbox solution (correct, administrated in the sitecollection's content database), and one installed via the farm solution (incorrect, administrated in the farm configuration database).

On first thought the simple fix is to deactivate the provisioning feature that originates from the farm deployment, remove the feature, then retract the global farm-solution, and remove it from the farm solution store. Next activate the feature deployed via sandbox-solution to arrive at the correct deployment situation. However, in a test execution we experienced that this approach gives an error upon the re-provisioning of the site columns: The local device name is already in use.

Error details in ULS log:

Unable to locate the xml-definition for FieldName with FieldId '<GUID>', exception: Microsoft.SharePoint.SPException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED)) ---> System.Runtime.InteropServices.COMException (0x8000FFFF): Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED)) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetGlobalContentTypeXml(String bstrUrl, Int32 type, UInt32 lcid, Object varIdBytes) at Microsoft.SharePoint.Library.SPRequest.GetGlobalContentTypeXml(String bstrUrl, Int32 type, UInt32 lcid, Object varIdBytes)

To come with a solution, I started with a root-cause analysis. Why are the provisioned site columns not completely deleted from the site upon its feature deactivation? The explanation is that the provisioning feature also performs a contenttype binding to the Pages library, and that in our testcase a page was created based on that contenttype. This effectually results in the contenttype being kept ‘in usage’ by the Pages library. On feature deactivation the site columns can still be removed at sitecollection level, but the contenttype not anymore due the descendant sibling binded to the Pages library.

The real problem however lies in the 'removed' site columns. They are deleted from sitecollection level, but due the preserved contenttype (Pages library) their definition has remained in the sitecollection's content database, with the same ID as on sitecollection level (note this is different for a contenttype binded to a list, that gets a new ID based on the ID of the source/parent contenttype at sitecollection). SharePoint administrates per provisioned artifact whether the origin is a feature, and if so effectively couples the artifact to that feature. SharePoint disallows these artifacts by automatically deleted or modified by another feature. As result the feature re-activation halts with an error when trying to (re)provision the sitecolumns that are still present deep down in the content database, coupled to the Pages library.

With this SharePoint-internal insight, I was able to come up with a faultproof approach to fix the 'duplicate features' issue. The trick is to initial leave the feature definition that originated from the erroneous farm solution in the configuration database, deploy the sandbox-solution (stores the feature definition in the sitecollection's content database), activate the feature with the same feature id from that sandbox solution. Now the feature activation proceeds completely without errors, and restores the site columns. Ultimately the farm-based solution can then be retracted from the farm solution store.

Note: I came to this insight by inspecting on SQL level. We all know it is not allowed to perform changes on SharePoint content database level (or loose your Microsoft support), but it is perfectly ‘SharePoint’-legal to review and monitor on SharePoint content database level.

Used/useful SQL statements:

SELECT * FROM ( SELECT *, Convert(varchar(512), CONVERT(varbinary(512), ContentTypeId), 2) As key FROM [ContentTypeUsage]) as T where key like '%< contenttypeid >%'

SELECT tp_Title FROM ( SELECT *, Convert(varchar(512), CONVERT(varbinary(512), ContentTypeId), 2) As key FROM [ContentTypeUsage] Join AllLists on ContentTypeUsage.ListId = AllLists.tp_ID ) as T where key like '%< contenttypeid >%'

More [functional] robustness issues with OOTB InfoPath Forms Services

Earlier I blogged about some robustness issues I encountered with the application of InfoPath Forms Services in an external facing SharePoint site. The basis of the framework developed in that project is re-used in context of the company's intranet. Hereby I ran into some new robustness issues. I'll describe them here, and also the outline of the applied solutions.

1. Placing XmlFormView webpart on a SharePoint page conflicts with inline server codeblocks in MasterPage
   Immediately upon adding the out-of-the-box Microsoft.Office.InfoPath.Server.Controls.XmlFormView webpart on a publishing page in the company's intranet, SharePoint throwed an exception:

   Source: System.Web
   Message: The Controls collection cannot be modified because the control contains code blocks (i.e. ).
   Stacktrace:
   at System.Web.UI.ControlCollection.Add(Control child)
   at Microsoft.Office.InfoPath.Server.SolutionLifetime.ScriptIncludes.AddCssLinkToHeader(HtmlHead head, String href)
   at Microsoft.Office.InfoPath.Server.Controls.XmlFormView.TryToAddCssLinksToHeader()
   at Microsoft.Office.InfoPath.Server.Controls.XmlFormView.OnDataBindHelper()
   at Microsoft.Office.InfoPath.Server.Controls.XmlFormView.OnPreRender(EventArgs e)
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal()
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint

   
   

   First analysis learned that the problem was related to the applied SharePoint MasterPage: it manifested itself with 2 of our custom MasterPages, but not on other custom nor the standard (Publishing) Masterpages. Inspection of the particular custom MasterPages disclosed the following server-side code:

   <% Response.Write("<script>var Collaboration_SiteRelativeUrl='" + SPContext.Current.Site.Url + "';</script>"); %> 

   
   

   The inclusion of this inline server codeblocks resulted in the conflict with the server-side operation of the XmlFormView webpart. The solution was to replace the inline server codeblocks-tags '<%' and '%>' approach by a HtmlHead (which is a webcontrol) databinding approach:

   
   

   <script>var Collaboration_SiteRelativeUrl="<%# SPContext.Current.Site.Url %>";</script> 

   
   

   And in the MasterPage codebehind:

   
   

   protected override void OnLoad (EventArgs e)
   {
       base.OnLoad(e);
       Page.Header.DataBind();
   } 

   
   

   
   

   
   


2. IE 6 gives a runtime Javascript error in case of RichTextBox control on the form
   While demonstrating the potential of the self-service WebForms functionality to internal stakeholders, I was asked to modify an earlier published InfoPath form with the placement of a RichTextBox control. After re-publishing the InfoPath form to the SharePoint target site, the form still correctly displayed in the content page; including the added RichTextBox control. However, when positioning the mouse in the RichTextBox control, the IE6 browser (Red: still the company's standard) reported a runtime Javascript error. Also the RichTextBox toolbox was not displayed, leaving the control pretty useless wrt a plain TextBox control.
   
   With these problem symptons, I kinda got a 'Deja-Vu' feeling. It looked very much like the problem earlier encountered with displaying InfoPath forms in IE 6. And indeed: via Javascript debugging I detected that the client-side runtime error manifested itself in the same OOTB InfoPath Inc/Core.js method as before: ErrorVisualization.ComputeAbsoluteTop(). Was then the problem caused by the usage of relative CSS-positioning applied in the custom branding, this time it proved completely InfoPath related. I could easily reproduce the same problem within a standard SharePoint WebPart page, with a standard MasterPage. The explanation of the IE 6 Javascript problem is in the HTML that InfoPath Forms Services renders for a RichTextBox control: an embedded iframe element within the DOM document. Due to the fault behaviour of the IE 6 offsetParent method implementation in combination with the non-robust/error-prone implementation of the OOTB InfoPath method, this results in the runtime Javascript error.The solution was to runtime at browser/client-side expand that OOTB InfoPath method to make it robust for the IE 6 faulty CSS offsetParent implementation.
   

   
   

   
   

   
   

   
   


3. InfoPath re-publishing adds duplicate site columns to the ContentType
   
   Upon re-publishing an InfoPath formtemplate during the demonstration, I noticed another issue. It looked as if all promoted fields in the ContentType were now duplicate present. A quick internet search learned that this is a known InfoPath feature/behaviour, just something I myself was not aware of upto now. And neither something I was particular happy with. I think it is pretty ridiculous to add a complete other set of fields to the InfoPath ContentType each time you re-publish the associated formtemplate. You have some control-options in the InfoPath publishing wizard to prevent the creation of another set of promoted fields, but this is manual-dependent and thus error-prone; let alone it is also very cumbersone and clumsy for the functional managers (the first-class users of the self-service WebForms functionality in the company portal). Therefore I sought for a manner to afterwards correct the modified ContentType, by removing all duplicate FieldLinks. Ideally did would be done fully automatic in the scope of the InfoPath publishing, and thus transparent and invisible to the WebForms functional managers. Sadly, SharePoint does not have an EventReceiver signalling the creation or update of a ContentType. The solution is therefore to deliver a custom SharePoint application page via which the WebForms functional manager can afterwards fix the InfoPath modified ContentType. He/she selects in the dropdown the ContentType that [potentially] needs a cleanup. The custom application page then inspects
   the FieldLinks in the ContentType for duplicate occurences, and removes each one found. The changes (deletions) in the SiteCollection ContentType are propagated to all childs. Also each duplicate detected FieldLink is removed from the Fields collection of each SPDocumentLibrary that is associated with the selected InfoPath ContentType (code snippet).
   

   
   

Aggregate the values from an InfoPath repeating section for data administration

A common requirement for setting up (web)forms is to enter multiple rows of similar information. For example, entering the name(s) of your child(ren) for an holiday booking. The InfoPath Repeating Section enables an intuitive usage model for this. Initial the form displays only 1 section for data entrance. Via a link or button the user can on-the-fly expand the form with more occurrences of that same section.

Question is how best to administer the entered data from the repeating section in a SharePoint library. Is this a Master/Slave relationship? Or should it be considered simple as embedded in the form data self? The former requires a relation data model employed within the SharePoint information architecture. Although possible, this is not the typical SharePoint model which is namely to administrate the entity data in a single List or Library. However, for the second alternative it is still required to potentially administrate multiple values per field in the repeating section.

SharePoint supports this via the concept of AggregateFunction at Field level. To collect the data from a repeating section, specify aggregate="merge" for the field within the InfoPath form template

When explicitly self-provision the SiteColumns and ContentType (Administrate data from InfoPath form in a self-provisioned ContentType), you ran into a problem here. The FieldSpecification CAML currently does not allow the Aggregate attribute as in:

<FieldRef
  ID="{5e046132-9284-4322-b1c7-9a742d60270e}"
  Name="FirstNameChild"
  ReadOnly="TRUE"
  Node="/my:myFields/my:group/my:Children/my:FirstNameChild" />

SharePoint will fail the activation of your Feature with the error message: The 'Aggregation' attribute is not allowed.
The remedie is to skip the 'Aggregate' attribute from the CAML specification, and afterwards add it via an EventReceiver to the provisioned SPField in the RootWeb:

  SPField aggregateField = site.RootWeb.Fields[new Guid("{5e046132-9284-4322-b1c7-9a742d60270e}")];
  aggregateField.AggregationFunction = "merge";
  aggregateField.Update();

Administrate data from InfoPath form in a self-provisioned ContentType

When registrating via XSNFeatureReceiver an InfoPath Form Template in a SharePoint sitecollection, automatically a ContentType and the associated SiteColumns are provisioned. This ContentType enables promotion of the data entered via the form to a SharePoint library. However, I prefer to apply explicit self-provisioned ContentType and SiteColumns for the data administration. The rationale is to have full control over the created information architecture entities. In the automatic created ContentType, the ID is arbitrary and thus differerent accross (re)deployments and environments. This hampers the content deployment process between environments. And at rolling out an upgrade of the InfoPath form template in the same site collection, a new version of the ContentType is provisioned with potential another ID. The automatic provisioned SiteColumns do not expose this particular problem. Their IDs are namely contained in the InfoPath form template, and therefore remain identical accross (re)deployments. But the SiteColumns have their own issues. Most noticable is a SiteColumn with type (Multi)Lookup. This cannot be correctly provisioned via a CAML FieldSpecification only, but requires a fix afterwards (Creating Lookup columns as a feature). Another problem that both the automatic provisioned SiteColumns and ContentType suffer of, is when there are changes in the definiton while the entity is already in use. To propagate the changes at the sitecollection-defined entities to their usage at List/Library level requires some afterwards fix via custom code.

Because of these issues I prefer to explicit self-provision both the SiteColumns and the ContentType for administrating the forms data. You then control the ID of the ContentType, you can fix the Lookup-relationships for SiteColumns, and you can propagate changes within the ContentType to all List/Libraries which already use the type. It costs some extra development time, but it surely pays off upon repeated full-automic deployments, and accross environments.

Peculiarities for handling space-character in field names for query and search: ManagedProperty name vs Field / ColumnName

For better human readability, and thus understanding by the end users, it is often desired to use spaces in the DisplayName of SiteColumns and ContentTypes FieldRefs: "The columnname". But beware, this gives some peculiarities with the different approaches to query and display the SharePoint content...

Enterprise Search

Upon first crawling of the content source, this ends up in a CrawledProperty with the name "The_x0020_ColumnName". This can be mapped to a ManagedProperty. The name of this property may not include any spaces. Here it could be "TheColumnName". With Enterprise Search, one can now query and select on the property 'TheColumnName'. For instance, query via the AdvancedSearchBox webpart, and display the results via the SearchCoreResults webpart. Rather handy to skip in the XML-based query (SQL-CAML), and the rendering of the query result (XSLT), the special handling of the space character.

CAML-based site content query

When query and displaying site data via the ContentQueryWebPart, one must select and display on the actual field / column name. That is, in the CommonViewFields specification, the actual column name must be used. Due to the space in the name, CAML space-mapping must be applied; resulting in 'The_x0020_columnname'. Example:

<property name="CommonViewFields" type="string">
Title,String;The_x0020_columnname,Choice;Editor,User
</property>

This is all pretty well-known SharePoint stuff.

Something that is less known, is that yet another CAML translation must be applied to successfully display the CQWP queryresults via XSLT rendering. The CAML-space character '_x0020_' must itself be translated into '_x005F_x0020_'. So for instance:

<td class="ms-vb" nowrap="">
    <xsl:value-of select="@The_x005F_x0020_columnname"/>
</td>

Thus...

To summarize: when you put a space in the displayname of a field, this may wind up in 4 different 'names' within the query/display pipeline:

1. The actual displayname itself: "The columnname"
2. The managed property name for within enterprise search: "TheColumnName"
3. The query fieldspecification within CQWP: "The_x0020_columnname"
4. The query result rendering used by CQWP: "The_x005F_x0020_columnname"

Tip: use Lookup iso Choice field for (semi)fixed set of defined values

Often, in your SharePoint information architecture there are some fields identified with a set of defined allowed values. A common approach is then to apply the Choice fieldtype herefore. For real fixed set of possible values this is a very sensible approach. For instance, for datatype sex; we'll have 'male', 'female', and well 'unknown' in case of doubts. However, more than often the set is (semi)fixed: departments within a company, the customer base of an IT consultancy, etc. In such situation, usage of Choice is not flexible nor user-friendly towards the functional managers of the application. For each modification, it is required to change the definition of the Choice-based field. And one must then also not forget to propagate this change to the lists on which the field / site column has been applied (direct, or via contenttype). All this is more technically doing, than SharePoint functional management. A better approach is to utilize the Lookup fieldtype, and refer to another list with in it the currently known set of defined values (masterdata values actually). Whenever a modification to this set is in order, functional management can suffice with adjusting this masterdata list. Actually, this is just sane data model normalisation. Like in such context, in addition to defining the set of allowed values, it is also possible to augment them with more details. For instance, department name (allowed value), and in another list column more details of the department.

Something you'll have to take into account when applying this datamodelling, is a peculiarity upon provisioning the SiteColumns. The way Lookup SiteColumn are administered in the SharePoint content database, is with the Guid of the referential SharePoint list. This results in a problem when provisioning the SiteColumns the SharePoint standard way via Feature, with the field specifications in XML. The ID of the referential list is typically unknown at coding/specification time, and will be different per environment provisioning. This is a known issue, and so there is also a known resolution. I'm not going to describe it here, of even try to take the credits for it. Frankly, I've used a blog-entry of Chris O'Brien, Creating Lookup columns as a feature, as start information source for hinting how to solve this sequential provision issue.