When using the (now infamous) "Today" column trick, in calculated columns, you'll no doubt notice that the dates resulting from this calculation don't dynamically update. This has become a major point of contention with the use of this technique in formulas because in most (if not all) cases, the whole point of using this in a formula is to track information that does actually need to be updated (daily, in most cases).
I've lost count on the number of discussion threads pointing out this flaw (several of which I've participated in myself), so in an attempt to come up with a solution, I'm going to list out a couple options you can take that can (could) be workable to get around this limitation.
Note – these are "coded" solutions, but are simple to deploy (modify the following code to meet the needs of your specific environment and best practices).
Option 1 – Console application added as a "Scheduled Task" in Windows
Performs a "SPListItem.SystemUpdate()" of all items on the target list at 12:01 a.m. each morning (as discussed here in a thread I participated in awhile back on the "SharePointU" forums).
This program uses "SPListItem.SystemUpdate()" in order to not modify any of the tasks visible details, but since it is an actual update, it will in fact force a re-calculation of any formula using the "Today" reference (the alternative of the "SPListItem.Update()" method will change the "Modified" date property, which in this case we don't want because it'd be preferable to preserve the date it was last modified by an actual person instead of the system).
Steps to create the application (using the object model – haven't tried with web services):
- In Visual Studio, create a new "Console Application" project (named something like "UpdateSPList").
- Add in references for "SharePoint" and "System.Configuration" (the latter is optional, but will allow you to use the appropriate "ConfigurationManager" call instead of "ConfigurationSettings").
- Add in an "Application Configuration File" (will house the name of the site and list – contained in this configuration file so you can make changes later).
- Add in two "key's" to hold the name of the site and list:
(Example)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Site" value="http://My_Portal/Sites/TheSite"/>
<add key="List" value="Tasks"/>
</appSettings>
</configuration> - Change the default "Program.cs" code to be:
using Microsoft.SharePoint;
using System.Configuration;
namespace UpdateSPList
{
class Program
{
static void Main(string[] args)
{
SPSite site = new SPSite(ConfigurationManager.AppSettings["Site"]);
SPWeb web = site.OpenWeb();
SPList list = web.Lists[ConfigurationManager.AppSettings["List"]];
web.AllowUnsafeUpdates = true;
foreach (SPListItem item in list.Items)
{
item.SystemUpdate();
}
web.AllowUnsafeUpdates = false;
}
}
} - Build (compile) the program.
- Copy the "exe" and "config" files from the "debug" folder into the server location that you'll use for production ("UpdateSPList.exe" and "UpdateSPList.exe.config").
- Create a new "Scheduled Task" in Windows scheduler that uses "UpdateSPList.exe" and have it scheduled to run after midnight of each day (I use 12:01 a.m., setup as applicable for you).
When ran, the application will connect into the site, find the list, open each item on the list and perform an update on it that will force the recalculation of all formulas which will update any dates based off the "Today" reference.
Option 2 – Add code to the page in SPD to update the contents each time the page is viewed.
This approach comes with a warning, if you choose to enable the ability to run server-side code in your pages, anyone who can upload pages, can access system pages and/or use SPD to connect to and modify pages, will be able to run their own code (use this approach at your own risk).
To make this approach work, we need to do two things – modify the "web.config" file to allow us to run code, and then add in the code.
- Modify the "web.config" file:
- Using an editor of your choice (notepad, Visual Studio, etc.), open the web configuration file for your site (generally located at "C:InetpubwwwrootwssVirtualDirectories80web.config" if using the "default" site for your instance).
- Modify it as follows (you'll be adding in the "PageParserPath" node):
<SafeMode MaxControls="200" CallStack="false" DirectFileDependencies="10" TotalFileDependencies="50" AllowPageLevelTrace="false">
<PageParserPaths>
<PageParserPath VirtualPath="/*" CompilationMode="Always" AllowServerSideScript="true" IncludeSubFolders="true"/>
</PageParserPaths>
</SafeMode>
This will tell the system the path that contains the pages we want to run code on ("/*" will allow it on all pages), and whether or not to actually allow the code (again – use with caution).
Once we have enabled the ability to run code, we need to add the code into the page.
- Using the default "Tasks" list as our example:
- Open SPD and connect to your site.
- Once connected, open the "AllItems.aspx" page for the "Tasks" list (Root of site > Lists > Tasks > AllItems.aspx).
- In the "Code" view of the site, locate the section "<SharePoint:RssLink runat="server"/>" (used as an example – you could place the code wherever you see fit) and add in the following just below it:
<script runat="server">
protected void Page_Load(object sender, EventArgs e){ SPSite site = new SPSite("<Your_Site_URL>");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["Tasks"];
web.AllowUnsafeUpdates = true;
foreach (SPListItem item in list.Items)
{
item.SystemUpdate();
}
web.AllowUnsafeUpdates = false;
}</script>
(Notice it's the exact same code as used in the "Console Application" except for the site and list are specified in the code rather than in a configuration file for this example)
- Save the page (ignoring any errors or "squiggly" lines you may see in the code view).
Since we've told the system to allow us to run code in the page (via the "web.config" file), once we now visit the page, all items on the list should be updated without throwing any errors (the update of items will occur each time the page is visited).
———————————————
Both of these solutions will work, but depending on your environment may, or may not, be doable (especially if you don't have access to the server running SharePoint or access via SPD – I generally don't develop for web services since I do have the access I need, but you may be able to work up a similar application that accesses SharePoint via its web services as another option).
Additionally, the above code should be used as a reference for how to create the "Update" functionality and can (should) be written in a better fashion (disposing objects etc.) to follow good programming practices…this is just an example – modify it as you see fit.
Aside from these two methods, you may also be able to use a workflow to update the list that fires off each time an item is updated. Although this approach does work as well, I don't like the idea of creating an endless loop and I believe there's also an issue with how many times a self-fired SPD workflow will run (it appears to stop working after a time).
There may be other methods as well to get the calculated column's formula to dynamically update each day, but both of the methods I've listed above seem to do the trick with minimal effort (and are easy to disable/update when needed).
I'm still looking for other approaches to tackle this, especially since I've been blogging recently on Mark Miller's site (EndUserSharePoint.com) regarding calculated columns (with a bunch of examples using the "Today" column trick). So if anyone has any other suggestions/approaches (not just coded, any other ideas that might work better for end-users, not just programmers) please share them.
Hopefully these ideas will help, they're not perfect (perfect would be the system doing what we want without these types of hacks), but at least as a work-around they'll do the job.
Till next time…
- Dink
Dessie Lunsford