Tuesday, December 23, 2014

Don't embed data view web parts within wiki pages on a publishing site

The Problem

If you have created a publishing site in order to use the various features that are associated with that site template, you may see this error message when you are attempting to customize a new page created on the site within SharePoint Designer:


Content in the embedded form may be changed by the server to remove unsafe content  .Do you want to reload your page to see the results of the save?

If you have embedded various web parts, such as Data Form Web Parts on the page, and attempt to save the page within designer, you will see the above message.  Frustratingly, sometimes it allows your custom web parts to save and you can view them in SharePoint.  However, other times it will remove one or more of your web parts from the page upon save, and they are gone, for good.

The Solution

Instead of creating the default page type if you wish to embed web parts in a SharePoint publishing site page, open the site in designer and choose one of the Web Part Page types:


Even though SharePoint itself is still going to define it as a wiki page on your site pages screen, the markup that causes the unsafe content reminder is not included.  So you can save without worrying about loosing any markup on save.

That's it.  Save yourself some headaches and choose this page type right from the start.

Friday, December 19, 2014

Search vs Metadata groupings

The Issue

SharePoint, particularly in Enterprise level settings (10000+ users), can contain thousands and thousands of data items, including documents, list items, external items, content and digital media.  Finding the specific piece of information the end user requires can be challenging.  There are three things typically done to make it easier to locate the correct information.

  1. Site architecture.  Specific sites or areas for specific document types, usually related to department (payroll, human resources) or activity (specific projects, team events).
  2. Search.  The "search" field that appears at the top of most SharePoint pages, and list specific searches configured for large lists.
  3. Metadata Groupings.  The information is organized to make rapid location easier, ie, a list is grouped by assignee, so that a specific assignee's task is more rapid to locate.

The first two types of organization tend to be well understood.  It makes sense that users would look for payroll documents on the payroll departments website, or look for project related documents on a project site.  Search itself is also well understood, as it is included on almost every large public facing website, and the use of search engines such as google has made the end user comfortable with this process.

However, metadata groupings are used less, both because they are less understood by the end user, and because they require the intelligent analysis of the information being stored to come up with a good metadata scheme.

If we are able to explain to the end user the advantages of using a metadata based organization over the other two methods, then we are able to implement this improvement in production and see the efficiency improvements in the resulting system.

This blog post looks at a classic search scenario, and provides evidence as to why, in this case, a metadata grouping is the best answer.

Searching by Date Range

For this example, we are going to be looking at a payroll system that provides the end users pay stubs.  The paystubs are released to the end users on pay day, and additional paystubs may be added in correction or if an employee has more than one position, each requiring their own paystub.

Over time, a SharePoint list that simply lists paystubs is going to become cumbersome, once the list gets beyond a certain size locating the correct historical paystub can become more difficult and involve a lot of clicks.

The classic way to solve this issue is to come up with a date range search.  Here, you have an end user enter in a start date and end date, click "search", and see the results of the search.  But is this the most efficient way?

Let's take a look from the user clicks perspective:

  1. User clicks start date field calendar.
  2. User clicks "previous month" multiple times within calendar to get correct month (1+).
  3. User types in year.
  4. User clicks end date field calendar.
  5. User clicks "previous month" multiple times within calendar to get correct month (1+).
  6. User types in year.
  7. User clicks "Search". Results are displayed.

This is a minimum 7 click activity, more if they have to manipulate the calendar more than one month or year in either direction. 

And, if the results they were looking for are not included in the date range, then they must redo all of the clicking above in order to create another date range that hopefully does include the paystub they were looking for.  There has got to be a better way!  Fortunately, their is.

Searching by Meta Data

If, instead of simply providing raw list data organized in rows, one row for each paystub, as the example above does, we group the records by date we can enable much more rapid location of a specific or multiple records within a date range.

In this case, we can add two fields to the pay sub list, "Month" and "Year".  These are calculated fields, extracted from the pay stub date.

We can then group our records by these fields, first by year, then by month.  SharePoint will sort months alphabetically so if we wish it to be in month order, we will have to apply the fix listed in this blog post:

