Introduction
Recently, we had a client who wanted an interactive form to enter leave request information and dates. In this form, the user must be able to select multiple ranges of dates, and the form must calculate the number of working days excluding weekends and holidays.
The solution we came up with used JavaScript and jQuery to do some simple lookups and calculations. Let’s jump right into this challenge, and build it step-by-step from scratch!
First Steps
Recording the date ranges is easily done by using a Repeating Section control in the form like so:
Set both the Name and CSS Class of the date pickers to dateFromPicker and dateToPicker. To calculate the days between dates in individual rows, we can use a Calculated Value control with the Name and CSS Class set to days with the formula dateDiffDays(dateToPicker, dateFromPicker) + 1 like so:
We can also add a total days Calculated Value control with formula sum(days) to the bottom of the repeater section, such that we have this:
Add a Total Days and Days Off label, clean things up a bit, and then publish or preview the form. You should have a working form where you can calculate days between ranges, and see the total number of days:
So now we have a working form, but we want to polish it up a bit.
First, lets remove the ugly borders from the repeater section:
Secondly, lets remove the alternating background color scheme that is applied appears when we have multiple rows in our repeater section:
Finally, we notice that the days column defaults as 1, even when there is no dates selected. Therefore, we can expand our days calculation to default to 0 when any of the date picker fields are empty using the formula If(or(isNullOrEmpty(dateFromPicker), isNullOrEmpty(dateToPicker)), 0, dateDiffDays(dateToPicker,dateFromPicker) + 1)
Our form now looks like this:
Advanced Steps – Counting Weekends And Holidays
The previous steps have been relatively straightforward. We now have a clean looking form that figures out how many days are between 2 dates. However, the days calculated does not take into account weekends or holidays:
We will first replace the dynamic Calculated Value days control with a simple Label control that we can write to. Set the new label’s Name and CSS Class to days like before.
To subtract weekends from 2 dates, we pass the value from the date fields into a JavaScript function that loops through the dates and checks if it is a weekend, and then returns the number of weekend days. Instructions on how to insert these scripts will be provided later.
Let’s first have a look at the getAllWeekendDays and isWeekendDay functions:
function getAllWeekendDays(date1, date2){
// Initialize date objects and variables
var date1Obj = new Date(date1);
var date2Obj = new Date(date2);
var numWeekendDays = 0;
// Loop over the days between the date range
while(date1Obj <= date2Obj){
// Add one to numWeekendDays if the day is a weekend
if(isWeekendDay(date1Obj)) numWeekendDays += 1;
// Get next day
date1Obj.setDate(date1Obj.getDate() + 1);
}
return(numWeekendDays);
}
function isWeekendDay(dateToCheck){
var dateObj = new Date(dateToCheck);
// The Date.getDay method returns 5/6 for Saturdays/Sundays
if(dateObj.getDay() == 5 || dateObj.getDay() == 6) return true;
return false;
}
To get the number of holidays between 2 dates, we first need to define which days are holidays. We first create an OOTB SharePoint Calendar list named Holidays in the same site as the Nintex form, and then add some holidays to it:
Then, in your form use this JavaScript function to get the holiday days:
function numberOfVacationAndWeekendDays(date1, date2){
// Use Deferred Object to register callbacks of sync/async functions
this.d = $.Deferred();
this.Date1 = date1;
this.Date2 = date2;
// Prepare the list query to use on the Calendar list
var query=@””
+ “”
+ date1
+ “”
+ date2
+ “”;
// Get the client context
var clientContext = new SP.ClientContext();
// Get the SharePoint list named ‘Holidays’ from the current site
var oList = clientContext.get_web().get_lists().getByTitle(‘Holidays’);
// Prepare the final list query
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml(“” + query + “”);
// Query the list using defined query in an asynchronous call
this.listItems = oList.getItems(camlQuery);
clientContext.load(listItems);
clientContext.executeQueryAsync(Function.createDelegate(this, queryOnSuccess), Function.createDelegate(this, queryOnError));
return this.d.promise();
}
function queryOnSuccess(sender, args){
// Count the number of calendar list items found between the date ranges
this.numDays = this.listItems.get_count();
currentHolidayNum = this.numDays;
var listItemEnumerator = this.listItems.getEnumerator();
var alertMsg = “”;
// Loop through all items found
while (listItemEnumerator.moveNext()) {
var oListItem = listItemEnumerator.get_current();
var eventDate = oListItem.get_item(‘EventDate’);
var allDay = oListItem.get_item(‘fAllDayEvent’)
if(allDay){
// If an item is fAllDayEvent = true, then the date returned is not in a ‘standard’ form
// We must use the toISOString method to convert the date to ISO standard
eventDate = eventDate.toISOString();
}
// If this holiday is on a weekend, subtract 1 so it won’t get counted twice
if(isWeekendDay(eventDate)){
currentHolidayNum -= 1;
}
}
// Get all weekend days between the input dates
var numWeekendDays = getAllWeekendDays(this.Date1, this.Date2);
// Return all holidays (that are not on weekends) + all weekends
this.d.resolve(currentHolidayNum + numWeekendDays);
}
function queryOnError(sender, args){
// Show error message if query fails
this.d.reject(“An error occurred!”);
alert(“Error encountered while trying to calculate the number of holidays and weekend days.”);
}
Credits to Ryan McCarney for generously providing the scripts above. Thank you!
Advanced Steps – Attaching Custom Events To Nintex Date Pickers
To attach scripts to your Nintex form, the recommended way is to save the script to a .js file, and then uploading them to your site assets folder like so:
Then, in your form’s settings, reference the uploaded files like so:
Now, we have the code saved to file and referenced from our forms. The next step is to attach the functions in the scripts to our date picker controls. This is so that when a user selects dates from the popup calendar, an asynchronous event fires and attempts to retrieve the number of weekends and holidays and update the days column appropriately.
To do this, we have to modify the default date picker controls and append our function calls to it’s onSelect attribute. After some digging around, I found that the date picker control is initialized by Nintex by taking the nfFillerLoadFunctions stack and popping the first item out, which contains the function to create date picker objects.
Therefore, we can ‘hijack’ this stack by popping out and discarding the default date picker control, and then inserting our own:
// Our own datepicker initializer function, copied from the default Nintex date picker with modification to the onSelect property
function nfInitDatePicker() {
NWF$(“.nf-date-picker”).datepicker({
showOn: “button”,
buttonImage: “/_layouts/15/images/calendar.gif”,
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
showOtherMonths: true,
selectOtherMonths: true,
showButtonPanel: true,
dateFormat: “m/d/yy”,
nextText: “Next”,
prevText: “Prev”,
buttonText: “Select a date.”,
currentText: “Today”,
closeText: “Done”,
monthNamesShort: [“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”],
monthNames: [“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”],
isRTL: false,
dayNames: [“Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”],
dayNamesMin: [“Su”, “Mo”, “Tu”, “We”, “Th”, “Fr”, “Sa”],
dayNamesShort: [“Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”],
firstDay: 0,
onSelect: function() {
var that = NWF$(this);
this.fireEvent && this.fireEvent(‘onchange’) || that.change();
if (that.blur) (that.blur());
// Trigger event to calculate leave days
DatesAddedOrChanged($(this).closest(“.nf-repeater-row”).index());
},
beforeShow: function(input, inst) {
}
});
// If the date control DOM element disabled, then diable the datecontrol functionality
// This is specially when the control is disabled by default
var dateControls = NWF$(‘.nf-date-picker:disabled:visible’);
var dateControlsCount = dateControls.length;
for (k = dateControlsCount – 1; k >= 0; k–) {
NWF$(dateControls[k]).datepicker(‘disable’);
}
}
NWF.FormFiller.Events.RegisterAfterReady(function (){
nfFillerLoadFunctions.pop(); // Discard the default datepicker initializer
nfFillerLoadFunctions.push(nfInitDatePicker); // Insert our own datepicker initializer
});
function DatesAddedOrChanged(repeaterRowIndex){
// Get date from and date to from the current row
var dateFrom= NWF$(“.dateFromPicker:eq(” + repeaterRowIndex + “) input”).val();
var dateTo= NWF$(“.dateToPicker:eq(” + repeaterRowIndex + “) input”).val();
// Get total number of holidays and weekends
var numHolidaysAndWeekend = numberOfVacationAndWeekendDays(dateFrom, dateTo);
// Set the value of the ‘days’ label control
numHolidaysAndWeekend.done(function(result) {
// Subtract holidays + weekends from total days. Please implement your own dateDiff() function: https://stackoverflow.com/questions/3299972
// See (1) below for more details
var totalOffDays = dateDiff(dateFrom, dateTo) – result;
if (totalOffDays <= 0) {
NWF$(“.days:eq(” + repeaterRowIndex + “) input”).val(0);
}
else {
NWF$(“.days:eq(” + repeaterRowIndex + “) input”).val(totalOffDays);
}
});
numHolidaysAndWeekend.fail(function(result) {
var error = result;
console.log(error);
//handle errors
alert(“Error encountered while trying to calculate the number of holidays and weekend days.”);
});
}
Wrapping Things Up
The above solution will work nicely when you need to check a range of dates for certain values like holidays and weekends. With some additionally programming, you could modify it further to check other things like if someone from the same department is already on vacation, or “black out” regions where managers do not allow vacation to be taken. You could then decide to show these restricted days as an alert to the user, cause the date fields to fail validation with an error message, or just automatically deduct the days and append a comment to an additional comments field.
If you want to take it another step further, you could calculate the number of vacation days accrued since the user started work up to the vacation dates, and see if the requested days would be more than what was accrued. You can store/retrieve the employee’s employment date in a SharePoint list or BCS connected external list, query a HR system’s data using a web service call in jQuery, or query an AD source using a LDAP query. Choosing which source to use depends entirely on your company’s data infrastructure and what would be the most cost effective to implement. The possibilities are endless when using the JSOM together with JS and jQuery.
(1) In our delivered solution for our North American client, we also added code to check what locale the user is in so that we can properly initialize date objects from ambiguous text inputs. We do this because American users tend to place months before days, while Canadians gravitate towards days then months. Checking the locale is important so that we capture the right dates and do the right calculations. We also have additional code to do field validation, and to deduct dates in a way that accounts for leap years and different time zones. I have left these code snippets out for brevity but let me know in the comments if these are important to you and I can start a new post or put them here!
In my next post, I will talk about presenting SharePoint forms in a way that allows users to print it cleanly to paper. I will also show how to add friendly “Print” links/buttons in SharePoint lists, and how to link directly to forms with added custom messages and also pop-up the print prompt.
Written By:
Softlanding
More By This Author
