Server-Side Javascript

A venture into DateTimes in #XPages

Recently, I was trying to change the display of some dates in our application. We’ve found that the simplest way for our international company to display dates is to stick to the dd-MMM-yyyy format since that seems the clearest. No one wonders whether 1/4/16 represent the 4th of January or the 1st of April, because they all see 04-Jan-2016.

This was simplest to fix for all the date fields that use simple inputText controls – just change the convertDateTime pattern.

						<xp:inputText value="#{modDoc.RevPerf1Date}" id="revisedPerformanceDateStart">
							<xp:dateTimeHelper></xp:dateTimeHelper>
							<xp:this.converter>
								<xp:convertDateTime pattern="dd-MMM-yyyy"></xp:convertDateTime>
							</xp:this.converter>
						</xp:inputText>

Then, I noticed that in place I wasn’t letting them edit the dates, it wasn’t using that format. We’re not displaying the inputText, but using a label computed from that control to determine what to display. This code may actually hurt your eyes, but it did convert the date into a US-format date, like 01/04/2016.

<xp:label id="label14" style="color:black;">
	<xp:this.value><![CDATA[#{javascript:if(modDoc.isNewNote()){ 
		if(modDoc.getItemValueDate("PerfDate1") != null) {
			var termBegin = @Text(modDoc.getItemValue("PerfDate1")); 
			if(termBegin != ""){ 
				var dt2:NotesDateTime = session.createDateTime(termBegin); 
				var d = new Date(dt2.toJavaDate()); 
				var mon = ("0" + (d.getMonth() + 1)).slice(-2) 
				var td = ("0" + d.getDate()).slice(-2); 
				var yr = d.getFullYear(); 

				mon + "/" + td + "/" + yr 
			}
		} else { 
			if(sessionScope.POPerformBeginDate != null && sessionScope.POPerformBeginDate != "null" && sessionScope.POPerformBeginDate != ""){ 
				var dt:NotesDateTime = session.createDateTime(sessionScope.POPerformBeginDate); 
				dt.toJavaDate() modDoc.setValue("PerfDate1",dt); 
				var d = new Date(dt.toJavaDate()); 
				var mon = ("0" + (d.getMonth() + 1)).slice(-2) 
				var td = ("0" + d.getDate()).slice(-2); 
				var yr = d.getFullYear(); 

				mon + "/" + td + "/" + yr 
			} 
		} 
	} else { 
		var termBegin = @Text(modDoc.getItemValue("PerfDate1")); 
		if(termBegin != ""){ 
			var dt2:NotesDateTime = session.createDateTime(termBegin); 
			var d = new Date(dt2.toJavaDate()); 
			var mon = ("0" + (d.getMonth() + 1)).slice(-2) 
			var td = ("0" + d.getDate()).slice(-2); 
			var yr = d.getFullYear(); 

			mon + "/" + td + "/" + yr; 
		}
	}}]]></xp:this.value>
</xp:label>

Before we decided to convert to the new format, the ugliness of the code didn’t matter. It was used in one place (printing purchase order modifications) and it worked. Since I didn’t want to invent my own library function for computing the text value of the date in the new format, I searched for a better way to format the dates. I ran across Declan Lynch’s blog entry on using SimpleDateFormat. Unfortunately, that just points in the right direction, rather than providing working code. So, when I tried to implement that for displaying the labels correctly, I just couldn’t get it to work. This frustration led me to the simple solution: use convertDateTime on the labels. Duh!

						<xp:label id="performanceDateStartDisplay" style="color:black;">
							<xp:this.value><![CDATA[#{javascript:getComponent("performanceDateStart").getValue();}]]></xp:this.value>
							<xp:this.converter>
								<xp:convertDateTime pattern="dd-MMM-yyyy"></xp:convertDateTime>
							</xp:this.converter>
						</xp:label>

Now, on the printed purchase order modification, I also had changes in dates detailed in the text as a sentence. So, you’d see To Change the Period of performance from 01/04/2016 to 01/08/2106 to 01/11/2016 to 01/15/2016, which was not using our newly minted date format. I couldn’t figure out a way to use the converters within the text without creating several computed labels (each with a rendered formula) to display the text. Then, I remembered my dalliance with SimpleDateFormatter.

So, within that control, I brought in the package and created a function that gets the field value as a Vector using getItemValueDateTimeArray and formats it using my chosen SimpleDateFormat. The text string gets built with four calls to that function and returns our text To Change the Period of performance from 04-Jan-2016 to 08-Jan-2016 to 11-Jan-2016 to 15-Jan-2016

<xp:text id="revisedPerformanceRange">
	<xp:this.value><![CDATA[#{javascript:function getFormattedDate ( doc:NotesDocument, fieldName:String ) {
	importPackage(java.text);

	var dateFormatter:java.text.SimpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
	var d:Date = new Date(@Today());

	if ( doc.hasItem (fieldName) ) {
		var valueVector:java.util.Vector = doc.getItemValueDateTimeArray(fieldName);
		var iterator = valueVector.iterator();

		while (iterator.hasNext()) {
			var itemvalue = iterator.next();
			if ((typeof(itemvalue)).endsWith("DateTime")) {
				d = new Date(itemvalue.toJavaDate());
				return dateFormatter.format(d);
			}
		}
	} else {
		return fieldName + " is not on the document"
	}

}

var modNotesDoc:NotesDocument = modDoc.getDocument();

var revisedPerformanceRangeText = "To Change the Period of performance from ";
	
revisedPerformanceRangeText = revisedPerformanceRangeText + getFormattedDate(modNotesDoc,"PerfDate1") + " to ";
revisedPerformanceRangeText = revisedPerformanceRangeText + getFormattedDate(modNotesDoc,"PerfDate2") + " to ";
revisedPerformanceRangeText = revisedPerformanceRangeText + getFormattedDate(modNotesDoc,"RevPerf1Date") + " to ";
revisedPerformanceRangeText = revisedPerformanceRangeText + getFormattedDate(modNotesDoc,"RevPerf2Date");

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

Took some fiddling to figure it out, but gave me exactly what I wanted, two different ways.

Categories: Java, Server-Side Javascript, Xpages | Tags: , , , , , | 2 Comments

Writing and speaking about your code might actually make it better

I like to think I’m a smart guy, but I know there are many people out there who are smarter than I am. Sometimes, just thinking about Kathy and Julian showing us what we can do in Java in 10 Lines or Less helps one tighten up some code. Other times, sitting down to write about a code problem or a solution might actually make your code better.

Our workflow application builds a list of approvers at each step that’s built from three fields on the workflow step configuration document. That list is stored on the document being approved so that we don’t have to do a lookup to determine who can take action. Sometimes, the approvers might be in more than one of those fields and thus, with sloppy coding, end up in the approver list more than once. The workflow step configuration allows us to choose which of those three fields is used to notify approvers (it could even be all three fields). Since we recently decided to keep track of who is notified, since they are the primary approver(s), the workflow code is now recording the names of those notified as it sends the message. Because the system is already used in 15 projects and will be used in 70+ projects at time in the future, it must be a highly configurable workflow.

My coding challenge was simple. I wanted to display on the XPage the primary approver(s) and the other (proxy) approvers, as separate lists. This is probably a two-minute exercise for someone who knows what they’re doing, so it took me an hour.

Because I worried about the duplicates, I didn’t use the remove method of the java.util.Vector, which would only take out the first instance of the duplicate. I lamented this, since playing with arrays is a little more involved. Basically, I decided to build the array by looping through looking for matches. Then, I was puzzled about how to find array elements in a Vector, but I got over that when I realized that I could use @Unique to clean both and make them both arrays! OK, not the best idea, but it worked.

<xp:label id="recipientListLabel">
	<xp:this.value><![CDATA[#{javascript:
var recipients=procureDoc.getDocument().getItemValue("RecipientList");
return "Approvers: " + @Unique(@Name("[CN]",recipients)); }]]></xp:this.value>
</xp:label>
<xp:label id="proxyListLabel">
	<xp:this.value><![CDATA[#{javascript:
var recipients = @Unique ( procureDoc.getDocument().getItemValue("RecipientList") );
var allApprovers = @Unique ( procureDoc.getDocument().getItemValue("TSWFCurApprovers") ); 
ar proxies = [];
for ( approver in allApprovers ) { 
	if ( @IsNotMember( allApprovers[approver], recipients) ) { 
		proxies.push ( allApprovers[approver] ); 
	}
} 
var proxyList = @Implode(@Name("[CN]",proxies),", "); 
return "Proxy Approvers: " + proxyList; }]]></xp:this.value>
</xp:label>

I was pretty happy with that, because I thought it would take me a lot of extensive looping and nonsense (since a quick search hadn’t revealed an array minus array code snippet.

But, as I sat down to write about this and see if someone could better it (assuming that it would take someone two minutes), I returned to the documentation. I just wanted to double-check that remove was a “method” and that I wasn’t being foolish and calling it a “function” instead. So, then, I saw it. The code simplifier. removeAll was exactly what I was looking for and, I’d bet, something every Java coder worth their salt could have slapped on the problem in an instant. Learning curve.

<xp:label id="proxyListLabel">
	<xp:this.value><![CDATA[#{javascript:
var recipients:java.util.Vector = procureDoc.getDocument().getItemValue("RecipientList");
var allApprovers:java.util.Vector = procureDoc.getDocument().getItemValue("TSWFCurApprovers");
allApprovers.removeAll(recipients); 

var proxyList = @Implode(@Name("[CN]",allApprovers),", ");
return "Proxy Approvers: " + proxyList; }]]></xp:this.value>
</xp:label>

It’s always good to learn. When I was a Scoutmaster, one of the things I told the older Scouts was that by teaching skills, they ended up learning them even better themselves. So, as I sat down to write this, more to share the experience than to teach, I ended up learning more. It forced me to research a little, to make sure I was covering my bases and allowed me to cut some inelegant code from 8 lines to 5 (though I imagine the daring would simply cut it to 2 lines).

So, next time you or your boss thinks there’s no time for writing up your thoughts on coding, or no time to speak at conferences, or no time to share your ideas at the local user group meeting, remember that you’re likely to end up with better code even if you’re the only one who contributes. The process forces it on you.

Categories: Java, Server-Side Javascript, Xpages | Tags: , , , , , | 2 Comments

Syntax errors will be the death of us all in #XPages

I upgraded one of our databases overseas and was puzzled when they reported that they suddenly could no longer see the attachments to their purchase orders. Actually, when I heard it, it was just for a single purchase order.

You see, we’ve got our attachments all stored in a separate database for each project. This is nice because it reduces the risk of truncations, moves the big data out of the main database and generally calms down the Notes admin team. However, it does require that there is a solid link between the main document and any attachment documents. Our main link is a transactionKey field, which identifies not only which main document the attachment belongs to, but also where that attachment is displayed on the main document’s XPage.

I had a custom control that handled one of those display areas as a tab in a tabbed panel. We had decided to add a new document type (release orders, to go with purchase orders), so I wanted my custom control to be more generic, to handle multiple document types. There are a few values that were based on the document type. Rather than passing 4 or 5 properties, I decided to pass in 1 and compute the others. Brilliant, except, I forgot how Javascript works.

My “brilliant” code….

<xp:scriptBlock id="scriptBlock1">
	<xp:this.value><![CDATA[#{javascript:switch ( compositeData.mainDocumentType ) {
case "Purchase Order":
	viewScope.signedDocumentTypes = "Purchase Order or subcontract";
	viewScope.transactionType = "PurchaseRecord";
	viewScope.transactionTag = "po";
	viewScope.additionalDirections = " and all invoices and goods delivery receipts";
	break;
case "Release Order":
	viewScope.signedDocumentTypes = "Release Order";
	viewScope.transactionType = "ReleaseOrder";
	viewScope.transactionTag = "rel";
	viewScope.additionalDirections = "";
	break;
default:
	// thus far, BPA only, but could be any mainDocumentType with no spaces in the name
	viewScope.signedDocumentTypes = compositeData.mainDocumentType;
	viewScope.transactionType = compositeData.mainDocumentType;
	viewScope.transactionTag = @LowerCase(compositeData.mainDocumentType);
	viewScope.additionalDirections = "";
};}]]></xp:this.value>
</xp:scriptBlock>

