SharePoint 2007 – associated lookup columns

No, this is not a mistake – we can also have associated lookup columns in SharePoint 2007 [update (Dec 13, 2010): check for two drawbacks in SharePoint 2007 below], though not with the extended UI creation capabilities available in SharePoint 2010. Let’s first have a look at this new functionality in SharePoint 2010 (and define more accurately the terminology – I don’t think that there is a specific term for this lookup field extension, but in this article I will call it as I did in the title of the posting):

So, as you can see from the screenshot (and probably tried that many times already yourself), when you create a new lookup column in a list, you have the option to select more than one column from the target lookup list (in SharePoint 2007 we had the option to select just one “show column”). Basically what SharePoint does when you create a lookup field from the UI is to create the “normal” lookup column as before and then create another read-only “associated” lookup column for every additional show field from the check-box list that you have selected. And this functions as follows – when you create a new item, or edit an existing one in the edit form you see only the “normal” lookup column there, but when you change it to point to another item in the lookup list, all “associated” lookup columns change their values so that they correspond to the related columns in the newly selected lookup item. The associated lookup columns are read-only but you can add them to the views of the list, so that you display all additional columns that you need from the lookup item. And since the main lookup and the auxiliary lookup fields are automatically synchronized (SharePoint does that for us), you don’t need any additional code in item event receivers for instance.

Let’s now have a look at the field schema of the “normal” and “associated” lookup columns:

<Field Type="Lookup" DisplayName="Department" Required="FALSE" EnforceUniqueValues="FALSE" List="{ceaf935e-b9c6-48a0-8c23-bcec58a24c91}" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" RelationshipDeleteBehavior="None" ID="{98948dfd-cea5-4d6c-ac47-25bafa5218de}" SourceID="{bca1cd44-3822-49b6-b68c-2ff28ced1726}" StaticName="Department" Name="Department" ColName="int1" RowOrdinal="0" Group="" />

This is the schema of the “normal” or main lookup column – as you see, there is nothing specific in its schema – it is just a regular lookup column. And this is the schema of one of the associated lookup columns that I created for the lookup column above:

<Field Type="Lookup" DisplayName="Department:Code" List="{ceaf935e-b9c6-48a0-8c23-bcec58a24c91}" WebId="4ee36ddf-5b1b-470b-9f9a-fbd970edf5aa" ShowField="Code" FieldRef="98948dfd-cea5-4d6c-ac47-25bafa5218de" ReadOnly="TRUE" UnlimitedLengthInDocumentLibrary="FALSE" ID="{cd2b77b9-6238-4e2e-99ee-826826dc09f2}" SourceID="{bca1cd44-3822-49b6-b68c-2ff28ced1726}" StaticName="Department_x003a_Code" Name="Department_x003a_Code" Version="1" />

So, as you see we have all attributes for a normal lookup column, but one extra attribute as well – “FieldRef”. The value of this attribute is a Guid and this is exactly the ID attribute of the main lookup column. So, obviously the “FieldRef” attribute is the one that defines this association between the main and the associated lookup columns. Notice also the “ReadOnly” attribite in the associated lookup field’s schema which is set to TRUE – this guarantees that the associated lookup column doesn’t appear in the new and edit forms of the SharePoint list (you don’t need it there anyway, since it is synchronized automatically).

This is indeed a very useful new functionality in SharePoint 2010, but you may already ask yourself what SharePoint 2007 has to do in the whole matter – we don’t have this functionality there. Well, it is true that SharePoint 2007 lacks the user interface for creating “associated” lookup columns but this doesn’t mean that we cannot create columns with code specifying the values for all attributes in their schema as we decide. And it turns out that when we create a lookup column in a list and set its “FieldRef” attribute to “point” to another lookup column in the same list (both columns should target one and the same lookup list) – the “associated” lookup functionality actually works – just like in SharePoint 2010 (note here – I tested this on SharePoint 2007 service pack 2). Another detail here is that you shouldn’t forget to set the “ReadOnly” attribute of the field to TRUE – otherwise the “associated” lookup column will appear in the edit form of the list and when you try to save the list item, the page will crush with a nasty error.

As to the question of how you can create a lookup column (an associated lookup column) with code (and set its FieldRef attribute) – probably the easiest way is to use the SPFieldCollection.AddFieldAsXml method, which accepts a string parameter containing the schema XML of the new field. Note that you will have to properly format the schema XML of the associated lookup field providing the correct values to its attributes (the final XML schema should look similar to the one above):

• in the ID attribute – a unique Guid value
• in the Name and StaticName attributes – unique (within the SharePoint list) internal name of the field
• in the List attribute – the ID of the lookup list
• in the WebId attribute – the ID of the web containing the lookup list (in most cases this is the web of the current list)
• in the FieldRef attribute – the ID of the main lookup field to which we want to associate the new lookup column
• in the ShowField attribute – the internal name of the column in the lookup list whose value should be displayed in the new lookup column
• in the ReadOnly attribute – this should be set to TRUE

And the net result will look something similar to this:

In the sample screenshot, the “Department” column is the main lookup column (displaying the “Title” column from the lookup list) and the “Department country” column is the associated lookup column (displaying the custom “Country” text column from the lookup list). Note also that the value displayed in the list view in the column of the associated lookup field is not rendered as a link, unlike the value in the column of the main lookup – actually this is exactly the same behavior as we have in SharePoint 2010. And most importantly again the same as in SharePoint 2010, the values of the associated lookup column or columns get automatically synchronized with the changes of the selected lookup item in the main lookup field.

[update Dec 13, 2010]

Shortly after the publishing of this article there was a comment (by Szymon) that once you create an associated lookup field you cannot delete it – something that I have overlooked. The error that you receive is “One or more field types are not installed properly. Go to the list settings page to delete these fields.” The reason for that is some kind of restriction in SharePoint 2007 (in SharePoint 2010 it works perfectly well) and I have seen the same error when I tried to delete “computed” (not to be confused with “calculated”) fields. After some research the one common thing between the associated lookup and the computed fields is that they both lack the “ColName” attribute, which means that there is no associated database column in the SharePoint “all user data” SQL table. Which also means that these types of columns don’t take space in the content database as one can expect. The bottom line is that you have a field that doesn’t take space in the SharePoint content database, but you still can’t get rid of its schema. As a work around you can set also its “Hidden” attribute so that the field doesn’t appear in the available view fields of the list in the standard SharePoint UI. Still, it is important that you know that once created you cannot delete (at least not using standard SharePoint object model) the associated lookup fields.

The other drawback that you have with associated lookup columns in SharePoint 2007 is that if you use them in list views or as view fields in SPQuery objects you need to always specify the main lookup column as well. If you miss it, you will receive the ugly SharePoint error “Cannot complete this action”.

XsltListViewWebPart – how to display columns based on permissions

The standard SharePoint security trimming in the XsltListViewWebPart works on the list item level – the items in the displayed SharePoint list may inherit the permissions from their parent list, but may also have different permissions and based on these the viewing user depending on his rights may see different result sets. In most cases this “horizontal” security trimming will be sufficient for the end users, but there may be cases when a “vertical” type of security trimming is also requested – meaning that certain columns in the list view are displayed only to users with particular rights for the target SharePoint list.

So, I created a small “proof-of-concept” type solution that demonstrates how this can be achieved. The implementation is fairly simple and involves only changing of the XSL used by the web part and modifying its “PropertyBindings” property. The modifications of the rendering XSL are obviously the core part of the implementation but you may wonder what the purpose of using the “PropertyBindings” property of the XsltListViewWebPart is (if you are not familiar with the “PropertyBindings” property and how to use it, you can check out my extensive posting on the subject here). The idea is simple – in the “PropertyBindings” property you can keep configuration information that also becomes available in the XSL of the web part. And the configuration data that is needed in our case is the permissions’ data for the list columns that the web part displays – it is about what rights exactly the user should have for the target list, so that he can see one or another column in the list view. The question here is why put this configuration in the web part’s property bindings and not in the fields’ schema itself for example (the field schema can easily be extended with custom attributes – e.g. xmlns:PermissionMask="0x7FFFFFFFFFFFFFFF"). The main reason for this is that if you use custom attributes in the field schema XML, you cannot then use them in the rendering XSL of the XLV web part, custom attributes simply don’t appear in the view schema XML that is available through the “$XmlDefinition” XSL parameter (check this MSDN article for a sample view schema XML in the XLV’s XSL – the field schema data is in the View/ViewFields/FieldRef elements). Another point here is that even if it were possible to store the field permission data in the field’s schema, this would impact all list views that use the customized XSL (and display the particular column), and with the setting of the “PropertyBindings” property only the current XLV web part will be affected. It is hard to judge whether this is of advantage or disadvantage  and probably depends on the specific case that you may have.

And let me first show you the “PropertyBinding” XML that should be added to the “PropertyBindings” property (I said “added”, because the property normally already contains the XML of other property bindings):

<ParameterBinding Name="ColumnPermissions" DefaultValue="Author|9223372036854775807|Editor|756052856929" />

