Building a search function with DataTables plugin (VIII)

Introduction

Dates, everybody loves to filter on dates. In Notes views to filter (select) on dates is a criminal offence but luckily filtering on dates in the datatable component is not that hard.

For our search function we are going to apply something as followed:

datatables08

It are two input fields, set as type ‘date’. In some browsers you get a datepicker presented. If you want to have a more fancy one you can always bring in an add-on.

Implementation

So for our HTML structure we included in our XPage:

datatables08b

So what are we going to filter on? In my example I have choosen to filter on the birthday. This is not a field available in the names.nsf so we have to add this ourselfs (now you know why HR always forget your birthdays). To do so I have updated my agent to generate person documents. This was my quickest approach:

bday = Round(Rnd()* 28 ,0)
If bday = 0 Then
bday = 1
End If

bmonth = Round(Rnd()* 12 ,0)
If bmonth = 0 Then
bmonth = 1
End If

byear = arr_birthyear( Round(Rnd()* UBound(arr_birthyear) ,0) )
birth = CStr(bmonth) + “-” + CStr(bday) + “-” + byear

Dim dateTime As NotesDateTime
Set dateTime = session.CreateDateTime( birth )
Call doc.ReplaceItemValue(“Birthday”, dateTime)

If you have a better one (I am sure of that) please drop a line how I can obtain it.

So with this birthday field on the person document, we need to updated our Notes view (adding a column). I had to hard-code the format otherwise I did not get a full year displayed:

@Text(@Year(birthday)) +”-“+
@If(
@Length(@Text(@Month(birthday))) <2;
“0” + @Text(@Month(birthday));
@Text(@Month(birthday))
) +”-“+
@If(
@Length(@Text(@Day(birthday))) <2;
“0” + @Text(@Day(birthday));
@Text(@Day(birthday))
)

 

Also we need to “broadcast” this additional value via our custom service bean:

String birthday = String.valueOf(columnValues.get(12));
if (null!=birthday){
jo.put(“bday”,birthday);
}

Nothing that fancy. We also need to include the extra column to our table header:

<thead>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Company</th>
<th>Job</th>
<th>Birthday</th>
</tr>
</thead>

THEN comes the main modification, in our JavaScript.

We need a function to normalize the date (from string to Date object):

var normalizeDate = function(dateString) {
var date = new Date(dateString);
var normalized = date.getFullYear() + ” + ((“0″ + (date.getMonth() + 1)).slice(-2)) + ” + (“0” + date.getDate()).slice(-2);
return normalized;
}

To apply a date filter to the datatable I have setup the next function:

var filterByDate = function(column, startDate, endDate) {
$.fn.dataTableExt.afnFiltering.push(
function(oSettings, aData, iDataIndex) {
var rowDate = normalizeDate(aData[column]),
start = normalizeDate(startDate),
end = normalizeDate(endDate);
if (start <= rowDate && rowDate <= end) {
return true;
} else if (rowDate >= start && end === ” && start !== ”) {
return true;
} else if (rowDate <= end && start === ” && end !== ”) {
return true;
} else {
return false;
}
}
);
};

In our datatables initialitation script we set the dates for the input fields (let’s hope people may retire in your country at age 65):

var d = new Date();
d.setYear(d.getYear() – 65);
document.getElementById(‘start’).valueAsDate = d;
document.getElementById(‘end’).valueAsDate = new Date();

We need to register any change in the input date fields and call the filterByDate function:

$(“.datePicker”).on(“keyup change”, function() {
var startDate = $(‘#start’).val(),
endDate = $(‘#end’).val();
filterByDate(4, startDate, endDate); // call our filter function
$(“#persons”).dataTable().fnDraw(); // manually redraw the table after filtering
});

To remove the filter we register a function on the onclick event of the Clear Data Filter button:

$(‘#clearFilter’).on(‘click’, function(e){
e.preventDefault();
$.fn.dataTableExt.afnFiltering.length = 0;
$(“#persons”).dataTable().fnDraw();
});

With all this in place we are good to go!

Wrap-up

Yet another great feature for our search form! Perhaps you prefer a slider more suitable for selecting date-ranges or a more fancy date-pciker that works across browsers. The choices are up to you!

Building a search function with DataTables plugin (VII)

Introduction

From my experience some Notes may contain a lot of columns (not sure what the limit is?) and represented on the web, often users need so see the same amount of columns. So what do you do then?

Column visibility to the rescue!

Part of the buttons module is the column visibility. This requires to load an additional resource:

<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/buttons-1.2.1/js/buttons.colVis.js</href>
</resource>

Also remember here to disable AMD loading.

From here it is quiet easy to have the visibility option available as a button in the ‘button bar’. Just add the colvis option in the configuration.

In case you want to result of (in)visibility of columns represented in any export option you need to specify the exportOptions property for the buttons e.g. as followed:

buttons: [
‘copy’,
‘csv’,
‘excel’,
{
extend: ‘pdfHtml5’,
orientation: ‘landscape’,
pageSize: ‘LEGAL’,
title: ‘Person Details’
},
{
extend: ‘print’,
exportOptions: {
columns: ‘:visible’
}
},
‘colvis’
],

Result

As a result a new button is presented and when clicked you can choose which column to make (in)visible:

datatables07

column API

In case you do not want to present a button, but for example anchor links the column API comes at help.

Here is a sample how to register change in a column visibility when clicking an anchor:

$(‘a.toggle-vis’).on( ‘click’, function (e) {
var db = $(“#persons”).DataTable();
e.preventDefault();

// Get the column API object
var column = db.column( $(this).attr(‘data-column’) );

// Toggle the visibility
column.visible( ! column.visible() );
} );

And here is the HTML for presenting the anchors:

Column visibility:FirstnameLastnameCompanyJob title

Finally the end result. Ofcourse you can be creative with CSS for the anchor links, adding different layout for each column state. Be creative!

datatables07b

In another post I will describe how you can accomplish something similar in Xpages with a viewpanel (with a bit of more code)…

Building a search function with DataTables plugin (VI)

Introduction

Often business analists who work with the data in tables eat & sleep in their spreadsheet world so a common asked question is: can we export the data? “Ofcourse you can!” with the datatable component.

Modifications

Below is a short summary of the changed/added items.

Configuration

In order to add the buttons you specify in the datatable configuration which buttons you would like to have e.g.:

dom: ‘Bfrtip’,
buttons: [
‘copy’,
‘csv’,
‘excel’,
{
extend: ‘pdfHtml5’,
orientation: ‘landscape’,
pageSize: ‘LEGAL’,
title: ‘Person Details’
},
‘print’
]

 

Required files

Which files are required for the buttons feature depend on which buttons you would like to display. In the download builder you can specify for which options you would like to go.

AMD fix

An important thing to notice is that most JS files use AMD loading. There have been posted multiple questions and answers how to fix AMD loading with XPages. In my repository I have just removed the amd checking.

Files overview

Probably the files you would like to use will be listed in the Theme design element:

<!– adding buttons –>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/buttons-1.2.1/js/dataTables.buttons.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/buttons-1.2.1/js/buttons.flash.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/jszip-2.5.0/jszip.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/pdfmake-0.1.18/build/pdfmake.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/pdfmake-0.1.18/build/vfs_fonts.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/buttons-1.2.1/js/buttons.html5.js</href>
</resource>
<resource>
<content-type>application/x-javascript</content-type>
<href>DataTables/buttons-1.2.1/js/buttons.print.js</href>
</resource>
<resource>
<content-type>text/css</content-type>
<href>DataTables/buttons-1.2.1/css/buttons.dataTables.min.css</href>
</resource>

 

Result

As a result you will see the buttons displayed above the table. If you apply filtering to the table the buttons will pick up the provided terms.

datatables06

A little less coding than an X-agent to create a PDF or spreadsheet:-)

A downsize might be that users are not aware of the amount of data that might reside in your datatable (the only x-number of entries are displayed). Pressing the PDF button with 40K of documents in your notes view takes a bit of time…

Building a search function with DataTables plugin (V)

Introduction

In the last post we moved the input fields out of the table and into a separate form. In this post I will include a nice usability feature for search term highlighting. This feature is particular helpful if you allow search in columns where values do not differ so much from each other like order numbers or article numbers.

Say hello to Mark()

Mark.js is a JavaScript keyword highlighter function. You can use it as pure JavaScript or jQuery plugin.

First let me show the end-result and then we will walk through the changes:

datatables05

Modifications

In order to enable Mark I made chances to the following items:

  • webcontent folder
  • theme
  • custom stylesheet
  • xpage
  • javascript library

In the webcontent folder I included the distributed files. I included only the jQuery plugin as a resource in the Theme document:

<resource>
<content-type>application/x-javascript</content-type>
<href>mark/jquery.mark.js</href>
</resource>

In the custom stylesheet I added a class definition to distinguish the searched term with some highlight:

span.markSearched {
background: yellow;
color: black;
}

In our xpage I added the name attribute for each input element e.g. name=firstname. This attribute will be used as reference to apply the Mark feature.

The main changes take place in the JavaScript. I have written a separate function for it so we do not need to call it on loading:

$(function() {
var db = $(“#persons”).DataTable();
db.destroy();
localStorage.clear();
// Map inputs with columns (nth-child(X))
var inputMapper = {
“firstname”: 1,
“lastname”: 2,
“company”: 3,
“job”: 4
};

// Initialize DataTables
var db = $(“#persons”).DataTable({

stateSave : saveState,
fixedHeader: true,
“language” : {
“lengthMenu” : “Entries per page _MENU_”,
// “info” : “Page _PAGE_ of _PAGES_”,
“infoEmpty” : “No entries found”,
“infoFiltered” : “”
},
scrollY : yScroll,
“ajax” : “api.xsp/Persons” ,
“columns” : [
{
data : “firstname”,
“defaultContent”: “<i>Not set</i>”
},{
data : “lastname”,
“defaultContent”: “<i>Not set</i>”
},{
data : “company”
}
,{
data : “job”
}
],

// Set elements per page
pageLength: 10,
// Disable entry selection
bLengthChange: false,
// Act on table rendering
drawCallback: function() {
// Iterate over all inputs (by mapper names)
$.each(inputMapper, function(colName, index) {
// Determine the entered keyword

var val = $(“input[name='” + colName + “‘]”).val();
// Determine the column related to the input
var $col = $(“.datatables-table tbody tr > td:nth-child(” + index + “)”);
// Remove marks in related column
$col.unmark({
“element”: “span”,
“className”: “markSearched”
});
// Mark in related column
$col.mark(val, {
// Define mark.js options (see https://markjs.io/)
“element”: “span”,
“className”: “markSearched”
});
})
}
});

// Trigger table redraw on search keyword change
$(“input”).on(“keyup change”, function() {
var db = $(“#persons”).DataTable();
var $this = $(this);
var val = $this.val();
var key = $this.attr(“name”);

// Search inside DataTable column
// subtract -1 because :nth-child starts with 1,
// DataTables with 0
db.columns(inputMapper[key] – 1).search(val).draw();
});

});

In the inputMapper variable we define the input fields and their order appearance in the table.

Instead the initComplete callback we will use the drawCallback function instead. The initComplete runs once, the drawCallback runs everytime the table is redrawn, e.g. when we enter search terms. For each input element we take the entered value and match it with values in the corresponding column. When a match is found a span element with the classname markSearched is applied.

Finally we register events for the input elements so if characters are entered or removed the table gets redrawn.

Wrap up

That’s it! I hope you like this feature. I wonder how easily you could do this with a viewPanel control…

 

 

 

Building a search function with DataTables plugin (IV)

Introduction

In the previous posts I demonstrated how to setup your application to generate a datatable component from your Notes view(s) and how to apply filtering & search capabilities to the columns.

In this post I will demonstrate how to build an external form to filter the datatable. I guess in most cases you want to provide some sort of search form and displayed separate from your table (left, right, top). For now we restrict the search form with input boxes.

Modifications

I have applied some modifications to the application. The complete and final code you can view in my Github repository.

Modifications:

  • java class that works as a custom service bean
  • javascript library for initializing datatable
  • xpage to display the datatable

Let’s add a form

The first thing we will do is by adding a Bootstrapped styled form to the xpage:

datatables04pre

Notice the following things:

  • The filter class for the input element
  • The data-column-index attribute for the input element

The filter class will be used to register events on:

$(‘.filter’).on(‘keyup change’, function () {
//clear global search values
db.search(”);
db.column($(this).data(‘columnIndex’)).search(this.value).draw();
});

The data-column-index attribute directs to the index of the corresponding column in the datatable.

With the form in place and the script updated we need to extend our java class so the values for the job title are included:

String job = String.valueOf(columnValues.get(10));
if (null!=job){
jo.put(“job”,job);
}

Let’s see the result

For example if I am looking for a person with firstname starting with Car.., from company Firm (something) acting as a host I get presented:

datatables04

Now with the initial search form in place we will extend it with features to improve it’s usability…

Building a search function with DataTables plugin (III)

Introduction

In the previous post about the datatables jquery component I demonstrated how to create input select controls for each column. In some cases an select control may however not be desired, due to the diversity of the entries in the column (e.g. track number, firstname, date).

Text inputs

In such cases you probably wont to use text inputs where a substring is being matched against the string entries in the column.

To achieve this our function to build the table have to be adapted slightly:

function initPersons(){

$(‘#persons tfoot th’).each( function () {
var title = $(this).text();
$(this).html( ‘<input type=”text” placeholder=”Search ‘+title+'” />’ );
} );

var db = $(“#persons”).DataTable();
db.destroy();
localStorage.clear();
var table = $(“#persons”).DataTable( {
stateSave : saveState,
fixedHeader: true,
“language” : {
“lengthMenu” : “Entries per page _MENU_”,
// “info” : “Page _PAGE_ of _PAGES_”,
“infoEmpty” : “No entries found”,
“infoFiltered” : “”
},
scrollY : yScroll,
“ajax” : “api.xsp/Persons” ,
“columns” : [
{
data : “firstname”,
“defaultContent”: “<i>Not set</i>”
},{
data : “lastname”,
“defaultContent”: “<i>Not set</i>”
},{
data : “company”
}
],
initComplete: function(){
this.api().columns().every( function () {
var column = this;

$( ‘input’, this.footer() ).on( ‘keyup change’, function () {
if ( column.search() !== this.value ) {
column
.search( this.value )
.draw();
}
} );

} );
}
});
}

As a result under each column input boxes which column filter functionality are added underneath the columns:

datatables03

Building a search function with DataTables plugin (II)

Introduction

In a previous post the base foundation was set up and a table was drawn on the xpage with the datatables jquery component. The data, residing in a Notes view, was delivered via a CustomServiceBean Rest service.

A datatable comes out of the box with a little search field which performs a search across the whole json data-set.

Individual column searching

In this post we will be adding select inputs for each column so you have a quick filter mechanism matching the values in the columns.

To achieve this we need to make some changes to the html table and our script to initialize the datatable object.

HTML table

We will place the select inputs on the bottom of the table, in the to be added footer section:

<table id=”persons” class=”table table-striped table-bordered”
cellspacing=”0″ width=”100%”>
<thead>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Company</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Company</th>
</tr>
</tfoot>
</table>

initComplete

In our JS function that initializes the datatable we add a callback function that is triggered when the table is fully loaded:

initComplete: function(){
this.api().columns().every( function () {
var column = this;
var select = $(‘<select><option value=””></option></select>’)
.appendTo( $(column.footer()).empty() )
.on( ‘change’, function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);

column
.search( val ? ‘^’+val+’$’ : ”, true, false )
.draw();
} );

column.data().unique().sort().each( function ( d, j ) {
select.append( ‘<option value=”‘+d+'”>’+d+'</option>’ )
} );
} );
}

Progress

As a result of the activities above our table now looks like:

datatables02

The seach field works in conjunction with the select inputs. Not bad for a few lines of code!

navigationPath & selection properties

Introduction

Once in a while you move back in time and understand why you have forgotten certain rules. Mostly because you have stopped using them or you experience the same pitfalls as others do and you just want to forget them to move ahead.

navigationPath and selection properties

One of those things are the navigationPath from the Application Layout control and the selection property in it’s navigation nodes.

navigationPath

The navigationPath allows you to control the currently selected titlebar Tab and the selection appearance of any Navigator control on the XPage.

selection

The selection property is nothing more than a regular expression used to select an item. The expression is matched against the navigationPath, described above.

selected

Selected is a property that indicates if a node is selected or not. This property often causes confusion.

Mix them together

So if you mix these properties above you can establish some nice usability improvement for your app-user. It can visualize (via style classes) when a certain titlebar should be highlighted or not.

Example Aap Noot Mies

The follwoing example will demonstrate it’s basic feature (and shows some oddness in the Dutch education system while Mies is nothing more than a cat).

As titlebar tabs I have: Aap, Noot and Mies.

As Navigator nodes I have Aap, Noot, Mies and Apes/Gorilla, Apes/Chimp.

Each Navigator links points to an individual XSP but the last two nodes belong to the Aap titlebar tab.

ccLayout

I have a custom control with the application layout control on it. For the navigationPath property I have set up a propery definition so I can adjust it for eac XSP:

<xe:this.configuration>
<xe:oneuiApplication
navigationPath=”#{javascript:compositeData.navigationPath}”

The titlebar nodes are defined as follwed:

<xe:this.titleBarTabs>
<xe:pageTreeNode label=”Aap” selection=”/Aap/.*”
page=”/aap.xsp” selected=”true”>
</xe:pageTreeNode>
<xe:pageTreeNode label=”Noot” selection=”/Noot/.*”
page=”/noot.xsp” selected=”true”>
</xe:pageTreeNode>
<xe:pageTreeNode label=”Mies” selection=”/Mies/.*”
page=”/mies.xsp” selected=”true”>
</xe:pageTreeNode>
</xe:this.titleBarTabs>

Here I have set a regex for the selection property.

Navigator control

On the same custom control I also have a Navigator control:

<xe:navigator id=”navigator1″>
<xe:this.treeNodes>
<xe:pageTreeNode label=”Aap” page=”/aap.xsp”></xe:pageTreeNode>
<xe:pageTreeNode label=”Noot” page=”/noot.xsp”></xe:pageTreeNode>
<xe:pageTreeNode label=”Mies” page=”/mies.xsp”></xe:pageTreeNode>
<xe:basicContainerNode label=”Apes”>
<xe:this.children>
<xe:pageTreeNode label=”Gorilla”
page=”/gorilla.xsp”>
</xe:pageTreeNode>
<xe:pageTreeNode label=”Chimp” page=”/chimp.xsp”></xe:pageTreeNode>
</xe:this.children>
</xe:basicContainerNode>
</xe:this.treeNodes>
</xe:navigator>

Here I do not have to define anything. The control will know itself when a selected node needs to be highlighted.

Gorilla XPage

In the app I have multiple XPages, in the gorilla xpage I have set the navigationPath property as follwed:

<?xml version=”1.0″ encoding=”UTF-8″?>
<xp:view xmlns:xp=”http://www.ibm.com/xsp/core&#8221;
xmlns:xc=”http://www.ibm.com/xsp/custom”&gt;

<xc:ccLayout navigationPath=”/Aap/Gorilla”>
<xp:this.facets>
<xp:image url=”/aap.jpg” id=”image1″ xp:key=”facet_1″></xp:image>
</xp:this.facets>
</xc:ccLayout></xp:view>

As a result the Aap titlebar Tab is highlighted:

titlebarTabs

Not really something that opens your mind but at least I have written it down (with a repo) in the hope I won’t have to re-learn it next time I have to use the application layout control.

 

 

 

Building a search function with DataTables plugin

Introduction

In Domino you have multiple options how to provide a search function for an application. I have seen many examples where a FT search query is build for a database, where the form type is defined, which fields should be used in the search etcetera. Most of them are a maintenaince nightmare, layout is little flexible and usability is lacking (e.g. perform a search towards a query which will result in zero hits).

What if you make it easier to maintain, more flexible for user specific desires and add facetted search principles? A win situation?

DataTables

In the following posts I will write how to use the jQuery DataTables plugin to provide a quick search function for your Domino apps. I assume you use XPages.

Stage 1 – Getting & presenting the data

In the initial stage we just try to get and present the data we need. As example I will be using the FakeNames application. I have created a public repo which has at the moment:

  • an xpage to present the data (dtPersons.xsp)
  • an xpage (api.xsp) which will contain rest services
  • a Java class that generates the data in JSON format
  • a CSJS library to transform the HTML table on dtPersons.xsp to  DataTables object
  • the required resources from DataTables.net
  • a Theme resource to have the resources available
  • a Notes view to use as data source.

In order to create some fake documents I have set up an LS agent to do so.

If you bind this all together, you have already a search function:

datatables01

Right on top you have a search field where you can query the columns in the table. The columns are sortable too. Probably it is matching most of the search functions you have seen for Domino. But wait! There is more possible…

 

JavaScript Internationalization & placeholders

Introduction

Some time ago I wrote about moving a Teamroom application up to IBM Bluemix. This month the XPages runtime is now general available.

In the post I mentioned I would come back on some design gotchas I saw while analyzing and preparing the Teamroom application for staging it to Bluemix. So here is (at least9 a first post.

What I noticed for example was the usage of the l18n library in SSJS and placeholders in properties files.

i18n formatting

This is a server script library with methods to format messages for localization, display dates in a locale-specific manner and miscellaneous Internationalization utilities.

So how does this works?

Create two or more strings properties file e.g.

  • strings.properties
  • strings_sv.properties

The strings.properties file content:

compliment=Handsome

hello.world=Hello {0}

The strings_sv.properties file content:

compliment=Snygging

hello.world=Hejsan {0}

Now create an XPage and place e.g. a Computed Field control on it and calculate it’s value:

<xp: text><xp: this.value><![CDATA[#{javascript:compliment = strings.getString(“compliment”);

return i18n.format(strings.getString(“hello.world”), compliment);}]]></xp: this.value></xp: text>

(Do not forget to enable Internationalization for your application in the Application Properties under section International Options)

Placeholders

Notice in the properties file the usage of a placeholder: hello.world=Hello {0} and how it is being referred: strings.getString(“hello.world”), compliment. Here the variable compliment (another string property value) is put in the placeholder.

As a result in the default language I get returned “Hello Handsome” and when I choose Swedish as default language in my browser I get returned “Hello Snygging”.

By wrapping it and formatting it with the L18n library you ensure it is fitted according the active language. This is very handy for dates, amounts etcetera.

Links of interest

More details about JavaScript Internationalization can be found in the IBM Notes Domino Application Development wiki.