Joining the Task List with Related Content in a DVWP

The Situation

SharePoint 2010 lets you set up approval work flows on lists, and stores the approval tasks in the out of the box Task list. But, out-of-the-box, when you view the “My Tasks” view of the Tasks list, all you can see about the content you are supposed to approve is the title, which links to the view form for the item.

Out of the Box View of My Tasks

Out of the Box View of My Tasks

To be able to display more information about the item we are approving, we’re going to create a linked datasource and a DVWP with a joined sub-view. (More information on how to do this can be found in the posts “Display item counts in a Data View Web Part” and “Joined Subviews with Linked Datasources in SharePoint 2010.”)

Unlike in the examples in those other posts, we can’t control how SharePoint manages the look ups between the related content list and the task list. Unfortunately, it seems that the only “look up” provided is an ID at the end of the string in the “Related Content” field. No problem, we can work with that!

The Solution

Go ahead and create your dataview webpart and initially, join a subview based on the ID of the related content  and the WorkflowLink of the Task List. Find the dvt_2 template in your code view. You should see a select for rows that looks something like this:

<xsl:variable name="Rows" select="../../../YourListName/Rows/Row[@ID=$dvt_ParentRow/@WorkflowLink]" />

Change it to the following (replacing “YourListName” with your joined list).

<xsl:variable name="ChildID">
<xsl:value-of select="substring-after($dvt_ParentRow/@WorkflowLink, '=')" />
</xsl:variable>
<xsl:variable name="Rows" select="../../../YourListName/Rows/Row[@ID=$ChildID]" />

What does this code do? First, it creates a variable called “ChildID” and then it extracts the ID of the related content from the WorkflowLink column in the parent task list. Finally, it runs a select on your related content list matching your related content’s ID with the associated Task.

Doing so, you can output a view like so:

A View the Combines Task Information with Related Content

A View the Combines Task Information with Related Content

Voila. Easy way to connect a task list with its related content.

Advertisements

Modify Column Headers in XSLTListViewWebPart (SP2010)

The Situation

You would like to use an out-of-the-box view of a list. But, the list’s column names are not the most end-user friendly. You’d like to modify the column names without losing any of the functionality of a list view web part, such as sorting and filtering. While you theoretically can use SharePoint Designer 2010’s design view to change the column header text, I’ve found it to be fairly unreliable. In other words, when I’ve tried it, I’ve had less than successful results. Fortunately, it’s easy to modify the XSLT to accomplish what we want to do.

The Set Up

In my example, I’m using the “My Tasks” view of a task list that’s associated with an approval workflow. I want my users to be able to quickly get to the the item they need to approve before being asked to approve or reject it. The column name for the associated item is “Related Content” – not exactly an intuitive title for most users. Since I know that in this site, the only thing stored in this task list is the tasks for the approval workflow, I can make this view more specific. We’re going to change “Related Content” to “Review Funding Request” and change “Title” to “Approve/Reject Request.”

To follow along you can use the out of the box approval workflow on any list, using the task list to track tasks.

The Step-By-Step

Assuming you’ve set up your approval workflow, the first step is to get the “Related Content” column in our “My Task”s view. The “My Tasks” view is an out-of-the-box view on the task list. So, navigate to your tasks list, switch to the “My Tasks” view and choose “Modify this View.”

Modify the My Tasks View in the Browser

Modify the My Tasks View in the Browser

Add the “Related Content” column to the view. I made it the first column.

Add the Related Content column to get the link to the associated item

Add the Related Content column to get the link to the associated item

Save your view and open the site in SharePoint Designer 2010. Navigate to the Tasks list, and choose to edit the the “My Tasks” view. Switch to the Design tab and choose to “Customize XSLT” and select “Customize Entire View.”

Switch to Design Tab and Choose to Customize the XSLT

Switch to Design Tab and Choose to Customize the XSLT

