MS Word Special Characters “MAY” break SharePoint Web Services

Today I was working with a client to resolve an issue with a jQuery solution we provided several months ago. The solution is a policy and procedures site that makes use of several filtered views with jQuery providing some “enhancements”. The “enhancements” were being handled dynamically with jQuery and the SPServices library by Marc Anderson.

Continue reading “MS Word Special Characters “MAY” break SharePoint Web Services”

Advertisements

Update CAML Query for SharePoint Data View Web Part

In a recent project, it was necessary to change the query being executed for a highly customized data view web part. One of the challenges you face when creating heavily customized data view web parts is the difficulty of changing the query through SharePoint Designer’s UI. Here’s a simple technique that should help.

When a DVWP is placed into the page, an SPDataSource is also created. This control has an attribute named SelectCommand that contains the CAML Query executed by the DVWP. You can go to the code and change it to whatever you need it to be.

	<DataSources>
		<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;" id="Veterans1"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/><WebPartPages:DataFormParameter Name="Directory" ParameterKey="Directory" PropertyName="ParameterValues" DefaultValue="B"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></InsertParameters></SharePoint:SPDataSource>
	</DataSources>

Here’s my original query:
&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;

Notice that the query is HTML Encoded. Here’s how the query appears when decoded:

<Query><Where><Eq><FieldRef Name="servicestatus" /><Value Type="Choice">{Service}</Value></Eq></Where><OrderBy><FieldRef Name="fullname" Ascending="True" /></OrderBy></Query>

One of my dynamic properties appears in this query enclosed in brackets (i.e. {Service}). I can now simply change the query and ensure that it is HTML encoded as a replacement to the original SelectCommand:

<Query><Where><And><Eq><FieldRef Name="servicestatus" /><Value Type="Choice">{Service}</Value></Eq><Eq><FieldRef Name="TributeStatus" /><Value Type="Choice">Approved</Value></Eq></And></Where><OrderBy><FieldRef Name="fullname" Ascending="True" /></OrderBy></Query>

This makes the SPDataSource appear as follows:

	<DataSources>
		<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;And&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;TributeStatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;Approved&lt;/Value&gt;&lt;/Eq&gt;&lt;/And&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;&lt;/View&gt;" id="Veterans1"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/><WebPartPages:DataFormParameter Name="Directory" ParameterKey="Directory" PropertyName="ParameterValues" DefaultValue="B"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></InsertParameters></SharePoint:SPDataSource>
	</DataSources>

Create a Featured News Section with jQuery

I was surfing the web the other day and took special notice of a news ticker on a popular online site. This set me to thinking, could I accomplish something similar with SharePoint news? Here is how I was able to accomplish a featured news section for a SharePoint site. Here’s a screenshot of the final result

image

The site visitor is presented with the last five featured news items and can quickly switch between the stories by clicking on the tab located at the top of the page. This is a standard content editor web part and can be exported for use on other sites within the same web application.

In order to complete the steps in this blog, you will need to obtain the following scripts

First, I created a resources document library on the top level of my SharePoint site and uploaded all of the scripts. I placed this in the top level site of the SharePoint web application so I could reference this library anywhere in SharePoint. I also created a “scratch pad page that I could edit in SharePoint designer”.

image

I created a new content type based off the Article Page content type and included a few more items that are needed for my implementation of SharePoint such as Corporate Location and Industry. I also added a Yes/No site column called Featured Item and a Publishing Image column I called Feature Image. These two columns will be used by the “Featured News”.

image

Finally, I created a new layout page and attached it to the new content type. Now, when authoring news my editors have the ability to choose if they want to feature a news item and if that news item should include a feature image.

With the content type configured and a few news articles published I opened SharePoint Designer with my scratch page open and ready for coding. The script reference needed to be added to the page.

<script type="text/javascript" src="/Resources/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="/Resources/jquery.SPServices-0.5.3.min.js"></script>
<script type="text/javascript" src="/Resources/jquery.tools.min.js"></script>

I also placed the content holders into the page.

<div id="FeaturedNews">
    <!-- UL to hold all of the news items as tabs -->
    <ul id="NewsTabs">
    </ul>
    <!-- div to hold all of the returned news snippets -->
    <div id="NewsPages">
    </div>
 </div>

