First published on MSDN on Jul 31, 2008
I had a customer question recently that required a bit of thought. They were building what essentially amounted to a glorified tasks list. They had set up numerous fields to collect details, but the two of concern are Due Date and Completion Date. By using these two dates, they wanted to be able to report on the following 4 categories:
The customer first approached the idea with workflow, to update a "Late" field in the list. This isn't a good approach, because this isn't what workflow is really designed for, as well as the fact that by using a field in the list, that field is editable. There's no way to really ensure the integrity of the data. The next option to think about is using a calculated field. This works except for the fact that you cannot use the Today function in a calculated field. (You'd need this to determine some conditions) You receive an error message that says “Calculated columns cannot contain volatile functions like Today and Me”. The reason you cannot use the function is because the function is only executed when you edit/save data to the list. (There are a few unsupport hacks around that will let you get past SharePoint to put this function into the field) So when I pull the data in my list back, it doesn't execute the function nested in the field value.
Now we're running out of choices. 😞 Thinking a little more, we decided that list views would be a viable option. We could create one view for each condition, and create a filter that would show what we wanted. This worked pretty well, and is pretty obvious how to do except for one caveat. In a filter, you cannot compare two dates. (You'll get an error that says "filter value is not supported in a date format".) Now we needed a way to work around this. Our good old friend, the calculated field, would work for us this time. We created a calculated field called "Completed Late" and set it to a yes/no value. The formula we used was Completed Date>Due Date. We could then use this field in the filter for our two completed views.
So in the end, we succesfully built a custom "tasks" list that could compare dates and report on what was completed and what was not.
