Omicron Llama

Coding all day, every day.

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 idea for the webpart stems to an older blog post of mine, “Calendar Year to View Web Part“.

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
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.

Leave a Reply

Your email address will not be published. Required fields are marked *