To make it easier to work with my returned date, I created a javascript object that allows me to quickly reference the details of any single item. It also contains functions to aid in the HTML output that will be needed by the jQuery Tools library for rendering the tabs and news story snippets.

 function FeaturedNewsStory()
 {
   this.Title = '';
   this.CompanyLocation = '';
   this.Comments = '';
     this.RollupImage = '';
     this.FeatureImage = '';
     this.ByLine = '';
     this.PublishDate = '';
    this.ServerURL = '';
 
    this.FeatureImageURL = function () {
      if (this.FeatureImage != null) {
        var startPos = this.FeatureImage.indexOf("src=");
        if (startPos < 0) {
          return 'N/A';
        }
        startPos += 5;
        var endPos = this.FeatureImage.indexOf("\"",startPos);
        var ImageURL = this.FeatureImage.substring(startPos,endPos);
        return ImageURL;
      }
      return 'N/A';
    };
 
    this.RollupImageURL = function () {
      if (this.RollupImage != null) {
        var startPos = this.RollupImage.indexOf("src=");
        if (startPos < 0) {
          return 'N/A';
        }
        startPos += 5;
        var endPos = this.RollupImage.indexOf("\"",startPos);
        var ImageURL = this.RollupImage.substring(startPos,endPos);
        return ImageURL;
      }
      return 'N/A';
    };
 
    this.GetStory = function() {
      var html = '';
      html += "<div class="Page">";</div>
      if (this.FeatureImageURL() != 'N/A') {
        html += "<img src='" + this.FeatureImageURL() + "'/>";
      }
      html += "<h1>" + this.Title + "</h1>";
      html += "<div class="Author">" + this.ByLine + "</div>";
      html += "<div>" + this.Comments + "</div>";
      html += "<div class="ReadMore"><a href='" + this.ServerURL + "'>Read More...</div>";
      html += "<cite></cite>" + this.CompanyLocation + "";
      html += "";
      return html;
    };
 
    this.GetTab = function () {
      var html = '';
      html += "<li></a></li>";
      if (this.RollupImageURL() != 'N/A') {
           html += "<img src='" + this.RollupImageURL() + "' alt='" + this.Title + "'/>";
      }
 
      var month = "";
      var month = this.PublishDate.substring(5,7);
      var day = "";
      var day = this.PublishDate.substring(8,10);
      var publishText = "";
      switch(month)
      {
        case '01'
          publishText = "JAN " + day;
          break;
        case '02'
          publishText = "FEB " + day;
          break;
        case '03'
          publishText = "MAR " + day;
          break;
        case '04'
          publishText = "APR " + day;
          break;
        case '05'
          publishText = "MAY " + day;
          break;
        case '06'
          publishText = "JUN " + day;
          break;
        case '07'
          publishText = "JUL " + day;
          break;
        case '08'
          publishText = "AUG " + day;
          break;
        case '09'
          publishText = "SEP " + day;
          break;
        case '10'
          publishText = "OCT " + day;
          break;
        case '11'
         publishText = "NOV " + day;
         break;
       default
         publishText = "DEC " + day;
         break;
     }
     html += "<div class="PubDate">" + publishText + "</div>";
     html += "</a>";
     return html;
   };
 }

With an object in place to make it easier to work with returned items, I make use of the SPServices library to call the SharePoint lists web service and execute the GetListItems method. This method returns a series of items from a list based on a CAML query. In order to limit the selection, the CAML query I need to execute is

   <Query>
      <Where>
        <And>
          <Eq>
            <FieldRef Name='ContentType' />
            <Value Type='Choice'>Company Press Release</Value>
          </Eq>
          <Eq>
            <FieldRef Name='Featured_x0020_Item' />
           <Value Type='Boolean'>1</Value>
         </Eq>
       </And>
     </Where>
     <OrderBy>
       <FieldRef Name='ArticleStartDate' Ascending='False' />
     </OrderBy>
  </Query>

This instructs SharePoint to only return items that are created based on my content type and have been set to be featured. It also instructs SharePoint to get the items based on the Article Date in descending order, placing the newest items at the top of the results.

