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
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.
No comments:
Post a Comment