Warn users of double booking in SharePoint calendar in real time

Recently I came across this situation where in our room booking system we needed to warn users of parallel booking, but didn’t want to stop them from making the booking because in many cases they are agreed between parties.

Initially it didn’t look like a big deal because I had Client-side Object Model which I could use in JavaScript, but I couldn’t achieve the whole functionality in JavaScript. I would explain down my post why I couldn’t write the whole solution in JavaScript, and I had to go for Server-side code.

Finally I was able to achieve the following (click on the picture to see the big version)

The thing which makes this solution interesting is that Id’s of the date controls are handled differently by SharePoint in this page. If you inspect the source of the new item page of the calendar, you will notice that the controls id’s are kept in an array in JavaScript. You can see the array declaration in the source

 <script type="text/// <![CDATA[
javascript
">var g_strDateTimeControlIDs = new Array();
// ]]>

And then it’s used to store id’s of the calendar controls like start date

 <script type="text/javascript">
g_strDateTimeControlIDs["SPEventDate"] = "ctl00_m_g_089e7bbe_83d6_4e8a_ab8f_ca7ca3d3d0cc_ff31_ctl00_ctl00_DateTimeField_
DateTimeFieldDate";
</script>

And for end date

 <script type="text/javascript">
g_strDateTimeControlIDs["SPEndDate"] = "ctl00_m_g_089e7bbe_83d6_4e8a_ab8f_ca7ca3d3d0cc_ff41_ctl00_ctl00_DateTimeField_
DateTimeFieldDate";
</script>

Hours and Minutes drop downs’ id’s are same except “Hours” and “Minutes” appended in the end of the above id’s.

The final solution consists of the following components;

1. Changing ScriptManager tag in master page to allow page methods

2. Changing web.config to add PageParser element to allow server-side code in the page

3. Creating a new item page for calendar

4. Changing the newitem page

5. Writing some javascript

6. Writing a page method in C#

The only reason I used page method in C# is that I couldn’t run the following query in JavaScript, I am still trying for this, and if I get lucky I would love to remove the server-side code from this solution


SPQuery query = new SPQuery();

query.ExpandRecurrence = true;

query.Query = "<FieldRef Name='EventDate' />";

query.CalendarDate = new DateTime( startDateTime.Year, startDateTime.Month, startDateTime.Day);

1. ScriptManager tag is already in the v4.master, but it has page methods disabled, we need to first enable the page methods by changing the “EnablePageMethods” attribute to “true” and it should then look like this;

ScriptManager id="ScriptManager" runat="server" EnablePageMethods="true" EnablePartialRendering="true" EnableScriptGlobalization="false" EnableScriptLocalization="true" />

by enabling this you can add static page methods to an ASP.NET page and mark them as Web methods. You can then call these methods from script as if they were part of a Web service, but without creating a separate .asmx file.

If you don’t want to enable Page Methods in master page, then you can add “ScriptManagerProxy” control in your page, it’s because a page can contain only one ScriptManager control in its hierarchy and v4.master already has one.

2. Now change the web.cofig for the site to add a PageParser element, so that our page can call server-side code


<SharePoint>

<PageParserPaths>

<PageParserPath VirtualPath=”/Team/Lists/Calendar/CustomNewEvent.aspx” CompilationMode=”Always” AllowServerSideScript=”true” />

</PageParserPaths>

</SafeMode>

 </SharePoint>

3. Create a custom new item page for calendar in SharePoint designer, it’s easy to do and I won’t be able to explain how to do it here.

4. Open the custom new item page in advance mode in SharePoint designer and scroll down to the place where there’s a table row (i.e) for “End Time” and add the following table row

 
 

 <span id="TimeOverlapLabel">  

 

 


I wanted to show the message below “End Time” but you can show it anywhere on the page and I did the styling in CSS, it’s all your choice.

Now search for place holder id “PlaceHolderAdditionalPageHead”, and the add the following inside the content tag

<asp:Content ContentPlaceHolderId=”PlaceHolderAdditionalPageHead” runat=”server”>

