Get Lotus Notes calendar entries for a time range in powershell

Question

I try to track the time I use for projects by adding the project id as a category to a lotus notes calendar entry. Now I want to select all appointments over a time range (i.e. the last 7 days), group them by category and sum up the time. Except for the time range thingy it works fine. The solution seams to be the method GetAllDocumentsByKey (https://help.hcltechsw.com/dom_designer/9.0.1/appdev/H_GETALLDOCUMENTSBYKEY_METHOD.html) I tried to define a time range, a string (subject or category) or an array (values: subject or category) as keyArray but nothing seams to work.

Any idea?

$notesINI = Get-IniContent $env:LOCALAPPDATA\IBM\Notes\Data\notes.ini
$InputNotesMailBox = $notesINI.Notes.MailFile
$InputNotesServer = $notesINI.Notes.MailServer
$DomSession = New-Object -ComObject Lotus.NotesSession
$DomSession.Initialize()
$DomDatabase = $DomSession.GetDatabase($InputNotesServer,$InputNotesMailBox)
$DomView = $DomDatabase.GetView('Calendar')

# This works fine but is really slow because it processes all the documents in my calendar (5Min for 6k documents)
$DomDocRange = $DomView
$DomDoc = $DomDocRange.GetFirstDocument()
$date_s = $(get-date -Year 2022 -Month 4 -Day 7)
$date_e = $(get-date -Year 2022 -Month 4 -Day 14)
while($DomDoc -ne $null){
    if($DocCategory -like "proiject-*"){
        $Termin_StartDate = $DomDoc.getItemVAlue('StartDateTime')[0]
        if(IsBetweenDates2 $date_s $date_e $(get-date $Termin_StartDate)){
            # add time and project id to multidimensional array
        }
    }
}

# does not work because I don't know how to filter getAllDocumentsByKey
$date_s = $(get-date -Year 2022 -Month 4 -Day 7)
$date_e = $(get-date -Year 2022 -Month 4 -Day 14)
$DomDateRange = $DomSession.CreateDateRange()
$DomDateRange.StartDateTime = $DomSession.CreateDateTime($date_s)
$DomDateRange.EndDateTime = $DomSession.CreateDateTime($date_e)
$DomDocRange = $DomView.getAllDocumentsByKey($DomDateRange, $true)
$DomDoc = $DomDocRange.GetFirstDocument()
while($DomDoc -ne $null){
    if($DocCategory -like "proiject-*"){
        $Termin_StartDate = $DomDoc.getItemVAlue('StartDateTime')[0]
        if(IsBetweenDates2 $date_s $date_e $(get-date $Termin_StartDate)){
            # add time and project id to multidimensional array
        }
    }
}

Answer 1

GetDocumentByKey is a function of a view that needs to be sorted by the key you are searching for. The "Calendar"- view is not suitable for this function.

You need to get away from NotesView and get your document collection from the database itself without using the view.

$DomDocRange = $DomDatabase.search( 'Form = "Appointment" & StartDateTime >= [InjectYourStartDateHere] & StartDateTime <= [InjectYourEndDateHere]', Nothing, 0 )
$DomDoc = $DomDocRange.GetFirstDocument()

The constructed @Formula needs to look like this in the end:

Form = "Appointment" & StartDateTime >= [04/01/2022] & StartDateTime <= [04/12/2022]

The brackets are important as they mark the given value as a date. The date needs to be in your local format (MM/DD/YYYY or DD.MM.YYYY or...).

Unfortunately I did not use the interface with Powershell yet, so I can't tell you, how the insert the LotusScript variable "Nothing" here, you could try omit it (use double comma) or use $null...