Lotus Notes: Agents - Error "Query is not understandable"

itsmevic
itsmevic used Ask the Experts™
on
Hello,

    I have a lotus Notes 8.5 agent that I right click on and choose to run, however it's returning an error window that says "Query is not understandable"  Can one of you experts take a look at it and let me know why it's telling me that?

    Basically, what this script is suppose to do is go out and search the subject line for key words.  Presently, it's coded to search just "UPPERCASE" however, this isn't always the case ase those keywords are sometimes in "lowercase" as well.  Inevitably, for those emails it finds with these keywords in the subject line I'd like to redirect those emails to a folder called TestSearch....is any of this possible ( - :  

Summary of Request:
*************************
- Find out why it's giving me that error code.

- Add code that will re-direct what it finds to a folder of my choosing.  i.e. "TestSearch" folder.

- Search subject line and body of email for keywords that are both in lowercase and
  UPPERCASE.

A million thanks!  I know it's a very difficult question but wanted to try and ask it anyway....you never know.  ( - :
Option Declare
Dim s As NotesSession
Dim db As NotesDatabase
Dim c As NotesDocumentCollection
Sub Initialize
	Set s = New NotesSession
	Set db = s.CurrentDatabase
	Set c = db.UnprocessedDocuments 'not Search
	
	Call c.RemoveAll( True )
({@Contains(@upperCase(Subject); "REQUEST""END""START""BEGIN""CANCELLED""NOTIFY")},nothing,0)
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'd use the fulltext index. Make sure that the db has a fulltext index. In order to comply with lowercase/uppercase (or any combination): make sure it is NOT set to case sensitive searches (=default).
I've had the best results when sentence- and paragraph breaks were included in the ft index (not default!) but in your case, this my not be neccessary.
I've added wildcharacters * before and after each keyword to make sure that the keywords are found in the middle of other words as well.

Sub Initialize
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim qry As String
	Dim coll As NotesDocumentCollection
	
	Set db = s.CurrentDatabase
	qry = {Field subject = "*Request*"} _
	+ { OR Field subject = "*End*"} _
	+ { OR Field subject = "*Start*"} _
	+ { OR Field subject = "*Begin*"} _
	+ { OR Field subject = "*Cancelled*"} _
	+ { OR Field subject = "*Notify*"} _
	+ { OR Field body = "*Request*"} _
	+ { OR Field body = "*End*"} _
	+ { OR Field body = "*Start*"} _
	+ { OR Field body = "*Begin*"} _
	+ { OR Field body = "*Cancelled*"} _
	+ { OR Field body = "*Notify*"}
	
	Set coll = db.FTSearch(qry, 0) ' requires fulltext index, not case sensitive
	
	Call coll.PutAllInFolder("FolderName", True)
End Sub

Open in new window

Sjef BosmanGroupware Consultant

Commented:
I'd use rules (either local or server-based).

If that's too 'easy', I'd use db.Search if the query has to be executed at most once per day. I'd search with a formula that contains @Matches or @Like.

@CRAK: ever tried this:

        qry = {Field subject = ("*Request*" | "*End*" | "*Start*" | "*Begin*" | "*Cancelled*" | "*Notify*")}

or

        qry = {Field subject contains (Request | End | Start | Begin | Cancelled | Notify)}

And AFAIK, double-quotes are meaningless in an FT-query, and the asterisk at the start probably won't work.
Haven't tried it.
I've used the other notation for numerous comples searches (nested ANDs and ORs) though, always including quotes (provided that the field is text). You may be right, but I like things to be clear. ;-)

A db.search is a good alternative, but like you said: if you don't need it too often (and possibly up to a certain db size).
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
Hey there CRAK,

    Thanks for the suggestion.  Went with your code, unfortunately when I right click on the agent and choose "Run" i'm getting this error:  "Object variable not set"  I'll click OK, then it displays a window labeled "Agent Log" which states:

Started running agent "TESTSEARCH" on 3/3/2010 12:54:28 PM
Running on all documents in database:  1264 total
Found 1264 documents(s) that match search criteria
Ran LotusScript code
Done running agent "TESTSEARCH" on 3/3/2010 12:54:49 PM

    I checked the "TESTSEARCH" folder that created however do not see these emails in there for some reason.  With the exception of the "Object variable not set" error it appears as if the code is running.

Sjef BosmanGroupware Consultant

Commented:
Is your database full-text indexed?

Author

Commented:
Hi there Sjef - That's a good question, how would one know this exactly?

Author

Commented:
I go to File>Properties>Choose Database in the drop-down at the top, then click on the "Design" tab.  I see the checkbox for "Include in multi-database indexing" is unchecked.  In the "Full Text" tab the settings are as such:

Update frequency (servers only) set to:  "Immediate"

Index settings:
*****************
Case sensitivity:             OFF
Index breaks:                 Words Only
Index Attachments:        OFF
Index Encrypted Fields:  ON

I assume this is what your asking?
Sjef BosmanGroupware Consultant

Commented:
Okay, pity...  ;-)

Did you try to run the code in the debugger?

Author

Commented:
In Notes, how can I get it into debug mode to run this code?
If the error comes from my code, it probably hasn't found any matching documents. In that case coll is empty and coll.count=0.
Please try this modification:

If Coll.Count>0 then
   Call coll.PutAllInFolder("FolderName", True)
End if
File > Tools > Debug Lotusscript (in R7) to enable/disable the debugger. You may also have a smarticon ready to do that!

Author

Commented:
Still getting that "Object variable not set" error when right clicking on agent and choosing "Run" in Agent view....  It's being stubborn with us it appears.  ( - :

Here are the Agent Properties

The Options section set to:
*******************************
"Private"
Everything else is unchecked.

The Runtime section set to:
********************************
Trigger On Event
In the drop-down, I've selected "Agent list selection"
Target I've selected "All documents in database"

When I click on the Key tab it's settings are:
Allow remote debugging - unchecked
Profile this agent - unchecked

Set runtime security level (1 = most secure)
1. do not allow restricted operations

Option Declare
Dim s As NotesSession
Dim db As NotesDatabase
Dim qry As String
Dim coll As NotesDocumentCollection
Sub Initialize
	Set db = s.CurrentDatabase
	qry = {Field subject = "*Request*"} _
	+ { OR Field subject = "*End*"} _
	+ { OR Field subject = "*Start*"} _
	+ { OR Field subject = "*Begin*"} _
	+ { OR Field subject = "*Cancelled*"} _
	+ { OR Field subject = "*Notify*"} _
	+ { OR Field body = "*Request*"} _
	+ { OR Field body = "*End*"} _
	+ { OR Field body = "*Start*"} _
	+ { OR Field body = "*Begin*"} _
	+ { OR Field body = "*Cancelled*"} _
	+ { OR Field body = "*Notify*"}
	
	Set coll = db.FTSearch(qry, 0) ' requires fulltext index, not case sensitive
	
	If Coll.Count>0 Then
		Call coll.PutAllInFolder("TESTSEARCH", True)
	End If
End Sub

Open in new window

Ah, that's it:

s Is now only declared, not initialised.

Before you assign db, add following line:

Set s = New NotesSession

Author

Commented:
Yep that was it CRAK it works fine now.  One question is it copying the emails it finds or moving the emails it finds, just curious?
Sjef BosmanGroupware Consultant

Commented:
Neither. It links the mails to the folder. A mail (document) can be in multiple folders.

Author

Commented:
Great input, thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial