It is useful to have colour coding of target dates in views but in traditional Notes applications ( as opposed to XPages ) this carries a big overhead if you do the date calculation in the actual column using @today etc.

Also see Steve McDonagh’s post on colour blindness. You could also use this approach for icons.

Image:Configurable low overhead scheme for date dependent colour coding in views

This scheme uses a scheduled agent to replace the column formula each night with a hard coded date. It runs on 120 views in less than 1 second.

The scheme also uses a configuration document for the formula so that super users can update the threshold values.

There are 3 parts to consider :

1) the column formula. The image below shows it in the configuration document.

The bits in yellow are the constructors that are used to insert the relevant dates for the banding.
In this case
> 5 days away is neutral
0 – 5 days is yellow
overdue = red

Image:Configurable low overhead scheme for date dependent colour coding in views

2) The column in the view.

This column is hidden and has the property “Use value as colour”.  This title of this column must start AGENTLINKED so that the scheduled agent can pick it up and modify it ( see below )
There must also be another column to turn the colour off again. The value of the formula after the agent has run is shown below – you can leave it blank and then run the agent to populate it.

Image:Configurable low overhead scheme for date dependent colour coding in views

Image:Configurable low overhead scheme for date dependent colour coding in views

3) The Scheduled Agent

The scheduled agent loops through every view and every column within each of those views.
Where it finds a column with he AGENTLINKED string in the title it tries to update this.

When updating it :

1) extracts the relevant configurable formula string from the configuration document shown above. You can have multiple configuration documents  in case you need different target thresholds for different views etc..

2) it replaces the [#n#] with a date which is n days from now

3) it inserts the modified formula into the column as shown above.

Sub Initialize
     ‘ Sean Cull, FoCul Ltd.
     ‘ Released under Apache 2.0
     ‘ The purpose of this agent is to modify the column formula
     ‘ for columns so that dates can be highlighted as they become overdue.
     ‘ the column title will need to start with the phrase “.AGENTLINKED”
     ‘ e.g .AGENTLINKED : Colour_on_001
     ‘ this is then used for the keyword document that contains the formula.
     ‘ in the keyword document date increments are notated byu [#x#] where x
     ‘ is the number of days from today.
     ‘ e.g  @If ( target_date_dt > [#3#]; bgd:Dark_Blue ; ……..
     ‘ full example : red := 255:0:0; blue := 0:0:255; green := 0:255:0; gold := 255:128:0; black :=0:0:0; white := 255:255:255 ; yellow:= 255:255:0; bgd := 224:241:255 ; Dark_Blue := 0:0:128; @If ( target_date_dt > [#3#]; bgd:Dark_Blue ; target_date_dt >= [#0#];yellow:black; target_date_dt < [#0#];red:white; -1:-1:-1)
     Dim session As New NotesSession
     Dim rightNow As New NotesDateTime(Now)
     Dim db As NotesDatabase
     Dim configview As NotesView
     Set db = session.CurrentDatabase
     Set configview = db.GetView(“vlukeywords”)
     ForAll v In db.Views
             ForAll vc In v.columns
                     If InStr(vc.Title,“AGENTLINKED”)>0 Then
                             Call process_column(vc,configview)
                     End If
             End ForAll
     End ForAll
process_column(vc As NotesViewColumn, configview As NotesView)
     Dim configdoc As NotesDocument
     Dim strvar As String
     Dim strvar2 As String
     Dim pos As Integer
     Dim pos2 As Integer
     Dim incrementstr As String
     Dim datestr As String
     Dim dt As New NotesDateTime(“Today”)
     Dim z As Integer
     Set configdoc = configview.GetDocumentByKey(“.” + vc.Title )
     If configdoc Is Nothing Then
             MsgBox ” could not find configuration key .” + vc.Title
             Exit Sub
     End If
     strvar = configdoc.KeywordList_tx(0)
     pos = InStr< /strong>(strvar,“[#”)
     Do While pos > 0
             Call dt.SetNow
             strvar2 = Right(strvar,Len(strvar)-(pos+1))                
             pos2 = InStr(strvar2,“#]”)
             If pos2 = 0 Then
                     MsgBox “Error, did not find closing #] in column formula”
                     Exit Sub
             End If
             incrementstr = Left(strvar2,pos2-1)
             Call  dt.AdjustDay( CInt(incrementstr))
             datestr = “[” +  CStr(dt.DateOnly) + “]”
             strvar = Replace(strvar,“[#” + incrementstr + “#]”, datestr)
             pos = InStr(strvar,“[#”)
     vc.Formula = strvar