Prompted by Andy at Notes411.com I decided to revisit the colour-coded view trick and simplify it a little by using a column with a Programmatic Name. The formula for the column which sets the row's background-colour is now:
age:=$1; factor:=20; val:=@Text(@Min(@Round((1/(age/factor))*255);255)); "<tr style=\"background-color:rgb(255,"+val+","+val+");\">"
$1? This is the "programmatic name" of the first column in the view. This column is hidden, sorted descending and has a programmatic name of "$1". It's formula is:
1 + ((@Date([24/11/2008])-@Date(@Created))/(60*60*24));
The logic for working out the document's age is now in it's own column. So what we've done is taken the presentational code out of the nightly agent and put it back in a column of its own. This has the added benefit of simplifying the nightly agent, which is now:
Set folder = DEXT.Database.GetView("Demos.Colour.Graded") folder.Columns(0).Formula = |1+((@Date([| + TodayDT.DateOnly + |])-@Created)/(60*60*24))| Call folder.Refresh
Still not as simple as it could be though. What I'd have liked to have done is have a hidden column which is simply today's date. All the nightly code would need to do is update the date. All the "logic" would stay in the view itself.
The trouble is that, unless I'm sorely mistaken (and I hope I am), referring to columns by their programmatic name is a little buggy. If what you put in the column is anything like a date then you just won't be able to refer to it programmatically from another column. All very odd behaviour, so I gave up trying in the end.
The one big flaw in all this (apart from the need for a nightly agent!) is that it's only ever going to be accurate to the day. If you have a view where you want to differentiate between a document that is 12 hours old one that is 12 minutes old then it won't work.
When you want to get more accurate that days then you'll need to use LotusScript in the form of a WebQueryOpen agent. More on this next time...