http://stevessharepointnuggets.blogspot.ca/2014/12/grouping-by-dates-months-in-month-order.html

This grouping results in the following output:

Now, let's take a look how many clicks it takes to locate a specific record:

  1. User clicks + sign by the year they are looking for.
  2. User clicks + sign by the month they are looking for.
The results are displayed.  In only two clicks the user was able to locate the same pay stub record that was located with 7+ clicks above.

And, in the case where the first two clicks did not locate the correct paystub, they are one click away from being able to view any previous or subsequent month in the same year, or two clicks away from a different year.  A vast improvement over the classic date range search approach.

Best Approach is a Hybrid

This post outlined the importance of metadata when used for grouping records, and how it can be more appropriate than a custom search in specific situations. 

If you are successfully able to combine all three ways of accessing data, through site architecture, standard and custom searches, and groupings using metadata, you will see much greater efficiency finding information within SharePoint, increasing adoption and satisfaction with the platform.


Wednesday, December 10, 2014

Grouping by Dates (months) in month order, not alphabetical, internal and external lists (business lists).

The Problem

Being able to "group" related data using the group by function in SharePoint is a useful way of displaying and organizing data for the end user.  Grouping by project, category, or other metadatavalues allows data organization that greatly enhances the end users ability to rapidly find data.

So, it stands to reason that we may wish to be able to group by dates, where you first group by year, and then by month to organize the data, as follows:


However, within SharePoint, if you generate your month field as follows (calculated field against a list):

Month: =TEXT(Modified,"mmmm")

Then SharePoints group by function will sort by alphabetical order rather than month order.  If you are grouping by month, April will always appear first in the list (or last, if you have reverse sort order).  This is usually not what the end user wants.

Internal List Solution

A solution for internal lists already exists, and is outlined nicely in this blog post:

http://blog.pathtosharepoint.com/2013/10/31/trick-or-treat-group-items-by-month/

External List Solution

I needed to expand this by applying it to an external list so that I could get the group by sort order indicated in the screen shot above.

The key to the solution is the inclusion of white space.  SharePoint sorts by whitespace, making elements with less white space appear after those with more.  Even better (in this case), it strips white space from the final page meaning the whitespace fix is invisible to the end user.

So, I needed to insert whitespace into my month field in order to have the group by function correctly sort by month.

As I am using an external content type, I am using sql to populate the data values.  I first created the month field in sql as follows:

LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as groupByMonth

This value is returned as an external content type field in my external content type, and can be used as a group by field.

As is, the sql above will result in months sorted alphabetically by SharePoint, we need to insert whitespace to get the correct sort order.  This results in the following sql:

SPACE(MONTH(Pay_Purchase.Date)) + LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as goupByMonth

Now, when I group by the month field it is in month order. 

I can modify the sql as follows to get reverse order (this is also possible within SharePoint Designer 'Sort and Group' options within the business list webpart):

SPACE(12 - MONTH(Pay_Purchase.Date)) + LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as goupByMonth

Whether you are attempting to group by a date field on an internal or external list, the whitespace fix works the same.  You can now group by month and have the correct sequence of values.

Thursday, December 4, 2014

Business List BLOB handling enhancements (multiple blobs, hiding no blob found links, alternate row colors) for external content types

Introduction

If you have worked with SharePoint business lists and blobs, using an approach similar to:

http://msdn.microsoft.com/en-us/library/office/ff634782(v=office.14).aspx

then you are familiar with creating an external content type with a blob field, updating the BDCM model to include handling for that blob, and how to import and use the external content type in a business list.  All good so far.

"Out of the box" the business list displays the items as standard list rows with the variable names used as row titles.  So some styling is usually necessary to make the list meet client demands.  And, out of the box, all blobs are displayed as links, even if there is no blob to resolve to in the database.  This is fine if every record you display always has an associated document blob, but if you have records where there is no document associated, the link is still displayed, and you get an ugly SharePoint error when you click the link.

