In Paul Calhoun’s Jump Start presentation (JMP101) at IBM Connect, he was talking about using Apache POI for exporting data to Excel. I’d gotten interested when he spoke about this before, which led me to developing my own two-part posting on exporting data to Excel without having Excel (part 1, part 2). What intrigued me was his discussion of both selected documents and ad hoc queries of Notes views. My immediate thought was that I could enhance capabilty and avoid the creation of multiple new views by configuring new exports for the users via queries of our own. Once I have that working, I’ll look at allowing the users to select documents and then, perhaps, allow ad hoc queries entered by users. It’s not that I don’t want to provide the capability, I just want to be cautious about it.
In our implementation, I’ve got our development team configuring reports for the users in documents, with the end users having no real-time input into the contents of the report. The thing I’d most like to change is to move the document selection from the view selection formula into those configuration documents.
Writing a Query
The first hurdle that I have is that despite 20 years of Notes development experience, I’ve never written a full-text query. I don’t usually access databases as an end user, so it never really comes up for me. Way back in the day, when I was at Exxon-Mobil, there were some full-text indexes and queries, but as the most junior developer on the staff, I never needed to be involved in designing the interface for that or in explaining how it worked. So, here in 2014, I’ve got to learn the syntax so we can create some pre-configured queries for our users.
I found the search operators help document relatively easily. While I don’t know SQL queries either, I was hoping that it would use the same syntax so that learning this would be useful later, so that getting help from outside our community would be easy and so that people from outside (including my end users) would be able to use it easily as well. At first glance, I thought they were similar, with CAPITALIZED words and logical operators (AND, OR, etc), but the similarity ends very quickly. The Notes search query is not at all like SQL queries.
|
Operator
|
Description and examples
|
Where operator works
|
| CONTAINScontains= |
Requires that the field before it must contain the text after it. There should be spaces between ‘CONTAINS’ and words surrounding it.Example
[Projectname] CONTAINS top secret
Finds documents whose Projectname field contains the words ‘top secret.’ |
Notes view – Yes
Notes domain – Yes
Web view/domain – No |
| IS PRESENT |
Requires that the field before it must be non-blank. There should be spaces between ‘IS PRESENT’ and words surrounding it. This operator can be used to find all documents that do (or do not) contain a value in a particular field.Example
[Subject] IS PRESENT
Finds documents whose Subject field is non-blank. |
Notes view – Yes
Notes domain – Yes
Web view/domain – No |
| field fieldnameFIELD fieldname[fieldname] (use square brackets) |
Means ‘search this field.’ You must specify the field to search by using its field name.ExampleEach of the following are equivalent ways to find documents whose Projectname field contains the phrase top secret:
FIELD Projectname CONTAINS top secret
field Projectname CONTAINS top secret
[Projectname] CONTAINS top secret
Note Notes stores file attachments in the $FILE field. |
Notes view – Yes
Notes domain – No
Web view/domain – Yes, but in view searches only |
| ( ) [parentheses] |
Determines the order in which sections of your query are processed. A part of the query enclosed in parentheses will be processed before parts outside the parentheses.Example
(FIELD Projectname CONTAINS top secret OR FIELD Title CONTAINS tuna) AND (Field body CONTAINS cat OR Field revdate > 01/01/2004)
Return documents whose Projectname field contains the words ‘top secret’ or whose title field contains the word ‘tuna’; and either the body field contains the word cat or the revdate field contains a date greater than 01/01/2004. |
Notes view – Yes
Notes domain – No
Web view/domain – Yes |
| andAND& |
Finds documents containing all the conditions or words linked by AND.Example
cat AND dog AND fish
Finds documents containing all three of these words. |
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes except ‘&’ does not work |
| orOR|ACCRUE, (comma) |
Finds documents containing either of the conditions or words and returns them ranked by number of appearances in the document.Example
cat OR dog OR fish
Finds documents containing at least one of these words.
Note ACCRUE works slightly better than OR when sorting results by relevance. |
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes |
| NOTnot! |
Excludes documents containing the term that appears after the operator.ExamplesYou can put NOT between words:
- cat AND NOT dog
Finds documents containing the word cat, but not if they also contain the word dog.
- (cat OR dog) AND NOT (fish OR frogs)
Finds documents containing the word cat or dog, but not if they also contain the word fish or frog.
- (cat AND dog) AND NOT fish
Finds documents containing the words cat and dog, but not if they also contain the word fish.
You can put NOT before any field name:
- NOT [Projectname] CONTAINS top secret
Finds documents whose Projectname field does not contain the words ‘top secret.’
You cannot put NOT after the math symbols =, <, >, <=, or >=; and before a date or number:
- [date1] = NOT 12/25/2002
is not an appropriate query.
|
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes |
| ” “ |
Requires that only documents containing the exact phrase are returned. Placing double quotes around operators (like AND, OR, CONTAINS etc.) allows them to be read as normal words.Example
“rock and roll”
Finds documents containing the phrase rock and roll. |
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes |
| PARAGRAPHparagraph |
Finds documents in which the words surrounding PARAGRAPH are in the same paragraph, and ranks them by how close they are.Example
car PARAGRAPH wheels
Finds documents in which ‘car’ and ‘wheels’ appear in the same paragraph and ranks them by how close the words are within the paragraph. |
Notes view – Yes(If the application’s full-text index has been created with the “Index sentence and paragraph breaks” option selected. For more information, see Full-text index options.)
Notes domain – Yes
Web view/domain – No |
| SENTENCEsentence |
Finds documents in which the words surrounding SENTENCE are in the same sentence, and ranks them by how close they are.Example
car SENTENCE wheels
Finds documents in which ‘car’ and ‘wheels’ appear in the same sentence and ranks them by how close the words are within the sentence. |
Notes view – Yes(If the application’s full-text index has been created with the “Index sentence and paragraph breaks” option selected. For more information, see Full-text index options.)Notes domain – Yes
Web view/domain – No |
| ? |
A wildcard that can represent any single letter. It does not work with dates or numbers.Examples
?one
Finds documents containing bone, cone, done, gone (and any other four-letter words that end with ‘one’)
???ck
Finds documents containing stack, clock, stick, truck; rack, rick, rock |
Notes view -Yes
Notes domain – Yes
Web view/domain – Yes |
| * |
A wildcard that can represent any extension of letters. It does not work with dates or numbers.Examples
*one
Finds documents containing bone, cone, clone, crone, done, drone, gone, telephone (and any other words of any length that end with ‘one’).
Also,
*one*
Finds documents containing bone, cone, clone, lonely, phoned, stoned, pardoned. |
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes |
| TERMWEIGHTtermweight |
Gives importance, or “weight,” to search words. You can use any value from 0 through 65537 to assign weight.Example
TERMWEIGHT 25 photo or TERMWEIGHT 75 audio or TERMWEIGHT 50 video
Finds documents containing at least one of the words. ‘Audio’ is most important, ‘video’ is next, and ‘photo’ is least important. Notes ranks results accordingly. You need an AND or OR between first TERMWEIGHT and subsequent ones. |
Notes view – Yes
Notes domain – Yes
Web view/domain – No |
| EXACTCASEexactcase |
Search for the exact case of the word sepcified after the operator.Example
exactcase Apple
Finds documents containing ‘Apple,’ but not ‘APPLE’ or ‘apple.’ |
Notes view – Yes(If the application’s full-text index has been created with the “Enabled case sensitive searches” option selected. For more information, see Full-text index options.)Notes domain – NoWeb view/domain – No |
| = (equal)< (less than)> (greater than)<= (less than or equal)>= (greater than or equal) |
Search for numbers or dates in numeric or date fields only.Example
FIELD date1<12/25/98
Finds documents whose ‘date1’ field contains any date before 12/25/98. |
Notes view – Yes
Notes domain – Yes
Web view/domain – No |
| – (hyphen) |
Finds documents with the hyphenated word pair.Example
full-text
Finds documents containing “full-text.” |
Notes view – Yes
Notes domain – Yes
Web view/domain – Yes |
Intermission
What? An intermission? Yeah, well, I started this post DURING IBM Connect in January. When I got back and tried to implement using query strings in my sample database, I crashed the development server 4 times in a row. The server was still on 8.5.3 and my need for query strings was not high. Fortunately, I’m on vacation at the beach as I write this. Yes, during a week of gorgeous weather in the Outer Banks of North Carolina, I am reading Mastering XPages and adding an entry to my blog. Some people read trashy novels at the beach, but I follow the example of Montgomery Scott and read technical manuals. Ok, so back to the topic….
Coding the call
Passing the query string from our report configuration documents to our function really isn’t that hard. I worried a lot about those crashes, so I started coding defensively, though I didn’t go so far as to add a try-catch block into the code. That will likely be inserted in the next version (after all, I expect to return to this as I get more savvy with POI’s Excel tools).
All we have to change in our code is to pull both the query string and sort column name from the report configuration document and append those string values to our function call.
var queryString = doc.getItemValue("QueryString");
var sortColumn = doc.getItemValue("SortColumnName");
postValidationError(control,"queryString length: " + queryString.length );
if ( queryString.length == 0 ) {
createWorkbookStreamWithLabels(wbName,sheetName,colFields,dbName[0],viewName[0],labels,totalLabels,"","");
return;
}
if ( queryString == null ) {
postValidationError(control,"queryString null");
createWorkbookStreamWithLabels(wbName,sheetName,colFields,dbName[0],viewName[0],labels,totalLabels,"","");
} else {
if ( queryString [0] == "" ) {
createWorkbookStreamWithLabels(wbName,sheetName,colFields,dbName[0],viewName[0],labels,totalLabels,"","");
postValidationError(control,"queryString empty");
} else {
createWorkbookStreamWithLabels(wbName,sheetName,colFields,dbName[0],viewName[0],labels,totalLabels,queryString[0],sortColumn[0]);
};
}
Applying the QueryString
Now, all we have to do is insert the query into our Excel sheet creation function. It’s actually very simple, as FTSearch and FTSearchSorted both modify the view object. You can simply drop the code in anywhere after you get a handle to the view and before you start processing documents.
Interestingly, after you apply either FTSearch or FTSearchSorted, the view remains sorted in original view order ONLY if the database is not full-text indexed, otherwise, it is sorted by relevance. In FTSearchSorted, you can override either behavior by selecting one column to sort instead (ascending or descending, of course). FTSearchSorted also allows you to use either a single query or a vector of queries.
Both methods return an integer count of the number of documents found by the search, so, in my code below, I simply return if there are no documents found. Yes, it ought to be fancied up, but I want to get this code out, so functionality is all I’m after today (There is Provencal rose’ chilling in the refrigerator, after all.) Here’s our code:
//apply the queryString
postValidationError(control,"Entries: " + myview.getEntryCount());
if ( queryString != "" ) {
postValidationError(control,"Sort Column: " + sortColumn);
// max number of documents return is set as myview.getEntryCount() so that all view entries could be returned
// default sort order is ascending
var docCount = myview.FTSearchSorted(queryString,myview.getEntryCount(),sortColumn);
postValidationError(control,"Query: " + queryString);
postValidationError(control,"Query count: " + docCount);
if ( docCount == 0) return;
}
postValidationError(control,"Queried view entry count: " + myview.getEntryCount());
Syntax for FTSearchSorted
FTSearchSorted(query:string) : int
FTSearchSorted(query:string, maxdocs:int) : int
FTSearchSorted(query:string, maxdocs:int, column:string) : int
FTSearchSorted(query:string, maxdocs:int, column:string, ascending:boolean, exact:boolean, variants:boolean, fuzzy:boolean) : int
FTSearchSorted(query:string, maxdocs:int, column:int) : int
FTSearchSorted(query:string, maxdocs:int, column:int, ascending:boolean, exact:boolean, variants:boolean, fuzzy:boolean) : int
FTSearchSorted(query:java.util.Vector) : int
FTSearchSorted(query:java.util.Vector, maxdocs:int) : int
FTSearchSorted(query:java.util.Vector, maxdocs:int, column:string) : int
FTSearchSorted(query:java.util.Vector, maxdocs:int, column:string, ascending:boolean, exact:boolean, variants:boolean, fuzzy:boolean) : int
FTSearchSorted(query:java.util.Vector, maxdocs:int, column:int) : int
FTSearchSorted(query:java.util.Vector, maxdocs:int, column:int, ascending:boolean, exact:boolean, variants:boolean, fuzzy:boolean) : int
| Parameter |
Description |
| query |
The full-text query or the intersection of multiple queries. See below for the query syntax. |
| maxdocs |
The maximum number of documents you want returned from the search. If you want to receive all documents that match the query, specify 0. Defaults to 0. |
| column |
The name or 0-based index of a sorted column. A specification of NotesView.VIEW_FTSS_RELEVANCE_ORDER (512) returns results in relevance order while honoring the use of the extended flags for exact case, variants, and fuzzy search. |
| ascending |
Sorts column data in ascending order if true, descending order if false. Defaults to true. Ignored if NotesView.VIEW_FTSS_RELEVANCE_ORDER is in effect.
The availability of a column to be sorted in ascending or descending order is determined by “Click on column header to sort” on the Sorting tab of the column properties. The relevant options are Ascending, Descending, and Both. Trying to sort a column in an unsupported direction throws an exception.
|
| exact |
Applies exact case to the search if true. Defaults to false. |
| variants |
Returns word variants in the search results if true. Defaults to false. |
| fuzzy |
Returns misspelled words in the search results if true. Defaults to false. |
Epilogue
So, using the code above, you should be able to create report configurations that produce sorted Excel sheets to any device. My favorite is to create on on my Motorola Xoom tablet and show people data from our databases right there. I’ll keep working on this because I’m sure I will need to sort in multiple columns. I know that if I put each of the entries into an array list, I could use multi-value sorting from Java, so I might pursue that.
I’ve updated the file on dropbox.
Like this:
Like Loading...
Lost in Code 