So, every Purchase Order attachment got the signedDocumentTypes ‘Purchase Order’, the type ‘Purchase Order’, the tag ‘purchase order’ and no additional directions. Only took about 4 hours of looking at data, chasing the various design changes and reading code before I looked at that piece of code and realized my obvious mistake.

Sometimes, you just need a break;

Categories: Server-Side Javascript, Xpages | Tags: , | Leave a comment

Are you sure? Asking for confirmation in #XPages

Often, we want to confirm with the user that they actually want to save or submit a document in XPages. I thought it would be very simple to customize the server-side simple action ‘confirm’ to include client-side data that the user had just entered, but that was not yet saved to disk.

So, I had what I thought was some simple and straight-forward SSJS:

<xp:confirm>
    <xp:this.message><![CDATA[#{javascript:var baseText = "Are you sure that you want to set the exchange rate for ";
        var effectiveDate = getComponent("effectiveDate").getValue().toString();
        var localCurrency = getComponent("localCurrency").getValue();
        var exchangeRate = getComponent("exchangeRate").getValue();
        return baseText + localCurrency + " to " + exchangeRate + " as of " + effectiveDate + "?"; }]]>
    </xp:this.message>
</xp:confirm>

I posted my question on StackOverflow and Paul Withers pointed out that I wasn’t going to get what I was looking for….

You’re computing SSJS to pass to a CSJS confirm() message. I would expect it to display values at the last refresh, not values just entered by the user. If you want the latest values, I think you’ll need to access them via CSJS.

So, I made the classic mistake of failing to know whether I and my data were client-side or server-side. Thus, my getComponent commands were getting a handle to the last version of the server-side component, not what the user just entered on the client-side. So, I needed to move back to the client-side to display client-side values. Fortunately, I remembered that if your client-side javascript evaluates to false, the server-side script never executes.

<xp:eventHandler event="onclick" submit="true" refreshMode="complete"
        immediate="false" save="true" id="eventHandler3">
        <xp:this.script><![CDATA[var baseText = "Are you sure that you want to set the exchange rate for ";
            var effectiveDate = document.getElementById("#{id:effectiveDate}").value;
            var localCurrency = document.getElementById("#{id:localCurrency}").value;
            var exchangeRate = document.getElementById("#{id:exchangeRate}").value;
            return window.confirm (baseText + localCurrency + " to " + exchangeRate + " as of " + effectiveDate + "?");]]>
        </xp:this.script>
        <xp:this.action><![CDATA[#{javascript:exchangeRateDoc.save();
    context.redirectToPage("/pro_exchangeRate_view.xsp")}]]>
        </xp:this.action>
</xp:eventHandler>

The key to the client-side javascript is to make sure you return the value of that window.confirm at the end. In my initial attempt, I didn’t return the value and my testers pointed out to me that my ‘Are you sure?’ was just taunting my users. It would ask the question, but it ignored the response. Clicking OK would save it, as intended, but clicking Cancel would ALSO save it! Talk about ignoring user input!

Hopefully, my mistake will prove instructive in your attempts to find your way in XPages…..

Categories: Client-Side Javascript, Server-Side Javascript | Tags: , , , , | 2 Comments

Exception avoided in FTSearchSorted in #XPages

Regular readers will remember that I’ve been playing around with using FTSearchSorted in order to create Excel spreadsheets from Notes databases in XPages. I’d had that in my sample database for a while, but hadn’t used it our production environment yet. I worried that my experience with crashes was going to be repeated and that it would take forever to integrate it into the design. Fortunately, I got over my concern about crashes (that’s what an admin team is for, right?) since my coding and the versioning all make it seem stable. The integration of the changes (modify one form, one button on a custom control and a few lines in a script library) was relatively quick. So, in the spirit of the 442nd, I decided to “Go For Broke”.

Having pushed the changes to my development environment, I set up a new report with a querystring and a sort column. My brilliant code would soon return a report of all contacts with their city = “Bethesda”, sorted by… Position since that was a column in the view and I was being random. It didn’t work. I played around with the querystring and finally decided to use FTSearch without using sort or my sort column. That worked. So, I tried FTSearchSorted, with the column name in parentheses, so it would definitely be string. Then, a number for the column instead. Then, I decided, I wouldn’t provide a sort column at all. That worked. So it wasn’t the method and it wasn’t the values I was passing to it. It just returned a silly null object every time.

So, I turned to Stack Overflow. Sure enough, someone else had the problem. The answer provided (too short in the Stack Overflow admins’ minds) was from Thomas Adrian, “make sure the view is user sortable”. Aha! I went back to my form, on which I had placed useful help text that I didn’t bother to read:

A column may only be used for sorting if it has been designed to allow “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.

I even bolded the important part of the text on my form. I hadn’t read it when I picked my sort column, so it threw an exception every time I used one of those unsorted columns.

Since I’ve demonstrated that even I won’t read my help text, I’ve changed the code on the button that allows you to select which column to use as your sort column so that you can’t choose one that isn’t sorted either ascending or descending. It will only list choices that are click to sort one way or the other (or both).

