Using a FTSearch on a date field

Question

In some SSJS I do a FTSearch to retrieve values that I then manipulate and feed to a repeat control. The query is pretty complex and I have it all working except this piece.

var qString = tmpArray[cTerms++] = "((FIELD [WFSCompletedDate] > " + dString + ") AND (FIELD WFSOrig CONTAINS \"" + user + "\"))";

and when the code runs it resolves to the below:

((FIELD [WFSCompletedDate] > 11/13/2013) AND (FIELD WFSOrig CONTAINS "Bill Fox/Workflo Systems"))

next I do:

var dc:NotesDocumentCollection = database.FTSearch(qString);
viewScope.put("vsCollCount",dc.getCount());

However, it never gets to the viewScope.put

In my overall query if I use all of it but comment out the line above the search works fine. The issue is that the code stops running at the FTSearch so dc.getCount is never executed. I have tried it with the [] and without them but the results are always the same. Where the field WFSCompletedDate is present in a document it contains a legitimate date, the field is never blank. Very confusing because in the larger query one part is: query] = "(!((FIELD WFSStatus = Draft) OR (FIELD WFSStatus = In Process) OR (FIELD WFSStatus = Approved) OR (FIELD WFSStatus = Denied))AND (FIELD WFSOrig CONTAINS \"" + user + "\") AND [_RevisionDate] > " + dString + " )"

and uses [_RevisionDate] and this does not cause a problem.

Thanks for the help

Check the sitesearch.ntf inside is all sorts of ftsearch magic and as far as I recall including date handling. Also just paste the string into notes and check what comes back — stwissel
Does it work if you omit tmpArray[cTerms++] = in your code line? — Knut Herrmann
Have you checked the xpages exception log in IBM_TECHNICAL_SUPPORT folder? — Panu Haaramo
I'm quite sure that this hasn't to do with your basic problem, but regarding FT syntax you EITHER use "FIELD fieldname ..." OR "[fieldname] ...". In other words: putting the field name into square brackets is an alternative to specifying a field — Lothar Mueller
I second Lothar's assessment. FIELD [WFSCompletedDate] > 11/13/2013 looks for an item with brackets in its name. FIELD WFSCompletedDate > 11/13/2013 or [WFSCompletedDate] > 11/13/2013 look for an item without brackets in its name. But Knut is on to something too: that double assignment syntax looks wrong... in the context of your larger code that we're not seeing, it might be "right", but if you want to make troubleshooting easier, steer clear of any code patterns that look wrong even when they're right. — Tim Tripcony

Lothar Mueller Lothar Mueller · Accepted Answer · 2013-12-16T11:10:26

Based on all the comments and re-comments this is my assumption:

The Notes client's advanced FTSearch dialog analyzes the item's datatype based on its design time definition, and not based on some values stored in it: we all know that we can put mostly anything into an item using backend classes, no matter what datatype the field was given at design time. An error usually won't occur befor the data are being combined with the form element.

So, to me it looks as if at design time the field's datatype had been set to Text while at runtime it somehow is reveiving date time values, most probably through (LotusScript?) backend codes.

Try to get your database and design elements into a form where you can perform a client based FT search fitting your needs. If that is working it most probably will also work as a code driven FT search.

Btw: if you build a query using "FIELD [WFSCompletedDate]" you're in fact looking for a field whos name contains the brackets, and as there's no such field on your forms you don't run into any errors, but of course you won't get any hits either.

EDIT: just found this one here: Query is not understandable - using field Fulltext search [Tags] = "foo" : be sure to read Thomas Adrian's own answer regarding multiple fields of the same name in his database!

Using a FTSearch on a date field

1 Answers