Notes Formula Language Date Difference

Question

I want to build a view only containing the most recent documents (newer than 2 months). I am using this select formular:

SELECT  @IsAvailable($Conflict) and docForm="ServiceOrders" and (@Today-PosDesValidFrom)<5259600

Where the 5259600 are the 2 Months in seconds because I read that the - Operator returns the timespan in seconds. I do not have any results in my view.

Answer 1

Never use @Today or @Now in view selection formulas. The view index will then never be up-to-date, hence the update task will run constantly to refresh the index. This will affect performance negatively.

What you should do is have a daily (or in your case probably nightly) agent that will flag the documents to display in the view. To make the agent as fast as possible, process a view where you are displaying the date in one of the columns.

The absolutely fastest would be like this:

1. Create a hidden view called (LookupServiceOrdersByValidDate).
2. The first column will contain the field name PosDesValidFrom (I will assume it is a date field, otherwise you need to convert it to date), and be sorted descending. Go to the fourth tab in the infobox for the column and make sure you have it set to Date/Time.
   
3. Create a second column where you display the text field DisplayIn2MonthView. Sort descending as well.
4. Save the view.

You now have a view you can loop through using an agent. Since it is sorted descending with the newest at the top, as soon as you reach a date more than 2 months back, you can stop the agent and be done.

The script would look something like this:

Dim session as New NotesSession
Dim db as NotesDatabase
Dim view as NotesView
Dim col as NotesViewEntryCollection
Dim entry as NotesViewEntry
Dim doc as NotesDocument
Dim validDate as NotesDateTime
Dim cutoffDate As NotesDateTime

' Get current date and time and go back 2 months
Set cutoffDate = New NotesDateTime(Now())
Call cutoffDate.AdjustMonth(-2)
' Drill down to view
Set db = session.CurrentDatabase
Set view = db.GetView("(LookupServiceOrdersByValidDate)")
' Create a collection of all entries in the view and loop through them
Set col = view.AllEntries
Set entry = col.GetFirstEntry()
Do Until entry is Nothing
    ' Get value in first column in view and use it to create new DateTime object
    validDate = New NotesDateTime(entry.ColumnValues(0))
    ' Check if we are within the 2 month cutoff date
    If Cdat(validDate.dateOnly)>=Cdat(cutoffDate) Then
        ' Get document and set flag to display
        Set doc = entry.Document
        Call doc.ReplaceItemValue("DisplayIn2MonthView","Yes")
        Call doc.Save(True,False)
    Else
        ' We are beyond the cutoff date, but we need to clear old flags.
        ' Read the value in the second column and see if it is "Yes"
        If entry.ColumnValues(1)="Yes" Then
            Set doc = entry.Document
            Call doc.ReplaceItemValue("DisplayIn2MonthView","")
            Call doc.Save(True,False)
        Else
            ' Since all "Yes" values should be at the top, if we
            ' get here there should be no more flagged documents.
            Exit Do
        End If
    End If
    Set entry = col.GetNextEntry(entry)
Loop
Print "All done."

Finally, modify the view to display the documents. You should probably use the Form field, not docForm. Set the selection formula to
SELECT !@IsAvailable($Conflict) AND Form="ServiceOrders" AND DisplayIn2MonthView="Yes" The view should now only contain the ServiceOrder documents with a ValidDate within the last 2 months. If you really want only replication/save conflicts in the view, make sure the view is set to not display response documents in a hierarchy (uncheck the box):