// <![CDATA[
src
=”../../Scripts/calendar.js”>
// ]]>
<script type=”text/c#” runat=”server” src=”../../Script/calendar.cs”></script>
<script language=”javascript” type=”text/javascript”>
_spBodyOnLoadFunctionNames.push(“OnLoad”);
function OnLoad(){
// Calendar is the name of the Calendar list, I am passing the name of the list
// so that I can call this code from any Calendar library  
FindOverlappedEvents(“Calendar”); } 
</script>

 </asp:Content>

I have only shown the code I have added inside the above content tag, there would be more already in there which you might not want to change.

Calendar.js and Calendar.cs are the files I have added in the scripts folder on the root of the site, by selecting “All Files” in the left navigation in SPD and creating a folder on the root, you can of course choose to store them somewhere else but remember to use the correct path in the above code.

5. Now add the following code in Calendar.js, the way I create a new .js or .cs in SharePoint designer is by creating a new css files and then renaming it.  it’s because SharePoint designer gives you option to create only HTML, ASPX or CSS in a given folder.

var g_eventOverlapped = false;

function PreSaveAction()

{

// This is in case user presses save button right after selecting date and time

// Remember: our code runs on onblur even of date and time controls

if(g_eventOverlapped == true) {

 return confirm(“Your selected time overlaps with an existing booking!\nIf you want to change the time, press ‘Cancel'”);

 }

 return true;

}

// This function is called from OnLoad of the form, it binds event handerl to the date and time controls

// onblur is the only event which works on these controls

function FindOverlappedEvents(listName)

{

var startDate = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]);

 var startHours = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]+”Hours”);

 var startMinutes = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]+”Minutes”);

 if(startDate != null && startHours != null && startMinutes != null)

 {

 startDate.onblur = function(){validateDates(listName);};

 startHours.onblur = function() {validateDates(listName);};

 startMinutes.onblur = function() {validateDates(listName);};

}

 var endDate = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]);

 var endHours = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]+”Hours”);

 var endMinutes = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]+”Minutes”);

 if(endDate != null && endHours != null && endMinutes != null)

 {

 endDate.onblur = function(){validateDates(listName);};

 endHours.onblur = function() {validateDates(listName);};

 endMinutes.onblur = function() {validateDates(listName);};

 }

// validate initially when new item window opens, because by default date and time are selected

validateDates(listName);

}

// This gets called every time onblur is fired on controls

function validateDates(listName)

{

var startDateCtl = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]);

 var startHoursCtl = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]+”Hours”);

 var startMinutesCtl = document.getElementById(g_strDateTimeControlIDs[“SPEventDate”]+”Minutes”);

 var startHour = startHoursCtl.options[startHoursCtl.selectedIndex].value

 var startMinutes = startMinutesCtl.options[startMinutesCtl.selectedIndex].value

 var endDateCtl = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]);

 var endHoursCtl = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]+”Hours”);

 var endMinutesCtl = document.getElementById(g_strDateTimeControlIDs[“SPEndDate”]+”Minutes”);

 var endHour = endHoursCtl.options[endHoursCtl.selectedIndex].value

 var endMinutes = endMinutesCtl.options[endMinutesCtl.selectedIndex].value

 // here is the call to the server-side method, which comes back to either OnCallComplete(result) or OnCallError(error)

PageMethods.GetCalendarOverlap(listName, startDateCtl.value, startHour,startMinutes,endDateCtl.value,endHour,endMinutes,OnCallComplete,OnCallError);

}

function OnCallComplete(result)

{

if(result != “”)

{

document.getElementById(“TimeOverlapLabel”).innerHTML = “Booking Error”;

document.getElementById(“TimeOverlap”).innerHTML = result;

// this makes sure that we’ve covered this event

g_eventOverlapped = true;

 

}

 

else

 

{

 

document.getElementById(“TimeOverlapLabel”).innerHTML = “”;

 

document.getElementById(“TimeOverlap”).innerHTML = “”;

// this makes sure that we’ve covered this event

g_eventOverlapped = false;

 

}

}

function OnCallError(error)

