SharePoint University

I was surprised that SharePoint couldn't do this by default but then again there may be a way around it.  Essentially we have a custom list where a user can submit a milestone for a project, it's due date, and it's completion date.  They also wanted to be able to see how many days overdue the request was if there was no completion date which I thought would be easy by doing the following:

=DATEDIF([Due Date], [Today],"d")

.. and then creating a view to only show the "Days Overdue" column if no completion date was present.

However, SharePoint states the following error message: A calculated column cannot use "volatile" functions such as [Me] or [Today].

Am I out of luck or is there a possible workaround for this?

beno,

Unfortunately there hasn't been any "official" documentation on how to manipulate views in this manner, but there are a few people who have come up with workable solutions which may help you in what you're trying to accomplish.

(Examples)
http://groups.google.com/group/microsoft.public.sharepoint.windowsservices/browse_thread/thread/9814c6dd7bfce2ab/d7eb3113628bf183?q=sharepoint+column+current+date
http://www.sharepointblogs.com/mkruger/archive/2007/06/26/using-today-in-a-calculated-formula-birthday-lists.aspx
http://www.sharepointblogs.com/mkruger/archive/2007/06/26/howto-using-sharepoint-calculated-columns-to-display-a-list-item-as-quot-x-quot-days-old.aspx

The easiest way to take advantage of the "[Today]" reference is to first create a column called "Today" (using the defaults when you create it).  After you have this column created, you can then reference "[Today]" without recieving the error messages you mentioned.  Once you have the view with your calculations present, go back and delete the "Today" column.  Yes, this sounds like an odd approach...but what happens is you basically fool the system into thinking that you're not using one of its reserved (volatile) terms because it references a physical column (which allows the calculation to hold).  By then deleting the column after you make its reference, you now wind up using the actual system "[Today]" reference instead.

Try this approach and see how things work.  From my experience, I've been successful each time in my calculations when I needed to make a reference to a "Today" date object, and have yet to get any errors as long as I follow this approach.

Hopefully this helps,

- Dink

Dink - that rocks!!  Thank you so much, it worked perfectly .. I really appreciate the help and quick response.

- Beno

No problem...glad to help

- Dink

Dink,

 Nice, nice, nice. I saw you do this a couple days ago on another post. Very nice solution. Responses like this are why I've got you on my 'Friends' list.

Mark
EndUserSharePoint

 

The solution given by Dink worked perfectly on that day when the requests were submitted.  However, I noticed that if you check the list the following day the "Days Overdue" does not update as one would hope.. it shows the Days Overdue based on the date the request was submitted.  This may be a strech for SharePoint but do you think it's possible to have the Due Dates updated automatically?

 - beno
 

 

