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.
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
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.
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
End Sub
Sub 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,“[#”)
Loop
vc.Formula = strvar
End Sub