Posts Tagged With: Paul Calhoun

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.

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());

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.