Skip to Content
Author's profile photo Wouter Lemaire

Full table search and sort at Client-side in OpenUI5 with UnderscoreJS

Hi all,

In this blog, I’ll talk about OpenUI5, OData services and data manipulation with UnderscoreJS.

Server-side data manipulation

When you are using OData services while working with OpenUI5, you will notice that OpenUI5 will send multiple requests to your OData service. It will send a request when you want to sort your data, it will send a request when you search through your data and so on… . It will send a request for almost every action on your data. This is good when your services are fast enough to handle all the requests and you’ll have to write less Javascript.

Why not client-side?

But why not sorting and searching through your data at client side? Manipulating your data at client side will reduce your requests to your backend, it will reduce logic in your backend and most devices will be able to handle this! Just be careful when working with lots amount of data.

Javascript vs ABAP?

Okey, I agree, when you’ve always worked in ABAP, ABAP will be much easier. But Javascript has, besides OpenUI5, many different libraries which you can use with OpenUI5. These libraries can make Javascript much easier, even easier than ABAP ! (maybe a little optimistic 🙂 ) One of the many libraries that I prefer for data manipulation at client-side is UnderscoreJS.

UnderscoreJS

Underscore is a JavaScript library that provides a whole mess of useful functional programming helpers without extending any built-in objects. It’s the answer to the question: “If I sit down in front of a blank HTML page, and want to start being productive immediately, what do I need?” … and the tie to go along with jQuery‘s tux andBackbone‘s suspenders.

Underscore provides 80-odd functions that support both the usual functional suspects:map, select, invoke — as well as more specialized helpers: function binding, javascript templating, deep equality testing, and so on. It delegates to built-in functions, if present, so modern browsers will use the native implementations of forEach, map,reduce, filter, every, some and indexOf.

Basically UnderscoreJS provides multiple Javascript helper functions. With UnderscoreJS your code will look more understandable and you will be able to do more with less code.

For more information about UnderscoreJS:

http://underscorejs.org/

Basic Example

Create an basic SAPUI5 Application with one view

Download UnderscoreJS and add it to your project

/wp-content/uploads/2014/02/underscore_392169.png

Include the underscorejs library to your index.html

/wp-content/uploads/2014/02/loadunderscore_392170.png

In this example I’m going to work with a public OData service:

http://services.odata.org/Northwind/Northwind.svc/

More specifically the collection Customers.

http://services.odata.org/Northwind/Northwind.svc/Customers

Design the view

In our view, I just add a table with the following parts

  • Toolbar
    • Button to count the rows: “Count rows”
    • Button to sort the table by country: “Sort by Country”
    • Textifeld to search over the full table
  • 6 Columns
    • CustomerID
    • CompanyName
    • ContactName
    • ContactTitle
    • Country
    • City

This table is connected to the model “WorkModel”.


createContent : function(oController) {
      var oTable = new sap.ui.table.Table({
            title: "Table Example",
            visibleRowCount: 7,
            firstVisibleRow: 3,
            selectionMode: sap.ui.table.SelectionMode.Single,
            toolbar: new sap.ui.commons.Toolbar({items: [
                new sap.ui.commons.Button({text: "Count rows", press: oController.getCount}),
                new sap.ui.commons.Button({text: "Sort Country", press: oController.sortByCountry}),
                new sap.ui.commons.TextField({id:"search",liveChange:oController.find})
            ]})
      });
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "CustomerID"}),
            template: new sap.ui.commons.TextView({text:"{WorkModel>CustomerID}"})
      }));
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "CompanyName"}),
            template: new sap.ui.commons.TextView({text: "{WorkModel>CompanyName}"})
      }));
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "ContactName"}),
            template: new sap.ui.commons.TextView({text:"{WorkModel>ContactName}"})
      }));
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "ContactTitle"}),
            template: new sap.ui.commons.TextView({text:"{WorkModel>ContactTitle}"})
      }));
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "Country"}),
            template: new sap.ui.commons.TextView({text:"{WorkModel>Country}"})
      }));
      oTable.addColumn(new sap.ui.table.Column({
            label: new sap.ui.commons.Label({text: "City"}),
            template: new sap.ui.commons.TextView({text:"{WorkModel>City}"})
      }));
      oTable.bindRows("WorkModel>/");
      return oTable;
  }






Controller of the view

In the controller I’m going to use UnderscoreJS for counting, sorting and for searching.

In the init of the controller I will do the following:

  1. Create OData model by using the url of the service
  2. Create two empty JSON models
    1. One to have always the original list, so I can always search on the full data (“OriginalModel”)
    2. A second one for the view, which I will use for the sort, count and to show the result of the search (“WorkModel”)
  3. Read the “/Customers” from the OData model
    1. Put the result in the JSON model for the original list (“OriginalModel”)
    2. Sort the result
    3. Put the sorted result in the second JSON model for the view (“WorkModel”)

The JSON models are required for client-side data manipulation. With the OData model we cannot change the data without sending a request to the backend because of the bindings.


onInit: function() {
      var sServiceUrl ="http://services.odata.org/Northwind/Northwind.svc"; 
      var oModel = new sap.ui.model.odata.ODataModel(sServiceUrl);
      var oModelJson = new sap.ui.model.json.JSONModel();
      var OriginalModel = new sap.ui.model.json.JSONModel();
      oModel.read("/Customers", null, null, null, function(data, oResponse){
            OriginalModel.setData(data.results);
            sap.ui.getCore().setModel(OriginalModel, "OriginalModel");
            var data = _.sortBy(data.results,function(value){
                return value.name;
            });
            oModelJson.setData(data);
            sap.ui.getCore().setModel(oModelJson, "WorkModel");
      }, null );
  }