Forall columns In columnArray
	If ( columns.Isresortascending Or columns.Isresortdescending ) Then
		' add each view name to an array if it is eligible for click to sort
		Redim Preserve headerArray ( count )
		headerArray ( count ) = columns.ItemName
		Print headerArray ( count )
		count = count + 1
	End If
End Forall

I’ve added a clearer explanation to Thomas’ answer, but that short sentence was all the answer I needed. Once my expansion of his answer clears the editors, it will look nicer up there, but since you’ve already read this blog post, you won’t need it, eh?

I’ve updated the sample database, so you can download it to see the rest of how it works if the code sample is not enough. I’ll be at MWLUG starting tomorrow afternoon, so make sure to say “Hello”!

Categories: Server-Side Javascript, Xpages | Tags: , , , , , , , | Leave a comment

Simple dialog returning a value to your #XPages

Since I’m still new to XPages, I’m always finding things that are a challenge. Most of the time, I don’t know how big a challenge they are because doing things in XPages isn’t the same as doing them in Old Notes.

Business case

On the payment request in our procurement module, the user needs to enter the exchange rate between the local currency and the base currency for approval routing. Sometimes, the payment has already been made in local currency while the exchange rate has changed. They might only know the amounts in the two currencies, but didn’t record the exchange rate at the time.

PaymentRequest

Solution

In discussions, I suggested that it might not be difficult to simply have a popup that allowed the user to enter the amounts and return the value to the payment request in the UI.

Surprisingly to me, I was right. It’s not that hard. My challenge was that I decide to put this exchange rate computer into a custom control so that I’d be able to re-use it. That meant having to figure out how to have the dialog do a partial refresh on the payment request, in order to recompute the total amount in the base currency (USD).

Existing design considerations

Our procurement module was originally designed by the brilliant minds over at Teamwork Solutions, led by Scott Good. It’s very nice, but this wasn’t included in the original requirements gathering. (What percentage of actual requirements do get into the original requirements gathering?)

Each of those line items displayed in the payment request is a separate Notes document, so, in Old Notes terms, this would be like an embedded view, but since we’re in XPages, it’s a repeat. (I am learning to love repeats!) The total payable amounts are ‘computed fields’ in XPages, which is like a computed for display field in Old Notes. As such, they wouldn’t actually save anything to the payment request document. So, we have some non-displayed computed fields with some server-side Javsacript (SSJS) that aggregates all the local amounts from those view entries and puts it into the control on the XPage. As such, when the exchange rate changes, there is a partial refresh.

So, on our exchange rate control, we had a partial refresh for onchange. Simple control, bound to a field on our payment request document.

<xp:inputText id="exchangeRate" value="#{payDoc.Exchange_Rate}" style="text-align:right;width:70.0px;">
	<xp:this.converter>
		<xp:convertNumber type="number"></xp:convertNumber>
	</xp:this.converter>
	<xp:eventHandler event="onchange" submit="true" refreshMode="partial" refreshId="paymentCostInfoPanel">
	</xp:eventHandler>
</xp:inputText>

Design

So, I created my exchangeRateComputer custom control. I decided to make the link part of the custom control so that implementing it on any other XPage would require the least work possible.  I decided to always set the control on the XPage to be named “exchangeRate”, so I don’t have to pass a string with the control name. As an added bonus, I wanted to fill out the local currency field for the user and just let them enter the final base currency amount they’d like to see.

ExchangeRateCalculator

I ran into one problem. I couldn’t get it to do the partial refresh from the exchangeRateComputer custom control. I’d tried some XSP.partialRefreshGet commands but was having problems and realized…. I might want the name of the element to be refreshed to be different on different XPages. Since passing the compositeData value into the XSP.getElementById seemed beyond my capabilities, I decided to simply defer all refreshes to the exchangeRate control itself. So, I ended up adding an onblur partial refresh. When the user clicks on the ‘Apply to payment request button’, it puts focus onto the exchangeRate control, then, closes the dialog. Closing the dialog blurs focus from the exchangeRate control and…. with an added onblur event, performs a partial refresh for me. (The onblur event is identical to the onchange, except for the name.)

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core" xmlns:xe="http://www.ibm.com/xsp/coreex">
	<xp:link escape="true" text="Compute Exchange Rate" id="computeLink" 
		style="margin-left:5px;">
		<xp:eventHandler event="onclick" submit="false">
			<xp:this.script><![CDATA[XSP.openDialog('#{id:computeDialog}');]]></xp:this.script>
		</xp:eventHandler>
	</xp:link>

	<xe:dialog id="computeDialog" title="Exchange rate calculator">
		<xp:table>
			<xp:tr>
				<xp:td>
				</xp:td>
				<xp:td>
					<xp:label id="instructions" value="Enter the amounts below to calculate an exchange rate">
					</xp:label>
				</xp:td>
			</xp:tr>
			<xp:tr>
				<xp:td style="width:150px;text-align:right;">
					<xp:label id="total_baseCurrencyLabel" value="Total Base Currency">
					</xp:label>
				</xp:td>
				<xp:td>
					<xp:inputText id="total_baseCurrency" style="width:100px;text-align:right;">
						<xp:this.converter>
							<xp:convertNumber type="number"></xp:convertNumber>
						</xp:this.converter>
						<xp:eventHandler event="onchange" submit="true"
							refreshMode="complete">
							<xp:this.action><![CDATA[#{javascript:var base = getComponent("total_baseCurrency").getValue();
var local = getComponent("total_localCurrency").getValue();
rate = local / base;
getComponent("rate").setValue(rate);}]]></xp:this.action>
						</xp:eventHandler>
					</xp:inputText>
				</xp:td>
			</xp:tr>
			<xp:tr>
				<xp:td style="text-align:right;">
					<xp:label id="total_localCurrencyLabel" value="Total Local Currency">
					</xp:label>
				</xp:td>
				<xp:td>
					<xp:inputText id="total_localCurrency"
						defaultValue="#{javascript:compositeData.localCurrency}"
						style="width:100px;text-align:right;">
						<xp:this.converter>
							<xp:convertNumber type="number"></xp:convertNumber>
						</xp:this.converter>
						<xp:this.validators>
							<xp:validateLongRange minimum="1"></xp:validateLongRange>
						</xp:this.validators>
						<xp:eventHandler event="onchange" submit="true"
							refreshMode="complete">
							<xp:this.action><![CDATA[#{javascript:var base = getComponent("total_baseCurrency").getValue();
var local = getComponent("total_localCurrency").getValue();
rate = local / base;
getComponent("rate").setValue(rate);}]]></xp:this.action>
						</xp:eventHandler>
					</xp:inputText>
				</xp:td>
			</xp:tr>
			<xp:tr>
				<xp:td style="text-align:right;">
					<xp:label id="rateLabel" value="Exchange Rate"></xp:label>
				</xp:td>
				<xp:td>
					<xp:text escape="true" id="rate"></xp:text>
				</xp:td>
			</xp:tr>
			<xp:tr>
				<xp:td>
				</xp:td>
				<xp:td>
					<xp:button value="Apply to payment request"
						id="copyButton">
						<xp:eventHandler event="onclick" submit="true"
							refreshMode="complete">
							<xp:this.script><![CDATA[var rate = XSP.getElementById("#{id:rate}"); 
XSP.getElementById("#{id:exchangeRate}").value = rate.innerHTML;
XSP.getElementById("#{id:exchangeRate}").focus();
XSP.closeDialog('#{id:computeDialog}');]]></xp:this.script>
						</xp:eventHandler>
					</xp:button>
					<xp:button value="Cancel" id="cancelButton">
						<xp:eventHandler event="onclick"
							submit="false">
							<xp:this.script><![CDATA[XSP.closeDialog('#{id:computeDialog}');]]></xp:this.script>
						</xp:eventHandler>
					</xp:button>
				</xp:td>
			</xp:tr>
		</xp:table>
	</xe:dialog>
