Best way to build a custom table linked to a NotesView with sorting and paging

Question

I have a view in my Xpage application that contains a lot of elements. From this view I need to build a table with custom rows (I can't just display the view, I need to build the rows to display myself because I need to compute data from other database, things that you can't do directly in a view).

In order to do so I know that I can use Dataview, Datatable or repeat control (other ideas maybe?). For sure I can't bring all the data on the client, it's way too much.

I am looking for a solution that will allow me to do paging (easy to do with the pager component) but more important sorting on header click. To be clear, I need sorting for all the entries of the view and not only for the current displayed page on the client.

What can be the more efficient way to do so ? I really have a lot of data to compute so I need the fastest way to do it.

(I can create several views with different sorting criteria if needed).

Answer 1

Any repeating control can have pagers applied to it. Also View Panels can include data not in the current view - just set the columnName property to blank and compute the value property. bear in mind you will not be able to sort on those column though - they're not columns, they're values computed at display time.

Any computed data is only computed for the entries currently shown. So if you have 5000 entries in the view but are only displaying 30 at a time, the computed data will only be computed for the current 30.

If your users need to be able to sort on all columns and you have a lot of data, basically they have to accept that they're requirements mean all that data needs computing when they enter the view...and whenever it's updated, by themselves or any other users. That's never going to be quick, and the requirements are the issue there, not the architecture. RDBMS may be better as a back-end, if that's a requirement, as long as the data doesn't go across multiple tables. Otherwise graph database structure may be a better alternative.

The bigger question is why the users need to sort on any column. Do the users really want to sort on the fifth column and then scroll down to entries beginning with a "H"? Do they want to sort on the fourth column and scroll down to entries for May 2014? On a Notes Client, that's a traditional approach, because it's easier than filtering. But usually users know what they're looking for - they don't want entries beginning "H", they want entries where the department is HR. If that's the case, sorting on all columns and paging is not the most efficient method either from a database design or a usability point of view.

Answer 2

To keep the processing faster and lightweight, I use JSON with JQuery DataTables. Depending on the Data-size and usage, JSON could be generated on the fly or scheduled basis and saved in Lotus Notes Documents or ApplicationScope variables.

$.each(data, function(i, item) {
   dataTable.row.add( [data[i].something1,data[i].something2,data[i].something3])
});

Answer 3

You can compute a viewColumn but if you have a lot going on I wouldn't go that route.
This is where Java in XPages SHINE!
Build a Java object to represent your row. So in java use backend logic to get all the data you need. Let's say you have a report of Sales Orders for a a company. And sales orders is pulling data from different places. Your company object would have a method like:

List<salesOrder> getOrders() {}

so in the repeat you call company.getOrders() and it returns all the rows that you worked out in java and populated. So your "rowData" collection name in the repeat can access all the data you want. Just build it into a table.

But now the sorting... We've been using jQuery DataTables to do just this.. It's all client side... your repeat comes down and then the DataTables kicks in and can make everything sortable... no need to rely on views.. works great...
Now it's all client side but supports paging and works pretty decent. If you're just pumping out LOTS of records - 6,000+ then you might want to look at outputting the data as json and taking advatange of some server cacheing... We're starting to use it with some really big output.. LOTS of rows and it's working well so far. Hopefully I'll have some examples on NotesIn9.com in the near future.