Disclaimer: As my intention is to write about the implementation in general (as for most of it, you could say that it is relatively straightforward to do; only OOTB SharePoint functionality combined with some SharePoint Designer magic is used), you will not find detailed steps on how to create a similar solution here. However, I will briefly describe some tricky parts which I hope will help others doing something similar.
Shortly before the recent FIFA World Cup started, I was approached by a colleague asking me if I could implement a tipping competition for it in SharePoint. Their office already had plans for a regular "offline" competition locally, but in order to promote our SharePoint based intranet, and to let others join as well, he suggested to do it in SharePoint.
After a little bit of testing, I came up with a working, though not perfect solution (the drawbacks and "security issues" are mentioned below). It has been, however, used without any major issues, and can be considered a success.
The Rules
For each match in the World Cup, people are allowed to submit a tip. We decided to let them tip the result after 90 minutes (irrelevant for the group stage, but important for Round of 16 onwards), and awarded points for each tip as follows:
- 5 points if the exact result is tipped (e.g. both result of the match and the tip are 2-1, or 3-3)
- 3 points for the correctly predicted goal difference of a match (e.g. if the result is 2-1 and the tip is 1-0 or 3-2; or if the result is 1-1 and the tip is 2-2)
- 1 point if the correct winner was predicted, but the goal difference is different (e.g. if the result is 1-0, but the tip is 3-0 or 2-0)
- 0 points otherwise
Implementation Basics
A dedicated site was created for the competition. In that site, the following elements were created
- a "Group" page for each of the 8 groups (A-H). Tipping for the individual matches of a group is done through here
- a dedicated page for each of the later stages (Round of 16, Quarter Finals, …) on which tipping for the corresponding matches is possible
- a scoreboard page that displays the current standings of the tipping competition
- a list that holds all the fixtures of the World Cup (group matches, Round of 16, …. Final)
- a list in which all the tips are stored. As authentication is done through Active Directory, it is always possible to know who submitted which tip ("Created By" field)
- a list in which comments are stored. These comments are displayed on all pages, allowing employees to chat and talk about the World Cup
Additionally, two Excel spreadsheets were created. The first spreadsheet is used to calculate the points of all tips by connecting to the SharePoint fixtures list and tips list, and comparing each tip with the actual result and awarding the points as defined above. Based upon this, a ranking is updated each time new results were entered into the SharePoint fixtures list.
The second spreadsheet is used to display these rankings in a Web Part on the scoreboard page.
This could have also been achieved inside a single spreadsheet, however in order to prevent other people from accessing the calculations but still be able to show the results, this "two file solution" was implemented.
How it looks like
The entry page of the tipping competition site contains a short welcome message and some brief infos. Below that, the next few matches are displayed, and the official World Cup RSS feeds from FIFA are displayed inside widgets from Widgetbox:
To make it very easy to navigate among the different group pages and other pages, all of them can be accessed from the site’s quicklaunch:
As mentioned before, on each page the comments list, called "Have Your Say", is displayed:
What I’m doing in this case is actually displaying two Web Parts: the top Web Part is a Custom List Form Web Part that allows employees to add a new comment, below that is the List Web Part which displays the latest comments. In order to make it look as if they are one, the bottom Web Part’s title bar.
A group page shows all matches of a group, the results (if the match was played already), and the tip for the user accessing the page. Once a match has started, tipping is no longer possible:
The pages for the later stages (Round of 16, Quarter Finals, …) contain the corresponding matches. As it can be seen below, a tip has been submitted for the match Uruguay-Netherlands, but none yet for Germany-Spain:
As mentioned before, the ranking is stored inside an Excel spreadsheet, which is displayed on the scoreboard page:
Technical Information
The Fixtures list is used to store all fixtures. Initially, only the group stage matches were entered with empty Home Goals and Away Goals. As soon as a result was final, it was entered in here.
Later onwards, the corresponding matches from the next stages were entered here as well. To differentiate between the different groups and stage levels, so that the corresponding matches can be show on the appropriate match pages, the column Group contains the group (e.g. A) or the stage level (e.g. Semi Final) for a match.
When people submit a tip, it is stored in the Tipps (sic!) list.The additional columns in this Custom List are Fixture, which is a lookup column that references the ID column from the Fixtures list, and Home Goals and Away Goals (in this case of course not the actual result, but rather the tip by the user). We can see who submitted which Tip in the Created By column.
The Excel spreadsheet imports these two lists. The evaluation for each match is done at the Tips list level. That means, for each tip it is being calculated how many points should be awarded.This is done by comparing the actual Match results (added here via lookup for easy referencing) with the tipped result, and awarding points for draw, home win, and away win. For example, for home win it is first c
hecked if the actual result was a home win AND the tip was a home wine, and if not 0 points are awarded. If it was a home win, we then check if the tip and the actual result are the same and award 5 points if so, otherwise we check the if the difference for both results (actual and tipped) is the same, and award 3 points, otherwise 1 point.
Points for away win are calculated the same way, the calculation for a draw is easier.
Lastly, we simply sum up the points from those 3 possibilities (note: at least 2 of them are 0 points, of course) in the Points to Award column.
These points are then summed up per user, and a ranking can be created.
Technical Snippets
While implementing this solution, I came across a few points that are worth mentioning:
One small problem I had was that I needed to show data from two separate lists. On the tipping pages, I wanted to display the tip of the current user (1) from the Tipps list, as well as the match details (2) from the Fixtures list.
To achieve this, I created a new Linked Source in SharePoint Designer made out of these two lists with a Join:
As it can be seen here, it was then possible to access both of them through a single connection:
In the Data View Web Part used to display the match details and the tip, I then filtered the Tips by the current user and by the ID from the Fixtures list. That way, only the tip for the current match by the current user is selected.
If no tip has been submitted, and thus the row count is 0, a button for first time tipping ("Tip!") is displayed, otherwise the existing tip together with an "Update!" button is shown.
I also wanted to prevent users from both submitting after a match had started and submitting more than one tip. The first step was to check if the current match has started already, and if so, do not display the Tip/Update button.
For this, I created two variables: one for the current time, the other one for the match time.
<xsl:variable name="matchdaytime">
<xsl:value-of select="number(translate(ddwrt:FormatDateTime(string(/dsQueryResponse/Fixtures/Rows/Row[@ID=$FixtureID]/@Match_x0020_Time),1033,’yyyyMMdd HHmmss’),’ ‘,”))"/>
</xsl:variable>
<xsl:variable name="curentdaytime">
<xsl:value-of select="number(translate(ddwrt:TodayIso(),’-T:Z’,”))"/>
</xsl:variable>
With these two variables, it was then possible to act accordingly (here shown with Update):
<xsl:choose>
<xsl:when test="$matchdaytime > $curentdaytime">
<input id="updateTippButton{@ID}" type="button" class="button" value="Update!" />
</xsl:when>
<xsl:otherwise>Tipping Period over</xsl:otherwise>
</xsl:choose>
As it can be seen, there is no action defined here once the button is clicked. I make use of jQuery in order to process the tip and submit it.
The following script shows the two important functions involved here: one is the $(document).ready() function from jQuery, which attaches an event handler to all Update/Tip buttons (uniquely identified with the match ID). The other one is the Tipp function that submits the tip (either through adding a new tip or updating the existing one) .
<script language="javascript">
function Tipp(fixtureID, tippID, cmd) {
//alert (fixtureID + "–" + tippID + "–" + cmd);
var Home = $("#HomeTeamGoals"+fixtureID).val();
var Away = $("#AwayTeamGoals"+fixtureID).val();
//alert(Home+" : "+Away);
if(Home != ” && Away != ” && Home>=0 && Home<=12 && Away >=0 && Away<=12) {
var id = "";
if(cmd=="Update")
id = "<Field Name=\"ID\">"+tippID+"</Field>";
var batch =
"<Batch OnError=\"Continue\"> \
<Method ID=\"1\" Cmd=\""+cmd+"\"> \
"+id+"\
<Field Name=\"Fixture\">" + fixtureID + "</Field> \
<Field Name=\"Home_x0020_Goals\">" + Home + "</Field> \
<Field Name=\"Away_x0020_Goals\">" + Away + "</Field> \
</Method> \
</Batch>";
var soapEnv =
"<?xml version=\"1.0\" encoding=\"utf-8\"?> \
<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" \
xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" \
xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\"> \
<soap:Body> \
<UpdateListItems xmlns=\"http://schemas.microsoft.com/sharepoint/soap/\"> \
<listName>Tipps</listName> \
&
nbsp; <updates> \" + batch + "</updates> \
</UpdateListItems> \
</soap:Body> \
</soap:Envelope>";
$.ajax({
url: "http://ap.landisgyr.net/WC2010/_vti_bin/lists.asmx",
beforeSend: function(xhr) {
xhr.setRequestHeader("SOAPAction",
"http://schemas.microsoft.com/sharepoint/soap/UpdateListItems");
},
type: "POST",
dataType: "xml",
data: soapEnv,
error: showError,
success: proceedSuccess,
contentType: "text/xml; charset=utf-8"
});
} else
{
alert("Incorrect Input");
document.getElementById("newTippButton"+fixtureID).disabled = false;
}
}
function showError(xData, status, error) {
alert("Error: " + status + " – " + error);
}
function proceedSuccess(xData, status, requestObject) {
location.reload();
}
$(document).ready(function() {
$(".button").click(function() {
//new
if($(this).attr("id").replace(‘newTippButton’,”) != $(this).attr("id")) {
Tipp($(this).attr("id").replace(‘newTippButton’,”), 0, "New");
}
//update existing
if($(this).attr("id").replace(‘updateTippButton’,”) != $(this).attr("id")) {
var id = $(this).attr("id").replace(‘updateTippButton’,”);
Tipp($("#FixtureID"+id).val(), id, "Update");
}
});
});
</script>
Drawbacks / Issues of my implementation:
As there was not much time left before the start of the World Cup, and also because I was busy with several other more important tasks, I focused on getting the solution to work even if it meant that there’d be some smaller bugs or issues.
One of them is that I did not any possibility for the users to submit multiple tips. As it can be seen in my code above, I only handled the case of submitting a single tip. So if a user wanted to submit multiple tips, for example for all matches in Group C, he/she had to tip each match individually.
One security issue was that there was the risk that people would discover that they can enter their tips directly into the list, and would then update them accordingly after the matches. I did not look for any way to prevent this, but simply did a timestamp check in the Excel spreadsheet. If a tip was submitted after a match started, it would’ve been highlighted. It didn’t happen.
As for the evaluation, there was still a lot of manual processing required. I could’ve tried to automate the processes more (e.g. I had to create the ranking manually), but in the end I decided that it would not be worth the effort. Doing it manually was a 5 minute task each day, and I didn’t know how long it would take me to create a fully automated version.
Summary
I really enjoyed creating this solution. While it definitely isn’t the technically most perfect, and it also lacks some other things (e.g. a very nice design….), it was a nice endeavour different from my other SharePoint activities. It allowed me to show my colleagues what else could be done with SharePoint.