Doing THIS with XPages Views - could NOT have been done in a standard Notes View

---

Notes Views is a fantastic feature of Notes/Domino and has been a true friend over the years. If the users work profile is "write few - read many" the standard view is really fast and versatile .

But as we all know standard Notes Views has its limits.

A few months ago I made a very quick and dirty solution for recording our domestic consumption of gas, electricity and water.
I have only made few observation over the last 3 months, since I keep forgetting ...
So to have more useful data I would like to have a daily average of the consumption

The old school way of doing this would be having an agent loop through all the documents, and calculate the numbers and save them in the documents.
The downside to this is, it is not real-time, the risque of save and replications conflicts etc.

In the traditional view all that is shown in a view is bound to the documents defined to be included in the view.
But views are even more limited since data/formula shown in a row in the view is bound to exactly one document (or a category).
You can of course include other data like @today, but not from other documents or other "real" data.

I will use my simple database as an example.


Since I don't update the database daily I would like to have a daily average to give me a felling of how large our consumption of gas, electricity and water are.

To do this I need to:

- for each row get hold of the previous row too
- fetch the date and the consumption data for both rows
- chech if it is the first row in a category then there will be nothing to calculate
- calculate days passed and the consumption in the period and calculate the average

This can NOT be done in a standard Notes view, but luckily it can be done in an XPages view :-)

Let's go the to the actual code and design

Add a row variable in the view
To get hold of the current row and the previous row I need to have a variable referring to the current row.
In the ViewPanel I have added the variable rowData



Add two columns, one for days passed between to entries, and one for the calculated average consumption per. day



Add code in each column to fetch and calculate data
This is of course the fun part

The rowData variable points to a NotesXspViewEntrywhich is kind of a NotesViewEntry.

You can NOT get the previous row from this, but you can get enough information about the position in the view, to get access to the previous row/entry.
To get the position in the view you need to call:

rowData.getNavigatorPosition() which will return the position as a string like "2.4" meaning second category forth document.

To get to the previous document row I parse the string and change the last entry so I get "2.3"
I then create a ViewNavigator to navigate the view and I make it go to the new position "2.3"

var nav:NotesViewNavigator = database.getView("Gas").createViewNav();
var result:boolean = nav.gotoPos(newPos, ".");

I can then from the navigator get a new ViewEntry pointing to the previous row

var entry:NotesViewEntry= nav.getCurrent()
 
I can now get the values I need from both rows and calculate my average.

var entry:NotesViewEntry= nav.getCurrent()
var date1:NotesDateTime=entry.getColumnValues().elementAt(1)
var date2:NotesDateTime=rowData.getColumnValues().elementAt(1)
var days:int = date2.timeDifferenceDouble(date1) / 86400; // 86400 seconds in a day
var value1:NotesDateTime=entry.getColumnValues().elementAt(2)
var value2:NotesDateTime=rowData.getColumnValues().elementAt(2)

return ((value2-value1)/days).toFixed(2)   

End result: An XPages with days passed and my average consumption 




Pros
Using the XPages view this way shows the endless possibilities with XPages and Domino. I am sure many of you have been confronted with requirements of calculating and viewing (statistical) data in a view, but had to back down, leaving the customer with the feeling..."hallo ..you can easily do that in Excel and not in Notes?"
My POC shown here was just to open up for new ideas to work the views, everything is possible! :-)

Cons
The data I have calculated in the two columns are not part of a view index which means that they will potentially be calculated each time they are rendered (if they are not cached)

To limit the work from the server I could have made another solution, which would involve the client only.
By instead getting the data from the DOM in the browser I could have achieved the same result ..but then again I would not have been talking about XPages ..would I ? :-)

Demo
Link to demo

(code update, had some really stupid String handling errors, sorry)
The Source Code - Average column

Posted on 10/25/2011 12:10:00 PM CEDT

JBKR-8MXHSX

/nevermind/blog.nsf

1EAD41256708F9E8C12579330049F3EB