</xp:view>

The last tweak to it is that my computed exchange rate in the dialog is just a computed field. I struggled a little until a dogpile search revealed that I needed to get that value as innerHTML. If you try getValue() on a computed field, you get bupkis, but if you grab the innerHTML, you’ve got the world in your hands.

It feels like that would be easier in Old Notes, but I hardly care any more. I have a new micro-solution in my toolbox and I expect to re-use this not only for other exchange rate computations, but to re-use the dialog and value-passing in many places. It’s all about building up your toolkit, right?

Categories: Client-Side Javascript, Old Notes, Server-Side Javascript, Xpages, XSP Functions | Tags: , , , , , , , , , , | 1 Comment

Exporting to Excel using Queries in #Xpages

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.

Categories: Server-Side Javascript, Utilities, Xpages | Tags: , , , , , | 3 Comments

Dirty pages and keeping users on them in #XPages

As I’ve mentioned before, sometimes XPages does not react the way your Notes users would expect it to react. In particular, I’ve had issues with ensuring that XPages warns the user when they try navigating away from a page that they’ve edited without first saving it. Such pages are “dirty”, since something has changed on them. Back in November we examined this in my post on Modified flags in XPages.

Unfortunately, sometimes I want to inform the user that they’re leaving a dirty page and other times I don’t. Also, sometimes, I seem to be able to add things that don’t warn the user that they’re leaving a dirty page. I’m sure there is consistency, but I haven’t figured out the rules, so I have added some code that handles these situations.

Simple warning of a dirty page

This was described in that prior post. Set the enableModifiedFlag to true and provide a couple of properties to handle it. This should fire whenever you close the XPage or navigate away via a standard link.

