Tag Archives: javascript

Customising Sharepoint 2007 lists with jQuery

This article explains how you can use jQuery with Sharepoint 2007 to customise how your lists appear on your site. With jQuery we will:

  • highlight specific columns or rows based on values
  • change the default “no records” message, and
  • change the default “add item” message.

Although it’s not envisaged you’ll need to do all three in every list you deliver!

Getting started

To get started first add the required list to your page.  Use the standard list webpart and customise as best you can within the webpart itself:

  • set which columns to present
  • set the order to display the columns
  • set the default appearance (or not) of the toolbar

We’ll use jQuery to interrogate that default view and customise as required.

Once the list is ready, next is to add jQuery to the page. Add a Content Editor webpart, use the Source Editor and add the required link:

<script src="/path/to/jquery-1.9.1.min.js"></script>

We always upload a local copy to our server (because a lot of our internal audience do not have internet access), but you could just link to jQuery on the interwebs.  This article provides the link and an explanation of when/why to use this approach.

Highlight specific columns or rows

This is easy to achieve using the jQuery Filter() function.  We’ll simply search for matching items and then add styling to highlight them.

First step is to update your view so that the value you want to highlight/select is included in the data displayed. jQuery can only work on it if it appears on the page. Ans since list data appears in a table, it will then be searchable within a <td> cell:

<script>
$("td").filter(function() { 
    return $(this).text() == 'Value to Match'; 
    })
    .css("color", "red");
</script>

Alternatively/preferably, when you match your required value apply a defined style (in a separate stylesheet) to it rather than manually making style changes:

<style>
  .highlighted {
       background-color: #ccc;
       color : red;
  }
</style>

...............

<script>
$("td").filter(function() { 
    return $(this).text() == 'Value to Match'; 
    })
    .addClass("highlighted");
</script>

But what if you want to highlight the entire row that matches the selected value, not just the value itself? With jQuery that’s simple, using the .closest() function:

<script>
$("td").filter(function() { 
    return $(this).text() == 'Value to Match'; 
    })
    .closest ("tr")
    .addClass("highlighted");
</script>

Since you’ll need to add a content editor webpart to include the required highlight script, consider adding visible text to that webpart that explains to the user what the highlighting represents.

The result:
highlight-beforeBefore (active and inactive records not differentiated)

highlight-afterAfter (inactive records highlighted with explanation below list)

Change the default Add Item message

Now let’s clean up the standard list option of “Add Item” that presents when you use the summary toolbar:

link-before 

The standard message is a bit vague.  To change it we’ll use the Filter() to search for the “Add Item” anchor text and change to something smarter:

<script>
$("a").filter (function() {
    return $(this).text() == 'Add new item';
    })
    .text("Add yourself to our database");
</script>

The result:

link-after

Change the default “No records” message

The issue with the standard “No records” message is not only is it not very friendly, it also expects you to have the full toolbar visible.

empty-list-before

Notice how the instructions talks of the ‘New’ button above, but that is only available if using the Full Toolbar. Something we rarely do since it adds a heap of confusing features for settings, etc.

To fix we can use the same approach as for the add item link: search for the standard text and replace with something more appropriate. To do so you can use the filter() function as above.

Problem here is the standard text is quite long (and will change if the list name changes). So a better solution would let us just match a key snippet of the standard text (eg “no items to show”) and let us then update the full text. The jQuery selector to match partial text is :contains() but this will surprise you in its result:

<script>
   $("td:contains('no items to show')")
      .css("background-color", "red");
</script>

Issue is :contains() will match not just the TD we’re after, but every TD that contains that TD. Since Sharepoint 2007 goes to town with tables, that’s a lot of TDs changing colour.

Getting around this issue is not simple (as this Stack Overflow question explains). However logically we know the TD we’re after is at the bottom of the tree (ie no child nodes other than text) so let’s use that to filter our matches. Again, Stack Overflow can help: http://stackoverflow.com/questions/743957/how-to-get-elements-which-have-no-children-but-may-have-text. Leading us to the following solution:

<script>
$("td:not(:has(*)):contains('no items to show')")
   .text("No items to show");
</script>

The result:

empty-list-after

Conclusion

This article shows some simple jQuery technicques to improve the appearance of lists in Sharepoint 2007. No promises are made that these are the most efficient methods, but they work. Show us the improved methods in the comments.

