Render SharePoint list headers in the order you want

When it comes to presenting the contents of a SharePoint list in a Data View Web Part (Data Form Web Part), your out-of-the-box options are a bit limited. It's certainly possible to group list items under headings, but SharePoint relentlessly displays these headings in alphabetical (or reverse alphabetical) order. This can be a bit frustrating if you had some other order in mind.

For example, I was planning to display a list of navigational links on a page. I had stored the link data in a list, and I'd even added a custom column to the list to contain the group headings and tagged all my links with a group value.

But when I switched to SharePoint Designer and created the DVWP, and I selected Show group header under Sort and Group ...

... SharePoint helpfully displayed the headers in alphabetical order!

What I really wanted was for the headings to display in a different order. I thought about finding ways to create headings where the order of importance would also be alphabetical order, but that proved far too difficult and awkward. So I resolved to figure out another way to manage the order of the headings.

I wanted "Head Office" as the first heading, followed by "UK Locations", then "Europe", then "Rest of the World". The only way I could think of to do this was to preface each heading with a number value (to force the order) then, in the web part, perhaps use the "substring" function to remove the numerical value from the beginning of each header.

So ... in SharePoint Designer, add your DVWP to the page.

Then use the Common Data View Tasks panel to arrange the list output under headers by clicking on Sort and Group and selecting the radio button next to Show group header. This displays the headers in alphabetical order as noted earlier, but this time, we can see the number prefixes I placed at the beginning of each header value.

To get rid of the number prefixes, we're going to use the substring function.

In SharePoint Designer, navigate to the DVWP and find the XSLT call that renders the header name. It should include the variable "$fieldvalue". It'll be towards the end of the DVWP code, and look like this:

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

In my version, I added a <strong> tag and, as I wanted my headers to be rendered in blue, I added a color attribute, like this:

<strong style="COLOR: #009ae4;"><xsl:value-of select="$fieldvalue" /></strong>

Then, to remove the number prefix, I changed the "$fieldvalue" call to look like this:

<strong style="COLOR: #009ae4;"><xsl:value-of select="substring($fieldvalue, 3)" /></strong>

The substring function here specifies the character to begin rendering the string, in this case, the third one. The result is that my headers still appear in the order specified by the number prefixes in the List but those number prefixes are invisible.

Hope this helps someone.

Display multiple random list items in Dataview Web Part

I always like to configure our corporate Intranet to minimise the amount of admin work that needs to be done. One way to keep content looking fresh with minimum manual updating is to put all your text into a SharePoint list, then present it on the page using a Data View Web Part, with an added bit of XSLT scripting to show a random item from the list.

I didn't have much trouble finding a bit of scripting (via Google) to achieve this effect. I ran in to difficulties when I wanted to take this one step further and present several random items from a SharePoint list. However hard I Googled, I just couldn't turn up comprehensive, step-by-step instructions on how to display more than one random item.

The solution turned out to be a combination of two different methods, that I'd picked up in two different locations. As always, as I couldn't find the total solution by Googling alone, I'll explain how to display a number of random items from a list here.

First things first: set up a list. In my case, I wanted to display a set of four large buttons that users can click on to be taken to content in the sub-sites below the top level of the Intranet. So to have a reasonable selection of buttons to choose from, I specified at least eight for each landing page. These were stored in a document library.

As a document library is a just a flavour of list, I decided to keep things simple by using the library to double for the list and added my metadata in extra columns in the Document Library.

So I used the Title field to store the text equivalent (alt attribute) for the button image. Then I added a custom column to hold the destination URL. You could use a Hyperlink-type content type, but I just opted for a Single line of text, to keep things simple. I also added an extra column to hold the value for the tab page the buttons would be appearing on, set up as a Choice.

Then I uploaded all the button images and made sure the metadata was completed correctly.

This is what my Document library looks like when populated with my image buttons.

My next task was to create the DVWP on the tab page to display the four buttons. You almost certainly already know how to do this, so I won't waste your time here. But just in case you are struggling, here's a site that shows you How to insert a DVWP. 

The next step might not apply to you, but I was looking to manage a button set for each of my main tab pages on our Intranet. So once I had the Dataview Web Part on the page, I needed to filter out the irrelevant buttons. 

In SharePoint Designer, there's a wizard that allows you to Filter, sort and set the number of items displayed.

As I'd already set up a column to hold the name of the tab my button were going to appear on, I just had to click on Filter and set up a criterion for the tab I was working on, in this case "Community".

This allows me to filter out all the buttons except for those tagged as "Community".

Before I did any more work in the wizard, I thought I'd tidy up the HTML of the web part.

The raw DVWP tends to nest tables inside tables which can get messy and complex to work with. Some people prefer to replace the table elements with DIVs, and sometimes I might do that. But here, I thought I'd stick with a simplified table as I wanted to iterate across the row, rather than down the column. So first I removed the extra nested table.