I have seen this fix all over the internet and as far as I can determine it is basically a hoax. What's the point of using Today in a calculation if it doesn't dynamically change? Sharepoint (I"m not sure about 2007) apparently does not provide this capability. There is no difference between using this fix and simply typing today's date into the formulae instead which is a heck of a lot easier. You can easily test this out by using the fix with a formulae that simply displays today's date (=today). It will work when the field is created but the next day it display yesterday's date instead of changing to today. That's absolutely useless. I would be tickled pink if someone showed me that I was wrong or that it worked correctly with 2007. ...murky 

Dink,

Thanks for trying to help.  However, I have tried this and agree with some of the other people that this solution is not dynamic.  It only works for the day in which you create the column and delete it.  Otherwise you have to edit an individual item within the list and save it for it to change the date to today's date.

Can anyone PLEASE point me in the right direction???  There are many metrics that we'd like to track that require a calculation of days delayed and I don't see a way to generate that metric through SharePoint.  The ideal way would be through a calculated column, but I haven't yet seen a way to do this.

Thanks

Well...after looking back through this some more, I am in complete agreement that this solution just really doesn't solve the problem.

Unfortunately, I haven't been able to find anything else yet that will actually to do the job, but I'll keep looking and will post back if I come accross something new.

Maybe when they finally get around to releasing SP1, concepts like this (and other numerous requested functionalities) will actually have a method to be achieved...but we'll see.

Sorry this (supposed) workaround didn't really work...

- Dink

 Anybody, have any luck in finding a solution to this problem?  I've been scouring everywhere and haven't seen any "dynamic" solution.

 Thanks.
 

The fundamental issue is that the calculation is only performed when the specific item (be it a custom list or document in a library) is actually modified.  This is why everyone sees the behavior they see.  If you were to use the techniques described above, you can manually get it to recalculate by changing any meta data on the item and saving it.

There are a couple of ways I can think of to get the calculation to update appropriately.

1. Write a console type application that runs on the server and uses the object model to iterate through the items in the list/library, calculate the "days between" and update the field with the value.  Schedule it to run on nightly basis.

2. Use BDC.  Create a web service whose only purpose is to return the "days between" count.  There's no real advantage to this over #1, except that having a service function like that might useful in other contexts.

3. Possibly use a self-kicking workflow sharepoint designer workflow.  I don't know that this is possible, but I'd investigate it. If this could be done, it would probably be easiest.  SPD workflows don't provide any kind of looping, so you have to figure out a way to get it to start itself over and over again.  I'm going to try and play around with that and I'll post back anything I find.

4. Convert to a data view web part and do the calculation in XSL.

I like #3 best because it's the least code intensive solution.

I would also consider using KPI's for this kind of business requirement (assuming you have MOSS).  A KPI is a visual green/yellow/red indicator of issues.  You can have a yellow flag for "due soon" and red flag for "overdue".  By default, KPI's have a nice drill-down (if you base them off views of a list) and they are also real-time.  I've been taking this approach more and more lately with my projects, using KPI's instead of email for reminder, for example. 

There's a bit more detail on this subject in this SharePoint Beagle article: http://sharepointbeagle.com/channels/realworld/Pages/PermitManagementSolutionOnMOSS2007.aspx

Hope this all helps,

--Paul Galvin, Conchango
  RSS @ http://feeds.feedburner.com/PaulGalvinsSharepointSpace
  Web site @ http://paulgalvin.spaces.live.com

 

 

Paul,

I agree that KPIs are a good tool to use for this type of metric.  However, KPIs don't allow for the use of [Today] or [Me] in their filters.  That means that we still rely on VIEWS for that kind of calculation.  Either way, you'd still need to get the Days Old ([Today] - Due Date) from somewhere in order to show on the KPI. 

I'm surprised there is no easy or known way of getting these values to update automatically.  Please let us know if you research into workflow functionality pays off.  I'd really like to avoid having to code anything.

Thanks,

Sami

 

Here's some followup on this process. Marc Anderson might have a direction on this thing. He's got two posts I'm investigating:

http://mdablog.spaces.live.com/blog/cns!B0C40902E1212960!459.entry
http://mdablog.spaces.live.com/blog/cns!B0C40902E1212960!440.entry

Let me know if you find these useful.

Regards,
Mark
EndUserSharePoint.com

 

 

Great suggestions Paul - based on those, here's what I've been playing with the past few days:

I've worked up a simple workflow in SPD that periodically updates each item in the list (since modifying any MetaData in an item will also update the "today" element) making sure that the calculated column values will accurate.  Essentially, in order to do this, I've had to create an intentional "infinite loop" that on item change will fire off the workflow.  Since the actions in the workflow modify the item's data, each time it ends, it calls the workflow again, and so on. 

Although it does work (I've tested it the past few days), I'm not too excited about having an endless loop running all the time, and am also concerned with the amount of "junk" data that is being written to logs each time the workflow runs.  Part of the problem is how to take in account for when a new item is added to the list, or more to the point, at what time of day the item is added.  Obviously, I told the workflow to start when a new item is added and when an item is modified, but I wasn't able to see (perhaps someone else can answer this part) a way to tell the workflow to wait until 12:00 am before running.  So, what I wound up setting was a delay of an hour in order to capture items added all throughout the day - so at most, the "today" element will only off between 12:00 am to 1:00 am.  This means the workflow will run 24 times each day for each item in the list - not counting any modifications of items by the actual users, which would fire off the Workflow again on each edit.

So again, yes this does work, but I'm not to keen on the looping part, and I'm not convinced that it the best approach to take (although, since it is a non-programmatic approach, it may be viable for those not wanting to "code" a solution).

The other approach I worked on (which I feel is much better), was creating a simple console application that I ran as a scheduled task.  The program runs each night (just after midnight)  and simply runs through each item on the list and updates a property - thereby updating the "today" element, which lets the calculated column return an accurate result.  This approach really seems to work more elegant since its a single process that hits all the list items at once, and is very easy to take offline if needed.

I'm going to work on each of these a bit more, but I am making some progress, so I'll post back when I have more.

- Dink

This problem is a big pain!!! I'm tired of every morning creating a [Today] field and then deleting it!!! Argh!!! I have to train someone to do this when I'm on vacation - and need to give them privs to create a column (and hope they don't delete something).

I have a SharePoint vacation resource planner for managers, and create a list showing an employee as 'Away' or '5 days until Away'.

I made it a little easier by creating a vb script. I tried to schedule it to run at 12:01 am, but I need to keep my pc logged into the network, and of course 'that's against the law'. The people that own the server don't want to help by creating a batch job to run for my app. Here is the simple script which goes directly to the 'create a new column' of the list:

 

'This WSH script will create a Today column, and then delete it - name Today.vbs
Dim i
Set wshShell = WScript.CreateObject("WScript.Shell")
'Open directly to the create new column in SharePoint
wshShell.Run

http://sharepoint/teams/AwayTool/_layouts/1033/fldNew.aspx?List=%7B35CA955D%2DD872%2D4D76%2D823D%2D5B805230CC43%7D
WScript.Sleep 20000 ' Give ie some time to load in case of a slow network
'Create new TODAY column
wshShell.SendKeys "Today"
WScript.Sleep 100
'TAB down to OK Button
For i = 0 to 8
wshShell.SendKeys "{TAB}"
Next
'Press OK button
wshShell.SendKeys "{ENTER}"
WScript.Sleep 5000
'TAB down to the new TODAY column
For i = 0 to 29
wshShell.SendKeys "{TAB}"
Next
'Open the TODAY column to edit
wshShell.SendKeys "{ENTER}"
WScript.Sleep 5000
'TAB down to the DELETE Button
For i = 0 to 7
wshShell.SendKeys "{TAB}"
Next
'Press the DELETE Button
wshShell.SendKeys "{ENTER}"
WScript.Sleep 10000
'Confirm the DELETE by pressing the OK button
wshShell.SendKeys "{ENTER}"
WScript.Sleep 5000
'Close the browser
wshShell.SendKeys "%F"
wshShell.SendKeys "C"