Sharepoint 2007, jQuery and Expand/Collapse Sections

One of the first uses for jQuery is to add some polish to your interface. For one Sharepoint 2007 project we wanted to add an FAQ (frequently asked questions). Preferred design was to simply list all the questions, and allow the user to click on any question to display the answer. This way the user could quickly scan all the available questions and only get the details for the questions they’re interested in.

The following documents our path to the final solution.

Stage One: Basic Expand/Collapse

There are plenty of resources online to help with this one. Our first version was a combination of this one:

http://designgala.com/how-to-expand-collapse-toggle-div-layer-using-jquery/

And this one:

http://api.jquery.com/hover/

This provides the required expand/collapse, with the question behaving like a link to encourage users to click on it.

Paste the following into a content editor webpart (source editor mode) and all is well:

<script type="text/javascript" src="/path/to/jquery-1.9.1.min.js"></script>
<script type="text/javascript">

$(document).ready(function() {
    $(".answer").hide();
    $(".question").hover (
        function () {
            $(this).css("text-decoration", "underline");
        },
        function () {
            $(this).css("text-decoration", "none");
        }
    );


    $(".question").click(
	function() {
            $(this).next(".answer").slideToggle(500);
    });
});

</script>

<style>
.faq {
  width:600px;
}
.question {
  color : #003399;
  cursor : pointer;  
}
.answer {
  margin-left: 12px;
  padding: 6px;
  background-color: #ddd;
}
</style>

<div class="faq">
<p class="question">Question1</p>
<p class="answer">Answer 1.</p>
<p class="question">Question2</p>
<p class="answer">Answer 2.</p>
<p class="question">Question3</p>
<p class="answer">Answer 3.</p>
<p class="question">Question4</p>
<p class="answer">Answer 4.</p>
</div>

If already managing a site level stylesheet, add these style references to it rather than defining in this one location.

Similarly if already using jQuery elsewhere, consider downloading to your site and referencing the local link. Very handy particularly if not all your users have internet access.

Stage Two: IE6 Flicker

A bonus level for us lucky few still supporting IE6. The toggle function has a crazy flicker event with IE6 when hiding the answer. Over to Stack Overflow for a solution: add overflow:hidden to the element being shown and hidden.

And so the other browsers don’t feel left out, we also added rounded corners to the answer background box:

.answer {
	margin-left: 12px;
	padding: 6px;
	background-color: #ddd;
	overflow:hidden;
	display:none;
	border-radius: 12px;
}

Stage the Third: Add expand/collapse icons

So this gives us expanding and collapsing sections accessed as a series of links. For the next enhancement we wanted to add expand/collapse icons with each link.

First we need to add the default “expand” icon. Quickest method is as background image for the answer style:

.question {
	color : #003399;
	cursor : pointer;
	padding-left : 20px;
	background-image: url('/path/to/expand.png');
	background-repeat: no-repeat;
}

Simply adjust the left padding to provide the necessary space for your image to appear without overwriting your text.

Then to swap the image when expanded, add the CSS code change call to the jQuery click event:

$(".faq .question").click(
	function() {
        	$(this).next(".answer").slideToggle(500);
            	$(this).css("background-image", "url(/path/to/collapse.png)");
    	});

Put it all together and now works beautifully when you click on a section to expand it. But now the problem is the image does not switch back when you collapse it. From that point on the icon remains as “collapse”.

So what this now needs is a way to know the current visibility status for each element as clicked. And the entire point of the slideToggle method is you don’t track the status; it does it for you. Once we start needing to track status time to ditch slideToggle and go to the fundamental show and hide methods.

$(".question").click(
  function() {
    if ($(this).next(".answer").is(":visible")) {
      //currently visible, so hide it
      $(this).next(".answer").hide(500);
      $(this).css("background-image", "url(/path/to/expand.png)");
    } else {
      //currently not visible, so show it	
      $(this).next(".answer").show(500);
      $(this).css("background-image", "url(/path/to/collapse.png)");
    }
  });

So now we have each question expanding/collapsing when clicked, and the intro icon updating accordingly. Perfect.

expandCollapse

As a bonus the show/hide functions do not appear to have the same flicker issue in IE6 as slideToggle() does. No idea why….

