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