Lotus Guru

Category Show-n-Tell Thursday

Update: Sorry for the delay in pointing this out but as Andre notes in the comments, my modification of Sean's code is actually not necessary due to the way the code is compiled. So, forget everything I say here and just use what Sean wrote because in addition to working just fine in an international context, it is also faster.

---

I took Sean Burgess's post "SNTT: Date based views that don’t perform like dogs" and a subsequent suggestion in the comments about ensuring it would work for non-US date formats, and came up with a bit of sample code that show's a complete solution. You can download the code from the OpenNTF Code Bin.

Basically Sean's technique is to run a nightly agent (e.g. 12:10 AM) that revises all date-based view selection formulae so they become hard-coded for the current date. All I did was change the agent and the formula syntax so instead of a selection formula that looks like this:

ASNDtdy := [06/08/2007]; SELECT DeliveredDate = ASNDtdy

you would instead have this:

ASNDtdy := @Date(2007; 6; 8); SELECT DeliveredDate = ASNDtdy

The latter being independent of the particular mmddyyyy or ddmmyyyy date format in use, either on the server or the client. NOTE: The syntax and spacing of the first line in the formula are VERY important as that is how the agent finds the relevant views. It doesn't matter if the formula you paste in initially has the current date or not.

To see how I've modified Sean's agent, click...

Option Public
Use "OpenLogFunctions" 'Get OpenLog from OpenNTF for those wondering

Sub Initialize
	On Error Goto ErrorHandler
	
	Dim session As New notessession	
	Dim db As NotesDatabase	
	
	tdystring = "ASNDtdy := "
	
	Set db = Session.CurrentDatabase 'change this so it loops through several dbs if you want to run one agent per server
	
	Forall v In db.Views
		If Left(v.SelectionFormula,Len(tdystring)) = tdystring Then
			v.SelectionFormula = tdystring & "@Date(" & Year(Today) & "; " & Month(Today) & "; " & Day(Today) & ")" & Strright(v.SelectionFormula,")")
		End If
		Forall c In v.Columns
			If Not (c.Formula = "") Then
				If Left(c.Formula,Len(tdystring)) = tdystring Then
					c.Formula = tdystring & "@Date(" & Year(Today) & "; " & Month(Today) & "; " & Day(Today) & ")" & Strright(c.Formula,")")
				End If
			End If
		End Forall
	End Forall
	
	Exit Sub
	
ErrorHandler:
	Call LogError
	
End Sub