FTSearch involving date fields is confusing me

Question

I have Custom Control with a search screen that lets the users select any of up to six different fields to search on. I had no trouble getting all the other fields working with the exception of the two date fields. They can fill in both begin and end dates or just one or the other. Pretty standard stuff but I cannot figure out how to write the code to make the query work and have it do the search when it involves dates.

var tmpArray = new Array("");
var cTerms = 0;
if(requestScope.cmbSendTo != null & requestScope.cmbSendTo != "") {
    a = @Right(requestScope.cmbSendTo, "(");
    b = @Left(a,3);
tmpArray[cTerms++] = "(FIELD Mnemonic = \"" + b + "\")";
}
if(requestScope.edtFrom != & requestScope.edtFrom != "") {
tmpArray[cTerms++] = "(FIELD From = \"" + requestScope.edtFrom + "\")";
}
//**************************************************************************
if(requestScope.edtDateRangeFrom != null & requestScope.edtDateRangeFrom != "") {
tmpArray[cTerms++] = "(FIELD DeliveredDate >= \"" + requestScope.edtDateRangeFrom + "\")";
}
if(requestScope.edtDateRangeTo != null & requestScope.edtDateRangeTo != "") {
tmpArray[cTerms++] = "(FIELD DeliveredDate <= \"" + requestScope.edtDateRangeTo + "\")";
}
//**************************************************************************
if(requestScope.edtOriginal != null & requestScope.edtOriginal != "") {
tmpArray[cTerms++] = "(FIELD SourceFilename = \"" + requestScope.edtOriginal + "\")";
}
if(requestScope.edtCaptiva != null & requestScope.edtCaptiva != "") {
tmpArray[cTerms++] = "(FIELD Filename = \"" + requestScope.edtCaptiva + "\")";
}
qstring = tmpArray.join(" AND ").trim();
requestScope.queryString = qstring;
return qstring

Any assistance would be appreciated

The idea behind this screen was taken from this video: XPages View Control - Add Full Text Search - http://www-10.lotus.com/ldd/ddwiki.nsf/dx/XPagesViewControlAddFullTextSearch.htm

score 1 · Answer 1 · answered Apr 28 '12 at 17:56

1

If I'm reading it right, your query is resolving to

FIELD DeliveredDate >= "xx/yy/zz"

My first instinct was that you needed this instead:

FIELD DeliveredDate >= [xx/yy/zz]

But documentation indicates that you don't need brackets or quotes, so this:

FIELD DeliveredDate >= xx/yy/zz

answered Apr 28 '12 at 17:56

Richard Schwartz

14,463
2
23
41

You have it exactly. That is what I "want" the query to resolve to. I even removed the quotes and got the same error as before. – RoyRumaner Apr 28 '12 at 18:32

Per Henrik Lausten · Accepted Answer · 2012-04-30T15:30:22.430

