Check free time on LotusNotes calendar with VBA

If you can help with this one, here's another Q that goes hand in hand with this one

https://www.experts-exchange.com/questions/21357530/Listing-all-lotus-notes-calendar-appointments-with-VBA.html

So you plan to do some home-made replication between your application and the Notes mailfile (contains the Calendar as well)? Can be done, sure. Since this is not a rent-a-coder site, I'd like to know what's your code by now, and how you planned to implement it yourself?

As a suggestion:
- open the user's mailfile
- get the Calendar view, it's sorted ascending on date/time
- get the first document that has a time greater than or equal to the starttime
- do while the document's time is less than or equal to the endtime
-   get info and do something with it
-   get the next document
- loop

well, I would like to know how to do it without using ole automation because I have had tons of problems with it in my add appointment routine. Basically I would like to avoid anything that has the UI in it if possible: example Notes.NotesUIdocument

what I would be the happiest with is somebody can give me a link to some code that does at least something similar to this, its hard to get started when you can't find hardly any documentation (let alone any examples). So I need to know what objects I have to do what with to at least get to the equivalent of a calendar view in the non-ui objects.  I hope this is making sense

yeah, the reason I used the UI for my current code is because the older example on that site was the ONLY one I could find anywhere, so I modified it to do what I needed. I would love to be able to convert that to backend code like what you posted above. (I think I might post another question on how to do that.) Can you tell me where / how you got what you got above? I am in desperate need of some kind of reference for these objects.

Thanks a bunch though, I appreciate it

You shouldn't have closed this question yet, for you haven't tried it (I assume). It doesn't matter, you'll be getting responses anyway :)

Where I got it? Trial and error, starting at the bottom, setting up Domino servers (R3.21) without having the faintest idea what I was doing, then gradually understanding what Domino was all about and learning the peculiarities while stumbling forward. Some major projects did the rest, although I must say there are still very white spots on the Domino-map for me to discover.

What you need is the Domino Designer Help database. The Notes-version and a PDF-file you can download from
    http://www-10.lotus.com/ldd/notesua.nsf/find/designerrnext
but you need a Notes client to study it. Or you can visit it on-line at
    http://www-12.lotus.com/ldd/doc/domino_notes/Rnext/help6_designer.nsf/Main?OpenFrameSet

There are some good books as well on Domino Designer, if you search in Google on "domino designer help database R6" you'll find them easily.

Most important thing: come back here and ask many questions :)

PS When you developed a better (i.e. non-UI version) of the application on the website, would you be so kind as to have the website-version replaced? For those who don't know what we're talking about, see http://www.fabalou.com/VBandVBA/lotusnotesappointment.asp

sjef,

I only closed the question because I knew it was going to be a while before I could get back to it.  I have it almost working, up to this point. What I mean by that is I can't get all documents for a certain day, I can only get one document at a time if I put the date and time in there.

Public Function IsTimeFree(ByVal UserName As String, ByVal StartDT As Date, ByVal EndLen As Integer) As Boolean
    Dim db As Object
    'Dim ns As New NOTESSESSION
    Dim ns As Object
    Dim vw As Object
    Dim doc As Object
    Dim MailDbName As String
    Dim ThisDate As Date
    Dim ToEnd As Integer
    ThisDate = StartDT
    ToEnd = EndLen

    MailDbName = "mail\" + UserName + ".nsf"
    Set ns = CreateObject("Lotus.NotesSession")
    Call ns.Initialize

    Set db = ns.GETDATABASE("fsunotes1", MailDbName, False)
    If Not db.ISOPEN Then Exit Function
    Set vw = db.GETVIEW("Calendar")
    'Call vw.FTSEARCH(CDate("3/23/2005"), 0) <--doesn't work