The “Name” attribute specifies the name of the xsl:param element that will be initialized with the value of the property binding in the web part’s XSL. Its value is in the “DefaultValue” attribute – it is a long string containing values delimited by the ‘|’ character. At odd positions you have field names (internal names actually) and at even positions you see big numbers, which are actually the permission masks that should be applied for the list columns which precede the corresponding number. The permission mask is determined by the standard SharePoint SPBasePermissions enumeration: 9223372036854775807 (hex 7FFFFFFFFFFFFFFF) corresponds to the “Full Control” permission level and 756052856929 (hex B008431061) corresponds to the “Contribute” permission level. This means that the user will see the “Author” (“Created by”) column only if he has “Full Control” rights and the “Editor” (“Modified by”) column only if he has “Contribute” rights for the SharePoint list that is displayed. Note that all fields that are not specified in the property binding will be always visible to all users.

Let’s now move to the custom XSL that should handle the rendering and display only the columns that the current user has rights to see. Before, I show you the complete source of the custom rendering XSL, I want to draw your attention to one important thing – the trick that is used in the XSL to check whether the permission masks for the fields have a match with the effective permissions of the current user for the source SharePoint list. It is very simple actually and uses … a standard SharePoint “ddwrt” XSLT extension method:

<xsl:if test="ddwrt:IfHasRights($checkResult)">

The “IfHasRights” extension method receives an integer parameter for the permission mask and returns true or false depending on whether the current user has those rights for the SharePoint list of the web part. Note that the check is made for the SharePoint list, not the items of the list and not for its parent SharePoint site.

