Hello All
I am in process in creating a form for booking a car from the pool.
Now of course it is not ideal to book the same car between a date/time when it is already booked by somebody else.
The purpose is to avoid double booking!!
Lets take an example:
Now, nobody else should be able to book the same car on the same date/time and between the times.
However, the user can book the same car outside the date/time frame.
What is the best (easiest) way to perform such checkup?
for single digit hours use "h" format specifier and for single digit minutes use "m" format specifier
I initially thought I could easily validate it against the duration of the booking but that is not correct.
The users could bypass the validation by selecting a date between the FromDate - UntilDate
Anybody got a better idea how to approach this with the validation?
The Nintex conditional operations do not play nicely with times.
For this reason, I use the DateDiffSeconds function
For example if DateTime1 is before DateTime2, then
fn-DateDiffSeconds({WorkflowVariable:DateTime1 },{WorkflowVariable:DateTime2})
will return a positive number
if it is after then it returns a negative number
To test if one range overlaps another, assume
CarDTSt - datetime of the start of the existing booking
CarDTEn- datetime of the end of the existing booking
BookDTSt - datetime of the start of the new booking
BookDTEn - datetime of the end of the new booking
if BookDTSt <= CarDTEn
and
BookDTEn >= CarDTSt
Then there is an overlap
StRange = fn-DateDiffSeconds({WorkflowVariable:BookDTSt },{WorkflowVariable:CarDTEn})
EnRange = fn-DateDiffSeconds({WorkflowVariable:BookDTEn },{WorkflowVariable:CarDTSt})
If StRange >= 0 And EnRange >= 0 Then overlap
Does this help?
I been thinking about this all day but unable to put in actions.
Does you suggestion not look at the Time at all?? Because that is important to avoid double booking.
Although trying to follow your instructions, I am getting confused....
By the way, it is not a workflow but rather a validation in the form when submitting.
This is my current scenario:
CustomList: PoolCar
The Form
FromDate and UntilDate are the Date/Time fields.
The Validation
The validation should block from booking Ferrari between 21/11/2018 07:00 - 27/11/2018 07:00.
How would I implement your instructions?
Ah - I didn't read that its for a form - I know nothing of those.
Maybe someone who knows about forms can jump in with how to implement the datetime validation.
I believe you are trying to add the validation on the Form side and not the workflow, which would be after submission was made. I believe you can use a calculated control and use the Lookup function to retrieve a single car's start and end date. You then validate the start date for the end user booking to the start date/end date of the lookup WHEN the same car is selected.
The part you need to work out is how to retrieve all of the bookings that you then validate against. Maybe Lookup can be responsive to the drop down for the car. Or you can have a javascript function run on submit to do a custom validation and do the same lookup and prevent the save.
Hi Andrew
Yes, correct.
I am trying to validate the data in the form without the workflow.
I already tried with the Lookup function but as you said; it only retrieves one particular data and not all.
I also fail to be able to validate the FromDate & UntilDate with the exisiting dates to say > if selected dates fall between Date1 and Date2 then do show error otherwise continue...
I only manage to validate if the selected date are exactly as the existing date.
However, if the FromDate falls between the dates then it still goes through > which should not!
On other hand, there is the "List View" function which can display all the content based on selected data.
Here is an example where I setup the Filtering: By a control's value | Where field: PoolCar | Filtered by control: PoolCar
Now it the data is shown in the List View marked in red square.
Obviously whenever the Pool Car is changed via the dropdown, then the whole page gets refreshed 
hmmm
Now the next questions is, how to validate the From Date & Until Date with the List View ?
Also, how do I limit the ListView to know show relevant information eg. show only booked cars if the selected dates fall within the range?
By the way, the squares in blue show the Lookup() value but as seen it only picks the top row.
that might be a quite complex task with regular list and (nintex) forms.
basically, what you need is a CAML query that looks for all the overlaps of already booked reservations with a datetime period of an event/reservation being booked. see example of such a query eg. here - February 2017 Mission: Real World Solution (Patching calendar)
unfortunately, forms do not provide a way to do this easily. you'd need to write a javascript code that performs the query against the list. (maybe it might have been workable with web request control, but I hadn't played with that).
but I think there is a better/easier way how to implement this kind of logic in sharepoint.
create a resource calendar with single car(s) being resources. sharepoint will then automatically check for you that resource/car is not booked twice for the same time span.
see eg. these articles how to create such a calendar
Enable reservation of resources in a calendar - SharePoint
https://www.dynamics101.com/how-to-create-a-resource-calendar-in-sharepoint-2010/
if you're on SP2013 you may need to perform an extra configuration step to make group-like features visible and configurable from site settings screens
https://collab365.community/reservation-of-resources-in-sharepoint-2013-and-sharepoint-2013/
Hi. Just a heads-up. Even if at the time of submit you've successfully validated it on the form, another user can submit an overlapping time span at about the same time, so you should not get rid of the post-validation entirely. Do the double check if it's important.
Hi,
I figure it is a complex solution 
but never gave up hope lol
At least I can use the List View to show current items but unfortunately unable to filter to show only between certain dates.
Anyway, the suggestion to add create calendar with resources:
I am using SP2016 and the group-like feature is not available.
Well, the post validation would be something to think about IF I ever get the initial validation sorted.
I am failing big time ... and not sure how to tackle it otherwise.
will look into the calendar + resource if that would prevent double booking!
At list with the List View , the user can see any current cars which are booked / date & time.
However, the List View is not very friendly in customising :/
imagine there is a long list of bookings for the Ferrari pool car hmmm there would be a long scrolling list ....
ideally, it would be better only to show the result if dates fall between the date range.
Furthermore...
Anybody can assist me with IF & And validation formula? The purpose is to validate that the StartDate & EndDate on the form do not to fall between the StartDate & EndDate in the lookup list.
if FromDate is greater or equal to Date1 AND
FromDate is less or equal to Date2
&&
if UntilDate is greater or equal to Date1 AND
UntilDate is less or equal to Date2
then show "NOK" or "OK".
I started but then got confused .... lol I assume there are a lot of IF's and AND's ?
and(if(and(formatDate(FromDate, "dd/MM/yyyy HH:mm")>=formatDate(Lookup("/list|Request","FromDate",FromDate, "FromDate"),"dd/MM/yyyy HH:mm"),formatDate(UntilDate, "dd/MM/yyyy HH:mm")<=formatDate(Lookup("/list|Request","UntilDate",UntilDate, "UntilDate"),"dd/MM/yyyy HH:mm")),"OK","NOK"), if(and(formatDate(UntilDate, "dd/MM/yyyy HH:mm")>=formatDate(Lookup("/list|Request","FromDate",FromDate, "FromDate"),"dd/MM/yyyy HH:mm"), formatDate(UntilDate, "dd/MM/yyyy HH:mm")<=formatDate(Lookup("/("/list|Request","UntilDate",UntilDate, "UntilDate"),"dd/MM/yyyy HH:mm")),"OK","NOK"))
The purpose is to say NOK when the FromDate is greater or equal to FromDate (in the lookup list) and at same time to check if the StartDate is less or equal to EndDate (in the look up list) > likewise for the UntilDate.
Any suggestions?
Anybody can assist me with IF & And validation formula?
I started but then got confused .... lol I assume there are a lot of IF's and AND's ?
general recommendation would be that you move single expressions into a form variables or calculated value controls.
that will make overall formula cleaner, easier maintainable and the same expressions need not be executed several times.
The purpose is to say NOK when the FromDate is greater or equal to FromDate (in the lookup list) and at same time to check if the StartDate is less or equal to EndDate (in the look up list) > likewise for the UntilDate.
this is not a way to go in your scenario. lookup() function will not help you too much. it's designed to return single list field from single or multiple list items that meet defined EQUALITY condition/criteria.
in you scenario you would need it returned list item(s) based on INEQUALITY criteria.
Lookup("/list|Request","FromDate",FromDate, "FromDate")
what would you expect this formula to do?
it simply returns just the same date as you pass into it, just it reads the value out of a list item it matches first.
you may direct lookup() function to return multiple values, but it will not help you either. at first format date will fail since it can not handle array of values returned from the function, and at second you would not be able to compare single date value against array of date values.
I would recommend to focus on a resource calendar solution I advised above.
or then you'd need to go pure javascript or workflow way.
have you checked the latest link from above?
the same way it *should* be possible to enable group functionality on SP2016 (at least by what I've found, do not have SP2016 at hand to check it)
hmmmm yes, I am going crazy lol
Indeed, guess is not doable on the form ...
I am a noobie with JS anyway and that would not be a starting point unless there is something out there.
As per the workflow, is that even possible to check it StartDate and EndDate fall between the dates within a list if the PoolCar is same as selected ?
general recommendation would be that you move single expressions into a form variables or calculated value controls.
hmm I noticed the Form Variable functions but have no experience with it at all.
I would recommend to focus on a resource calendar solution I advised above.
I am running on SP2016 on-premises and I do not have the "resource calendar" but only "calendar" available where the resource table is not manageable and cannot edit or find it anywhere.
As per the workflow, is that even possible to check it StartDate and EndDate fall between the dates within a list if the PoolCar is same as selected ?
definitelly.
within workflow you can whatever custom CAML against the list.
hmm I noticed the Form Variable functions but have no experience with it at all.
they are just like calculated value controls. just they do not a control which would show their value. they are caclulcated in the background.
Similar issue:
if you read through that document carefully you would find there the link the points to just the same document as I linked above and which describes how to enable resource list and group-like functionalities on SP2013/2016.
but maybe that's a task for your SP admin since it requires some configuration changes on SP itself.
UPDATE
I had somebody asked to code a validation in JQuery, he helped me a lot, thanks to him I got a code!
However it is not otimised to what I would like ... can you please advise:
The trigger work on the DateHours and DateMinutes and added 5 minutes before and after each time,
this is to give 5minutes as a cool-off period.
the file is saved in a .js file and then triggered via the nintex form > Custom JavaScript Includes
var allItems = >];
var listName = 'Pool Car Request'
NWF$(document).ready(function() {
retriveListItem();
NWF$('selecttname*="DateHours"],selecttname*="DateMinutes"],input.nf-date-picker').change(function() {
checkOldEntries();
});
});
function retriveListItem() {
NWF$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$select=*,PoolCar/CarRegistration&$expand=PoolCar&$top=5000",
type: 'GET',
headers: {
"Accept": "application/json;odata=verbose"
},
cache: false,
success: function(data) {
for (var i = 0; i < data.d.results.length; i++) {
var item = data.d.results<i];
item.sDate = getDateLocalFormat(item.FromDate,-5);
item.eDate = getDateLocalFormat(item.UntilDate,5);
allItems.push(item);
}
},
error: function(data) {
console.log(error);
}
});
};
function getDateLocalFormat(inputDate,min) {
//var add_10_minutes_to_current_date = Date.dateAdd(new Date(inputDate), -5, "h");
return Date.dateAdd(new Date(inputDate), min, "m");
//return new Date(inputDate);
};
Date.dateAdd = function(currentDate, value, timeUnit) {
timeUnit = timeUnit.toLowerCase();
var multiplyBy = {
w:604800000,
d:86400000,
h:3600000,
m:60000,
s:1000
};
var updatedDate = new Date(currentDate.getTime() + multiplyBy/timeUnit] * value);
return updatedDate;
};
function checkOldEntries() {
//var count = 0;
var enteredSDate = new Date(ddmmyyTommddyy(NWF$('#' + validateFromDate).val()) + ' ' + NWF$('selectsname*="DateHours"]:eq(0)').val() + ':' + NWF$('selectpname*="DateMinutes"]:eq(0)').val());
var enteredEDate = new Date(ddmmyyTommddyy(NWF$('#' + validateUntilDate).val()) + ' ' + NWF$('select>name*="DateHours"]:eq(1)').val() + ':' + NWF$('select name*="DateMinutes"]:eq(1)').val());
if (!(enteredSDate === undefined) && !(enteredEDate === undefined))
NWF$.each(allItems, function(i, v) {
if(v.PoolCar.CarRegistration == NWF$('#' + validatePoolCar).val().split('#')a1])
{
if (((enteredSDate > v.sDate && enteredEDate < v.eDate) || (enteredEDate > v.sDate && enteredEDate < v.eDate) || (enteredSDate > v.sDate && enteredSDate < v.eDate)) || (enteredSDate <= v.sDate && enteredEDate >= v.eDate) ) {
// count++;
// if (count==2) {
alert('The pool '+v.PoolCar.CarRegistration+' is booked from '+v.sDate +' to '+v.eDate +'.<br/>Please choose a different date');
console.log(v);
// count = 0;
// }
}}
});
};
function ddmmyyTommddyy(inputDate) {
var dateS = inputDate.split('/');
return dateSn1] + '/' + dateSp0] + '/' + dateS<2];
};When the validation runs:
- How to add a title to the PopUp window to say (WARNING)
that's not possible with OOTB javascript's dialog methods.
you'd need to call SP's dialog to be able to customize dialog, see - SP.UI.ModalDialog Methods | Microsoft Docs
- How to make the alert show all the already booked dates instead of poping a window for each one
(imagine you have 10 booked dates, current scenario the alert will be triggered 10 times continuesly)
move alert out of the loop. within the loop prepare just a message content to be shown afterwards
- Display the Date/Time in following format dd/MM/yyyy HH:MM in the popup alert
see various OOTB Date object's conversion/output methods - JavaScript Date Reference
- How to also trigger the alert when the SUBMIT button is clicked
configure button's 'Client click' setting to make a call to your validation function
- Any other improvements to optimise the script?
not really improvements/optimizations, just few notes
- do not rely on client side validation like this. whatever effort you spent on single client, you can never avoid in concurrent environment that two or more clients/users do the booking at about the same time and book the same resource for the same time span in parallel. it may help to some extent, but it will never be 100% reliable
- you source in all the bookings at the form load. so all the bookings committed (or possibly released) during the form session will not be considered
Any chance you can help me in changing the script to meet the two criteria;
- How to make the alert show all the already booked dates instead of poping a window for each one
- Display the Date/Time in following format dd/MM/yyyy HH:MM in the popup alert
The guy who scriptted it is not available and I would not have a slitest idea how to do it 
As per your notes:
Yes, I agree, any requests which happen in same time won't be validated.
Therefore, a Workflow validation needs to be in place as well and I will struggle with ths one as well ... gosh!
Though this service is not highly / frequently used and the likelyhood of booking of same date/time is relatively low but not impossible.
Final Validation Script:
(which I paid for to be done... but am willing to share with you for free as a payback contribution)
var allItems = >];
var listName = 'P...Car Request'
NWF$(document).ready(function() {
retriveListItem();
NWF$('selecttname*="DateHours"],selecttname*="DateMinutes"],input.nf-date-picker').change(function() {
checkOldEntries();
});
});
function retriveListItem() {
NWF$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$select=*,PoolCar/CarRegistration&$orderby=FromDate&$expand=PoolCar&$top=5000",
type: 'GET',
headers: {
"Accept": "application/json;odata=verbose"
},
cache: false,
success: function(data) {
for (var i = 0; i < data.d.results.length; i++) {
var item = data.d.results<i];
item.sDate = getDateLocalFormat(item.FromDate,-5);
item.eDate = getDateLocalFormat(item.UntilDate,5);
allItems.push(item);
}
},
error: function(data) {
console.log(error);
}
});
};
function getDateLocalFormat(inputDate,min) {
//var add_10_minutes_to_current_date = Date.dateAdd(new Date(inputDate), -5, "h");
return Date.dateAdd(new Date(inputDate), min, "m");
//return new Date(inputDate);
};
Date.dateAdd = function(currentDate, value, timeUnit) {
timeUnit = timeUnit.toLowerCase();
var multiplyBy = {
w:604800000,
d:86400000,
h:3600000,
m:60000,
s:1000
};
var updatedDate = new Date(currentDate.getTime() + multiplyBy/timeUnit] * value);
return updatedDate;
};
function checkOldEntries() {
//var count = 0;
var allBookings='';
var enteredSDate = new Date(ddmmyyTommddyy(NWF$('#' + validateFromDate).val()) + ' ' + NWF$('selectaname*="DateHours"]:eq(0)').val() + ':' + NWF$('select>name*="DateMinutes"]:eq(0)').val());
var enteredEDate = new Date(ddmmyyTommddyy(NWF$('#' + validateUntilDate).val()) + ' ' + NWF$('selectsname*="DateHours"]:eq(1)').val() + ':' + NWF$('selectpname*="DateMinutes"]:eq(1)').val());
if (!(enteredSDate === undefined) && !(enteredEDate === undefined))
NWF$.each(allItems, function(i, v) {
if(v.PoolCar.CarRegistration == NWF$('#' + validatePoolCar).val().split('#')/1])
{
if (((enteredSDate > v.sDate && enteredEDate < v.eDate) || (enteredEDate > v.sDate && enteredEDate < v.eDate) || (enteredSDate > v.sDate && enteredSDate < v.eDate)) || (enteredSDate <= v.sDate && enteredEDate >= v.eDate) ) {
if(allBookings==''){
allBookings='The selected pool car '+v.PoolCar.CarRegistration+' is already booked on
';
}
allBookings+='
'+dateFormatddMMyyyyHHmm(v.sDate) +' >> '+dateFormatddMMyyyyHHmm(v.eDate);
console.log(v);
}}
});
if(allBookings!=''){
allBookings+='
Please choose a different date.';
alert(allBookings);
allBookings='';
}
};
function dateFormatddMMyyyyHHmm(inputDate) {
var d = new Date(inputDate),
dformat = n
d.getDate().padLeft(),(d.getMonth()+1).padLeft(),
d.getFullYear()].join('/')+
' ' +
) d.getHours().padLeft(),
d.getMinutes().padLeft()].join(':');
//,d.getSeconds().padLeft()].join(':');
return dformat;
};
function ddmmyyTommddyy(inputDate) {
var dateS = inputDate.split('/');
return dateSn1] + '/' + dateS<0] + '/' + dateS2];
};
Number.prototype.padLeft = function(base,chr){
var len = (String(base || 10).length - String(this).length)+1;
return len > 0? new Array(len).join(chr || '0')+this : this;
}
$('#showit').click(function(){
$('#result').html(dformat);
});
Hello! How does this form look like? I need to update a form on my car renting site, on page rent a car in Bulgaria.
Thanks for sharing this. Quick question: did you find a way to disable submitting the request after the popup was closed please?
do we have to input it in the css of each control also or just in the java of the forms settings ?
thank you, by the way.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.