Using SPServices, the call is executed and then parsed using jQuery into an array of FeaturedNewsStory objects.

   function loadNewsArticles() {
     //Create an array to hold all of the returned results.
     var itemList = new Array();
  
     // Set up a temporary object to hold default values for the list.
     // The CAMLQuery is defined in this object.
     var newsDefaults = {
       webURL "/News",
       listName "Pages",
      CAMLViewFields "",
      CAMLQuery "Company Press Release1",
      CAMLRowLimit 5,
      CAMLQueryOptions ""
    };
 
    // Make use of the SPServices library to call the SharePoint lists web service
    $().SPServices({
      // Set the operation
      operation "GetListItems",
      // Required so something is returned.
      async false,
      // Set the default WEBUrl where the news pages library is located
      webURL newsDefaults.webURL,
      // Set the name of the list/library
      listName newsDefaults.listName,
      // Set the View Fields
      CAMLViewFields newsDefaults.CAMLViewFields,
      // Set the CAML Query
      CAMLQuery newsDefaults.CAMLQuery,
      // Set the total number of items to return.
      CAMLRowLimit newsDefaults.CAMLRowLimit,
      // Set the query options
      CAMLQueryOptions newsDefaults.CAMLQueryOptions,
      // Set a callback function to handle the returned data.
      completefunc function(xData, status) {
        // Iterate through all returned rows and create a new FeaturedNewsStory
        $(xData.responseXML).find("z\\row").each(function () {
          var item = new FeaturedNewsStory();
          item.Title = $(this).attr("ows_Title");
          item.CompanyLocation = $(this).attr("ows_Corporate_x0020_Location");
          item.Comments = $(this).attr("ows_Comments");
          item.RollupImage = $(this).attr("ows_PublishingRollupImage");
          item.FeatureImage = $(this).attr("ows_Featured_x0020_Image");
          item.PublishDate = $(this).attr("ows_ArticleStartDate");
          item.ByLine = $(this).attr("ows_ArticleByLine");
          item.ServerURL = $(this).attr("ows_ServerUrl");
          // Extend the array and place the new item into it.
          itemList.push(item);
        });
      }
    });
    // Return the array of items.
    return itemList;
  }
 

Finally, call the script once the page is ready and render the contents.

   // Use the jQuery way to queue a function for execution when
   // the page is ready. 
   $(document).ready(function () { initialize(); });
  
   // Function that makes a call to the web service and then
   // renders the featured news content within the specified 
   // placeholders.
   function initialize() {
     var newsPages = $("#NewsPages");
    var newsTabs = $("#NewsTabs");
    var Items = new Array();
 
    // Get all of the featured news items.
    Items = loadNewsArticles();
    for(i=0; i < Items.length; i++)
    {
      var item = Items[i];
      // Make use of the FeaturedNewsStory object to get the appropriate markup for a tab.
      $(newsTabs).append(item.GetTab());
      // Make use of the FeaturedNewsStory object to get the appropriate markup for a news snippet.
      $(newsPages).append(item.GetStory());
    }
 
    // Use jQuery Tools to create tabs.
    $(newsTabs).tabs("#NewsPages > div");
  }

While this may already be working, CSS is needed to improve the presentation. Here’s the full solution, including CSS, that is placed inside a standard content editor web part