<xp:view xmlns:xp="http://www.ibm.com/xsp/core" enableModifiedFlag="true">
	<xp:this.modifiedControl>
		<![CDATA[#{javascript:if ( true ) {return "saveButton1"}}]]>
	</xp:this.modifiedControl>
	<xp:this.modifiedMessage>
		<![CDATA[#{javascript:"Purchase order modified. Click OK to save, cancel to continue without saving"}]]>
	</xp:this.modifiedMessage>
...
</xp:view>

Unfortunately, I’ve found that sometimes, it doesn’t warn me.

Warning when clicking links

I’ve got some navigation on the left side of my XPages, set up in a lovely dojo accordian with links to different parts of the application. However, not every link is set up in the same way. Some are simple and use the modifiedControl to warn users, like this one:

<xp:link text="Attachments" escape="true" id="link3" value="/att_attachmentManagement_view.xsp">
</xp:link>

Some, however, run some scripts to remove some sessionscope variables when switching pages and I was having challenges getting the server-side Javascript to execute. So, I found a way to do both:

<xp:link text="Payment requests" escape="true" id="paymentLink" target="_self" value="#">
	<xp:eventHandler event="onclick" submit="true" refreshMode="complete">
		<xp:this.action><![CDATA[#{javascript:clearDynamicViewSettings();
context.redirectToPage("/pro_paymentRequest_view.xsp");}]]></xp:this.action>
	</xp:eventHandler>
</xp:link>

The unfortunate thing is that when I’m using the redirect, it ignores the dirtiness of the page. Fortunately, Per Henrik Lausten found a way to deal with it on StackOverflow (thanks to PSolano), and posted it as an XSnippet on OpenNTF.

if (XSP._isDirty()){
  if (confirm ("Are you sure you want to navigate away from this page?" + "\n" + "\n" +
    "This document may contain unsaved changes." + "\n" + "\n" +
    "Press OK to continue, or Cancel to stay on the current page.")) {
    return true;
  } else {
    return false;
  }
} else {
  return true;
}

So, when I added that explicitly as client-side Javascript to each eventhandler, it worked beautifully. Now, being a fan of reusable code, I wanted to put it in a CSJS script library and invoke the function all over the place. My function was as shown above, with a little wrapper:

function isClean() {
	if (XSP._isDirty()){
	    ....
	}
}

Then, I just needed to invoke it. My first attempt looked great to me. Call isClean() and the client side would warn the user, then stop them as appropriate.

<xp:eventHandler event="onclick" submit="true" refreshMode="complete">
	<xp:this.action><![CDATA[#{javascript:clearDynamicViewSettings();
context.redirectToPage("/pro_paymentRequest_view.xsp");}]]></xp:this.action>
	<xp:this.script><![CDATA[isClean();]]></xp:this.script>
</xp:eventHandler>

Ummmm, but that didn’t work. In order to prevent the server-side Javascript from executing, the client-side Javascript needs to RETURN false, not just compute it.

<xp:eventHandler event="onclick" submit="true" refreshMode="complete">
 <xp:this.action><![CDATA[#{javascript:clearDynamicViewSettings();
 context.redirectToPage("/pro_paymentRequest_view.xsp");}]]></xp:this.action>
 <xp:this.script><![CDATA[return isClean();]]></xp:this.script>
</xp:eventHandler>

That’s added bonus knowledge – not only are we learning how to stick to dirty pages, we’ve also learned that if you have the CSJS return a value of false, it will not execute the SSJS. I’m sure you can grasp the opportunities for validation provided there.

What about when you don’t want to warn them?

In our application, the user can select the action to perform from a combobox and then click a button to execute it. (Thanks to Scott Good, Henry Newberry and the folks at Teamwork Solutions!) The challenge here is that when the user changes the value in the combobox, the page is dirty! I mean, no data may have changed other than that combobox, but the XPage still feels dirty. Since some of those actions will result in saving the document and all of them result in navigation away from this page in an expected and managed way, we don’t want it to feel dirty. To dodge the problem, I just add a little CSJS to convince that page that it’s not really dirty.

<xp:this.script><![CDATA[XSP._setDirty(false,"");]]></xp:this.script>

So, then the modifiedControl is not tipped off and we execute the intended commands. Now, I’m pretty sure we don’t run into this problem with standard save buttons, but we’re in an unusual case of taking two steps to execute one action.

Note

YMMV. These private calls to XSP methods may go away and using them requires care to ensure you’ve got the right parameters, or so the XPages Portable Command Guide advises us. Of course, everything we code may call functions that will go away and we always need to get the parameters right. So, I’m gonna use them, though sparingly.

Categories: Client-Side Javascript, Server-Side Javascript, Xpages | Tags: , , , , , , , , , , , , | Leave a comment

Copying design elements via script in #XPages

One of my first posts was about copying a view from one database to many using XPages. Well, as I was surfing the help documents to learn more about FTSearch in order to extend the capabilities of my Excel exporting capabilty, I found createNoteCollection.

createNoteCollection can be used to create a collection of all notes in a Notes database. That is, not just Notes documents, but all Notes design elements. Not only that, but you can quickly choose what kind of notes you want in your note collection. While there are boolean parameters for each type of note you might want, I am most intrigued by the combination ones, which allow you to select by category.

selectAllAdminNotes (ACL and replication formulas)
selectAllCodeElements (agents, database script, outlines, script libaries and misc code elemnts)
selectAllDataNotes (documents AND profile documents)
selectAllDesignElements (code, format, index, help, icons and shared fields)
selectAllFormatElements (actions, forms, framesets, image resources, java resources, misc format elements, pages, style sheets and subforms)
selectAllIndexElements (folders, misc index elements, navigators and views)
selectAllNotes (everything)

So, once you create  your note collection, then you can walk the collection using getFirstNoteID and getNextNoteID, accessing each design element (or document). Then, using NotesDatabase.getDocumentByID to get a handle to it as a NotesDocument, so you can use NotesDocument.CopyToDatabase to copy the design element across to the new database.

var db:NotesDatabase=database;
var notecollection:NotesNoteCollection=db.createNoteCollection();
notecollection.selectAllDesignElements();
var noteID:String=notecollection.getFirstNoteID();
var note:NotesDocument;
var nextNote:NotesDocument;
while ( noteID != null ) {
	note=db.getDocumentByID(noteID);
	if ( note != null ) {
		note.copyToDatabase();
	}
	noteID=notecollection.getNextNoteID();
	nextNote=db.getDocumentByID(noteID);
	note.recycle();
	nextNote=note;
}

Now, it is a little blunt to grab all design elements and copy them from one database to another. One problem is that you are certain to have some duplication of design elements and I’m sure that will create problems. I know that I was able to have two identically named views in my original post, so I didn’t copy the view if it already existed in the destination database. So, you might want to create collections in both databases and compare the design notes t0o make sure not to create duplicates (either by deleting the one in the destination database first or simply not copying the new one in).

Categories: Old Notes, Server-Side Javascript, Utilities, Xpages | Tags: , , | Leave a comment

Exporting from #XPages to Excel without Excel, Part 2

Yesterday, I posted about exporting to Excel without having Excel on the destination device, which I found particularly useful when using my Android tablet and the Chrome browser. It was a relatively long post, necessitating a two-part post.

Today, I want to spend some time showing the server-side javascript library that I’ve developed (based on the one Russ Maher presented at the AdminDev 2012 conference). So, we’re going to delve into some Apache POI, examining how we pass the configuration information in and which lines of code implement that. I expect this examination will be useful for me as well, since it will open my eyes wider to the potential within the code. Russ assures me that while he dabbles in POI, Bill Buchan actually speaks POI, so there is at least one Notes genius who knows far more than both of us….

The Function Call

Before we delve into the code, lets first examine what we’re passing to the function. Each of these values is acquired either from the XPage or from the report configuration. Using these, we have a lot of flexibility to create and format our spreadsheet.

function createWorkbookStreamWithLabels(workbookName,sheetName,fieldList,viewName,colLabels,totalLabels)

workbookName: From the XPage, which defaults the value to the report name selected. It will be used as the filename for our spreadsheet.

sheetName: Also from the XPage, defaulting to ‘Report’, but editable by the user to place as the worksheet name for the single worksheet of our spreadsheet.

fieldList: From the report configuration. This is a list of the field names, in order, to be used as values in the columns. This implementation uses field values instead of column values, but it could easily be modified to use those instead.

viewName: From the report configuration. View containing the documents to be exported.

colLabels: From the report configuration. These will end up in the first row of the spreadsheet and be styled for emphasis.

totalLabels: From the report configuration. These are used to identify which columns will get totaled. This was clearer and more reliable than using column numbers.

Creating the Stream

I actually want to start by examining the last piece of code in the library, the download of the file to the client. To me, the great strength of this whole undertaking is that I can get a spreadsheet to my device without any Excel software being loaded there. As I noted yesterday, when I used the XPage and this script library to create a spreadsheet on my tablet, the usefulness skyrocketed in the estimation of my office-mate, Ariwan. “You could download the inventory to a tablet, walk around, making notes on your tablet and then return and update the database.” Of course, I started thinking one step further and wanting to import the marked up spreadsheet right back into the database, but that code will have to wait for a while. Nonetheless, we see an immediate impact beyond just creating spreadsheets.

By using the OutputStream, we’re able to take the export that we build in the workbook, wb, and simply prompt the user to download the file as an output stream. On my tablet, this downloaded the file without prompting, which was exactly what I wanted. In a quick test, I tried to use .xlsx, but it refused to work – that might be fixed in the later releases of the POI library (I’m using 3.6 currently, though 3.9 has already been released).

//Create the filename for the spreadsheet
var fileName = workbookName+".xls";

// The Faces Context global object provides access to the servlet environment via the external content
var extCont = facesContext.getExternalContext();
// The servlet's response object provides control to the response object
var pageResponse = extCont.getResponse();
//Get the output stream to stream binary data
var pageOutput = pageResponse.getOutputStream();

// Set the content type and headers
pageResponse.setContentType("application/x-ms-excel");
pageResponse.setHeader("Cache-Control", "no-cache");
pageResponse.setHeader("Content-Disposition","inline; filename=" + fileName);
//Write the output, flush the buffer and close the stream
wb.write(pageOutput);
pageOutput.flush();
pageOutput.close();

Error-handling

I found myself trying to trouble-shoot and, as always, being frustrated with XPages because I don’t know how to do that very well. I got real used to being able to step through my LotusScript, to being able to look at field values on a document using properties, to using print statements and popups to track what was going on, but I haven’t yet filled my toolkit with ways to trouble-shoot XPages. So, I borrowed Don Mottolo’s postValidationError function from his SSJS Form Validation XSnippet. Whenever I want to return an error, I just pass the control and some text to that function and it should post the message in my display errors control. I’ve only got a few of these in the library because it started working properly before I added all the potential error-trapping and who has the patience to add all that? (Yeah, I’ll add more later, since I’m sure I’ll run into bugs in production. Idiot-proof code is impossible because idiots are just too darn inventive!)

function postValidationError(control, msg) {
if ((typeof msg) != "string")
	return;
var msgObj = new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, msg, msg);
facesContext.addMessage(control.getClientId(facesContext), msgObj);
control.setValid(false);
}

