Dynamically Updating a SharePoint Calculated Column Containing a “Today” Reference

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

 

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.

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):

    1. In Visual Studio, create a new “Console Application” project (named something like “UpdateSPList”).
    2. 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”).
    3. 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).
    4. Add in two “key’s” to hold the name of the site and list:(Example)
1 <?xml version=“1.0“ encoding=“utf-8“ ?>
2 <configuration>
3   <appSettings>
4     <add key=“Site“ value=“http://My_Portal/Sites/TheSite“/>
5     <add key=“List“ value=“Tasks“/>
6   </appSettings>
7 </configuration>
    1. Change the default “Program.cs” code to be:using Microsoft.SharePoint; using System.Configuration; namespace UpdateSPList
01 {
02      class Program
03      {
04           static void Main(string[] args)
05           {
06                SPSite site = new SPSite(ConfigurationManager.AppSettings["Site"]);
07                SPWeb web = site.OpenWeb();
08                SPList list = web.Lists[ConfigurationManager.AppSettings["List"]];
09                web.AllowUnsafeUpdates = true;
10                foreach (SPListItem item in list.Items)
11        {
12                    item.SystemUpdate();
13        }
14               web.AllowUnsafeUpdates = false;
15           }
16      }
17
18
19 }
  1. Build (compile) the program.
  2. 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”).
  3. 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.

  1. Modify the “web.config” file:
      1. 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).
      2. Modify it as follows (you’ll be adding in the “PageParserPath” node):
    1 <SafeMode MaxControls=“200“ CallStack=“false“ DirectFileDependencies=“10“ TotalFileDependencies=“50“ AllowPageLevelTrace=“false“>
    2 <PageParserPaths>
    3 <PageParserPath VirtualPath=“/*“ CompilationMode=“Always“ AllowServerSideScript=“true“ IncludeSubFolders=“true“/>
    4 </PageParserPaths>
    5 </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.

  1. Using the default “Tasks” list as our example:
      1. Open SPD and connect to your site.
      2. Once connected, open the “AllItems.aspx” page for the “Tasks” list (Root of site > Lists > Tasks > AllItems.aspx).
      3. 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:
    01 <script runat=”server”>
    02 protected void Page_Load(object sender, EventArgs e)
    03
    04 {   SPSite site = new SPSite(“<Your_Site_URL>”);  
    05     SPWeb web = site.OpenWeb();  
    06     SPList list = web.Lists["Tasks"];  
    07     web.AllowUnsafeUpdates = true;  
    08     foreach (SPListItem item in list.Items)  
    09     {      
    10         item.SystemUpdate();  
    11     }
    12     web.AllowUnsafeUpdates = false
    13 }
    14
    15 </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)

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

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.

Advertisements

How To: Make your SharePoint 2010 site collection read-only, restrict delete

SharePoint 2010 allows administrators to set certain site collections to read only or restrict delete. This is done on a centralized site collection level and has nothing to do with permission assignments on your lists and libraries.
Page Content

SharePoint 2010 allows administrators to set certain site collections to read only or restrict delete. This is done on a centralized site collection level and has nothing to do with permission assignments on your lists and libraries.

Here is a scenario: a project represented by a site collection in SharePoint has completed and all of the deliverables must be read only, or new content must not be added only existing ones modified.

Let`s take a look how you can implement such a lock using PowerShell. Also, for more PowerShell hands on scenarios check out my new book.

From within SharePoint 2010 Management Shell execute the following command to prevent users from adding, updating, or deleting content, basically a read-only site collection:

1 Set-SPSite -Identity "http://myserver/sites/site1" -LockState "ReadOnly"

Now, when you access a list item for example and try to edit it – the controls will be grayed out. The same goes for when you try adding new item – you’ll get an error right on the new item form.

2011-12-08-SP2010CollectionReadOnly-01.png

2011-12-08-SP2010CollectionReadOnly-02.png

To make the site collection accessible again call:

1 Set-SPSite -Identity "http://myserver/sites/site1" -LockState "Unlock"

For the scenario where no new content can be added but existing content modified and deleted, here is the command:

1 Set-SPSite -Identity "http://myserver/sites/site1" -LockState "NoAdditions"

When users try to add new content, the form will still show but the following error will occur when saving the new item:

0x81020066 Additions to this Web site have been blocked. Please contact the administrator to resolve this problem.

2011-12-08-SP2010CollectionReadOnly-03.png

Lastly, to prevent access to the site use the following command:

1 Set-SPSite -Identity "http://myserver/sites/site1" -LockState "NoAccess"

Resulting in the following error when site collection is accessed:

0x81020071 Access to this Web site has been blocked. Please contact the administrator to resolve this problem.

2011-12-08-SP2010CollectionReadOnly-04.png

To unlock any of the above locks, use the Unlock command above.

Using different login pages on different Site Collections in a single Web Application

Find out how to configure different login page for every Site Collection in a single Web Application.
Page Content
2011-12-12-UsingDifferentLoginPages-01.jpg

SharePoint 2010 makes it easy for you to host multiple Site Collections within a single Web Application. In terms of Internet-facing websites this capability is extremely powerful when it comes to hosting mini-sites. And although they all might use the same credential store, they might need a login page using their own branding. Find out how to configure different login page for every Site Collection in a single Web Application.

Delivering personal experiences on the web

SharePoint 2010 is a powerful and flexible platform for hosting Internet-facing websites. It supports all the different scenarios: from large company websites to mini-sites supporting for example a product launch campaign. No matter your requirements, SharePoint can help you get everything in place to support your business.

When it comes to hosting multiple Site Collections in a single Web Application you have two options. You can either use manage paths or choose for Host Named Site Collections. While the first choice is most often being made from the governance point of view, it’s the second option that your marketing department loves. Using Host Named Site Collections makes all the Site Collections look from the outside just as if they were separate Web Applications! They have their own URL, that can be a whole different domain than the root Site Collection, which is great when it comes to support marketing campaigns.

In the last few years more and more sites shift from only providing static information to delivering personalized experiences. After authenticating, using visitor’s profile, you can tailor the user experience to his specific needs offering more value from the visit. To provide the best user experience you want to make the authentication process as unobtrusive as possible making it an integral part of your site. And this is exactly when things get challenging.

Authentication settings in SharePoint 2010

As you might know authentication settings in SharePoint 2010 are scoped to Web Application. This means that all Site Collections within the same Web Application share authentication providers but also the login page. Although you might be using a single user store for all the visitors of all your sites, the odds are high that you would want a different login page for each one of them! While this seems impossible at first, you can use a simple trick to get this to work.

Configuring separate login pages for different Site Collections

One way of configuring separate login pages for different Site Collections is to replace the standard login page with an HTTP Handler which will, based on the requested URL, find the right Site Collection, get the URL of its login page and redirect the visitor to it.

Configuring prerequisites

For the purpose of this article we will use two Site Collections with URLs /sites/site1 and /sites/site2. Both Site Collections are hosted within a Web Application that allows anonymous access. In each Site Collection create a Publishing Page called Login. This page should be accessible for anonymous users to allow them to authenticate.

The last step in the preparation process is to register the login pages for each Site Collection. Because the Web Application allows us to define only one login page, we have to store the URL of the Site Collection’s login page elsewhere. Using the Root Web’s Property Bag is a great place to do that as it’s lightweight and easy to work with once you have the reference to the parent Site Collection.

Out of the box SharePoint doesn’t provide any user interface to manage the properties from the site’s Property Bag. If you need to set a value of a property in the site’s Property you can do this either declaratively, using the Property Element, using a Feature Receiver or using PowerShell. The following code snippet shows how to set a value of a property in the Root Web’s Property Bag using PowerShell.

1 $rootWeb = Get-SPWeb http://mavention/sites/site1 
2 $rootWeb.SetProperty('MaventionLoginPage', '/sites/site1/pages/login.aspx'
3 $rootWeb.Update()

After executing the above script for both Site Collections we are ready to proceed with the next step which is creation of the HTTP Handler that will redirect the visitors to the right login page.

Creating the login redirection HTTP Handler

As mentioned before, a Web Application allows you to configure only one login page. To be able to have separate login pages for different sites, you have to replace the default login page with an endpoint that is able to redirect the visitor to the right login page based on the requested URL. We chose for HTTP Handler because it’s lightweight and introduces less overhead than an Application Page.

The following code snippet presents the code of the redirection HTTP Handler:

01 using System; 
02 using System.Linq; 
03 using System.Web; 
04 using Microsoft.SharePoint; 
05 using Microsoft.SharePoint.Utilities; 
06   
07 namespace Mavention.SharePoint.Authentication { 
08     public class LoginRedirection : IHttpHandler { 
09         public static readonly string ReferrerParameterName = "ReturnUrl"
10         public static readonly string LoginPagePropertyName = "MaventionLoginPage"
11   
12         #region IHttpHandler Members 
13   
14         public bool IsReusable { 
15             get { return false; } 
16        
17   
18         public void ProcessRequest(HttpContext context) { 
19             string referrerRelativeUrl = context.Request.QueryString[ReferrerParameterName]; 
20   
21             Uri requestUri = context.Request.Url; 
22             string referrer = SPUrlUtility.CombineUrl(requestUri.GetLeftPart(UriPartial.Authority), referrerRelativeUrl); 
23             string loginPageUrl = null
24             using (SPSite site = new SPSite(referrer)) { 
25                 loginPageUrl = site.RootWeb.GetProperty(LoginPagePropertyName) as string
26            
27   
28             if (!String.IsNullOrEmpty(loginPageUrl)) { 
29                 char joinChar = loginPageUrl.Contains('?') ? '&' : '?'
30                 loginPageUrl = String.Format("{0}{1}{2}={3}", loginPageUrl, joinChar, ReferrerParameterName, referrerRelativeUrl); 
31                 context.Response.Redirect(loginPageUrl); 
32            
33             else
34                 throw new InvalidOperationException(); 
35            
36        
37   
38         #endregion 
39    
40 }

We start with retrieving the requested URL (line 19). Using that URL we can determine which Site Collection has been requested (line 22). Next, we retrieve the instance of the specific Site Collection (line 24) and from the property of the Root Web we read the URL of the login page for that specific Site Collection (line 25). If the URL of the login page has been configured, we append to it the URL that has originally been requested (line 30) so that we can redirect the user back to that URL upon a successful authentication. The last thing left to do is to redirect the visitor to the right login page (line 31).

Configuring the HTTP Handler as authentication endpoint

To make use of the HTTP Handler that we have created previously we have to register it with the Web Application hosting our Site Collections. For this go to Central Administration and in the Web Applications section navigate to the Manage web applications page. In the overview of Web Applications available in your Farm, select your Web Application and in the Ribbon from the Security group click the Authentication Providers button.

2011-12-12-UsingDifferentLoginPages-02.jpg

Next choose the Zone, that your visitors use to access your website with. When the Edit Authentication dialog appears, scroll down to the Sign In Page URL section, select the Custom Sign In Page option, and as URL provide the URL of the HTTP Handler we have created previously:

2011-12-12-UsingDifferentLoginPages-03.jpg

Now, when you navigate to a location within your Site Collection that requires authentication you should be redirected to the login page of that specific Site Collection.

2011-12-12-UsingDifferentLoginPages-04.jpg