Behold you will now have a couple thousand lines of XLST code in your code view. Don’t panic! Most of it can be ignored. You are going to do 2 things – add a custom template, and replace 18 instances of a specific line of code with a call to your custom template. (Find and Replace in SPD2010 is awesome.)

The Custom Template

Basically, instead of just outputting the field’s title, we want to output our custom text if the current field title matches the one we’re trying to replace. I’m replacing 2 field titles, so I’m passing in the current field title as a parameter, and then using a choose statement to do the comparison. If the current field title matches either of the 2 I’m replacing, I return my custom text. If not, I return the field title. Pretty simple. (Note that I begin the name of my custom XSLT templates with “CES” because that’s the organization I work for. You should have your own naming convention that ensures your template names will not conflict with Microsoft’s.)

<xsl:template name="CESDisplayCustomTitle">
<xsl:param name="fieldtitle" />
<xsl:choose>
<xsl:when test="$fieldtitle = 'Title'" >
<xsl:value-of select="'Approve/Reject Request'" />
</xsl:when>
<xsl:when test="$fieldtitle = 'Related Content'">
<xsl:value-of select="'Review Funding Request'" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$fieldtitle" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>

I put my custom templates at the end of the existing XSLT code. To find the right spot, put your cursor in the code view in SPD2010 and search for

</xsl:stylesheet></Xsl>

Insert the custom template just before this code.

(Note: don’t be shocked when you open the listview later and your code has moved. SPD2010 does that. No worries.)

Now, we need to call our template. You’re looking for the following code to replace:

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

And you’re going to replace it with this code:

<xsl:call-template name="CESDisplayCustomTitle">
<xsl:with-param name="fieldtitle" select ="$fieldtitle" />
</xsl:call-template>

(Note: Update the template name to match your custom template name. Also,  make sure to highlight all of the XSLT EXCEPT your custom template when you do your find and replace selection. If you do the find and replace and include your custom template, you’ll get an infinite loop error.)

You should be able to run a find and replace procedure, and then click into the design view to have SPD2010 update the design view with your new column headers.

Perform the Find and Replace

Perform the Find and Replace

And, the finished view of the list, with all functionality intact.

New Column Headers with all Functionality Intact

New Column Headers with all Functionality Intact

Caveat

This doesn’t work for attachment columns. But, it would be easy to handle those, as well. Attachments are output with disable-output-escaping=”yes.” You’d need to account for that in your custom template and then replace all the instances of:

<xsl:value-of select="$fieldtitle" disable-output-escaping="yes"/>

with your call to the custom template. I’m not changing that column name, so I didn’t include that.

Happy coding!

Display item counts in a Data View Web Part

In SharePoint 2010, you can now do inner and left joins in CAML queries. But, if you can manage that via SharePoint Designer I’ve yet to figure out how. (I haven’t given up yet – anyone have any great ideas?) Instead, in SharePoint Designer all of our “joins” happen via XSLT.  It’s not really a “join” at all. Marc Anderson explains all this really nicely. So, if we want to get a count of all the child items in a parent/child relationship, we need to modify the XSLT.

The good news is that it’s actually really easy.

The Set Up

In my example, I have 3 lists – Interview Questions, Program Area, and Skills/Assets. The idea is that we have a list of questions that might be used, and a list of the skills or assets they assess and Program Areas they’re related to. One interview question can be relevant for multiple skills or program areas, so we have a look up columns in the Interview Questions table that allows multiple values from the Skills/Assets list or the Program Area list. Then, on a page in our site, we want to provide links for each skill and program area that goes to a page that filters the Interview Questions by that skill or program area. I’ve already done the skills. We’ll do the program area together.

We want to get item counts for Program Areas

We want to get item counts for Program Areas

So, if you’re playing along, we’ll need 3 lists:

  1. Skills/Asset (1 column: Title)
  2. Program Area (1 column: Title)
  3. Interview Questions (3 columns: Title, Program Area, Skill/Asset)