Create some cell styles

As I mentioned yesterday, we deal with a lot of international date formats and, in any reporting, dates are all over the place. So, I added a date format to be applied to those cells. Similarly, as mentioned above, I want my headers to get some styling to emphasize them.

I’ve also designated my numeric format, using two digits after the decimal. Note that if the user has their machine set to use European number formatting, with periods separating thousands and a comma to show the decimal, the export won’t care – it passes the values and the user’s local settings display it according to their preferences. I’m not certain if the preferences at the server (if any) would have any effect on how you must code it, so am curious what experience others have there.

I’m sure that if we delve into the POI documentation, we can learn all kinds of ways to format the cells and I expect that once this deploys, we’ll be working on learning those. We start, however, with just these three.

//Create helper class and styles for dates
var createHelper:HSSFCreationHelper = wb.getCreationHelper();
var dateStyle:HSSFCellStyle = wb.createCellStyle();
dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

var headerStyle:HSSFCellStyle = wb.createCellStyle();
var headerFont:HSSFFont = wb.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);

// set number formatting to use a thousands separator and two digits
// note that it will display using the user's thousands separator and decimal notation
// rather than forcing US-standard settings
var format:DataFormat = wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));

Handle various data types

The next important issue is that when passing the data through, you must make sure the Excel file understands the data type.

//depending on the type of value, use proper method to retrieve
if (valueType.endsWith("DateTime")) {
	var reportValue = (String) (itemValue);
} else if (valueType.endsWith("number")) {
	var reportValue = Number (itemValue);
} else if (valueType.endsWith("string")) {
	var reportValue = itemValue;
} else if (valueType.endsWith("Vector")) {
	var reportValue = (String) (itemValue); // multi-value field
	reportValue = reportValue.replace("[",""); // remove opening bracket
	reportValue = reportValue.replace("]",""); // remove closing bracket
} else {
	var reportValue = itemValue.toString;
}
if ( reportValue == null ) {
	reportValue = "Value not found"
}

The Whole Library

