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.

Display Calendar Entries

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.

Form Design

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)
		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 ), "" )
	End If
	If Not ( MarkerEnd > 31 ) Then
		Set Item = doc.ReplaceItemValue ( DAY_FLD_PREFIX + Cstr ( MarkerEnd ), "FFFFFFF" )
	End If
	Call doc.Save ( False, False )

	Exit Function
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

No Information About Free Time

No Information About Free Time When planning a meeting, some users do not see any information about free time of some of the invited persons. When you right click on the planner, you can open the calender. When you click left, a “You are not authorized … ” error message occurs in the client’s statusbar.
Access to free time information is set company wide by policy to be visible to all people.

I found out that in all cases where this issue occurs, the “AllowList” field in the persons entry in clubusy.nsf was not empty, although it should be empty

Field Allow List

It seems that the field value is not properly deleted or updated when either the adminp tries to install the new mail policy or the user edited the calendar profile document.
When you delete the entries from the field, information about free time is provided.
So far, so good. After the server has updated the clubusy.nsf the error occurs again.
Then I looked closer at the calendar profile and found an entry in the “AllowBusyAccess” field.


Deleting the entry in this field and running a “tell sched validate username” after deleting the user document in the clubusy.nsf finally solved the problem.

Update: Now SPR “MFUN77EK4R”