?
Solved

Check free time on LotusNotes calendar with VBA

Posted on 2005-03-20
27
Medium Priority
?
1,789 Views
Last Modified: 2013-12-18
My application currently has a routine that transfers an appointment to a lotus notes calendar when the appointment is made. (if it matters, the code I use for this is here: http://www.fabalou.com/VBandVBA/lotusnotesappointment_ver6.asp)

I want to have a similar function (preferably directly accessing the database as opposed to using ole automation like in the above example) which will work like this:

Function CalIsOpen(NotesDB, StartTime, EndTime) as Boolean
specified time block is open: Return true
specified time block is closed: Return false

specific code or a link to some specific code will get the points
0
Comment
Question by:jkorz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
27 Comments
 
LVL 8

Author Comment

by:jkorz
ID: 13584868
If you can help with this one, here's another Q that goes hand in hand with this one

http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21357530.html
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13585932
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
0
 
LVL 8

Author Comment

by:jkorz
ID: 13586812
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




0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 13589350
It's COM, not OLE.

I checked Google on <vba notes calendar>, and the 2nd link was yours. When studying that code, I noticed that you did everything (indeed) through the UI, and there's absolutely no need for that. The Domino Object Model has both the UI objects and the non-UI objects. Things are similar, yet considerably different. In your question above, I see no need to use the UI, that's why in my answer I assumed only backend calls.

I'll explain (in LotusScript/VBA-like code):

    Dim mdb As NotesDatabase
    Dim ns As NotesSession
    Dim vw As NotesView
    Dim doc As NotesDocument

    Set ns= CreateObject("Domino.NotesSession")
    Set db= ns.GetDatabase(server, filename, False)
    If Not Db.IsOpen Then Exit Sub
    Set vw= db.GetView("Calendar")
    Set doc= vw.GetDocumentByKey(date) ' assume date is conformant to Notes definitions
    Do Until doc Is Nothing
        If doc.date>endtime Then Exit Do
        ' get info and do something with it

        Set doc= vw.GetNextDocument(doc)
    Loop

In the code you can't use FieldGetText. The NotesDocument equivalent is GetItemValue, it returns an array, so you always have to index the outcome to get a single value. What might be better is to use the ColumnValues-property of a document (works only when the document is retrieved using a view). The first column contains the exact date and time of the appointment, in ColumnValues(0). ColumnValues(8) and (10) contain the startdate and enddate of the appointment. ColumnValues(11) contains subject, room, location and chair. Please look in the design of a mail database to find out what the other columns contain.
0
 
LVL 8

Author Comment

by:jkorz
ID: 13596460
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13598968
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
0
 
LVL 8

Author Comment

by:jkorz
ID: 13613597
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
0
 
LVL 8

Author Comment

by:jkorz
ID: 13613607
oops, damn submit button

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

Expert Comment

by:Sjef Bosman
ID: 13613885
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
0
 
LVL 8

Author Comment

by:jkorz
ID: 13615138
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!)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13619966
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13620323
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        
0
 
LVL 8

Author Comment

by:jkorz
ID: 13624942
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13628984
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.

0
 
LVL 8

Author Comment

by:jkorz
ID: 13654445
ftsearch with only the date returns nothing, calsummary returns nothing also... i'm working with LN 6.0.3 if that makes a difference
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13655260
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#
0
 
LVL 8

Author Comment

by:jkorz
ID: 13662538
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#
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13662791
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.
0
 
LVL 8

Author Comment

by:jkorz
ID: 13662962
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13663486
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.
0
 
LVL 8

Author Comment

by:jkorz
ID: 13663588
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13663655
But your array would stay the same. Start with i= 0, and increment i right in the first statement in the loop.
0
 
LVL 8

Author Comment

by:jkorz
ID: 13674212
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13679420
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...
0
 
LVL 8

Author Comment

by:jkorz
ID: 13679605
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)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13679985
:) 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.
0
 
LVL 8

Author Comment

by:jkorz
ID: 14251632
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question