{

if(error !== null)

{

document.getElementById(“TimeOverlap”).innerHTML = “There’s an error checking for duplicate events”;

}

}
6. Create Calendar.cs in the Scripts folder and add the following code;

[System.Web.Services.WebMethod]

public static string GetCalendarOverlap(string listName, string startDate, string startHour, string startMinutes, string endDate, string endHour, string endMinutes)

{

 try

{

SPWeb web = SPContext.Current.Web;

SPList calendarList = web.Lists[listName];

// Construct a query that expands recurring events

SPQuery query = new SPQuery();

// If I could run the following CAML in JavaScript using SharePoint Object Model, I wouldn’t have gone for server side code.

query.ExpandRecurrence = true;

query.Query = “<FieldRef Name=’EventDate’ />”;

startHour = startHour.TrimEnd(“:”.ToCharArray());

string[] startDateParts = startDate.Split(“/”.ToCharArray());

DateTime startDateTime = new DateTime(int.Parse(startDateParts[2]), int.Parse(startDateParts[1]), int.Parse(startDateParts[0]), int.Parse(startHour), int.Parse(startMinutes), 0);

endHour = endHour.TrimEnd(“:”.ToCharArray());

string[] endDateParts = endDate.Split(“/”.ToCharArray());

DateTime endDateTime = new DateTime(int.Parse(endDateParts[2]), int.Parse(endDateParts[1]), int.Parse(endDateParts[0]), int.Parse(endHour), int.Parse(endMinutes), 0);

query.CalendarDate = new DateTime( startDateTime.Year, startDateTime.Month, startDateTime.Day);

// Returns all items (including recurrence instances) that

// would appear in the calendar view for the current day

SPListItemCollection calendarItems = calendarList.GetItems(query);

DateTime eventStartDateTime;

DateTime eventEndDateTime;

string eventTitle;

bool timeOverlap = false;

string events = string.Empty;

foreach (SPListItem item in calendarItems)

{

eventStartDateTime = DateTime.Parse(item[“EventDate”].ToString());

eventEndDateTime = DateTime.Parse(item[“EndDate”].ToString());

eventTitle = (string)item[“Title”];

timeOverlap = false;

//if newstartdate >= eStartDate and newStartDate < eEndDate

if(startDateTime >= eventStartDateTime && startDateTime < eventEndDateTime)

{

timeOverlap = true;

}

//if newEndDate > eStartDate and newEndDate <= eEndDate

else if(endDateTime > eventStartDateTime && endDateTime <= eventEndDateTime)

{

timeOverlap = true;

}

// if eStartDate >= newStartDate and eStartDate < newEndDate

else if(eventStartDateTime >= startDateTime && eventStartDateTime < endDateTime)

{

timeOverlap = true;

}

// if eEndDate > newStartDate and eEndDate <= newEndDate

else if(eventEndDateTime > startDateTime && eventEndDateTime <= endDateTime)

{

timeOverlap = true;

}

// if newStartDate = eStartDate and newEndDate = eEndDate

else if (startDateTime == eventStartDateTime && endDateTime == eventEndDateTime)

{

timeOverlap = true;

}

 

if(timeOverlap == true)

{

// This HTML decoration is better done at the client side, but I was being lazy and did it here

events += “ Title : “+ eventTitle +”
“;

events += “Start Date: “+ item[“EventDate”].ToString() +”
End Date: ” + item[“EndDate”].ToString() + ”
“;

events += “Created By: ” + item[“Author”].ToString().Split(“#”.ToCharArray())[1] + ”

“;

}

}

if(events != string.Empty)

{

events = “Time Overlap Warning!<br/>Your selected time overlaps with the following booking(s);<br/><br/>”+events;

}

return events;

}

catch(Exception ex)

{

throw ex;

}

}

3 Comments

Conditional Formatting in SharePoint Designer 2010 doesn’t compare dates correctly

I wrote previously about not being able to pass dates in British format from Time Intelligence Connection Formula filter to Reporting Services report, because although SharePoint displays the dates in British format, but on the server-side it doesn’t deal with them in the same format.  Here is the link to the post.

The same occurred to me when I was trying to apply very simple conditional formatting to add traffic lights for overdue items as below

* Current date = 22/07/2011

As it can be noticed in the above picture that the condition is false for 29/07/2011 but true for 05/08/2011. If I go to advance view the condition it generates behind the scenes is the one below

I forced the Due Date to the correct format in this condition I used “ddwrt:FormatDate” function

ddwrt:DateTimeTick(ddwrt:GenDisplayName(ddwrt:FormatDate(
string($thisNode/@DueDate), 2057, 1))) <
ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))  

