1

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).

jeremy_krein
  • 97
  • 6
  • We will have a webinar in March that covers two different solutions to your problem. One is the Dojo DataGrid that comes with XPages and the other is DataTables that is build on Bootstrap. http://www.tlcc.com/admin/tlccsite.nsf/pages/xpages-webinar?opendocument – Howard Feb 26 '15 at 14:21
  • I am more jQuery than dojo so the DataTables solutions seems great! How can I join the webinar ? – jeremy_krein Feb 26 '15 at 14:53

3 Answers3

2

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.

Paul Stephen Withers
  • 15,699
  • 1
  • 15
  • 33
  • For a computed value in a view, can I for example from a UNID get a document from an other Database and display one of its field ? (I really have no knowledge in lotus script and formula language) Really good point for the sorting. Let's say that I want the sorting only on the column that contains regular values from the view and not the computed one. Is there a way to do so ? Sorry but what is RDBMS? – jeremy_krein Feb 26 '15 at 09:16
  • In SSJS it would be: var myDb = session.getDatabase("myServerName","myFilePath"); var myDoc = myDb.getDocumentByUNID("myDocID"); Sorting can be enabled in a View Panel in two steps. You need to ensure the corresponding column in the View design element has sortability set (second tab of Column properties, otherwise no index is stored to allow sorting on that column). You then need to enable sorting on the ViewColumn in your XPage. Repeat Controls don't have an option for adding sorting, but you may be able to re-sort the underlying dominoView datasource, if you want to use a Repeat Control. – Paul Stephen Withers Feb 26 '15 at 09:37
  • RDBMS is relational database management system, e.g. SQL. Sounds like that's not needed if you don't need to sort on columns not in the view. – Paul Stephen Withers Feb 26 '15 at 09:38
  • In a NotesView you can't have SSJS for a computed value. It's only Lotus Script and Formula language no ? I know how to sort a view. The question is more how to dynamically change the sort (ASC or DESC) and which column to sort from the data binding with the view. – jeremy_krein Feb 26 '15 at 09:43
  • The SSJS would be on the XPage, in the viewColumn entry. For the dynamic sorting, as I mentioned, you need to enable it on the relevant column in the View design element, so the index exists. Then in the corresponding viewColumnHeader entry on the XPage set sortable="true" ("Sort Column" checkbox) – Paul Stephen Withers Feb 26 '15 at 11:02
  • Ah ok, the thing I was missing is I didn't know that I can compute my own value for the viewColumn in a Xpage View component (lol how could I miss that!!). Thank you for your help. I don't put the question as answered yet so maybe I will get other suggestions but I will in few days. – jeremy_krein Feb 26 '15 at 12:21
1

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.

David Leedy
  • 3,583
  • 18
  • 38
  • Why Java?, I used to do the same with javascript. In the repeat I have my getOrders() function that return an array of javascript objects containing all the information. If we use the DataTables for sorting and paging, and not the repeat, it means that I have to load all the orders on the client. Now if I have 5000 orders, and for each order in my function getOrders() I need to compute a lot of data (for example getting info from other notes application, etc.) So I will need to wait for the server to compute everything. I think it will be way to long! – jeremy_krein Feb 26 '15 at 16:06
  • I'm not trying to push java here. If you're getting all your data via a JavaScript object great. I didn't realize that and thought you were tying to do a lot with computed columns. I also thought some of your data was not coming from a single view since you're reaching out to other databases. – David Leedy Feb 26 '15 at 18:02
1

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])
});
AnilSh
  • 36
  • 3
  • I really like the approach of generating one JSON on a scheduled basis, store it somewhere and after every user just get the json, no need for computation everytime a user wants to get the page. But the problem is just compute the json every like 5 minutes it's not so good practice, you have a suggestion about how to store a json and update it only under certain conditions (event that trigger the update of the JSON). Or a kind of caching maybe. – jeremy_krein Mar 01 '15 at 09:23
  • Computing the json on periodic basis on the server will prevent the user from waiting for the data. You may use agent to compute the json and store it in a normal notes document. For Caching, it depends on the size of output data. If data is in KBs, you can store it in a RichText Field and store it in an applicationScope variable and store another field to check for the Time i.e. if data is older than 5/50 mintues then update the applicationScope with new Data. If Data is in MBs, store it in a file and link it direclty i.e. http://domino_server/database/0/DocUNID/$file/filename.json – AnilSh Mar 09 '15 at 16:02