Inside the "rowview" template, first take out the TR, TD and TABLE tags ...

As soon as you do that, the corresponding closing tags are highlighted by SharePoint Designer for you.

... SharePoint Designer then highlights the closing tags for you, making them easy to find and delete.

Now, because I'd knocked out the nested table and I wanted to iterate across the row, I needed to put an opening and closing <TR> tag inside the main table of the DVWP. Like this:

Wrap the dvt_1_body template in TR tags.

Next, I wanted to remove the column that holds the List Column names. You can do this quickly by right-clicking on the column in the Design view and selecting Delete, then Delete Columns from the drop-down menu.

Deleting the table column is very simple.

Next I needed to remove the <TR> tags that render the items from the list down the column. Doing this will cause the items to render across the row, instead. You might want to remove the legacy width attributes while you're at it.

So I removed the unneeded TR tags ...

Next, I needed to get all the "value-of"s into a single table cell and discard the remainder, like this.

Re-arranging the value-of calls and eliminating the unwanted TD tags.

My next job was to get the buttons to render across the row, and to have the ALT attribute inserted, and make each button a link to the relevant content I was highlighting. Rather than explain it, here's the final code ... you should be able to figure out what it's doing for yourself.

Here's the final code ...

It's starting to take shape, now:

Next, I needed to go back to the Wizard and set the Paging to Display All Items.

Select Display all items ...

At this point I was finally ready to apply the bits of XSLT scripting that would do the work of presenting four random items from the list, and would change each time a visitor came back to the page or refreshed.

I'd already previously implemented DVWPs where the script selected one item at random from the list to display. Suitable techniques for this are not difficult to find on the Internet via Google search. I've come across several ways to do this. The one I've most commonly used is:

<xsl:template name="dvt_1.body">
<xsl:param name="Rows"/>
<xsl:variable name="Random" select="ddwrt:Random(1,count($Rows))" />
<xsl:for-each select="$Rows[position()=$Random]">
<xsl:call-template name="dvt_1.rowview" />
</xsl:for-each>
</xsl:template>

... but this only displays one random item from the list. My challenge was to get the DVWP to display four random items. I figured this would be made more difficult because I didn't want to any of the items to be repeated, which would be a possibility with a routine that selected four items randomly, one after another.

But while digging around on Google, I came across several different ways of rendering a random item from a list and came to understand that I might have to combine two different methods to get the effect I wanted.

In the end I settled for a slightly different way of rendering a random item that turned up in several different SharePoint blogs, but this is the one I came back to. 

<xsl:for-each select="$Rows">
    <xsl:sort select="ddwrt:Random(1, $RowCount)" order="ascending"/>
    <xsl:call-template name="dvt_1.rowview" />
</xsl:for-each>

What this is doing is sorting the items in the list into a random order and rendering the first one. It's a slightly different approach to the one I was using, but it does get me a step closer to where I want to be.

I did get error messages with this saying that the variable wasn't defined, so I added in the line:

<xsl:variable name="RowCount" select="count($Rows)" />

... before the <xsl:for-each= ...> line and it seemed to work okay.

All I had to do next was figure out how to select the first four items in this randomised list and I'd be there. I found something on another blog that selected the first x number of items from a list, so I figured I might be able to combine the two elements to get a randomised first four items ... I added the line:

<xsl:if test="position() &lt; 5">

This is saying: display the row if the position is less than five (ie, four, then) ... so the "for-each" section should now look like this:

<xsl:variable name="RowCount" select="count($Rows)" />
<xsl:for-each select="$Rows">
<xsl:sort select="ddwrt:Random(1, $RowCount)" order="ascending" />
<xsl:if test="position()&lt;5">
<xsl:call-template name="dvt_1.rowview" />
</xsl:if>
</xsl:for-each>

Now the buttons will change randomly each time the user lands on the page for the first time, or indeed refreshes the page. The final effect looks like this:

The images display randomly across the row.

Then it changes to this, when you refresh.

Hit refresh and another four images display.

You can probably adapt this method to suit the set of random items you want to display.

Hope this helps someone.

Manage an email subscription list in SharePoint

I had a request from the Business about using a SharePoint teamsite to store an Excel email subscription list so that users could add and delete subscribers. It seemed to me that the requester was missing a trick, as SharePoint is capable of so much more than just storing documents and data. So I suggested that we transfer the Excel document data into a SharePoint list and use SharePoint's SMTP function to send out the email newsletter to the subscribers.

Most of it was quite easy. For example, it wasn't difficult to figure out that I'd need a teamsite containing two lists, one for the newsletter content and another for the subscribers.

But I ran into a bit of a barrier when I tried to load the subscribers' addresses into the "To" field of the Workflow email, as these were people outside the Business without access to our Intranet. As usual when I have a tricky problem, I Googled for a solution, but that got me nowhere, so I braced myself to figure out an answer for myself.

Subscribers list

I set this one up first as I knew I'd need to call it as a lookup in the Newsletter Content list.

It doesn't need to be complicated, but I included other information to help identify the subscriber's company and who in our organisation was their contact or sponsor, so the columns of this list looked like this:

• Title - with the default value of "Subscriber" (Single line of text)
• Company - so we know who they work for (Single line of text)
• Email - obviously  (Single line of text)
• Position - so we know their job title (Single line of text)
• Underwriter - name of their sponsor/contact in our company (Single line of text)
• UWteam - name of the team the subscriber deals with (Choice)
• Owner - admin responsible for taking care of this subscriber's profile (Choice)

I could have made the Underwriter field a Name field, but we had no plans to include this group of people on any communications, so I kept it simple. The Team and Owner fields are choices to minimise user error.

NewsletterFooter list

My first thought was to hard code the email footer text into the Workflow email, but then I decided to include it in a separate (third) list so that if any of the info changed, the administrators of the list could edit the text. The columns were simply:

• Title - with a default value of "Edit details" (Single line of text)
• Address - just what it says (Single line of text)
• Phone - Phone numbers, fax and website URL (Single line of text)

I did try to include the Disclaimer in here as well, but then remembered you can't do a look up on a Multiple Line field.

Newsletter content list

How many fields you put in this list depends entirely on what kind of newsletter you want to send out. I kept our one simple, with just a few columns:

• Title - used for the newsletter headline (Single line of text)
• Body - the main text goes here (Multiple lines of text)
• Link01 - to hold an embedded link (Hyperlink or Picture)
• Link02 - to hold an embedded link (Hyperlink or Picture)
• Link03 - to hold an embedded link (Hyperlink or Picture)
• Address - for the footer (Lookup)
• Phone - for the footer (Lookup)
• Disclaimer - for the footer (Multiple lines of text)
• Recipients - the subscribers (Lookup)

So the Address and Phone fields lookup the data from the NewsletterFooter list. The Recipients column looks up the data from the Subscribers list and I ticked the Allow multiple values box.

So far so good.

The Workflow email

I figured I could use a Workflow Email function to mail out the newsletter. Shouldn't be difficult, right?

So using HTML and embedded CSS styles, I put together an email that would gather up the content from the list and compile it into a simple rich format email. As this was going to subscribers outside our company, I knew any imagery or attachments would have to be stored on an external server. None of this is difficult and is amply documented anywhere else.

The snag came when I wanted to load the subscribers' email addresses in the "To" field of the email.

Trying to use a WorkFlow lookup on the "To" field resulted in not very many options. Certainly no Recipients field. I figured that this was because the Worklow Lookup was only accomodating Single line of text fields. I had to find another way. The trouble was, after an hour or two of Googling, I was no nearer to finding out how to get the subscriber email addresses into the "To" field.

After sleeping on it, I wondered if I could use a variable to dump the text of the subscribers' emails in. So I set up a variable by clicking the Variables button.

I gave the variable a name, "var_recip", and set its Type to string.

Next I had to populate the variable. You can do that by finding Build a dynamic string on the Actions dropdown. 

Then move this new action to sit above the Email action. Next click on the text workflow variable, and select the Variable: var_recip option. The click on dynamic string to bring up the String Builder.

In the String Builder, click on Add Lookup, then find Recipients in the drop down.

Click OK. Now click on Variable and select Variable: var_recip.

And that was pretty much it. You can test it by adding just your own email in the Newsletter's Recipients column. Maybe it was just too obvious for anyone to include in a blog ...

How to put the subscribers' emails in the BCC

The other thing was that I didn't really want to have the entire subscription list revealed in the "To" field of the email. It would be better to somehow include them in the BCC. Except that a Workflow doesn't have a BCC ... okay, that's not quite true. It does have a BCC, but you just can't see it.

This one I was able to solve via Google. I found this anonymous post that explains how to hack the code and turn the CC field into a BCC field. [http://blog.summitcloud.com/2010/03/how-to-bcc-in-sharepoint-workflow-email/]

So, in SharePoint Designer, first add your var_recip variable to the CC field in the email, by clicking on the lookup icon next to the CC field (circled in red in the screengrab). Then select Workflow Lookup from the list or options. Click Add to bring up the Define Workflow Lookup dialogue box. Change Source to Workflow Data, and select Variable: var_recip in the Field window.

Click OK. Click OK again. And click OK again. Finally, click Finish to close the Workflow Designer window.

Now find the .xoml file in SharePoint Designer's left-hand navigation bar and right-click on it to reveal the drop-down menu. Select Notepad under the Open With option.

Now search the Notepad document for "BCC", then make the "BCC" text "CC", and the "CC" text "BCC".

Save the changes.

That's it. If it's worked, your CC field should now be blank.

Trigger the Workflow Email from the list back in the teamsite. My email looked like this ...

Hope that helps someone.