Everything else in here should be pretty self-explanatory. We simply walk through the view, almost using LotusScript, moving from document to document, getting the appropriate field values and passing them to the Excel file. When it works, it’s lovely.

function createWorkbookStreamWithLabels(workbookName,sheetName,fieldList,viewName,colLabels,totalLabels){
//import the appropriate java packages
importPackage(java.lang);
importPackage(org.apache.poi.hssf.usermodel);
importPackage(org.apache.poi.hssf.util);

var control = getComponent("comboBox1");

//Find the database connection document
var dbConView:NotesView = database.getView("TSDbConnectionLU");
var doc:NotesDocument = dbConView.getDocumentByKey("TAMIS II main db");
//Get the maindb object
if (doc != null){
	var maindb = session.getDatabase(doc.getItemValueString("DbServer"),doc.getItemValueString("DbPath"), false);
}
if ( maindb == null ) {
	postValidationError(control,"Main db Not Found");
}
var view:NotesView=maindb.getView(viewName);
if ( view == null ) {
	postValidationError(control,"View Not Found");
}

//Create placeholders for the notes document and temperary document
var doc:NotesDocument;
var ndoc:NotesDocument;

//Create a new workbook object from the poi library
var wb:HSSFWorkbook = new HSSFWorkbook();
//Create additional sheets using same sytnax and different sheet name
var sheet1:HSSFSheet = wb.createSheet(sheetName);

//Create helper class and styles for dates
var createHelper:HSSFCreationHelper = wb.getCreationHelper();
var dateStyle:HSSFCellStyle = wb.createCellStyle();
dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

var headerStyle:HSSFCellStyle = wb.createCellStyle();
var headerFont:HSSFFont = wb.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);

//Create the Column Header Rows
var row:HSSFRow = sheet1.createRow(0);
for( i = 0 ; i <= colLabels.length-1 ; i++){
	var hCell:HSSFCell = row.createCell((java.lang.Integer)(i));
	hCell.setCellValue(colLabels[i]);
	hCell.setCellStyle(headerStyle);
}

// Style the cell with borders all around. GREY 25% - 22, GREY 40% - 55, GREY 50% - 23, GREY 80% - 63
var style:HSSFCellStyle= wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(55);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(55);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(55);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(55);

// set number formatting to use a thousands separator and two digits
// note that it will display using the user's thousands separator and decimal notation
// rather than forcing US-standard settings
var format:DataFormat = wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));

//initialize the row and cell counters
var rowCount=0;
var cellCount=0;

//Get the first document in the view and then cycle through
//the view to create the detail rows.
doc = view.getFirstDocument();

while(doc!=null){
var row:HSSFRow = sheet1.createRow(++rowCount);
for( f = 0 ; f <= fieldList.length-1 ; f++){
	var itemValue:NotesItem = doc.getColumnValues().elementAt(Number (fieldList[f]) - 1 );
	var valueType = typeof(itemValue);

	if (itemValue != null) {
		var reportValue;
		//depending on the type of value, use proper method to retrieve
		if (valueType.endsWith("DateTime")) {
			var reportValue = (String) (itemValue);
		} else if (valueType.endsWith("number")) {
			var reportValue = Number (itemValue);
		} else if (valueType.endsWith("string")) {
			var reportValue = itemValue;
		} else if (valueType.endsWith("Vector")) {
			var reportValue = (String) (itemValue); // multi-value field
			reportValue = reportValue.replace("[",""); // remove opening bracket
			reportValue = reportValue.replace("]",""); // remove closing bracket
		} else {
			var reportValue = itemValue.toString;
		}
		if ( reportValue == null ) {
			reportValue = "Value not found"
		}
		var dataCell:HSSFCell = row.createCell((java.lang.Integer)(f));
		dataCell.setCellValue(reportValue);
		dataCell.setCellStyle(style);
	}
}
doc = view.getNextDocument(doc);
}

// add a row for totaling and put the formula in where it is useful
var row:HSSFRow = sheet1.createRow(++rowCount);

// cycle through totalLabels, find match in colLabels, make a totalling cell
for( i = 0 ; i <= totalLabels.length-1 ; i++){
for( j = 0 ; j <= colLabels.length-1 ; j++){
if ( totalLabels[i] == colLabels[j] ) {
var summaryCell:HSSFCell = row.createCell((java.lang.Integer)(j));
// add the SUM formula to that cell
summaryCell.setCellFormula("SUM(INDIRECT(CONCATENATE(ADDRESS(2,COLUMN()),\":\")&ADDRESS("+rowCount+",COLUMN())))");
}
}
}

// auto size all the columns
for( j = 0 ; j <= colLabels.length-1 ; j++){
sheet1.autoSizeColumn (j);
}

//Create the filename for the spreadsheet
var fileName = workbookName+".xls";

// The Faces Context global object provides access to the servlet environment via the external content
var extCont = facesContext.getExternalContext();
// The servlet's response object provides control to the response object
var pageResponse = extCont.getResponse();
//Get the output stream to stream binary data
var pageOutput = pageResponse.getOutputStream();

// Set the content type and headers
pageResponse.setContentType("application/x-ms-excel");
pageResponse.setHeader("Cache-Control", "no-cache");
pageResponse.setHeader("Content-Disposition","inline; filename=" + fileName);
//Write the output, flush the buffer and close the stream
wb.write(pageOutput);
pageOutput.flush();
pageOutput.close();

//  Terminate the request processing lifecycle.
facesContext.responseComplete();
}
function postValidationError(control, msg) {
if ((typeof msg) != "string")
return;
var msgObj = new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, msg, msg);
facesContext.addMessage(control.getClientId(facesContext), msgObj);
control.setValid(false);
}

The sample database is available, as is the first post on this topic.

Categories: Server-Side Javascript, Xpages | Tags: , , , , , , , , , , , | 15 Comments

Blog at WordPress.com.

%d bloggers like this: