Display Date Range As Bar In Notes Views

You can find a lot of stuff on the internet like Chris Blatnick’s ” Using Color Columns To Make A Gantt Chart” to display column values either as color or as picture ( you might take a look at “A Splash of Color” as well ).
Today I would like to share my solution on how to build a view to display a date range like it is used in group calendar applications.

I tried to keep the view design as simple as possible to avoid performance impacts. As you can see in the sample database there are no formulas in the columns. All necessary information is stored in a “collector” document.

The form contains one field for each day in a month ( Day_1 … Day_31 ). All fields are of type text with multiple values enabled. To colorize Day_1 ( and all subsequent colums ) simply put 0 : 255 : 255 into Day_1 which results in an aquamarine color.
Surfing the web for more information about view column colors I found this article on breakingpar.com.
Instead of using 6 decimal numbers for foreground and background color, you can use hexadecimal values as well. This would make it easier to handle the data when passing it via LotusScript to the collector document.

We can now build a list of colorcodes.

' Declaration

Dim ColorCode List As String

Sub Initialize
	ColorCode("Alice_Blue") = "F0F8FF"
	ColorCode("Antique_White") = "FAEBD7"
	ColorCode("Aqua") = "00FFFF"
	ColorCode("Aquamarine") = "7FFFD4"
	ColorCode("Azure") = "F0FFFF"
	ColorCode("Beige") = "F5F5DC"
	ColorCode("Bisque") = "FFE4C4"
	ColorCode("Black") = "000000"
	ColorCode("Blanched_Almond") = "FFEBCD"
	ColorCode("Blue") = "0000FF"
	ColorCode("Blue_Violet") = "8A2BE2"

...
End Sub

You can call the following function and pass a colorcode from the list as a parameter. You can use any hexadecimal value that is not in the list as well.

Function SetMarker ( doc As NotesDocument, user As String,_
DateFrom As NotesDateTime, DateTo As NotesDateTime, color As String ) As Integer

	On Error Goto ERR_HANDLE
	SetMarker = 0
	Dim item As NotesItem
	Dim i As Integer
	Dim MarkerBegin As Integer
	Dim MarkerEnd As Integer
	Dim MarkerColor (1) As String

	If Iselement(ColorCode( color)) Then
		MarkerColor(0) = ColorCode( color)
		MarkerColor(1) = ColorCode( color)
	Else
		MarkerColor(0) = color
		MarkerColor(1) = color
	End If

	MarkerBegin = Day ( DateFrom.DateOnly )
	MarkerEnd = Int ( Day ( DateTo.DateOnly )) + 1

	Set Item = doc.ReplaceItemValue ( DAY_FLD_PREFIX + Cstr( MarkerBegin ), MarkerColor)
	If MarkerEnd - MarkerBegin > 1 Then
		For i = (MarkerBegin +1) To (MarkerEnd -1)
			Set Item = doc.ReplaceItemValue ( DAY_FLD_PREFIX + Cstr ( i ), "" )
		Next
	End If
	If Not ( MarkerEnd > 31 ) Then
		Set Item = doc.ReplaceItemValue ( DAY_FLD_PREFIX + Cstr ( MarkerEnd ), "FFFFFFF" )
	End If
	Call doc.Save ( False, False )

EXITPOINT:
	Exit Function
ERR_HANDLE:
	SetMarker=1
End Function

The function writes the necessary values to the document. It translates the DateFrom and DateTo parameters and writes the according colorcodes. As you can see in the next screenshot, only two fields are needed to build a bar for a date range.

Notes colorizes the column displaying the colorcode from the document and subsequent columns as long as you do not set the column to another color. Any hexadecimal value longer than 6 will reset the column color to default.

DOWNLOAD Sample Database