The line if(requestScope.edtFrom != & requestScope.edtFrom != "") { is not complete. You miss the part to test for. I assume it lacks the null check and therefore should be:

if(requestScope.edtFrom != null & requestScope.edtFrom != "") {

Furthermore, you need to format the date to return what you expect for the query (e.g. MM/dd/yyyy). The formatting in the inputText control only applies to the visual formatting and not the format of the actual content.

Finally, you need to remove the quotes around the date.

The following code example based on your code will return the date without formatting and then return the date with the correct formatting:

<xp:button value="Label" id="button1">
    <xp:eventHandler event="onclick" submit="true" refreshMode="partial" refreshId="computedField1"></xp:eventHandler>
</xp:button>

<xp:inputText id="edtDateRangeFrom" value="#{requestScope.edtDateRangeFrom}">
    <xp:this.converter>
        <xp:convertDateTime type="date"></xp:convertDateTime>
    </xp:this.converter>
    <xp:dateTimeHelper></xp:dateTimeHelper>
</xp:inputText>

<xp:text escape="true" id="computedField1">
    <xp:this.value><![CDATA[#{javascript:var tmpArray = new Array("");
    var cTerms = 0;
    if(requestScope.edtDateRangeFrom != null & requestScope.edtDateRangeFrom != "") {
        tmpArray[cTerms++] = "(FIELD DeliveredDate >= \"" + requestScope.edtDateRangeFrom + "\")";
        var dateFormatter = new java.text.SimpleDateFormat( "MM/dd/yyyy" );
        var formattedDate = dateFormatter.format( requestScope.edtDateRangeFrom );
        tmpArray[cTerms++] = "(FIELD DeliveredDate >= " + formattedDate + ")";
    }

    qstring = tmpArray.join(" AND ").trim();
    requestScope.queryString = qstring;

    return qstring}]]>
    </xp:this.value>
</xp:text>

It will return the following where the 2nd part is the format you are looking for:

(FIELD DeliveredDate >= "Fri Apr 27 12:00:00 CEST 2012")
AND (FIELD DeliveredDate >= 04/27/2012)

Here is your code with all these updates:

var tmpArray = new Array("");
var cTerms = 0;
var dateFormatter = new java.text.SimpleDateFormat( "MM/dd/yyyy" );
var formattedDate = "";
if(requestScope.cmbSendTo != null & requestScope.cmbSendTo != "") {
    a = @Right(requestScope.cmbSendTo, "(");
    b = @Left(a,3);
tmpArray[cTerms++] = "(FIELD Mnemonic = \"" + b + "\")";
}
if(requestScope.edtFrom != null & requestScope.edtFrom != "") {
tmpArray[cTerms++] = "(FIELD From = \"" + requestScope.edtFrom + "\")";
}

if(requestScope.edtDateRangeFrom != null & requestScope.edtDateRangeFrom != "") {
    formattedDate = dateFormatter.format( requestScope.edtDateRangeFrom );
    tmpArray[cTerms++] = "(FIELD DeliveredDate >= " + formattedDate + ")";
} 
if(requestScope.edtDateRangeTo != null & requestScope.edtDateRangeTo != "") {
    formattedDate = dateFormatter.format( requestScope.edtDateRangeTo );
    tmpArray[cTerms++] = "(FIELD DeliveredDate <= " + formattedDate + ")";
}


if(requestScope.edtOriginal != null & requestScope.edtOriginal != "") {
tmpArray[cTerms++] = "(FIELD SourceFilename = \"" + requestScope.edtOriginal + "\")";
}
if(requestScope.edtCaptiva != null & requestScope.edtCaptiva != "") {
tmpArray[cTerms++] = "(FIELD Filename = \"" + requestScope.edtCaptiva + "\")";
}
qstring = tmpArray.join(" AND ").trim();
requestScope.queryString = qstring; // this just displays the query
return qstring // this is what sets the search property

Per, the missing null was just a result of a bad copy. I like what you did and added the 2nd part to my code and still it is resulting in Query not understandable. I have a feeling it has a lot to do with the format of the dates on the form and in the view it is using for it's search. — RoyRumaner, Apr 28 '12 at 20:58
We need to see the full query :-) Try adding the query to a sessionScope var too and then print that value to e.g. a computed field — Per Henrik Lausten, Apr 28 '12 at 21:32
I really wish we had a debugger for JS. I have the query as a requestScope and show it as the view title. It breaks prior to that getting set so nothing is displayed. — RoyRumaner, Apr 28 '12 at 22:13
When it shows as view title, you could show it in a computed field too. Comment out your execution of the query and show us just the query string. Other than US all dates are either ascending or descending (yyyy-mm-dd, dd-mm-yyyy) so that could be source of trouble. Extra quotes or the lack of too. So if you get 'query is not understandable' your code executes until the query. So comment that line out and show the value. Also you can add the --console parameter to rparams when starting Domino designer - you will have a local console for local preview then — stwissel, Apr 29 '12 at 05:32
Roy, try adding my debug toolbar from OpenNTF to your application. You can then use dBar.debug("msg") calls to print debug statements to a message area on screen. — Mark Leusink, Apr 29 '12 at 07:34
Roy, I have tested using your code from the question comnbined with my answer and I am able to produce the following qsstring: (FIELD Mnemonic = "tes") AND (FIELD From = "From test") AND (FIELD DeliveredDate >= "04/16/2012") AND (FIELD DeliveredDate <= "04/16/2012") AND (FIELD SourceFilename = "original test") AND (FIELD Filename = "captiva test") — Per Henrik Lausten, Apr 29 '12 at 15:11
Per, it worked perfectly. Thank you for all the help. Hopefully the next person that runs into this problem will see this and it will help them as well. — RoyRumaner, Apr 30 '12 at 15:36
Just a remark: you don't need cTerms if you use tmpArray.push() — D.Bugger, May 01 '12 at 10:21
Are you saying the syntax could be tmpArray.push() = "(FIELD From = \"" + requestScope.edtFrom + "\")"; — RoyRumaner, May 02 '12 at 15:58

