I’m trying to fight my way through learning LS. I’m trying to write an agent that looks at the datecreated field in a document (actual field name) and compares it to today. Then I want it to count how many documents were created today, how many were created over the last two days, and how many were created 3 days ago or more. I can make it go through the documents in my database and can retrieve the field data, but I’m having problems comparing that to todays date. Here’s what I got so far:
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim datecreated As Variant
Dim under1day As Integer
Dim over1under2days As Integer
Dim over2days As Integer
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument()
Do Until (doc Is Nothing)
datecreated = doc.DateCreated
If (Datevalue(datecreated) > Datevalue(Today)) Then
'need some help here tried a lot of things but keep
' getting errors about incompatible data types, etc.
End If
Messagebox(datecreated(0))
Set doc = dc.GetNextDocument(doc)
Call session.UpdateProcessedDoc(doc)
FYI, NotesDate time in Lotus Script can be one of the more tricky areas of Lotus Script as you have to watch our when you’re working with either a DateTime, a variant or string. So don’t be discouraged.
Taking Dmytro’s solution of using timedifference, here is how I would adjust your code. First see if it’s 3 or more days old if not, 2, if not 1.
…
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument()
Dim tmpDT As NotesDateTime
Dim NowDT As NotesDateTime
Dim DaysAgo3 As NotesDateTime
Dim DaysAgo2 As NotesDateTime
Dim DaysAGo1 As NotesDateTime
Set NowDT = New NotesDateTime( "Today" )
Call NowDT.AdjustDay(-1)
Set DaysAgo1 = New NotesDateTime( NowDT.localtime )
Call NowDT.AdjustDay(-1)
Set DaysAgo2 = New NotesDateTime( NowDT.localtime )
Call NowDT.AdjustDay(-1)
Set DaysAgo3 = New NotesDateTime( NowDT.localtime )
Set NowDT = New NotesDateTime( "Today" ) ' Reset
Do Until (doc Is Nothing)
' Think of it as NowDT - doc.Created = # seconds
' Then / ( 60 sec / min * 60 min / hour * 24 hrs / day) - to get days.
' The decimal 0 below is to force the type to double
Set tmpDT = New NotesDateTime( doc.Created ) ' Use your field instead
Msgbox "Number of days ago =[" & Cstr( Int( NowDT.TimeDifference( tmpDT ) / ( 60.0 * 60.0 * 24.0) ) ) & "]"
If DaysAgo3.TimeDifference(tmpDT) > 0 Then
' Do your 3 or more days ago
Elseif DaysAgo2.TimeDifference(tmpDT) > 0 Then
' Do your 2 or more days ago
Elseif DaysAgo1.TimeDifference(tmpDT) > 0 Then
' Do your 1 or more days ago
Else
' Less than a day.
End If
Set doc = dc.GetNextDocument(doc)
Loop
OK I’m getting closer. Something’s still not quite right about my if statement. Right now I’m checking for documents created more than a day ago but the results aren’t correct.
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim datecreated As Variant
Dim under1day As Integer
Dim over1under2days As Integer
Dim over2days As Integer
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument()
under1day = 0
Dim ndttoday As NotesDateTime
Dim createdDateTime As NotesDateTime
Set ndttoday = New NotesDateTime( “Today” )
Set createdDateTime = New NotesDateTime( “” )
Do Until (doc Is Nothing)
datecreated = doc.DateCreated(0)
Messagebox(datecreated)
Call ndttoday.AdjustDay( -1 ) ' set to one day ago
createdDateTime.LSLocalTime = doc.Created
If ndttoday.TimeDifference( modifiedDateTime ) > 0 Then
under1day = under1day + 1
End If
Set doc = dc.GetNextDocument(doc)
Call session.UpdateProcessedDoc(doc)
Messagebox(under1day)
Whoo hoo I got it! Here’s the final code. Would there have been an easier way to do it, or does it look OK? Everything works, thanks for your help!
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim over3days As Integer
Dim over1day As Integer
Dim totaltickets As Integer
Dim ndt3daysago As NotesDateTime
Dim ndt1dayago As NotesDateTime
Dim createdDateTime As NotesDateTime
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument()
Set ndt3daysago = New NotesDateTime( "Today" )
Call ndt3daysago.AdjustDay( -3 ) ' change to three days ago
Set ndt1dayago = New NotesDateTime( "Today" )
Set createdDateTime = New NotesDateTime( "" )
Do Until (doc Is Nothing)
totaltickets = totaltickets + 1
createdDateTime.LSLocalTime = doc.DateCreated(0)
If ndt3daysago.TimeDifference( createdDateTime ) > 0 Then
over3days = over3days + 1
Elseif ndt1dayago.timedifference(createdDateTime) > 0 Then
over1day = over1day + 1
Else
createdtoday = createdtoday + 1
End If
Set doc = dc.GetNextDocument(doc)
Call session.UpdateProcessedDoc(doc)
Loop
Messagebox (totaltickets & " Outstanding Tickets" & Chr(13) & Chr(13) & over3days & " Tickets over 3 days old " & Chr(13) & over1day & " Tickets 1-3 days old")