30 09 2009
Work Plan Web Part
The requirement for this idea was to take a current Excel spreadsheet solution and “Sharepointify” it.
Word of warning: I think this is by far the most confusing post I’ve ever written. It may just be for personal reference only…
The current system is a work plan for the team leader written in Excel which shows all the team’s deliverables in the first column, and deadlines for the deliverables in subsequent columns. Each column represents a month, and there are 12 columns to show a year view.
The first month displayed is back two months to have a quick overview of where we’ve been, and then to display the 9 months ahead.
See this table for the general idea (current month at time of writing is September):
| Deliverable | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun |
| websites | Develop new subsite | Review subsite for kids | 2 year main site review | |||||||||
| newsletters | topic1 | topic2 | topic 3 | topic 4 | ||||||||
| articles | journal1 | journal2 | journal3 | journal4 | ||||||||
| factsheets | item1 | item2 | item3 | |||||||||
| regional work | south east | north west | scotland | wales | south west |
An extended requirement is to be able to view the current year in its entirety, like so.
| Deliverable | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| websites | new subsite ideas | prepare new subsite info | Develop new subsite | Review subsite for kids | 2 year main site review | |||||||
| newsletters | older topic1 | oldertopic2 | topic1 | topic2 | ||||||||
| articles | older journal 1 | older journal 2 | journal1 | journal2 | journal3 | |||||||
| factsheets | facts 1 | facts 2 | item1 | |||||||||
| regional work | north east | east midlands | east of england | south east | north west | scotland |
The idea to this solution is to store the deadlines as Calendar events in a standard Sharepoint calendar, and have the Deliverables stored in a custom list (with only a Title column). The Calendar list would have an extra column added of type “Lookup” which refers to the custom list.
See this article from EndUserSharepoint on using Lookup instead of a “Choice” field:
With this setup in place, you then create a Calendar Event item to set a deadline, and you then select the deliverable to which this deadline relates from the dropdown box when creating the event.
To the webpart.
The difference with this solution is that instead of iterating over the Calendar List, we now iterate over the Lookup Custom List.
For each item in the Lookup list, we need to identify which items in the Calendar List relate to this item. I got some help from @AidanGarnish and @einaros on using CAML for this. The CAML query and related C# code I ended up using looks like this:
This gave me an SPListItemCollection of items that were relating to a specific lookup Item (in this case, a specific deliverable, the current one from the foreach iterator).
This SPListItemCollection is a list of Items that are in the Calendar List. They all belong to the same “Deliverable” thanks to the CAML above. I then use the “Start Time” field to find out which Month each event belongs in.
The Month number I will use to find out in which column to dislay this event. As with the previous blog post regarding the Year to View Web Part, month “1”, January, refers directly to column index 1 in the table.Columns array. I can therefore simple insert the “Title” of this item straight into column one.
This works fine when we’re implementing the second of the two example tables above, but the first table shifts the column indices according to the current month. As of the time of writing, today is (still) September, and we wish to view 2 full months previously. Therefore the table starts on “July”.
I need to find out on which month this shifted table starts.
startMonth = DateTime.Now.Month – 2;
if (startMonth
The second line of these is a simple hack, if the start month appears to be negative.
(for example, if it’s currently February, 2 – 2 = 0, but we need to start on December (month 12), so I do 12 minus the number that the first line returns: 12 – (0*-1) = 12)
(Another example, if the current month was January (month 1) , 1 – 2 = -1, but 2 months before January is November, so 12 – (-1 * -1) = 11, or November)
I now need to find out what column a specific event should belong to when the columns are shifted. This is what I use, which I discovered after a few hours of long maths, modulus maths and playing around:
DateTime deadLineDate = (DateTime)thisItemDeadLine[“Start Time”];
int magicNumber = deadLineDate.AddMonths(startMonth).Month – 3;
The Title for “thisItemDeadLine” (an SPItem from the SPListItemCollection above) is then inserted into the table column with the index of the number in “magicNumber”.
If this is a rolling 12 month view, we need to see if this item has dropped off the edge of the table…
if (deadLineDate.Year == year)
{
// Is the item for this year?
// If so, then has it dropped off the edge of the table?
if (deadLineDate.Month
continue;
}
row[magicNumber] = thisItemDeadLine[“Title”] + “(” + deadLineDate.Day + “)”;
The toughest part of all this was getting the correct columns for the shifted dates – if you really want to see the code in full then just drop a comment and I’ll get back to you.
Year-to-View with Sharepoint Calendar BugFixed! Twitter Converastion – Jimmywim – Glennhilton & Others