score 0 · Answer 3 · answered Apr 28 '12 at 15:54

0

Double check the query being created here. Perhaps print it to the screen or grab it from the debugger. You may see a problem there within the query and I believe you should be able to take that exact query and paste it in the search window of the database that has been full text indexed.

Also, have a look at this doc which covers notes query syntax, it may help you troubleshoot. I didn't see anything wrong in your code though.

http://www.loganmachinists.com/help/help8_client.nsf/f4b82fbb75e942a6852566ac0037f284/0e044d2c0639c979852572fe00687f29?OpenDocument

answered Apr 28 '12 at 15:54

Ken Pespisa

21,989
3
55
63

I think you missed my point Ken. The query works for the four text specific options. It does not work for the two date options. – RoyRumaner Apr 28 '12 at 16:42
Right. What does the query look like when it includes the date options? Can you provide an example? – Ken Pespisa Apr 28 '12 at 17:03
it returns "query is not understandable" – RoyRumaner Apr 28 '12 at 17:36
Roy, what does the query with dates look like? What date format are you using? – Per Henrik Lausten Apr 28 '12 at 17:46
It fails as soon as I submit so I cannot get the result of the query other than it is not understandable. I am using the date picker to select the date. The dates are formatted as mm/dd/yy on all related fields. This is the date field in the search screen – RoyRumaner Apr 28 '12 at 17:50
Roy, add a sessionScope.queryString = qstring; and then look at that sessionScope variabe afterwards to see if the query makes sense. Let us know how it looks like – Per Henrik Lausten Apr 28 '12 at 18:30
Even using the debug toolbar from OpenNTF, it just does not get that far. It breks prior to setting any scope variables on the date option – RoyRumaner Apr 28 '12 at 22:37
It breaks prior to setting any scope variables on the date option and the sessionScope.queryString = tmpArray that I set immediately after the line that sets it returns [0] ?? – RoyRumaner Apr 28 '12 at 22:45

score 0 · Answer 4 · answered Apr 28 '12 at 17:52

0

dates should always ( in my experience ) be written in the format mm/dd/yyyy so for instance

[deliverdatemin] >= 1/1/2012 and [deliverdatemax] <= 1/30/2012

An easy way to find out which query you are generating is to use the following piece of code to throw an error with the query generated

//youre own code  
throw new java.lang.exception(queryvariable);

Or you could simply do a print() to display the query on the serverconsole

answered Apr 28 '12 at 17:52

jjtbsomhorst

1,667
11
28

Unfortunately I have no access to the server console and your code produced the following error Error while executing JavaScript computed expression Script interpreter error, line=26, col=21: [ReferenceError] 'java' not found 25: requestScope.queryString = qstring; // this just displays the query 26: throw new java.lang.exception(qstring); 27: 28: return qstring // this is what sets the search property – RoyRumaner Apr 28 '12 at 18:05
1

@rrumaner: "Unfortunately I have no access to the server console..." always have some way for debugging at hand for production environment. perfectly tested and working application may have problems because of data or some environment properties. when I was in trouble with FT, every FT query got logged into OpenLog. If not an option for you, save it to dummy document in your database (clean up regularly). – Frantisek Kossuth Apr 29 '12 at 14:22
Frantisek, I have a complete Dev, QA, Prod environment here. I do not have access to the server console because the servers are in another state and as a consultant I am not authorized to access them remotely. I am using plenty of debugging tools including the DebugToolbar from Mark Leusink on OpenNTF. I am also using the Logger code to write a to a log file for (almost) every step in the process. These applications do not get to Prod until they have been through at least a month of QA which simulates a true production environment. I think I have it handled, don't you? – RoyRumaner May 02 '12 at 15:28

score 0 · Answer 5 · answered May 02 '12 at 08:14

As of my concern The best way to handle the date field is that converting our date value for one specific format using NotesDateTime., Because this is the best date conversion for xpage.

Dim dateTime As New NotesDateTime( "date String" )

or

Dim dateTime As New NotesDateTime( NotedateTime.getDtaeOnly() )

FTSearch involving date fields is confusing me

5 Answers5

Linked