We have a fairly simple calendar web part on our Intranet home page, where we list upcoming events of interest to employees. Since items need to be visible for different lengths of time, a lot of advance notice for Board meetings, less for Friday’s pizza lunch, we included an “ActivateOn” and “Expires” column. We set a filter in the Current Items View to only show items between those two dates. This worked pretty well, for a while.
Recently, we started processing vendor invoices and employee travel reimbursements on an alternating weekly schedule. As soon as that change went into effect, I started hearing people ask the accountants “are we paying vendors this week?” So, I suggested we add a recurring calendar item that would indicate what type of payment was up next. This seemed like a simple task. Simple, it turns out, is another word for impossible.
Consider what we needed to make this work. A series of events need to be entered into the calendar either with ActivateOn and Expires dates, or we needed a way to programmatically activate and expire entries on alternate weeks. One “easy” solution would be to create the entries in Excel and upload them; OK, that would be easy for me since the accountant would be building the spreadsheet. On closer review, that is a bad solution. The user would be left thinking that if the task is at all complicated, SharePoint can’t do it. The easiest way for me to do it would be to add a script to a program I wrote that runs timed jobs. Since that program can now talk to SharePoint, it could create calendar entries according to a simple rule. That’s a bad idea for the same reason as using Excel; SharePoint CAN do this. I knew I could set up a filter to perform this task, and I wanted to show what SharePoint could do on its own, out-of-the-box as it were.
Before we got very far with the filter option, the request got more complicated. “If you can show what items are being paid each week, can you also show when the next payday is?” On the surface, these are similar – events that occur at two-week intervals. But payment processing events are only visible for one week while paydays are visible for two weeks. Clearly, this was too complicated for the formula capabilities of a filter.
Remember, we want to craft a solution that a user can control (no coding) without it being tedious. Our solution involves four choices (plus a default) for when the item should be displayed, and two calculated columns. The display choices are:
- DisplayOneWeek (Yes/No column) = Visible for one week
- DisplayTwoWeeks (Yes/No column) = Visible for two weeks
- ActivateOn (Date column) = Visible beginning on specified date.
- Expires (Date column) = Stop displaying item on specified date
- Default (visible 30 days prior to event start, until day after event end date)
The two calculated columns, CalcStart and CalcEnd, key off these choices, and the event start and end dates to calculate the period during which the item should be displayed. Anyone who is familiar with programming and has access to a development environment will cringe at this code. The only thing worse than looking at it, is writing it. I have many complaints about the “editor” of calculated columns, but I’ll call attention to my favorite. When you click on a column name to include it in your formula, the column name is always pasted at the end of the formula, regardless of where you have the cursor. As a developer, I know why this happens, it is easier to append to a stream of characters than it is to determine a point (cursor location) and insert into a stream. Still, a few extra minutes by the programmer could save SharePoint users lots of frustration – OK, end of rant. Shown below is the “code” for the CalcStart and CalcEnd columns.
You can see that the calculation is driven by the user’s choice. If the item is visible for one week, the start date is the event’s start date minus six days. A two week visible item starts 13 days before the event. Otherwise, unless the user specifies a start date, the default option is to set the event’s start date minus 30 days. Ending the display is easier, unless the user specifies an end date; the display ends the day after the event.
In theory, a user could define the next payday, choose “visible for two weeks” make it a recurring item and be set for life. If you are familiar with SharePoint, you know why that doesn’t work. Next week, I’ll explain that and how we worked around it.