This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal



Oct 28, 2014, 3:26 PM
20 Posts

Search query for empty date field

  • Category: Other
  • Platform: Windows
  • Release: 9.0
  • Role: Developer
  • Tags: Query,Search
  • Replies: 4

An XPage contains a view panel showing data from a Domino view called "ByAcct."
Every document in ByAcct contains a Date/Time field called "Closed."
I want to use a search string to apply to the view results, depending on the value of a sessionScope variable  called "AcctsToDisplay."
If AcctsToDisplay is "Closed" I want to show only those documents where the field Closed has a value.
If AcctsToDisplay is "Open" I want to show documents where Closed has no value.
I have tried various search strings in the "Search in view results" property of the view panel, such as:
FIELD Closed IS PRESENT
NOT FIELD Closed  = ""
[Closed] is present
I must not be using the right syntax, because I get the error "Query is not understandable" in each case.  
What query string should I be using to filter the view results the way I want? 

Oct 28, 2014, 4:34 PM
33 Posts
Have you tried....

FIELD [Closed] IS PRESENT

 

Try it in the Notes Client to see if it gives you what you want.

Oct 28, 2014, 5:48 PM
20 Posts
Tried that...

Tried using this syntax and got a different error:

The field name used in the query cannot be found in the database.

The field Closed is in every document, and the database is full-text indexed.

Oct 30, 2014, 3:40 PM
453 Posts
The search hates blank dates

I ran into this with a date field called CompletedDate which I set when the document was completed. But was blank otherwise. Domino does not like Blank Dates. So what I did was on creating the document I set the default value of CompletedDate to @TextToTime(@Text(@Time(1970;1;1;0;0;0);"S2") + " GMT") in a computed field on the Notes Form. Probably should do it as part of the XPage but .... it works. Now if I search for CompletedDate > Jan 1 1970 I get only those documents with an actual completeddate and if I search for completionDate < Jan 2 1970 I get the uncompleted documents. Takes a bit of fooling around to get it to work but then it works fine. Another problem with blank dates is if you have two or more replica of the Db on different servers. The date can get set correctly on server1 but when the document replicates to server2 it will not be typed as a date field but as a text field - a real mess. 

Here is a link to my question on StackOverFlow and some answers:

http://stackoverflow.com/questions/20578448/using-a-ftsearch-on-a-date-field

 

Oct 30, 2014, 7:45 PM
20 Posts
Thanks

Thanks for the response.  Search queries using the IS PRESENT operator work on searches  with text fields but apparently not with date fields.  
I've come up with a work-around that also gives me the desired results.  I build separate Domino views for "Open" and "Closed" accounts, using view selection formulas:
 SELECT (Form="Accounts") & (!@IsNull(Closed))  for Closed and 
SELECT (Form="Accounts") & (@IsNull(Closed)) for Open
These formulas have no problem with empty date fields.
Then in the viewName property of the view panel, I select one or the other of these views, depending on the value of the sessionScope variable AcctsToDisplay.  (In addition, I have a third view displaying all documents, open and closed, for the case when AcctsToDisplay="All." )

 


This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal