Urgent Help needed with this question. I hope someone has time to give me a hand

Posted on 2006-06-13
Last Modified: 2013-12-18
Hi all,

Dim session As New NotesSession
      Dim dbCur As NotesDatabase
      Set dbCur = session.CurrentDatabase
      Dim sPath As String
      dim doc as notesdocument
      spath = Left( dbCur.FilePath, Len(dbCur.FilePath) - Len(dbCur.FileName) )
      dim ssearchcriteria as string
           'THIS IS WHERE THE PROBLEM OCCURING, when I set the search criteria as below, it doesn't seem to pick up the right       documents but it sends an either way. In the following selection criteria, all what I am trying to do is to look into all documents i
      the database and based on the values of the testday field, select the document to use in the reminder.
            KEEP IN MIND THAT TESTDAY field is a multivalued date field, which has many values. I am not sure if this creating the

            ssearchcriteria = testdays => today & testdays =< today + 30
            I also tried
            ssearchcriteria = doc.testdays => today & doc.testdays =< today + 30
            but had problems with object variable not set.
           'I can't use doc in the selection criteria becaue i am not setting the doc to any value as yet, so I don't get the
           object variable not set error.


            IS THIS CORRECT?
            Can anyone tell me what I am doing so wrong for this not to work?


         Dim db1 As New NotesDatabase( "", spath & "testdb.nsf" )
        Dim view As NotesView
      Set view = db1.GetView("alltests")
     ' === Build the lookup keys array
      Dim aLookupKeys(1) As String
      aLookupKeys(0) = doc.testdays(0) => today & doc.testdays(0) =< today + 30

       ' how can I set the doc here so I don't get object not set error. session.documentcontext
      won't be good and uidoc won't be good either as this is a scheduled agent

       aLookupKeys(1) = doc.customer(0)
    ' === Gather the collection using view.GetAllDocumentsByKey

      dim testcoll as notesdocumentcollection
      Set testcoll = view.GetAllDocumentsByKey(aLookupKeys)
      ' === Declare the loop variables
      Dim stestdates As String
      Dim doc1 As NotesDocument
     ' === Walk the collection and build the text output of test dates
      Set doc1 = testcoll.GetFirstDocument
      Do Until doc1 Is Nothing
            stestdates = stestdates & doc.pserialno(0) & Chr(13)
            Set doc = testcoll.GetNextDocument(doc1)
      ' === Report the testdates based on selection criteria in lookupkey(1)
            I need to replace this by creating again a spreadsheet object based on the results(stestdates) and again send that to
           a specific user.

       Also, if I need to run on all docs in the db,but send reports (spreadsheet to users based on their security), do I need to use lookupkeys or can I just use ssearchcriteria above.
For example,

In db, I have customer a, customer b, customer c. customer 1000.
In the view, I have it and categorized by customer
each customer has 100's of test dates stored in many test documents in the product form. The testday field

Now, the agent above suppose to run on all the documents in the database and collate the testdays based on lookup criteria, then create a report for each customer based on the output I receive from this loop to send to them via email.

So the real question, which one of the two above do i need? the lookup(getalldocumentsbykey) or (sserachcriteria)?

Your help with this is highly appreciated.
Thank you all in advance
Question by:varvoura
  • 5
  • 4
LVL 22

Expert Comment

ID: 16892944
Hi varvoura,
regarding this part (for now):

>   ssearchcriteria = testdays => today & testdays =< today + 30
>   I also tried
>   ssearchcriteria = doc.testdays => today & doc.testdays =< today + 30

This is not the way to specify condition.

    doc.testdays => today

will end up like this:
    "31.05.2006", "01.07.2006", "30.11.2006" => "13.06.2006"

how would you evaluate that, True or False?



Author Comment

ID: 16892956
Do you mean to say that you'd have to compare each element of the array to today's value. Something similar to you earlier posting but arraycompare instead??

LVL 22

Expert Comment

ID: 16893072
I see you have the problem with how to assemble the query:

The FTSearch method of NotesView creates a subset of the view or folder consisting of documents that match a search query. The search query, specified as the first argument of FTSearch, is a string that meets the rules for full-text search queries. The rules require that a search entity be a single word or multiple words enclosed in quotes (double quotes in a LotusScript string constant). Search entities can include ? and * wildcards, and can be combined by ! (not), & (and), and | (or) operators.
A database does not need to be full-text indexed for a search to work, but the search is slower. The UpdateFTIndex method of NotesDatabase creates or updates a full-text index. The IsFTIndexed property of NotesDatabase tests for the existence of an index.


> 'I can't use doc in the selection criteria becaue i am not setting the
> doc to any value as yet, so I don't get the object variable
> not set error.

where are you're pulling the data from?
You'll have to set the doc before getting the data from it, won't you?


