Ged rid of @today and @yesterday functions in view selection and column formulas

Ged rid of @today and @yesterday functions in view selection and column formulas

Who doesn’t know the request to display data depending on an actual date in Dominon Views? Well Domino has easy functions like @today and @yesterday etc and they do actually work.. but the Domino Server view indexer (and the administrator) don’t like them at all.. and in big environments with big databases with tons of data and an lots of users this simple @formulas could keep the server very very busy.

So what to do?

An agent which will set the today’s date in each document.

  • the good: problem solved..
  • the ugly: always changing data, huge amount of replication

Tell the user this request is not possible..

  • the good: problem (kind of) solved..
  • the ugly: unsatisfied users, rumours about lotus notes is not even capable of such simple things…

Automatic Design Change approach, which we will discuss here..

Basically the agent computes the view selection and column formulas and set it each night (at 1:00) in the defined views. I changed the view names and formulas in order to make this example easier..
The selection formula selects all documents which are created in the last 8 days, and the column formula gives another background color to documents which are created today..

  • the good: problem solved, no mass data modification, customizable selection and column formulas
  • the ugly: the agent has to be adjusted if view changes occur (new views, column orders, etc.)
We could get even further and keep all the Formulas in a profile etc. in order to have the code more generic..
	Agent Change View Selection Formula
	Created Jul 19, 2011 by Frédéric Dehédin/Zurich
	Description: Comments for Agent
Option Public
Option Declare
Dim lstViewList List As String
Dim lstColList List As Variant
Sub Initialize
	On Error GoTo processError

	Call initViewData()

	Dim s As New NotesSession

	Dim db As NotesDatabase
	Set db = s.currentDatabase

	Dim sToday As String
	Dim sYesterday As String

	'get todays date
	Dim dateTime As New NotesDateTime( "" )
	dateTime.LSLocalTime = Now
	sToday = dateTime.Dateonly

	'get yesterdays date
	Dim vYesterday As Variant
	vYesterday = Evaluate("@text(@Yesterday)")
	sYesterday = CStr(vYesterday(0))

	Dim view As NotesView

	Dim sActualSelFormula As String
	Dim sNewSelFormula As string 

	Dim sActualColFormula As String
	Dim sNewColFormula As String 

	'process the defined View Selection Formulas
	ForAll vw In lstViewList
		Set view = db.getView(ListTag(vw))
		On Error GoTo processNext
		'again do not make this stuff for folders...
		If view.isFolder = False Then
			sActualSelFormula = ""
			sNewSelFormula = ""
			sActualSelFormula = view.Selectionformula
			If sActualSelFormula <> ""  Then
				sNewSelFormula = Replace(vw, "@Today", {@TextToTime("}+sToday+{")})
				sNewSelFormula = Replace(sNewSelFormula, "@Yesterday", {@TextToTime("}+sYesterday+{")})
				view.SelectionFormula = sNewSelFormula
			End If
			Call view.Refresh()

		End If
	End ForAll

	Dim col As notesviewcolumn

	'process the defined Column Formulas
	ForAll vwCols In lstColList
		Set view = db.getView(ListTag(vwCols))
		On Error GoTo processNext2
		'again do not make this stuff for folders...
		If view.isFolder = False Then
			ForAll colFormula In vwCols
				Set col = view.Columns(ListTag(colFormula))
				sNewColFormula = Replace(colFormula, "@Today", {@TextToTime("}+sToday+{")})
				sNewColFormula = Replace(sNewColFormula, "@Yesterday", {@TextToTime("}+sYesterday+{")})
				col.Formula = sNewColFormula
			End ForAll	

			Call view.Refresh()

		End If
	End ForAll
	On Error GoTo processError
	Exit sub
	Call raiseError()
End Sub

Function RaiseError()
	Dim thisType As String
	Dim es As String
	'          thisType = Typename(Me)
	' Not a class, use the calling module instead
	If (thisType = "") Then thisType = GetThreadInfo(11)
	es = thisType & "::" & GetThreadInfo(10) & ": "
	If (Err = 0) Then
		es = es + "Manually raised an error"
		es = es +_
		"Run time error: (" + Trim(Str(Err)) + ") " + _
		Error$ + " at line: "+ Trim(Str(Erl))
	End If
	Print es
End Function
	Sub initViewData
	Description: Comments for Sub
Public Sub initViewData
	lstViewList("vwTicketsToday") = {SELECT Form = "fmTicket" & (@Created > @Adjust(@Today; 0;0;-8;0;0;0) | @Modified > @Adjust(@Today; 0;0;-8;0;0;0)) & DeleteFlag != "1"}
	lstViewList("vwTicketsTodayByUser") = {SELECT Form = "fmTicket" & (@Created > @Adjust(@Today; 0;0;-8;0;0;0) | @Modified > @Adjust(@Today; 0;0;-8;0;0;0)) & DeleteFlag != "1"}
	lstViewList("vwTicketsTodayByCat") = {SELECT Form = "fmTicket" & (@Created > @Adjust(@Today; 0;0;-8;0;0;0) | @Modified > @Adjust(@Today; 0;0;-8;0;0;0)) & DeleteFlag != "1"}

'create a List element with the column formulas which contain date formulas. the Index is the position of the column in the view (0 = first column, 1 = second column, etc.)
	dim sColList1 list As String
	sColList1(0) = {@If(@Created = @Today; 241:241:128:0:0:0; "")}

	lstColList("vwTicketsToday") = sColList1
	lstColList("vwTicketsTodayByUser") = sColList1
	lstColList("vwTicketsTodayByCat") = sColList1

End Sub

Leave a Reply