xpages ftsearch documents from an interval of dates

Question

I made an xpage element for ftsearch using a tutorial from IBM

My request: is there possible having 2 date fields ( as input requirements for the search ) to find those documents having the creation date inside the interval consisting of those 2 dates?

Should I create a computed field dtCreated where I will store the creation date and then in the search property of the view I should add something like this:

      var tmpArray = new Array("");
        var cTerms = 0;
        if (sessionScope.searchDate1) && (sessionScope.searchDate2) {
        tmpArray[cTerms++] = "(Field dtCreated > \"" + sessionScope.searhcDate1 + "\")" && "(Field dtCreated < \"" + sessionScope.searhcDate2 + "\")";
        }
    ....
    ....

Or there is another alternative?

My 2 session variables are having Short (5/5/14) Date format. Also, my default value for those 2 dates (session variables) are "" but of course I add some values before clicking the Submit button.

Thanks for your time!

score 5 · Accepted Answer · answered May 15 '14 at 11:30

5

You can use the special field _creationDate as creation date (see answer from Tommy). Based on this the following example query will work in the Notes client:

Field _creationDate > 01-01-2014 AND Field _creationDate < 01-03-2014

In order to get this query to work with your specific code do this:

var tmpArray = new Array("");
var cTerms = 0;
var dateFormatter = new java.text.SimpleDateFormat( "dd-MM-yyyy" ); 
if (sessionScope.searchDate1) && (sessionScope.searchDate2) {
    tmpArray[cTerms++] = "Field _creationDate > " + dateFormatter.format(sessionScope.searchDate1) + " AND Field _creationDate < " + dateFormatter.format(sessionScope.searchDate2);
}

answered May 15 '14 at 11:30

Per Henrik Lausten

21,331
3
29
76

Now I don't get any error, but my search result are wrong, basically all the documents are shown in the view panel, even my 2 session variables are well defined with some values. Also, I tried java.text.SimpleDateFormat( "MM-dd-yyyy" ); but still the same issue. I modified the if syntax into ( ... && ... ) – Florin M. May 15 '14 at 11:38
1

Try the search in your Notes client in order to verify the date format (Field _creationDate > 01-01-2014 AND Field _creationDate < 01-03-2014) – Per Henrik Lausten May 15 '14 at 11:39
It works. I reopened my app in browser and modify just the format into ("MM-dd-yyyy"). Thank you for your help and time! – Florin M. May 15 '14 at 11:44

Tommy Valand · Answer 2 · 2014-05-14T05:04:28.550

0

If you want to do an FTSearch, _CreationDate can be used

Documentation (scroll to Searching for Header Information): http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/index.jsp?topic=%2Fcom.ibm.designer.domino.main.doc%2FH_CUSTOMIZING_SEARCH_FORMS_7304.html

E.g. (there might be typos):

tmpArray[cTerms++] = "(Field _creationDate>" + sessionScope.searhcDate1 + ") AND (Field _creationDate<" + sessionScope.searhcDate2 + ")";

Or my preferred syntax:

tmpArray[cTerms++] = "[_creationDate>" + sessionScope.searhcDate1 + "] AND [_creationDate<" + sessionScope.searhcDate2 + "]";

edited May 14 '14 at 05:04

answered May 13 '14 at 12:15

Tommy Valand

868
6
10

thanks for your suggestion. Unfortunately, I get an error/exception: Exception Notes error: Query is not understandable – Florin M. May 14 '14 at 11:16
Try to print the query in the log, or somewhere you can read it. For the query to work, both sessionScope.searchDate1 and sessionScope.searchDate2 has to have a value, and be in a format that Domino understands. – Tommy Valand May 14 '14 at 19:26

score 0 · Answer 3 · answered May 14 '14 at 21:01

To build on Tommy's answer:

Specifically, try "yyyy/m/d" as the format. If those values in sessionScope are java.util.Date (the type that date fields use), you could try something like this (typing off the top of my head, not in an app, so my apologies for typos):

var tmpArray = [];
var cTerms = 0;
if(sessionScope.searchDate1 && sessionScope.searchDate2) {
    var formatter = new java.text.SimpleDateFormat("yyyy/M/d");
    tmpArray[cTerms++] = "[_CreationDate] >= " + formatter.format(sessionScope.searchDate1) + " and [_CreaationDate] <= " + formatter.format(sessionScope.searchDate2)
}

What you want to end up with is a FT query like:

[_CreationDate] >= 2014/1/1 and [_CreationDate] <= 2014/2/1

Thanks for your tip. I edited my question, specifying the session variables type. Still not working for me — Florin M., May 15 '14 at 06:12

xpages ftsearch documents from an interval of dates

3 Answers3