Columns in the Interview Questions Table

Columns in the Interview Questions Table

Add some data to each of those tables. Be sure to have more skills and/or program areas than you use. (This helps with finding the empty template later.)

Now create a page. This can be a wiki page or a web part page, and it doesn’t really matter what it’s called. I did this right on my site’s home page.

Fire up SharePoint Designer. Before we get into editing the page, we first have to create a linked data source. Microsoft has general information about doing this. But, I created a quick video to show you exactly what to do for this tutorial:

The Solution

Now that we have a linked data source and a page, it’s time to use our data source  in a Data View Web Part (DVWP) on our page.

So, find your page and jump into edit mode. I always use split mode (code and design visible). The first step is to insert an empty Data View Web Part. Place your cursor where you want the DVWP. Click Insert -> Data View -> Empty Dataview.

Insert an Empty Data View Web Part

Insert an Empty Data View Web Part

Click the link that says, “Click here to select a data source.” and select the linked data source we created earlier.

Choose our Linked Datasource

Choose our Linked Data source

The Data Source Details pane on the right side will populate with the columns and data from our linked data source. Select the Title column from the Program Area data source and select “Insert Selected Fields as…” -> “Multiple Item View.”

Insert the Program Area as a Multiple Item View

Insert the Program Area as a Multiple Item View

By default, SPD adds all DVWP’s as tables. For our purpose, we don’t want a table. We want a bulleted list. So, click the Design Tab and choose the Bulleted List style. This is also the point at which you can sort and filter, if you need to do any of that. I’m sorting by Title. You may get a message to “Click to set the display text if no matching items are found.” Go ahead and click it and set it to “(0).”

Setting the Design to Bulleted List

Setting the Design to Bulleted List

Okay, so far we haven’t done anything too impressive. Now is when the fun starts. Position your cursor at the end of one of the Program Area titles, add a space, and then move over to the Data Source Details panel. We’re going to add our Interview Questions Data Source as a Joined Subview. It doesn’t matter which field you choose, as we’re going to delete all the field information anyway. I’m using the title, since it’s a required field. A dialog box will pop up asking you to select the fields on which to join. In our case, we want the Title field from the left row and the “Program_x0020_Area.” field from the right row. You can click “More Fields” to get a secondary dialog box that will show you the actual field values. That can be very helpful. (Note that sometimes SPD will give you a blank dialog box for the joined subview. That’s SPD looking ahead and realizing there are no matches. Try placing the cursor after an item in your main list that you know is used in the look up column.)

Join the Subview

Join the Subview

Now we should see a table inserted after each item in our bulleted list, that displays either the matching rows or our “no match” text. But, wait – nothing matches! That’s okay. We can fix that. If we were using a single select look up, the best way to fix that is to follow these directions. But, since we’re using a multiple select look up, we need to modify the filter on the XSLT.

Click into the code view and do a search for <xsl:variable name=”Rows” select=. You’re looking for the following line of code:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[@Program_x0020_Area.=$dvt_ParentRow/@Title]" />

This line of code tells the XSLT to match any rows where the Program_x0020_Area. field equals the parent’s Title field. We’ll never return any rows this way, because they’ll never match exactly. However, the Program_x0020_Area. field will CONTAIN the parent’s Title field. So, we need to change this from an “equals” operation to a “contains” operation. The XSLT contains operation syntax looks like this:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />

Change that field and you should see your DVWP update to display a table of rows under each bullet point for which there are matches. We don’t want a table of rows, so let’s modify our XSLT some more. Right below where you found the select statement we just modified, you should find an xsl:choose statement. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
<xsl:call-template name="dvt_2.empty" />
</xsl:when>
<xsl:otherwise>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_2_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>
</xsl:if>
<th class="ms-vh" nowrap="nowrap">Title</th>
</tr>
<xsl:call-template name="dvt_2.body">
<xsl:with-param name="Rows" select="$Rows" />
<xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
</xsl:call-template>
</table>
</xsl:otherwise>
</xsl:choose>

(Note: your template may be named dvt_1. SharePoint Designer increments template names as DVWPs are added to the page. It’s always a good idea to rename these to something more descriptive, such as dvt_[listname].)

The first thing we want to do is remove the call-template to the dvt_2.empty template and replace it with a simple text output of “(0)”. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>

Next, we want to highlight everything between the <otherwise> tags, and replace it with the the following:

(<xsl:value-of select="$dvt_RowCount" />)

The dvt_RowCount is a variable that SharePoint Designer sets based on the count of the rows returned from our modified select statement.

Finally, delete the rest of the dvt_2 templates. Your complete dvt_2 template should now look like this:

<xsl:template name="dvt_2">
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="dvt_ParentRow" select="current()" />
<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />
<xsl:variable name="dvt_RowCount" select="count($Rows)" />
<xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0" />
<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>
(<xsl:value-of select="$dvt_RowCount" />)
</xsl:otherwise>
</xsl:choose>
</xsl:template>

And, your rendered DVWP should look something like this:

XSLT Rendering Child Item Counts

XSLT Rendering Child Item Counts

I also added a link to each Program Area Title, which passes the title as a query string parameter to another page.

Add a Link and Pass the Title as a Query String Parameter

Add a Link and Pass the Title as a Query String Parameter

While this tutorial has a lot of steps, once you’ve done it once, you’ll see how easy it really is – 15 lines of XSLT to generate the item count of your joined subview!

Hope that helps you out. And if any one figures out that CAML join syntax in SPD, let me know.

Displaying Multiple Select Fields One Per Row in a Dataview Web Part

As I’m working more and more with SharePoint Designer 2010, I’m finding that I’m developing a library of XSLT snippets that might be useful for others. This is one example. (Note, all screenshots are from SPD2010. Click the images to view a larger version.)

The Scenario

You have a list that uses another list as a look up and allows for multiple values. You want to display that list in a Data View Web Part. The out of the box behavior is to display the multiple values as a semi-colon delimited string of text. Like so:

Default Behavior - an unreadable string of text

Default Behavior – an unreadable string of text

We can make the text readable by adding a parameter to the select statement – disable-output-escaping=”yes.” That will render a functional list of links.

<xsl:value-of select="@Program_x0020_Area" disable-output-escaping="yes"/>

A Functional List of Links

A Functional List of Links

But, a semi-colon delimited list of links isn’t exactly the most user-friendly presentation.

The Solution

Thankfully, there’s a fairly simple way to fix it, though it does require diving into XSLT. I grabbed the base template from kicktech, and simply implemented it in SharePoint Designer.

In split view, find the XSLT in your page. You can put this extra template anywhere outside of the existing templates. I like to add my templates at the end of the stylesheet. So, search for </xsl:stylesheet></XSL> and paste the following right above it.

<xsl:template name="string-replace-all">
<xsl:param name="text" />
<xsl:param name="replace" />
<xsl:param name="by" />
<xsl:choose>
<xsl:when test="contains($text, $replace)">
<xsl:value-of select="substring-before($text,$replace)" />
<xsl:value-of select="$by" />
<xsl:call-template name="string-replace-all">
<xsl:with-param name="text"
select="substring-after($text,$replace)" />
<xsl:with-param name="replace" select="$replace" />
<xsl:with-param name="by" select="$by" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>

Now, find the column where you are outputting your multiple select lookup. It should look something like this:
<xsl:value-of select="@YourColumnName" disable-output-escaping="yes"/>

We are going to replace that simple select with a call to our custom template. But, we need to wrap it with variable tags, so that when we’re done, we can once again disable the output escaping. Then, we’ll do a simple select on our new variable. Here’s an example of replacing the semi-colon with a line break (change “@YourColumnName” to the column you want to display):
<xsl:variable name="thisRow">
<xsl:call-template name="string-replace-all">
<xsl:with-param name="text" select="@YourColumnName" />
<xsl:with-param name="replace" select="';'" />
<xsl:with-param name="by" select="'<br/>'" />
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="$thisRow" disable-output-escaping="yes"/>