And here is the complete source of the custom XSL (check the extensive comments inside it for more details)

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">

 

  <!-- import the standard main.xsl, so we have all standard stuff -->

  <xsl:import href="/_layouts/xsl/main.xsl"/>

  <xsl:output method="html" indent="no"/>

 

  <!-- we get here the field permissions configuration from the PropertyBinding with the same name -->

  <xsl:param name="ColumnPermissions" />

  <!-- this is the standard XmlDefinition parameter - the XLV initializes this one with the view schema -->

  <xsl:param name="XmlDefinition" />

 

  <!-- this variable contains the parsed configuration data like <token>Author</token><token>9223372036854775807</token> etc -->

  <xsl:variable name="tokens">

    <xsl:call-template name="Tokenize">

      <xsl:with-param name="string" select="$ColumnPermissions" />

      <xsl:with-param name="delimiter" select="'|'" />

    </xsl:call-template>

  </xsl:variable>

 

  <!-- here we create a copy of the original XmlDefinition removing all View/ViewFields/FieldRef elements for which the user doesn't have rights -->

  <xsl:variable name="XmlDefinition2Raw">

    <xsl:apply-templates mode="transform-schema" select="$XmlDefinition" >

      <xsl:with-param name="tokenSet" select="msxsl:node-set($tokens)" />

    </xsl:apply-templates>

  </xsl:variable>

 

  <!-- the one above is a sequence of tags, in order that it can be used exactly like the standard $XmlDefinition it should be converted to a node set -->

  <xsl:variable name="XmlDefinition2" select="msxsl:node-set($XmlDefinition2Raw)" />

 

  <!-- this one is simply a copy of the template with the same match from the standard vwstyles.xsl (thus we override it), the only difference is that it uses our trimmed $XmlDefinition2 instead of the standard $XmlDefinition -->

  <xsl:template match="/">

    <xsl:choose>

      <xsl:when test="$RenderCTXOnly='True'">

        <xsl:call-template name="CTXGeneration"/>

      </xsl:when>

      <xsl:when test="($ManualRefresh = 'True')">

        <xsl:call-template name="AjaxWrapper" />

      </xsl:when>

      <xsl:otherwise>

        <xsl:apply-templates mode="RootTemplate" select="$XmlDefinition2"/>

      </xsl:otherwise>

    </xsl:choose>

  </xsl:template>

 

  <!-- the same as the template above -->

  <xsl:template name="AjaxWrapper" ddwrt:ghost="always">

    <table width="100%" border="0"  cellpadding="0" cellspacing="0">

      <tr>

        <td valign="top">

          <xsl:apply-templates mode="RootTemplate" select="$XmlDefinition2"/>

        </td>

        <td width="1%" class="ms-vb" valign="top">

          <xsl:variable name="onclick">

            javascript: <xsl:call-template name="GenFireServerEvent">

              <xsl:with-param name="param" select="'cancel'"/>

            </xsl:call-template>

          </xsl:variable>

          <xsl:variable name="alt">

            <xsl:value-of select="$Rows/@resource.wss.ManualRefreshText"/>

          </xsl:variable>

          <a href="javascript:" onclick="{$onclick};return false;">

            <img src="/_layouts/images/staticrefresh.gif" id="ManualRefresh" border="0" alt="{$alt}"/>

          </a>

        </td>

      </tr>

    </table>

  </xsl:template>

 

  <!-- this template creates the copy of the standard $XmlDefinition trimming the View/ViewFields/FieldRef elements for which the user doesn't have rights -->

  <xsl:template mode="transform-schema" match="View" >

    <xsl:param name="tokenSet" />

    <!-- copy the root View element -->

    <xsl:copy>

      <!-- copy the root View element's attributes -->

      <xsl:copy-of select="@*"/>

      <!-- copy the child elements of the root View element -->

      <xsl:for-each select="child::*">

        <xsl:choose>

          <xsl:when test="name() = 'ViewFields'">

            <!-- special handling of the ViewFields element -->

            <ViewFields>

              <!-- iterate the ViewFields/FieldRef elements here -->

              <xsl:for-each select="child::*">

 

                <!-- get the permission mask for the FieldRef element, by the Name attribute -->

                <xsl:variable name="checkResult">

                  <xsl:call-template name="GetValueFromKey">

                    <xsl:with-param name="tokenSet" select="$tokenSet" />

                    <xsl:with-param name="key" select="./@Name" />

                  </xsl:call-template>

                </xsl:variable>

 

                <xsl:choose>

                  <!-- if the permission mask is not empty and the ddwrt:IfHasRights returns true, copy the field -->

                  <xsl:when test="$checkResult != ''">

                    <!-- this is how we check whether the user has sufficient rights for the field (checking the permission mask of the field against the user's permissions for the source list) -->

                    <xsl:if test="ddwrt:IfHasRights($checkResult)">

                      <xsl:copy-of select="."/>

                    </xsl:if>

                  </xsl:when>

                  <xsl:otherwise>

                    <!-- if we don't have the field in the configuration simply copy the FieldRef element -->

                    <xsl:copy-of select="."/>

                  </xsl:otherwise>

                </xsl:choose>

 

              </xsl:for-each>

            </ViewFields>

          </xsl:when>

          <xsl:otherwise>

            <xsl:copy-of select="."/>

          </xsl:otherwise>

        </xsl:choose>

      </xsl:for-each>

    </xsl:copy>

  </xsl:template>

 

  <!-- several helper templates that parse the configuration string and return the permission mask for the field by providing the field's internal name -->

  <xsl:template name="GetValueFromKey">

    <xsl:param name="tokenSet" />

    <xsl:param name="key" />

    <xsl:apply-templates select="$tokenSet/token[text() = $key]" />

  </xsl:template>

 

  <xsl:template name="NextNode" match="token">

    <xsl:value-of select="following-sibling::*"/>

  </xsl:template>

 

  <xsl:template name="Tokenize">

    <xsl:param name="string" />

    <xsl:param name="delimiter" select="' '" />

    <xsl:choose>

      <xsl:when test="$delimiter and contains($string, $delimiter)">

        <token>

          <xsl:value-of select="substring-before($string, $delimiter)" />

        </token>

        <xsl:call-template name="Tokenize">

          <xsl:with-param name="string" select="substring-after($string, $delimiter)" />

          <xsl:with-param name="delimiter" select="$delimiter" />

        </xsl:call-template>

      </xsl:when>

      <xsl:otherwise>

        <token>

          <xsl:value-of select="$string" />

        </token>

        <xsl:text> </xsl:text>

      </xsl:otherwise>

    </xsl:choose>

  </xsl:template>

 

</xsl:stylesheet>

One note about the XSL code – if you have a look at it, you will notice that it replaces (overrides) two of the core XSL templates used in the standard vwstyles.xsl file. It then provides a modified copy of the standard view schema XSL parameter ($XmlDefinition) in these templates. And on the other hand if you check the vwstyles.xls file, you will notice that there are still other XSL templates in it that also use the standard $XmlDefinition parameter (and thus not the modified copy) – these are the templates that handle aggregations and groupings, which means that the customized XSL above won’t be able to handle properly these cases.

And finally a few words on how to use this sample: the first thing is to save the XSL to a XSL file in the TEMPLATE\LAYOUTS or TEMPLATE\LAYOUTS\XSL folder (a subfolder of these two is also possible). Then you need to select your XLV web part (it may be an XLV from a standard list view page or an XLV that you placed on a content page) and change its PropertyBindings and XslLink properties. You can do that with code or using my web part manager utility which provides an easy to use UI for that (you can download it from here). For the “PropertyBindings” property, you should append the XML of the field permissions configuration which should look like the sample above. In the “XslLink” property you should specify the link to the XSL file in which you have saved the custom XSLT above – provided you’ve saved the XSL file as TEMPLATE\LAYOUTS\columns_main.xsl, the value that you should put in the “XslLink” property should be: /_layouts/columns_main.xsl.