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.