Retrieve Documents Within Date Range using VB/VBA

H

hewett_nick

Dear contributors,

I apologise if there is a more appropriate group for this query - I can
see relatively few Notes related posts in the VB/VBA groups and
vice-versa.

Currently I have an Excel VBA application that will retrieve all
documents from a Lotus Domino database created after a certain date by
passing a date to the search request in the following way...

Private Sub GetNotesData(crDate1 As String)
Dim LNdate As NotesDateTime
Dim LNdocs As NotesDocumentCollection
Dim LNdoc As NotesDocument

With Application
.StatusBar = "Retrieving recordset from " & SRVR & ":\\" &
DBASE & " - Please be patient..."
.DisplayAlerts = False
End With

' Set the time
Set LNdate = LNsession.CreateDateTime(CDate(crDate1))

' Do the search
Set LNdocs = LNdb.Search(SELEQT, LNdate, 0)

'
'
' Etc.

Is it possible to reduce the recordset (and therefore the horrendously
slow retrieval times) by also passing it a second date, before which
documents were created?

In fact, is there an alternative/better method to use than
CreateDateTime? Currently the session is returning some documents that
were created before crDate1 as though CreateDateTime is possibly the
date and time the document was moved to the particular server/disk it
is being read from.

Br, NickH
 
K

Karl-Henry Martinsson

Dear contributors,

I apologise if there is a more appropriate group for this query - I can
see relatively few Notes related posts in the VB/VBA groups and
vice-versa.

Currently I have an Excel VBA application that will retrieve all
documents from a Lotus Domino database created after a certain date by
passing a date to the search request in the following way...

Private Sub GetNotesData(crDate1 As String)
Dim LNdate As NotesDateTime
Dim LNdocs As NotesDocumentCollection
Dim LNdoc As NotesDocument

With Application
.StatusBar = "Retrieving recordset from " & SRVR & ":\\" &
DBASE & " - Please be patient..."
.DisplayAlerts = False
End With

' Set the time
Set LNdate = LNsession.CreateDateTime(CDate(crDate1))

' Do the search
Set LNdocs = LNdb.Search(SELEQT, LNdate, 0)

'
'
' Etc.

Is it possible to reduce the recordset (and therefore the horrendously
slow retrieval times) by also passing it a second date, before which
documents were created?

In fact, is there an alternative/better method to use than
CreateDateTime? Currently the session is returning some documents that
were created before crDate1 as though CreateDateTime is possibly the
date and time the document was moved to the particular server/disk it
is being read from.
Click to expand...

First of all, you are using db.Search(), which is not only slow but also
very CPU intensive on the server. Full-text index the database and use
db.FTSearch() instead.
Second, why not use a smarter search criteria. If you have a creation date
in the document, you can search using that. I often add a creation date,
since it might happen that a document has to be copied from one database to
another, or something. When you cope a document, the time and date will not
be when the original was created, but when it was copied. So to prevent
problems, I think it is a good practice to maintain a separate creation
date. And you never know when you need to search for a date, like you just
found out. :)

So assuming you build a full-text index, it should look something like this:

query = "[CreationDate]>" & startdate & " AND [CreationDate]<" & enddate
set col = db.FTSearch(query,0)
' Do your stuff here

Notice that you only get 5000 documents return by a query, this can be
changed in the server document, though. So for a big database, it may not
work.

What you could do is to have a categorized view, and use
view.GetAllDocumentsByKey() to get the documents for each date, starting at
the first and ending at the last.

set view = db.GetView("(LookupDocumentsByDate)")
For i = startdate to enddate
set col = view.GetAllDocumentsByKey(Format$(i,"mm/dd/yyyy"))
' Do your stuff here
Next


Good luck!

/Karl
 
H

hewett_nick

Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)>" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
....

@Date(S1Date)>28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?

Kind regards, NickH
 
H

hewett_nick

Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)>" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
....

@Date(S1Date)>28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?

Kind regards, NickH
 
K

Karl-Henry Martinsson

Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)>" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
...

@Date(S1Date)>28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?
Click to expand...

You are using the wrong syntax for a Full text search. It is documented in
the help.

You should use something like this:

[S1Date]>28/10/2006 AND [S1Date]<28/11/2006

or

FIELD S1Date>28/10/2006 AND FIELD S1Date<28/11/2006

I prefer the first way.

/Karl
 
H

hewett_nick

Thanks for trying Karl but if I use square brackets Notes complains
because S1Date is a text field.

I've tried both VBA help and Notes help - neither offer any info on
FTSearch.

I'll have to stick with my current work-around which looks at all the
documents returned by my original query and simply ignores those that
were created outside the specified date range. Its slow but it works.

Kind regards, NickH
 
K

Karl-Henry Martinsson

Thanks for trying Karl but if I use square brackets Notes complains
because S1Date is a text field.
Click to expand...

Bad design. :)
I've tried both VBA help and Notes help - neither offer any info on
FTSearch.
Click to expand...

Huh? In designer: "Help", "Help Topics", expand "LotusScript/COM/OLE
Classes", expand "Lotusscript Classes A-Z", expand "NotesDatabase class" and
click on "FTSearch method":

FTSearch method

Conducts a full-text search of all the documents in a database.

Defined in
NotesDatabase

Syntax
Set notesDocumentCollection = notesDatabase.FTSearch( query$, maxDocs%
[,sortoptions [, otheroptions]] )