Author Comment

ID: 16893142
Yes, that's what I want to do.

supposedly from every document in the database.

I am suppose to run the query on the entire database(which by the way has 1000's or documents), and based on this seach criteria, set some sort of notification sent to customer to notify them and give them a list of all the tests that are suppose to be due within a month from the current date.
Reminder of their upcoming product tests.
By right, in the database (each product document) for each custome and a custome may have 100's of products to test on is already stored in the database with test dates created when the product document is created based on intervals. Now if the agent runs every day, based on the 1 to 30 days criteria, it should select those dates and report on them to the customer.

Have I confused you more or are you now able to understand what I am trying to achieve.

I am going to revise the other post now (postdrag) and then I'll take a look at your suggestion here and I'll post back ASAP.

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

LVL 22

Expert Comment

ID: 16893254
I should've read the question to it's end, my mistake
Forget ftsearch, I suggest you to use the view that contains all the documents you have to analyze, categorized by Customer.
Then use GetAllDocumentsByKey, like this:

    'after you declare everything and set db and view...
    Dim cur As NotesDocument

    Set docColl = view.GetAllDocumentsByKey( "CustomerName" )

    If docColl Is Nothing Then Exit Sub
    If docColl.Count = 0 Then Exit Sub
    'To loop through all docs in the collection one by one:
    Set cur = docColl.GetFirstDocument()        
    Do Until cur Is Nothing

        'here do whatever you need with the current doc

        Set cur = docColl.GetNextDocument( cur )

figure out the best way to gather the collection (maybe it would be better to have not categorised view with all docs that need processing), I don't know your situation...
You can loop through all the docs of the view very similar to above:

    '...after you set the view:

    Set cur = view.GetFirstDocument
    Do Until cur Is Nothing

        'to get all the dates (one by one) in your date field:
        Forall dat In doc.GetItemValue("YourDateField")
            Print CStr( dat )
        End Forall

        Set cur = view.GetNextDocument( cur )

And for creating spreadsheets in the Excel from Lotus, you'll find help here:

Hope this helps,
LVL 22

Accepted Solution

mbonaci earned 500 total points
ID: 16893370
OK, create the view that has only docs that needs processing (that are due in the next 30 days).
You'll do that by specifying the selection formula like this:

Dat1 := @TextToDate( "Today" );
Dat2 := @Adjust( @TextToDate( "Today" ); 0; 0; 30; 0; 0; 0 );

SELECT Form = "Test" & DueDate > Dat1 & DueDate < Dat2

Then, in your agent, use the code to loop through all the docs in the view.
You'll have to set some king of flag field in each processed doc (doc whoose reminder has been sent), otherwise you'll send the notification for the same doc every day (for 30 days) until it's due date becomes today.

So you'll do it like this:

  '...after you set the view:

    Set cur = view.GetFirstDocument
    Do Until cur Is Nothing

        If cur.flagProcessed <> "" Then
            '... notification here

            cur.flagProcessed = "1"
            Call cur.Save( True, True, True )
        End If

        Set cur = view.GetNextDocument( cur )

Got it?


Author Comment

ID: 16893416
Yup, great, this will save me a lot of heartache!!

I'll close this question now and if I need any further help, I'll open another one with reference to this, but I have doubt that i'll have any problems with all the explanation that you have for me.

Great for the notification, but I decide to also create an the excel and also send a doc link to it in the notification, would that be possible also.
I am trying to give user two options:

1. Option 1(ur code above) and the notification with the doclink to the view
2. Option 2 (also in the body of the email) render a spreadsheet with the output.
Anyway, for now, if option 2 doesn't work, i'll leave it for later and I'll try tackling later.

Thank you so much for your prompt response and with all my nagging, you're still able to tolerate me!

LVL 22

Expert Comment

ID: 16894180

one correction, although you would've figure it out yourself,
    If cur.flagProcessed(0) <> "" Then

    If cur.flagProcessed <> "" Then


Author Comment

ID: 16894267
Thanks Marko, you're a great help.
I'll be looking at this first thing in the morning and my boss will be very happy thanks to you. And the best bit about this is that I understand what's going so for similar issues I can tackle it on my own.

One small thing though, hope this doesn't bother you:

The form that has the field with the testdates is the product form and the test dates are only testdates, not already created documents. I know that I can create the view with testdates(which multivalue date field) column set to display (show muliple values as separate entries) in that view. Will that still work?
I am presuming that the DueDate set below is the testdate field which has all the test dates for that specific product. Is this correct?

Dat1 := @TextToDate( "Today" );
Dat2 := @Adjust( @TextToDate( "Today" ); 0; 0; 30; 0; 0; 0 );

SELECT Form = "Test" & DueDate > Dat1 & DueDate < Dat2

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now