<script type="text/javascript" src="/Resources/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="/Resources/jquery.SPServices-0.5.3.min.js"></script>
<script type="text/javascript" src="/Resources/jquery.tools.min.js"></script>
<script type="text/javascript">
var newsItems = new Array();
$(document).ready(function () { initialize(); });
function initialize() {
  var newsPages = $("#NewsPages");
  var newsTabs = $("#NewsTabs");
  var Items = new Array();
  Items = loadNewsArticles();
  for(i=0; i < Items.length; i++)
  {
    var item = Items[i];
    $(newsTabs).append(item.GetTab());
    $(newsPages).append(item.GetStory());
  }
  $(newsTabs).tabs("#NewsPages > div");
}
function FeaturedNewsStory()
{
  this.Title = '';
  this.CompanyLocation = '';
  this.Comments = '';
  this.RollupImage = '';
  this.FeatureImage = '';
  this.ByLine = '';
  this.PublishDate = '';
  this.ServerURL = '';
  this.FeatureImageURL = function () {
    if (this.FeatureImage != null) {
      var startPos = this.FeatureImage.indexOf("src=");
      if (startPos < 0) {
        return 'N/A';
      }
      startPos += 5;
      var endPos = this.FeatureImage.indexOf("\"",startPos);
      var ImageURL = this.FeatureImage.substring(startPos,endPos);
      return ImageURL;
    }
    return 'N/A';
  };
  this.RollupImageURL = function () {
    if (this.RollupImage != null) {
      var startPos = this.RollupImage.indexOf("src=");
      if (startPos < 0) {
        return 'N/A';
      }
      startPos += 5;
      var endPos = this.RollupImage.indexOf("\"",startPos);
      var ImageURL = this.RollupImage.substring(startPos,endPos);
      return ImageURL;
    }
    return 'N/A';
  };
  this.GetStory = function() {
    var html = '';
    html += "<div class='Page'>";
    if (this.FeatureImageURL() != 'N/A') {
      html += "<img src='" + this.FeatureImageURL() + "'/>";
    }
    html += "<h1>" + this.Title + "</h1>";
    html += "<div class='Author'>" + this.ByLine + "</div>";
    html += "<div>" + this.Comments + "</div>";
    html += "<div class='ReadMore'><a href='" + this.ServerURL + "'>Read More...</a></div>";
    html += "<cite>" + this.CompanyLocation + "</cite>";
    html += "</div>";
    return html;
  };
  this.GetTab = function () {
    var html = '';
    html += "<li><a href='#'>";
    if (this.RollupImageURL() != 'N/A') {
         html += "<img src='" + this.RollupImageURL() + "' alt='" + this.Title + "'/>";
    }
    var month = "";
    var month = this.PublishDate.substring(5,7);
    var day = "";
    var day = this.PublishDate.substring(8,10);
    var publishText = "";
    switch(month)
    {
      case '01'
        publishText = "JAN " + day;
        break;
      case '02'
        publishText = "FEB " + day;
        break;
      case '03'
        publishText = "MAR " + day;
        break;
      case '04'
        publishText = "APR " + day;
        break;
      case '05'
        publishText = "MAY " + day;
        break;
      case '06'
        publishText = "JUN " + day;
        break;
      case '07'
        publishText = "JUL " + day;
        break;
      case '08'
        publishText = "AUG " + day;
        break;
      case '09'
        publishText = "SEP " + day;
        break;
      case '10'
        publishText = "OCT " + day;
        break;
      case '11'
        publishText = "NOV " + day;
        break;
      default
        publishText = "DEC " + day;
        break;
    }
    html += "<div class='PubDate'>" + publishText + "</div>";
    html += "</a></li>";
    return html;
  };
}
function loadNewsArticles() {
  var itemList = new Array();
  var newsDefaults = {
    webURL "/News",
    listName "Pages",
    CAMLViewFields "<ViewFields/>",//"<ViewFields><FieldRef ID='Featured_x0020_Image'/><FieldRef ID='Title'/><FieldRef ID='Comments'/><FieldRef ID='PublishingRollupImage'/><FieldRef ID='Corporate_x0020_Location'/></ViewFields>",
    CAMLQuery "<Query><Where><And><Eq><FieldRef Name='ContentType' /><Value Type='Choice'>Company Press Release</Value></Eq><Eq><FieldRef Name='Featured_x0020_Item' /><Value Type='Boolean'>1</Value></Eq></And></Where><OrderBy><FieldRef Name='ArticleStartDate' Ascending='False' /></OrderBy></Query>",
    CAMLRowLimit 5,
    CAMLQueryOptions "<QueryOptions/>"
  };
  $().SPServices({
    operation "GetListItems",
    async false,
    webURL newsDefaults.webURL,
    listName newsDefaults.listName,
    CAMLViewFields newsDefaults.CAMLViewFields,
    CAMLQuery newsDefaults.CAMLQuery,
    CAMLRowLimit newsDefaults.CAMLRowLimit,
    CAMLQueryOptions newsDefaults.CAMLQueryOptions,
    completefunc function(xData, status) {
      $(xData.responseXML).find("z\\row").each(function () {
        var item = new FeaturedNewsStory();
        item.Title = $(this).attr("ows_Title");
        item.CompanyLocation = $(this).attr("ows_Corporate_x0020_Location");
        item.Comments = $(this).attr("ows_Comments");
        item.RollupImage = $(this).attr("ows_PublishingRollupImage");
        item.FeatureImage = $(this).attr("ows_Featured_x0020_Image");
        item.PublishDate = $(this).attr("ows_ArticleStartDate");
        item.ByLine = $(this).attr("ows_ArticleByLine");
        item.ServerURL = $(this).attr("ows_ServerUrl");
        itemList.push(item);
      });
    }
  });
  return itemList;
}
</script>
<style type="text/css">
#NewsTabs {
  margin 0 !important;
  padding 0px;
  height 68px;
  border-bottom 1px black solid;
}
#NewsTabs li {
  float left;
  padding 0;
  margin 0;
  list-style none;
}
#NewsTabs a {
  floatleft;
  font-size13px;
  displayblock;
  padding5px 30px;
  text-decorationnone;
  border1px solid #666;
  border-bottom0px;
  height66px;
  background-color#efefef;
  color#777;
  margin-right2px;
  -moz-border-radius-topleft 4px;
  -moz-border-radius-topright4px;
  positionrelative;
  top1px;
}
#NewsTabs li img
{
  clear both;
  text-align center;
  width 32px;
  margin-top 8px;
  display block;
  border none;
}
#NewsTabs ahover {
  background-color#F7F7F7;
  color#333;
}
/* selected tab */
#NewsTabs a.current {
  background-color#ddd;
  border-bottom1px solid #ddd;
  color#000;
  cursordefault;
}
.Page
{
  displaynone;
  border1px solid #666;
  border-width0 1px 1px 1px;
  min-height150px;
  padding15px 20px;
  background-color#ddd;
  overflow auto;
  height 275px;
  font-family Arial, Helvetica, sans-serif;
  font-size 12px;
}
.PubDate
{
  font-size 8px;
  color white;
  background #444;
  margin-top 4px;
  padding-left 8px;
  padding-right 8px;
  padding-top 2px;
  padding-bottom 2px;
}
#FeaturedNews{
    width 550px;
}
#NewsPages img
{
  float right;
  width 200px;
  margin 0 3 0 15;
  border 3px solid #666;
}
#NewsPages cite
{
  font-size 12px;
  font-variant normal;
  font-style normal;
  padding-top 0px;
  margin-top 22px;
  display inline-block;
}
#NewsPages h1
{
 font-size 14px;
 padding-top 0px;
 margin-top 0px;
 padding-top 8px;
 display inline-block;
 border-top 1px solid #666;
 border-bottom 1px solid #666;
 padding-bottom 8px;
 margin-bottom 16px;
}
div.clear
{
  clear both;
}
</style>
<div id="FeaturedNews">
<ul id="NewsTabs">
</ul>
<div id="NewsPages">
</div>
</div>