In the above line, 2057 is the LCID for British English, and “1” is a simple date format.

And it worked for me

 

1 Comment

More than two conditions in CAML query

CAML doesn’t support more than two conditions in a query. I just realized that I never needed more than two conditions in a query, but I just did when I was designing an SSRS report against a SharePoint list.
But there’s nothing to worry about, here is the workaround;

<Query>

<Where>

<Or>

    <Contains>

    <FieldRef Name=”Comment” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” />     </Value>

    </Contains>

<Or>

    <Eq>

    <FieldRef Name=”ID” />

    <Value Type=”Integer”><Parameter Name=”SearchParam” /></Value>

    </Eq>

<Or>

<Contains>

    <FieldRef Name=”Title” />

    <Value Type=”Text”><Parameter Name=”SearchParam” /></Value>

 </Contains>

<Contains>

    <FieldRef Name=”Record_x0020_Number” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” /></Value>

</Contains>

</Or>

</Or>

</Or>

</Where>

</Query>

Keep nesting for more conditions.

Leave a comment

Remove hyperlink from Lookup column in SharePoint

For whatever reason if you don’t want users to be able to click on a lookup column in a list, there’s no straight forward method of disabling that link. Here’s the way I disable them in SharePoint 2010, and it wasn’t any different in 2007 either apart from fancy UI.

In the image below, Country is a lookup type of column

 

 Open the list view in SharePoint designer for modification

 

 Select the lookup column item and in the document map bar in the bottom, choose the context menu for “xsl:value-of”, and select “Edit Tag…”

You will see the “Edit Tag” window

The above XPath “$thisNode/@*[name()=current()/@Name]” when executed, returns the following hyper link tag

<a onclick=”OpenPopUpPage(‘http://servername/_layouts/listform.aspx?PageType=4&ListId={9CF20D94-56E4-426B-AAA3-97CEA2B23570}&ID=3&RootFolder=*’,RefreshPage); return false;”href=”http://servername/_layouts/listform.aspx?PageType=4&ListId={9CF20D94-56E4-426B-AAA3-97CEA2B23570}&ID=3&RootFolder=*”>United States</a>

In order to get the clean value which is in this case “United States”, we need to remove the decoration around it, so we are going to remove everything before ‘>‘ and after ‘<‘ by changing the tag in the Quick Tag Editor to

<xsl:value-of select=”substring-before(substring-after($thisNode/@*[name()=current()/@Name],’&gt;’), ‘&lt;’)” disable-output-escaping=”yes”>

Then press the tick button on the “Quick Tag Editor” to save the modified tag, save the page and view it in browser

Links are gone.

Neat, isn’t it?

21 Comments

PerformancePoint Time Intelligence Filter with SSRS report connection error

Recently while designing a Dashboard in SharePoint 2010, I created a SSRS 2008 R2 report, which had a parameter of type date, “no problem, it would work great with Time Intelligence Filter”, that’s what I thought, but it didn’t.

My SharePoint site is using UK regional settings, but my reporting services are installed with default options, means it uses US settings.

In the picture below it can be seen that both of these controls understand UK date format, which is logical that they are considering the client’s regional settings.

 

 

 

 

But when I connect these two, it seems that the date which is being passed to the report is not in the UK format. I can confirm this by selecting an earlier date, for example 10/07/2011, the report interprets it as 7th Oct.

 

 

 

 

 

 

 

 

 

 

 

I haven’t found the solution to this problem yet, but I am using a workaround which is to deploy the dashboard without filter and then edit the page in browser and use SharePoint date filter, it works straights away.

So in this case I would blame the Time Intelligence Filter, because the report is working fine with filter web part in the browser.

 

 

 

3 Comments

Broken list references in SharePoint Designer 2010 reusable workflow when deployed

Has this ever happened to you that a list is already on your live system and you are asked to create a workflow for that? I know it happens quite often. In this case list workflow is not an option, unless you are creating it directly on live server, very unlikely, but still I have seen this happening.

If you don’t have to go for Visual Studio based workflow, then reusable workflow in Designer is the only option. Usually the course of action is as follows;

  • Get the list template from live server, for which you want to create the workflow, if you don’t already have the updated list on development
  • Create the a list with the same name as on live server based on the template on the development server
  • Create a reusable workflow and reference the list all over it
  • Test the workflow, it works
  • Be happy J
  • Save the workflow as a template solution
  • Upload that solution on live system, and activate the related workflow feature in the web site
  • Open the workflow designer, feel happy that your workflow is there
  • Open the workflow for edit

And to you surprise all the references to the list are broken

You go back on live system, check the list name, you match it with the list name you have on development server and they are exactly same

Then why my references are broken, one of the biggest possibilities is because the list on production server was renamed after it was created, and workflow references lists by their path i.e

{$ListId:Lists/YourCustomList;}

It’s because the URL of the list always remains as it was first created, even if you change its title.

But it’s too late to know this at least for this workflow, but might save a lot of time on next ones.

There is another possibility that you renamed the list name on the development machine for any reason after its creation, and then original names of both of the list don’t match.

Create the list on development which matches the original name when it was first created on the live one, and it will be fine when deployed. You can find the original name in the list URL.

Remember renamed columns, lists or libraries are always painful for developers, if possible delete and recreate these items instead of changing their names.

Leave a comment

Can’t save office documents directly to Sharepoint 2010 on Windows 2008

When SharePoint didn’t let us save documents back to the source library from client applications we knew it had something to do with Windows 2008, and before too long we figured out we need to install “Desktop Experience” feature in order to save documents back to SharePoint directly from client applications.

And there you go.

Leave a comment

ULS Viewer for better bug tracking

I have always hated trying to find errors in log files, until recently when I found ULS Viewer. It has made finding error in huge log files a lot easier, specially in the case of SharePoint, where SharePoint gives you correlation id and you can take it to ULS Viewer and set filter on it.

I find it extremely useful, here is the URL;

http://code.msdn.microsoft.com/ULSViewer

Happy hunting J

Leave a comment

SQL Server 2005 Reporting Services doesn’t work in integrated mode with SharePoint 2010

When my company made the decision to upgrade our intranet from SharePoint 2007 to SharePoint 2010 version, I was over the moon, but immediately I was told that we wouldn’t be upgrading to SQL Server 2008, it was still fine, because Microsoft states that SharePoint 2010 works with SQL Server 2005 SP2 with cumulative updates. It’s only later when we found out that SQL Server Reporting Services 2005 would not work with SharePoint 2010 in integrated mode.

This is not all, we were lucky enough to get our hands on Visual Studio 2010, but guess what? You can’t develop or even open SSRS 2005 reports in Visual Studio 2010.

Happy coding J

Leave a comment

SharePoint alerts are not working after upgrade from 2007 to 2010

When we decided to upgrade SharePoint from 2007 to 2010, out IT imposed that the new server would be virtual and our intranet URL and server names will be different too. We went for ‘database attach’ upgrade and almost everything went according to the plan.

It was only after a few days we noticed that the email alerts were not working for everyone. After thorough checking of MS Exchange, anti virus and Windows on the server, we came to a conclusion that only those alerts which were created in the old version were not working.

One option was to write some code and update all the alerts in the application, but luckily we found a better solution from Microsoft, it’s a script which updates all the alerts in an application without sending any notifications to the users. It worked for us.

Update alerts by using Windows PowerShell (SharePoint Server 2010)

Leave a comment