CODEWITHSUNDEEP
lotus-noteslotusscript
Bob Cross
Bob Cross

Reputation: 37

Can someone help me with Exception occurred calling method NotesDatabase.FTSearch(string)

I'm trying to do a database full text search and struggling to get it to work. Since I'm working with date fields, I deleted my full text index, set all my blank date fields to a fictitious date (3/15/2050), compacted the db and then rebuilt the full text index.

However, I am still getting the error for this query string: (FIELD Form CONTAINS 'Opp') AND (FIELD Topic CONTAINS 'A') AND (FIELD DateTeam >= 3/1/2019) AND (FIELD DateTeam <= 3/31/2019)

I didn't have the single quotes in there initially, but tried it as a suggestion from another post. I also tried putting # before and after my dates, but no luck there either.

What am I missing? My customer is getting frustrated.

Upvotes: 0

Views: 1386

Answers (4)

Bob Cross
Bob Cross

Reputation: 37

The approach I was taking was correct. The problem, however, was that my update agent to set all the date fields had a typo in it so it wasn't setting the right fieldnames to dates. My oversight. Once I found that and corrected it, I retraced my steps and it all works as intended now. Thank you to all for helping me find the solution.

For anyone else, I took these steps: Open database properties and delete the full text index Run this command on the console to compact the db: lo compact folder/db.nsf -c Run an agent against all the docs to set the date fields to a fictitious date Open database properties again and create a new full text index When completed, run this command on the console: load updall -f folder/db.nsf Run the agent again to reset the fictitious dates to blanks

Upvotes: 0

D.Bugger
D.Bugger

Reputation: 2359

I tried this in my mail database:

(FIELD Form CONTAINS "Memo") AND (FIELD Subject CONTAINS "the") AND (FIELD PostedDate >= 3/1/2019) AND (FIELD PostedDate <= 30/6/2019)

And it works correctly. So double quotes instead of single, and you might have to check whether your date format corresponds with the server's.

Upvotes: 0

John Curtis
John Curtis

Reputation: 11

You didn't post the verbiage in the exception, nor say what Domino version you were using, so this is somewhat guesswork.

It could be that the UNK table (Domino's internal field catalog) thinks the field is a string field. The first value stored in a field is the type that the FT index uses to determine the indexed data type. Of course any type can be stored in the NSF data but the FT code tries to limit this type of search to fields it believes will work.

Now if those CONTAINS values are actually = values, then DQL can help you. We'll be putting CONTAINS as a verb into DQL in V11, but it (DQL) can certainly perform your date range term. Just a thought.

-John Curtis

Upvotes: 1

Oliver Busse
Oliver Busse

Reputation: 3395

This might help: https://www-10.lotus.com/ldd/dominowiki.nsf/dx/full-text-syntax As far as I know date values must be in [] to work. A good start is to test your formula in the client with the FT search function instead of altering your code.

By the way: you can shorten your formula omitting the FIELD keyword and putting the field name also in [].

Upvotes: 0

Related Questions