So this post covers these four changes:
  • Change header field names and styling
  • Alternate Row colors
  • Handle multiple blobs for one record
  • Hide blob links when no associated document present
This post assumes you have a fully functional business list embedded and working within a SharePoint page.

Change Header Field Names and Styling

The first task is relatively simple (non-dynamic).  Changing header names simply consists of:

1. Locate the header row start tag: <table id="BdwpRows" border="0" width="100%" cellpadding="2" cellspacing="0">
2. Locate your first header field by tag: <th class="ms-vb" align="left">
3. Locate the tag that specifies field title: <xsl:with-param name="fieldtitle">
4. Replace the xsl in the field title with your header name.

The final result is this, changes bolded:

        <th class="ms-vb" align="left">
          <xsl:call-template name="dvt.headerfield" ddwrt:atomic="1" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">
            <xsl:with-param name="fieldname">@Employee_Number</xsl:with-param>
            <xsl:with-param name="fieldtitle">
              Employee #
            </xsl:with-param>
            <xsl:with-param name="displayname">
              <xsl:value-of select="$ColName_0" />
            </xsl:with-param>
            <xsl:with-param name="fieldtype">text</xsl:with-param>
          </xsl:call-template>
        </th>
Styling changes are just as simple.  You can directly inject style into the header field tag, or reference an external class such as below (if you also reference the style sheet either within your page or the master page):

<th class="DarkGray" align="left">

Alternate Row Colors

On larger or longer lists, a lot of displayed data can be hard to read if the background color for each row remains white.  We may wish to alternate row colors in order to make reading the rows easier.  This is how this is accomplished.

1. Locate the tag that identifies the beginning of the row elements within the business list web part: <xsl:template name="dvt_1.rowview">
2. Locate the tag that identifies the beginning of one of the row columns:      
<td class="ms-vb">
        <xsl:attribute name="style">
3. My table rows by default are all gray.  I want them to alternate white and gray.  So, insert the following xml within the style tag defined above:
      <xsl:choose>
             <xsl:when test="position() mod 2 != 0">background-color:white</xsl:when>
             <xsl:otherwise />
      </xsl:choose>
This will override the gray rows and provide a white row for every second row.  Final xml looks like:

<xsl:attribute name="style">
    <xsl:choose>
        <xsl:when test="$dvt_1_form_selectkey = @*[name()=$ColumnKey]">color:blue</xsl:when>
        <xsl:otherwise />
    </xsl:choose>
    <xsl:choose>
        <xsl:when test="position() mod 2 != 0">background-color:white</xsl:when>
        <xsl:otherwise />
    </xsl:choose>

  </xsl:attribute>

Multiple Blobs in One Record

If you have already created a business list with one field for blob handling, you may wish to add another to associate two (or more) documents with the same record.  Fortunately, this change is very simple.

First, you will need to update your external content type to pull back both binary blob fields from the database, and create a filename for the blob when it is downloaded.

Once this is done, you will export your BDCM as specified in the link at the beginnning of this post, and insert your markup to handle the two blobs.

I created two external content types, one with each blob type to isolate the markup for each and test to ensure each was working first.  Once this was done, I could modify the second blob markup so that I could insert it into the BDCM model that also contained the first.

The first thing to handle is changing the method call for the second blob.

If you already have one blob up and running, you will be familiar with describing the blob handling method with this tag:

<Method IsStatic="false" Name="PDFReadStream">

If you have multiple blobs, then you need to rename the method to keep it unique.  I renamed the second blob handling method as follows:

 <Method IsStatic="false" Name="PDFReadStream2">

We also need to ensure the calling markup uses the correct method, so within the blob markup locate the tag:

<MethodInstance Type="StreamAccessor" ReturnParameterName="PDFRead" ReturnTypeDescriptorPath="PDFRead[0].EEDRReport" Default="true" Name="PDFReadStream" DefaultDisplayName="EEDRReport">

Update this tag to call your new method name:

<MethodInstance Type="StreamAccessor" ReturnParameterName="PDFRead" ReturnTypeDescriptorPath="PDFRead[0].EEDRReport" Default="true" Name="PDFReadStream2" DefaultDisplayName="EEDRReport">
        
Finally, we need to ensure that the filename for you blob document is correct.  If you leave it as it is now, both document types in your business list will have the same filename.  Probably not desired functionality.

If you look at the sql in the next section, you will see that one of the values returned is a dynamically generated filename (filename for blob).  We want this filename to be associated with this document.

Locate the tag that specifies filename within your streamaccessor method:

<Property Name="FileNameField" Type="System.String">FileName</Property>

Update this to use the new name specified in the sql below and populated through the external content type:

<Property Name="FileNameField" Type="System.String">EEDRFileName</Property>

That's it!  As long as you have working markup for both blob types, then these changes will allow you to handle two or more blobs per record.

Hide Blob links Where No Document is Present

The final section of this post is the most complex.  The issue here is that SharePoint generates the blob link whether it actually resolves to a blob or not, there is no handling to detect if a document is present before the link is displayed.  So we have to build that.

The first step is to update the read list stored procedure or view you are using to populate the external content type.  We need to return a value that indicates the presence of a document, in this example, I am using the COUNT function.

Within the stored procedure, I now include a row that returns the number of documents associated with a record.  Here is the sql (in bold) I used to accomplish this:

SELECT...EEDR.EEDRReport,  -- binary blob
EP.Employee_Number + '_' + LEFT(CONVERT(VARCHAR, EP.Pay_Date, 120),10) + '_EEDRReport.pdf' as EEDRFileName,  -- filename for blob
(select COUNT(EEDRReport)
from EEDR
where PK1 = EP.PK1
and PK2 = EP.PK2) as EEDRCount  -- blob count

Once this is completed, update your external content type within SharePoint Designer to include this field. You should be able to open the read list operation associated with your external content type and the above stored procedure, and see the variable "EEDRCount" listed there on the "return parameters configuration" page of the config wizard.  Put a checkbox beside your new field to include it in the updated external content type.  Click save.

Create a new page within SharePoint and associate your updated business list. Ensure you can see the row count variable being displayed.

Open the SharePoint page within SharePoint designer, edit mode.

Locate the blob display tags, started with:
 <xsl:variable name="downloadUrl" ...

Locate the tag that generates the blob link:

 <xsl:attribute name="onclick">

Just below this tag, replace the "click to download" text with the following:

<xsl:choose>
            <xsl:when test="@EEDRCount != 0">Click to download</xsl:when>
             <xsl:otherwise />
           </xsl:choose>
Save this change and view in SharePoint.  If the link does not resolve to a blob, it will not be displayed.

As a final step, you can remove both the header field and row field for the Count variable, as there is no need for end users to see this value.

Remove the header markup:

  <th class="DarkGray" align="left">
          <xsl:call-template name="dvt.headerfield" ddwrt:atomic="1" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">
            <xsl:with-param name="fieldname">@EEDRCount</xsl:with-param>
            <xsl:with-param name="fieldtitle">
              Row Count
            </xsl:with-param>
            <xsl:with-param name="displayname">
              <xsl:value-of select="$ColName_9" />
            </xsl:with-param>
            <xsl:with-param name="fieldtype">number</xsl:with-param>
          </xsl:call-template>
        </th>

Remove the row markup:

<td class="ms-vb">
        <xsl:attribute name="style">
           <xsl:choose>
             <xsl:when test="position() mod 2 != 0">background-color:white</xsl:when>
             <xsl:otherwise />
           </xsl:choose>
        </xsl:attribute>
        <xsl:variable name="fieldValue">
           <xsl:call-template name="LFtoBR">
             <xsl:with-param name="input">
              <xsl:value-of select="@EEDRCount" />
            </xsl:with-param>
           </xsl:call-template>
        </xsl:variable>
        <xsl:copy-of select="$fieldValue" />
      </td>

Save your changes and view in SharePoint.

That's it!  These updates to the default business list created by SharePoint will enhance end user experience and avoid ugly SharePoint errors when a document is not found by the BCS.