Parameters
query$ String. The full-text query. See below for the syntax.
maxDocs% Integer. The maximum number of documents you want returned from
the query. Set this parameter to 0 to receive all matching documents.
sortoptions Integer. Optional. Use one of three constants to specify a
sorting option:
FT_SCORES (8) sorts by relevance score (default).
FT_DATE_DES (32) sorts by document creation date in descending order.
FT_DATE_ASC (64) sorts by document creation date in ascending order.
otheroptions Integer. Optional. Use one of two constants to specify
additional search options:
FT_STEMS (512) uses stem words as the basis of the search.
FT_DATABASE (8192) search includes Domino databases.
FT_FUZZY (16384) searches for related words. Need not be an exact
match.
FT_FILESYSTEM (4096) search includes files that are not Domino
databases.
Note These values are also used with the FTDomainSearch and
UnProcessedFTSearch methods in the NotesDatabase class.

Return value
notesDocumentCollection A collection of documents that match the
full-text query, sorted by the selected option.When the collection is sorted
by relevance the highest relevance appears first. To access the relevance
score of each document in the collection, use the FTSearchScore property in
NotesDocument.

Usage
If the database is not full-text indexed, this method works, but less
efficiently. To test for an index, use the IsFTIndexed property in
NotesDatabase. To create an index on a local database, use the UpdateFTIndex
method.
This method searches all of the documents in a database. To search only
documents found in a particular view, use the FTSearch method in NotesView.
To search only documents found in a particular document collection, use the
FTSearch method in NotesDocumentCollection.
If you don't specify any sort options, you get the documents sorted by
relevance score. If you ask for a sort by date, you don't get relevance
scores. If you pass the resulting DocumentCollection to a NotesNewsletter
instance, it formats its doclink report with either the document creation
date or the relevance score, depending on the sort options you use.
If the database has a multi-database index, you get a multi-database search.
Navigation through the resulting document collection may be slow, but you
can create a newsletter from the collection.

Query syntax
To search for a word or phrase, enter the word or phrase as is, except that
search keywords must be enclosed in quotes. Remember to escape quotes if you
are inside a literal.
Wildcards, operators, and other syntax are permitted. For the complete
syntax rules, see "To use operators to refine a search" in Notes 5 help.

See Also
FTSearch method (in NotesDocumentCollection)
FTSearch method (in NotesView)
FTSearchScore property (in NotesDocument)
IsFTIndexed property
LastFTIndexed property
NotesDocumentCollection class
UnprocessedFTSearch method
UpdateFTIndex method

I'll have to stick with my current work-around which looks at all the
documents returned by my original query and simply ignores those that
were created outside the specified date range. Its slow but it works.
Click to expand...

You could also create a view of all documents you want to act on,
categorized by date. Then you use view.GetAllDocumentsByKey() to retrieve
all documents from each day, one date at a time.
 
H

hewett_nick

Thanks again Karl,

I don't have Design access which I guess explains the lack of available
help. I'm assuming this also means I will be unable to create views.

Its not the end of the world, like I say, we have a working solution -
if people complain about it being slow I'll just tell them to blame the
database designer. ?;^)

Br NickH
 
K

Karl-Henry Martinsson

Thanks again Karl,

I don't have Design access which I guess explains the lack of available
help. I'm assuming this also means I will be unable to create views.
Click to expand...

Why not just look at the helpfile through a browser? There are several
places where the help files are posted online.

http://www-10.lotus.com/ldd/notesua.nsf/0/31ff9be43583a4c7852569810054522f
is one place...
Its not the end of the world, like I say, we have a working solution -
if people complain about it being slow I'll just tell them to blame the
database designer. ?;^)
Click to expand...

Did you try my suggestion to use view lookups? Was that faster?

/Karl
 
H

hewett_nick

Your persistance is admirable Karl, :)
Did you try my suggestion to use view lookups? Was that faster?
Click to expand...

No, I'm not that familiar with Notes and assumed that views would
probably present a problem either because I don't have design access
or, if that's not an issue, that they would only be created locally.

The tool needs to work for a number of different people in different
locations - which presently it does. The only issue is that it seems a
little slow (takes about 45 mins to retrieve 10,000 documents) but like
I say this isn't a major issue. The user has the option of setting the
application to run at a specified time so they can have it run while
they're at lunch or at home.

I guess the only person that really gets bugged by it is me when I have
to test it.

Thanks again for your help Karl,

NickH
 
K

Karl-Henry Martinsson

Your persistance is admirable Karl, :)


No, I'm not that familiar with Notes and assumed that views would
probably present a problem either because I don't have design access
or, if that's not an issue, that they would only be created locally.
Click to expand...

I know you don't know too much about Lotus Notes, but you keep saying that
you don't have design access to the database. That is probably irrelevant.
You should be able to use existing views for your lookups. You write the
code in eithe ranother Notes database (where you have design access) or in
VB/VBA, Delphi or some other tool that support COM. You then code against
the Notes database, using the code I posted... Not hard at all.
The tool needs to work for a number of different people in different
locations - which presently it does. The only issue is that it seems a
little slow (takes about 45 mins to retrieve 10,000 documents) but like
I say this isn't a major issue. The user has the option of setting the
application to run at a specified time so they can have it run while
they're at lunch or at home.
Click to expand...

Write it as a small VB application and distribute it to the users. Or write
it as a Notes application and have it replicate out to the different
servers, that would be better and make maintenance/changes/updates easier in
the future. Obviously you have a Notes infrastructure, take advantage of
that...
I guess the only person that really gets bugged by it is me when I have
to test it.

Thanks again for your help Karl,
Click to expand...

You are welcome.

/Karl
 
Top