The final result is a one-per-line list of the items selected in the multiple select column.

Final Result - One Item Per Line

Final Result – One Item Per Line

You might note that XLST has a built-in translate function that would do the same thing if you’re only wanting to replace one character with another. However, it doesn’t work to replace a single character with a set of characters.

Associated Calculated Columns in Reusable Workflows (SP2010)

One of the great things about SharePoint 2010 is the ability to create reusable workflows. No more tying a workflow to an individual list or library. Now, you can tie a workflow to a content type, and reuse it in multiple list or library instances. Unfortunately, today I found what I think is a bug in using calculated columns in reusable workflows. Let me lay out the scenario.

Scenario

To play along, you’ll need to understand a bit about site columns, content types, and reusable workflows.

    1. Create a test content type with the following site columns.
      • Cost 1 (currency column)
      • Cost 2 (currency column)
      • Total Cost (calculated column with the function =SUM([Cost 1],[Cost 2]))
    2. Create a test list and set the default content type to be the test content type.
    3. Create a test reusable workflow (no steps needed yet), publish it, and attach it to the test content type.

Got that all set up? Great.

Chances are good, you’re going to want to access the columns from your content type in the workflow – either for logging purposes or to generate messages in emails or what have you. But, since the workflow isn’t directly connected to the list, you’ll need to create association columns to the data you want to use. That’s a straight-forward process, and takes just 3 quick steps.
      1. Click “Association Columns” in the ribbon.
      2. Click “Select a Site Column.”
      3. Find your site column and select it.
Steps to Set Up an Association Column

Steps to Set Up an Association Column

We should be good to go! Now when you want to use the Association Column, it will show up as part of “Current Item” in your workflow.

Selecting an Association Column

Selecting an Association Column

Super easy, right?

The Problem

Hold the phone. Nothing is ever quite THAT simple with SharePoint. What happens with the datatype of that Site Column?

Well, if this is a non-calculated column, you’ve got no problem. SharePoint Designer recognizes the data type of the column and asks you how you want to format it.

Select the format of the returned field

Select the format of the returned field

The same is true if you’re using a list workflow with a calculated column.

But, if you’re using a reusable workflow with a calculated column, you’re out of luck. SharePoint Designer will only return it as a string.

No option to select return type with calculated association columns

No option to select return type with calculated association columns

Worse yet, the string that is returned includes information on the data type – in the case of my currency column, the string returned is “float;#40.0000000000000.”

Unusable string is returned

Unusable string is returned

So, what do you do?

I’m guessing you could write some crazy string manipulation functions to handle the formatting of this weird string.

Or, you could try to set the value of the Association Column to an intervening workflow variable of type double. The interface will choose to return the Association Column as a double.

Setting an Association Column to a Local Variable

Setting an Association Column to a Local Variable

You could try that. But, it won’t work. It LOOKS like it will work. But, if you try to do it, here’s what you get:

Coercion Failed: Unable to transform the input lookup data into the requested type.

Coercion Failed: Unable to transform the input lookup data into the requested type.

Is anybody else getting the feeling this is a bug? No? Okay. Moving right along.

The Total Hack Solution

What I’ve found is that the easiest thing to do is to (if possible) is just replicate your calculations within SharePoint Designer. In my case, this resulted in a series of addition problems with intervening variables.

Replicating the calculation in SPD

Replicating the calculation in SPD

Barring that approach, I think your only option would be to use a list-based workflow on the list to update a secondary non-calculated column and firing that workflow before the reusable workflow. Of course, that completely defeats the purpose of having a reusable workflow.

*sigh*

Any body else have any bright ideas?