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
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
  • 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?

Industry Leaders: 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!


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.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…

717 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