Controlling the Result Type for Calculate Fields

I recently was faced with the problem of rendering a calculated field using a feature. The feature creates and provisions a set of columns and content-types available for use in the site. However, one of the problems I faced was creating a calculated field that needed to render it’s output as currency. Since SharePoint users the defaults when defining new fields, it was performing the calculations correctly, but displaying them as raw, unformatted text.

When looking through the SDK, there doesn’t appear to be any way to control the content (or it has not yet been documented). So, I decided I’d let SharePoint help me out. I created a new list and added several currency columns to the list. I then created a calculated column that would subtract the last currency number from the first currency number. Next, I saved the list as a list definition and downloaded it to my local computer.

The .stp file that is created is nothing more than a CAB file. I appended the filename with .cab and then opened it up. Inside is a manifest.xml file that contains the definition of the list (if you’re ambitious enough, you could create your own list definitions and upload them by reversing the process).

Opening the manifest.xml file, I then locate all of the field definitions for the list. This is where I found the payload. There is an attribute that can be added to a calculated column definition to control the result of the calculation. It is the ResultType attribute. So, here’s my definition:

          <Field Type="Calculated" DisplayName="Var" Format="DateOnly"
                Decimals="2" LCID="1033" ResultType="Currency" ReadOnly="TRUE"
                ID="{a415e859-b8f9-4168-a63d-e5fbcbbfba7b}"
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" StaticName="Var"
                Name="Var" ColName="sql_variant1" RowOrdinal="0">
                =_x0056_al2-_x0056_al1

As you can see, the calculated field’s result type is controlled by the ResultType attribute. I took this new found knowledge back into my project, plugged it in and everything worked just as expected!