Stage the Last: hide other sections when expanding one

As a final step, one possible enhancement is to make the list automatically close an open question when opening another.

However will leave that as an exercise for the reader, because we didn’t get that far..

Using Javascript with Sharepoint 2007

It’s relatively simple to include Javascript in your Sharepoint pages. With some judicious editing you can add your code inline or via separate code files.

Embedding Javascript

To include Javascript in your page:

  1. use a Content Editor Webpart (CEWP)
  2. edit via the Source Editor button (not Rich Text Editor), and
  3. add your code within standard <script></script> tags

Within the script block just add your normal code, comments, etc. The only issue you’ll have is being able to indent the text and make it look pretty. Tab doesn’t help; you’ll need to use spaces (the horror!).

Don’t use the Rich Text Editor
Important! Always use the Source Editor function to manipulate your code, or any other content within the same CEWP. If you use the Rich Text Editor at any point it will delete all your script.

Linking to Javascript source files

Sometimes your javascript code may be a little too long to embed. Or, like me, you’ve been burnt once too often by clicking the Rich Text Editor button by mistake. At such times you might prefer to link to separate javascript files rather than embedding all directly.

To link to a separate file:

  1. save the Javascript content as a separate .JS file and upload to somewhere on your site. I tend use a /scripts subfolder within the default documents library.
  2. add/edit your CEWP to reference the script file:
<script type="text/javascript" src="full path to script file"></script>

For example, here’s a CEWP that includes a separate Javascript file and then references a function (presumably) defined within it.

Managing timezones with PHP and Javascript

In a current project we need to keep a log of all activities and present that log to the users. And once we start needing to tell users about times we raise the whole spectre of timezones.

After some heated design discussions we narrowed our requirements to needing to provide a choice of two possible display formats for users.

  1. show all activities in (my) local time eg if I’m in Melbourne, show all activities in Melbourne time, even if not completed in Melbourne
  2. show all activities in (their) local time – what we called “original” time eg if I’m in Melbourne, show all activities completed in Sydney in their original Sydney time, show all activities completed in Perth in their original Perth time.

For the latter format to make sense we agreed to add a timezone code to indicate where we were at the time. For convenience these were all given as time +/- UTC. Eg 4:23PM (UTC +11).

implementation – capturing activity times

To allow us to manipulate activity times after recording them we need to record two bits of information:

  • time completed
  • timezone completed in

Our decision for those two was to record the time in UTC and then for the timezone simply record the difference from UTC for the timezone where the activity was completed. That way we knew that whatever we did on display we had a consistent and understandable set of times in the database.

For example, I’m in Sydney (current timezone is UTC+11). An activity I complete at 4pm is recorded in the database as:

  • completion time: 5am that morning (4pm converted to UTC)
  • completion timezone: UTC+11

getting the timezone the activity was completed in

Getting the timezone is easy, and requires a few lines of Javascript added to the client where the activity is completed. So we can get the value to the server to manipulate and record in the database we pass it back to the server as a hidden form variable on our login page.

The magic function we need is getTimezoneOffset. This returns the number of minutes ahead/behind of GMT for the current user. For reasons that become clear when we start looking at presenting dates we convert it to seconds. And for reasons that are lost to us we need to negate the value because it returns the offset the wrong way around. Eg if the client is currently one hour ahead of UTC, getTimezoneOffset returns -60.

Putting all that together adds the following to our login page:

<script language="JavaScript"> 
   var d = new Date(); 
   var offset = 0 - (d.getTimezoneOffset() * 60); 
   document.write ("<input type='hidden' name='offset' value = '" + offset + "' />")
</script>

We added that code to the login form. That way we can capture it once and store as a session variable to use when recording all subsequent activities for that user in the same session.

getting the UTC time the activity was completed at

In getting the time an action was completed we can either take the client time or server time. Doesn’t really matter (if both are confidently accurate) since we’ll convert either to UTC.

For simplicity we chose to work from the server time. In PHP we can get the UTC of the current server time in 2 lines:

$timestamp = time();   
$timestampUTC = $timestamp - date("Z", $timestamp);

The “Z” option for Date returns the offset to UTC in seconds based on the server’s current timezone. And since timestamps simply count seconds it’s simple to add/remove our conversion factor.