With UnderscoreJS we can easily do a count on the JSON model which is used in the view (“WorkedModel”)


getCount: function(){
      var model = sap.ui.getCore().getModel("WorkModel");
      var count = _.countBy(model.getData(), function(value) {
            return 'all';
      });
      alert(count.all);
  },





Also for the sort I’m using an UnderscoreJS function.

  1. Get the “WorkModel”
  2. Change the data using UnderscoreJS
  3. Set the “WorkModel” with the new data

sortByCountry: function(){
      var model = sap.ui.getCore().getModel("WorkModel");
      var data = _.sortBy(model.getData(),function(value){
            return value.Country;
      });
      model.setData(data);
      sap.ui.getCore().setModel(model,"WorkModel");
  }





Full Table search

In this case we start from the “OriginalModel” to search over all records. Otherwise the seconde search will be incorrect.

For the search I will use mutliple UnderscoreJS functions.

I start with using the filter function to get only the records that meet the search term.

To find out which records that meet the searchterm, I use some other UnderscoreJS function in the filter function.

  • I use a combination of chain, map, properties and filter for concatenating all field values as one string seperated by spaces.

var fields = [
      'CompanyName',
      'CustomerID',
      'ContactName',
      'ContactTitle',
      'Country',
      'City'
  ];
  var concat = _.chain(fields)
                          .map(function(field) { return _.property(field)(item); })
                          .filter(function(result) { return result !== undefined && result !== null })
                          .value()
                          .join(' ');




  • After that I loop over all the different words in the searchterm and return every record that contains all the searchterms.

return _.every(search.split(' '), function(searchTerm) {
  return concat.indexOf(searchTerm) >= 0;
});




Full code will look like this. I filter on the “OriginalModel” and set the “WorkModel”


find: function(oEvent){
      var search = oEvent.getParameter("liveValue");
      var model = sap.ui.getCore().getModel("OriginalModel");
      var FilteredData = _.filter(model.getData(),function(item){
            try{
                var fields = [
                      'CompanyName',
                      'CustomerID',
                      'ContactName',
                      'ContactTitle',
                      'Country',
                      'City'
                  ];
                var concat = _.chain(fields)
                                          .map(function(field) { return _.property(field)(item); })
                                          .filter(function(result) { return result !== undefined && result !== null })
                                          .value()
                                          .join(' ');
                return _.every(search.split(' '), function(searchTerm) {
                      return concat.indexOf(searchTerm) >= 0;
                    });
            } catch(e) {
                throw e;
            }
      });
      var oModelJson = new sap.ui.model.json.JSONModel();
      oModelJson.setData(FilteredData);
      sap.ui.getCore().setModel(oModelJson,"WorkModel");
}



Result

Table with the originial data of the OData service

/wp-content/uploads/2014/02/fulltable_392198.png

Sorted on country

/wp-content/uploads/2014/02/sort_392199.png

Full table search  ( Country and ContactTitle )

/wp-content/uploads/2014/02/search_392200.png

Count

/wp-content/uploads/2014/02/count_392201.png

Testing

For testing, I’ve started chrome with:  –disable-web-security

/wp-content/uploads/2014/02/chrome_392203.png

Conclusion

This is one of the advantages of Javascript. It gives you freedom to use other libraries and frameworks. I think this is a great example of other libraries in combination with OpenUI5. But there are a lot more libraries and frameworks! With Javascript almost everything is possible!

You can find the full project on github: https://github.com/lemaiwo/OpenUI5-and-UnderscoreJS

I’ve also added the index and the view to the attachments

Kind regards,

Wouter

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo John Patterson
      John Patterson

      Wouter

      nice one, I like jQuery but i think too often it is over used and used in the wrong places. I am starting to see a lot blogs like this one from Martin Fowler SegregatedDOM telling us how to avoid jQuery soup, keep your concerns separated, dont write your DOM manipulations in the controller, put the code into controls or helpers.


      I would like to see underscorejs become a best practice for application/data logic for UI5, its easy to use and a better fit for purpose than jQuery.


      jsp

      Author's profile photo Arun Santhanam
      Arun Santhanam

      Good one.

      Author's profile photo Chandrashekhar Mahajan
      Chandrashekhar Mahajan

      One of the excellent blog! many times we want to implement full table search functionality and this is nice blog on how to do that at client side. Thanks for sharing this information!

      Regards,

      Chandra

      Author's profile photo Robin Panneels
      Robin Panneels

      Great stuff Wouter.

      Robin

      Author's profile photo Mateus Luis Oliveira
      Mateus Luis Oliveira

      Hi.

      One of my big douts is how I connect my project with SAP?

      For example if I want to get data from table mara, how can i do that?

      Someone can help me in this first step?

      Author's profile photo Wouter Lemaire
      Wouter Lemaire
      Blog Post Author

      Hi Mateus,

      To expose your data from your SAP system you will need gateway. With gateway, you can convert the data from your MARA table into OData. A great document about this:

      New E2E Tutorial: Gain Hands-on Developer Experience with SAPUI5 and SAP NetWeaver Gateway

      Kind regards,

      Wouter

      Author's profile photo Mateus Luis Oliveira
      Mateus Luis Oliveira

      Thank you Wouter Lemaire.

      I will study this guide and try to understand.

      Author's profile photo Kim Schoukens
      Kim Schoukens

      Thanks! Nice one! Keep up the good work 🙂

      Author's profile photo Former Member
      Former Member

      ➕ ➕

      very very good!!