Set doc = vw.GETDOCUMENTBYKEY(#3/23/2005 8:00:00 AM#) <--does work (same as next field)
    Set doc = vw.GETDOCUMENTBYKEY(ThisDate)
end function

oops, damn submit button

how else can I go about getting all the documents for a day?

What you need to do is to walk through the documents that follow the first. In code:

    Tomorrow= ThisDate+1 ' values count days
    Set doc= vw.GetDocumentByKey(ThisDate) ' will find a document on or after ThisDate
    Do Until doc Is Nothing
        If doc.StartTime(0)>=Tomorrow Then Exit Do
        ' the current document "happens" today
    Loop

What I am saying is that
vw.GETDOCUMENTBYKEY(#3/23/2005 8:00:00 AM#)  returns the document for that specific time only
vw.GETDOCUMENTBYKEY(#3/23/2005#)  returns nothing

From what I understand (which may be completely wrong) the getdocumentbykey method is designed to only return one document, so when I put in the start date/time of an appointment, I get that appointment, but what I need is a list of all appointments for a day

is what you are saying that
vw.GETDOCUMENTBYKEY(#3/23/2005 8:00:00 AM#) will get every appointment on or after 8:00 on 3/23/05?

(if you are then I feel like a moron!)

No, that's not what I meant to say.

GetDocumentByKey(#3/23/2005#) should return the first document with a key greater than or equal to that date. You could try with GetDocumentByKey(#3/23/2005 00:00:00 AM#) but that shouldn't matter.

Then, after you found the first document, you have to retrieve documents sequentially {using GetNextDocument(doc)} until you run out of documents or the document has not the right date. That seems to be a part I left out the last post... :$

Again:

    Tomorrow= ThisDate+1 ' values count days
    Set doc= vw.GetDocumentByKey(ThisDate) ' will find a document on or after ThisDate
    Do Until doc Is Nothing
        If doc.StartTime(0)>=Tomorrow Then Exit Do
        ' the current document "happens" today
        Set doc= vw.GetNextDocument(doc)
    Loop

There is no straightforward way to retrieve all documents at once (it involves Full-Text searching and a call to FTSearch). I'll try the code myself, to find out if GetDocumentByKey with only a date works or not.

Doesn't seem to work :( The False only indicates that a partial string match will be enough. No strings involved (yet).

There's another view, called CalSummary. Its first column is a string-value of the date and time of an appointment. The modified code would be:

    Set vw=db.GetView("CalSummary")
    key= "03/23/2005" ' format unknown by me, system-dependent
    Set doc= vw.getdocumentbykey(key)
    Do Until doc is Nothing
        If doc.StartDate(0)<>dt Then Exit Do
        ...
        Set doc= vw.getnextdocument(doc)
    Loop        

Ok here is what I got that works, you were pretty much right to begin with, I just needed to play around with it a little and I finally got some time to today... what I ended up doing instead of just checking if the time was open, was outputting an array of calendar events for the specified user and day. Here's what I came up with:

Thanks for all your help

Public Sub TestMe()
    Dim tmp
    Dim tmplen As Integer
    Dim i As Integer
    tmp = GetUsedTime("<USERNAME>", "<PASSWORD>", "3/24/2005", tmplen)
    For i = 1 To tmplen
        Debug.Print tmp(i, 3) & "(" & tmp(i, 1) & "): " & tmp(i, 4) & " Duration: " & tmp(i, 2) & " Mins"
    Next i
End Sub

Public Function GetUsedTime(ByVal UserName As String, ByVal UserPass As String, ByVal StartDate As String, Optional ByRef OutLen As Integer) As Variant
    Dim db As Object, ns As Object, vw As Object, doc As Object, MailDbName As String
    Dim strSDT As String, strEDT As String, strFType As String, strSubj As String
    Dim ToEnd As Integer, intIncrement As Integer, int8to5 As Integer, i As Integer
    Dim ThisDate As Date

    Dim ArrOut(1 To 15, 1 To 4)
   
    intIncrement = 30
    int8to5 = 17   '8am to 5 pm = 540 mins - 30 mins (4:30, last appt) = 510 mins / 30 min intervals = 17 intervals
   
    ThisDate = CDate(StartDate & " 8:00 AM")

    MailDbName = "mail\" + UserName + ".nsf"
    Set ns = CreateObject("Lotus.NotesSession")
    Call ns.Initialize(UserPass)

    Set db = ns.GETDATABASE("<SERVERNAME>", MailDbName, False)
   
    If Not db.ISOPEN Then Exit Function
    Set vw = db.GETVIEW("Calendar")
    Set doc = vw.GETDOCUMENTBYKEY(ThisDate)
    Do Until Not doc Is Nothing
        ThisDate = DateAdd("n", intIncrement, ThisDate)
        int8to5 = int8to5 - 1
        If int8to5 = 0 Then GoTo DoReturnFalse
        Set doc = vw.GETDOCUMENTBYKEY(ThisDate)
    Loop
    i = 1
    Do Until doc Is Nothing

        strSDT = doc.GETITEMVALUE("STARTDATETIME")(0)
        ArrOut(i, 1) = strSDT
        strEDT = doc.GETITEMVALUE("ENDDATETIME")(0)
        ArrOut(i, 2) = DateDiff("n", CDate(strSDT), CDate(strEDT))
        strFType = doc.GETITEMVALUE("FORM")(0)
        ArrOut(i, 3) = strFType
        strSubj = doc.GETITEMVALUE("SUBJECT")(0)
        ArrOut(i, 4) = strSubj
       
        If DateDiff("d", CDate(strSDT), ThisDate) <> 0 Then
            Set doc = Nothing
        Else
            Set doc = vw.GETNEXTDOCUMENT(doc)
            i = i + 1
        End If
    Loop
    If i > 1 Then
        GetUsedTime = ArrOut
        OutLen = i - 1
        Exit Function
    End If
   
DoReturnFalse:
    GetUsedTime = False
   
End Function

That loop bothers me, with the DateAdd function in it: terribly inefficient. Did you try my CalSummary suggestion? Else you'd better set up FullText searching, because the FTSearch-method will return all the required documents in one go.

ftsearch with only the date returns nothing, calsummary returns nothing also... i'm working with LN 6.0.3 if that makes a difference

Ah, you're trying FTSearch. Great! The syntax for a query is not very easy, so you might need a push there. CalSummary didn't work?? How odd, but what's your code in both cases?

About the CalSummary: you can open the view in your Notes Client. Press Shift-Cntrl AND click on View/Goto. You'll see the hidden views as well. Then see what's in the first column, it should be text in your local date format. That's what makes this solution "less perfect".

The query for the FTSearch you can try from the search bar in your database, see View/Search this view. In it, type something like
    field startdatetime = #12/23/2004#
The format of the date depends (again) on your local settings. In many European countries, it would be
    field startdatetime = #23-12-2004#

I did the CTRL+SHIFT and went to the goto menu... I did notice alot of hidden views, but none called calsummary (or anything even calendar related as far as I can tell).  

with ftsearch, all I did was changed GETDOCUMENTBYKEY to FTSEARCH basically...

I am in the USA, so our dateformat is #12/23/2004#

No CalSummary? Hmm. What's the template you're using? It must be a standard Notes mail template, for R6, with the name StdR6Mail. Whether other templates contain the same view I don't know, you'd have to look in the design if there's one around that fits your need.

Using FTSearch, you'll get a NotesDocumentCollection that you have to walk through. Instead of the GetView you do an FTSearch, so basically the code would be something like this:
    .
    .
    Set db = ns.GETDATABASE("<SERVERNAME>", MailDbName, False)
    If Not db.ISOPEN Then Exit Function
    Set dc = db.FTSearch("Field startdate = #" & Format$(ThisDate, "ddddd") & "#")
    Set doc= dc.GetFirstDocument
    Do Until doc Is Nothing
        .
        Set doc = dc.GETNextDOCUMENT(doc)
    Loop

That's about it.

You're good sjef... aside from a couple minor changes, it's perfect, thanks a bunch

Public Function GetUsedTime(ByVal UserName As String, ByVal UserPass As String, ByVal StartDate As String, Optional ByRef OutLen As Integer) As Variant
    Dim db As Object, ns As Object, vw As Object, doc As Object, MailDbName As String
    Dim strSDT As String, strEDT As String, strFType As String, strSubj As String
    Dim i As Integer
    Dim strSrch As String

    Dim ArrOut(1 To 15, 1 To 4)

    MailDbName = "mail\" + UserName + ".nsf"
    Set ns = CreateObject("lotus.NotesSession")
    If Not UserPass = "~" Then
        Call ns.Initialize(UserPass)
    Else
        Call ns.Initialize
    End If
   

    Set db = ns.GETDATABASE("fsunotes1", MailDbName, False)
   
    If Not db.ISOPEN Then Exit Function
       
    Set vw = db.FTSEARCH("FIELD startdate = #" & StartDate & "#", 0)
    Set doc = vw.GETFIRSTDOCUMENT

    i = 1
    Do Until doc Is Nothing
       
        strSDT = doc.GETITEMVALUE("STARTDATETIME")(0)
        ArrOut(i, 1) = strSDT
        strEDT = doc.GETITEMVALUE("ENDDATETIME")(0)
        ArrOut(i, 2) = DateDiff("n", CDate(strSDT), CDate(strEDT))
        strFType = doc.GETITEMVALUE("FORM")(0)
        ArrOut(i, 3) = strFType
        strSubj = doc.GETITEMVALUE("SUBJECT")(0)
        ArrOut(i, 4) = strSubj
       
        If DateDiff("d", CDate(strSDT), StartDate) <> 0 Then
            Set doc = Nothing
        Else
            Set doc = vw.GETNEXTDOCUMENT(doc)
            i = i + 1
        End If
    Loop
    If i > 1 Then
        GetUsedTime = ArrOut
        OutLen = i - 1
        Exit Function
    End If
   
DoReturnFalse:
    GetUsedTime = False
   
End Function

Thanks for the compliment :) I always forget the details, and I don't pay attention to closing arguments (in this case, the last parameter for FTSearch).

To speed up the search process (I think), you could add that only Calendar documents should be searched, using
    Set vw = db.FTSEARCH("FIELD Form = Calendar and FIELD startdate = #" & StartDate & "#", 0)

There is a shorter way to write this stuff:
    Set vw = db.FTSEARCH("[Form]=Appointment & [startdate]=#" & StartDate & "#", 0)

Instead of the Set doc = Nothing you can easily jump out of the loop using
    Exit Do
so your code could be:

    If DateDiff("d", CDate(strSDT), StartDate) <> 0 Then
        Exit Do
    End If
    Set doc = vw.GETNEXTDOCUMENT(doc)
    i = i + 1

Last remark: if you start with i= 0, and then right after the Do Until you set i= i+1, then you wouldn't have to subtract 1 from i at the end, and you could test for >0 if anything was found.

the only reason I had I set to 1 was because I didn't want a zero based array... and yeah, the exit do is a good idea

But your array would stay the same. Start with i= 0, and increment i right in the first statement in the loop.

here's something interesting...

although the ftsearch method is far more elegant, the first method I put up where it increments the time executes almost instantly whereas the ftsearch method takes a whopping three seconds

(I put timers in the code to check)

just an fyi

Disadvantage: it takes 3 seconds. Advantage: it ALWAYS takes 3 seconds. It's not so efficient, and Full-Text index maintenance is expensive, so a view is usually preferential.

Still no sign of the CalSummary view? Odd...

well, the way I have it set up is that it searches counselor's calendar's for free time to schedule appointments. It is makin the secretaries who schedule the appointments REALLY happy. It has to go through and check either one or 10 calendars at a time, so if it checks 10, that's 30 seconds and they might as well just go to the calendars and look through each one for free time like they were doing before. You have no clue how excited I am to finally get this done though, I have been trying to figure out a way to do this for like 6 months.

I did run into a little snag with recurring events though... since the startdate is the first date it occurs I had to adjust a little bit, i'll post the final rock solid code when I get to work

again, thanks for all the help, i've got a pretty good handle on LN programming now (even though I still think it works kind of back-asswards)

:) Back-asswards: you're pretty much asking for it when trying to use VBA, aren't you? It's great that it works, congratulations!

I don't want to discourage you, but did you know that the functionality you want is standard in the Notes-client? Look into the database Busytime.nsf, you can find a lot of information about it in the Administrator's Help database. See the topic called Calendaring and Scheduling.

With this database, you might be able to find out for all users in one go if they are available or not. I'm sorry to say that I never investigated this functionality in-depth, so I cannot help you with it.

As a followup (months later)... Since this page is getting more search engine hits than the article I wrote on it:

Here's an article I wrote partially based on this question

http://www.vortexwd.net/lotusnotescalendaring1.asp