You’ll have noticed there are a few config requirements if this approach is going to work. We were able to rely on these being true in our case but you’ll need to check for your own implementation:

  • Javascript enabled on the client
  • correct time (and timezone) set on the client
  • correct time (and timezone) set on the server

If you cannot rely on any of these then you’ve a few changes to make!

displaying times

With our activity recorded in the database as UTC it is a simple task to convert to the display times we require. To provide a different display format, we simply need to choose which offset we apply to the UTC time recorded in the database.

local time

This is the easiest to generate. Simply update the recorded UTC time with the current UTC offset of the client that we captured when they logged in. Since we converted that value to seconds when uploading we simply add it to the UTC timestamp from the server:

$timeLocal = $timeUTC + $_SESSION['offsetClient']; 
$timeLocalDisplay = date("D d M Y H:i:s", $timeLocal);

original time

For original time we just need to apply the offset originally recorded in the database, and then tweak the presentation of the timezone to something legible.

$timeOriginal = $timeUTC + $offsetActivity  //both from the DB
$timeOriginalDisplay = date("D d M Y H:i:s", $timeOriginal);
if ($offsetActivity == 0) { 
   $timeOriginalDisplay .= " (UTC)"; 
} else { 
   if ($offsetActivity > 0) {   
      $timeOriginalDisplay .= " (UTC +" . ($offsetActivity/3600) . ")"; 
   } else {   
      $timeOriginalDisplay .= " (UTC -" . ($offsetActivity/3600) . ")"; 
   } 
}

That code gives us a date like “Mon 23 Jun 2006 12:23:45 (UTC +7)”.

A third option (that we did not implement) was to give the timezone as relative to our own. Eg if we’re at “UTC+4″ the previous date would be “Mon 23 Jun 2006 12:23:45 (+3)”. This format would be easy to calculate as we already have our current timezone recorded as a session variable after login. However no-one really wanted it (or could agree how to present it) so the idea was dropped.

conclusion

So far this design has handled all the requirements we’ve thrown at it. Only changes we made after implementation was to move some of the display conversion code into mySQL so our queries return the dates in the formats we require. Otherwise, all works well.

What’s missing?

There is one glaring whole in the design and that is the ugliness called daylight saving. It becomes an issue if displaying local times. Our design converts the original activity time to the local time based on the current difference between local time and UTC. But if we’ve moved in or out of daylight savings (so the relative difference now is different to what it was then) our calculation will be out.

To manage that we need to know whether we, locally, were in/out of daylight savings when the activity was completed, and whether we are in/out of daylight savings now. Achievable but a not insignificant piece of work. The client agreed and thus we decided to ignore this issue reasoning:

  • a max of one hour out is liveable, and
  • the issue is consistent (meaning a series of activities reported in local time will still present in the correct order, even if for a period of time they are all 1 hour too early/late)

Comparison and Logical Operators: Javascript, SQL, PHP and ASP Compared

Comparison and Logical operators are used for testing data values. Sadly the operators to use are not consistent across languages. This article summarises the most common operators for the key web development languages:

  • Javascript
  • ASP
  • PHP
  • SQL

For SQL we checked both mySQL and MS SQL. All operators listed here should work in both.

Comparison

Operator Javascript ASP PHP SQL
Less than < < < <
Less than or equal to <= <= <= <=
Greater than > > > >
Greater than or equal to >= >= >= >=
Equal to == = == (equal)
=== (identical)
=
Not equal to != <> != (not equal)
<> (not equal)
!== (not identical)
<>

Testing for “identical” in PHP arrived with PHP 4. Two values are identical if they are the same, and the same type. Eg the values “1″ (a string) and 1 (an integer) are equal but not identical.

Logical

Operator Javascript ASP PHP SQL
AND && AND &&
AND
AND
OR || OR ||
OR
OR
NOT ! NOT ! NOT

PHP provides two options for AND and OR with different operator precedences. Refer to the PHP manual: Operator Precedence for details.

useful reference lists

Javascript
comparison and logical operators (w3schools)
ASP
comparison and logical operators (msdn)
PHP
comparison operators (php.net)
logical operators (php.net)
MS SQL
comparison and logical operators (msdn)
MySQL
comparison operators (mysql.com)
logical operators (mysql.com)