query calender list so user cant book same date and time

Question

Update

var context= SP.ClientContext.get_current();
var web = context.get_web();
var list = web.get_lists();
var targetList;

function createItem() {
    targetList =  list.getByTitle('AppbokningarList');

    var listItemCreateInfo = new SP.ListItemCreationInformation();
    this.newItem = targetList.addItem(listItemCreateInfo);

    var listItemTitle = document.getElementById('Textrubrik').value;
    alert(listItemTitle);

    var listItemCustom = document.getElementById('datepicker').value;
    alert(listItemCustom);

    var listItemFromTime = document.getElementById('timepicker').value;
    alert(listItemFromTime);

    var listItemtoDate = document.getElementById('datepickerto').value;
    alert(listItemtoDate);

    var listItemToTime = document.getElementById('timepickerTo').value;
    alert(listItemToTime);

    var listItemBeskrivning = document.getElementById('Textbeskrivning').value;
    alert(listItemBeskrivning);

    newItem.set_item('Title', listItemTitle);
    var result = listItemCustom + " " + listItemFromTime;
    newItem.set_item('EventDate', result);
    var result2 = listItemtoDate + " " + listItemToTime;
    newItem.set_item('EndDate', result2);
    newItem.set_item('Description', listItemBeskrivning);

   var camlQuery = new SP.CamlQuery(),
    collListItem, strViewXml;

    strViewXml = "<View><Query><OrderBy><FieldRef Name='EventDate'/></OrderBy><Where><And><Lt><FieldRef Name='EndDate'/>";
    strViewXml += "<Value Type='DateTime'>" + result + "</Value></Lt><Gt><FieldRef Name='EventDate'/>";
    strViewXml += "<Value Type='DateTime'>" + result2 + "</Value></Gt></And></Where></Query></View>";

    camlQuery.set_viewXml(strViewXml);
    collListItem = targetList.getItems(camlQuery);

    context.load(collListItem);
    if (collListItem.get_count() > 0) {

    }

    newItem.update();
    context.load(newItem);


    context.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

}
function onQuerySucceeded() {
    alert('Item Created: ');

}

function onQueryFailed() {
    alert('Item Failed: ');
}

Answer 1

First, the order you're attempting to perform the check vs the item creation in needs modified. You either need to check if a record will be valid, then submit it with separate actions -- or use something like an Exception Handling Scope to detect if the new item would violate your rules as it is being created. I think performing each action separately is probably the simplest method to explain and implement.

Second, you should try cleaning your code up first to solve some basic sources of problems:

• Your ItemCreationSuccess method only sets the text of some element with an id of message to the empty string which could be why you're not seeing any alerts or other behavior
• You also have a some scoping issues: you're setting this.collListItem equal to targetList.getItems(camlQuery) but then you call context.load on an item called collListItem -- this.collListItem is not the same object as collListItem in your function. I would recommend reading up on some good answers about this on StackOverflow as well as some on implied global variables to better explain what happens here.

Now let's tackle the first part of your problem; understanding if an item already exists that would overlap with a new request -- I used DateTime valued fields (like in the default SharePoint calendar list type) so that I can use numerical comparisons (greater than or less than) and highly suggest you do also instead of using text fields; you can convert them back and forth as you submit or display them on the page in your app.

function checkAvailabilityAsync(ctx, targetList, fromDate, toDate) {
    // consider adding and returning a $.Deferred() object if you are using jQuery
    var frDate = new Date(fromDate),
        tDate = new Date(toDate),
        camlQuery = new SP.CamlQuery(),
        collListItem, strViewXml;

    strViewXml = "<View><Query><OrderBy><FieldRef Name='EventDate'/></OrderBy><Where><And><Lt><FieldRef Name='EndDate'/>";
    strViewXml += "<Value Type='DateTime'>" + tDate.toISOString() + "</Value></Lt><Gt><FieldRef Name='EventDate'/>";
    strViewXml += "<Value Type='DateTime'>" + frDate.toISOString() + "</Value></Gt></And></Where></Query></View>";

    camlQuery.set_viewXml(strViewXml);
    collListItem = targetList.getItems(camlQuery);
    ctx.load(collListItem); 

    ctx.executeQueryAsync(
            // anonymous success handler
            function() {
                // you don't need to use 'createDelagate' here because the variables
                // in the outer function are available here
                if (collListItem.get_count() > 0) {
                    console.log('there is already an event on this day');
                } else {
                    console.log('go ahead and create the event the user wants')
                }
                // resolve the jQuery.Deferred here if implemented
            },
            // anonymous failure handler
            function(sender, args) {
                alert("There was a problem running checkAvailability: \n" + args.get_message());
                // reject the jQuery.Deferred here if implemented
            }
    );
    // return the jQuery.Deferred.promise() here if implemented
}

Then to call this you would do something maybe in a separate function like this:

(function() {
    var myCtx = SP.ClientContext.get_current(),
        myList = myCtx.get_web().get_lists().getByTitle("Calendar"),
        // notice 24 hour time format which is probably what you'll prefer
        fromDate = "11 Nov 2014 14:00",
        // or you can specify AM/PM like this
        toDate = "11 Nov 2014 3:00 PM";

    checkAvailabilityAsync(myCtx, myList, fromDate, toDate);
}());

Something to remember here, the new Date() constructor used in the checkAvailability function accepts many different types of date strings, so play around with it some in your browser dev tools javascript console to get a feel for which format you like best.

Anyway, I acknowledge that this isn't a complete solution, but I think this will get you headed in the right direction.

I also highly recommend taking some of the JavaScript classes on Code School (paid after the first few) or MS Virtual Academy (free -- particularly recommend "Javascript Fundamentals for Absolute Beginners")

Answer 2

John-M gives a good outline. After a lot of searching, I was able to use the code below to query a list and check if a time slot was already booked.

Here are the specifics for ensuring the timestamp is included in your CAML query.

    (function() {

            // using jQuery get the different datetime values on the form
            var dateValue = $($("input[id^='EventDate']")[0]).val();
            var hourValue = $($("select[id$='_$DateTimeFieldDateHours']")[0]).val();
            var minuteValue = $($("select[id$='_$DateTimeFieldDateMinutes']")[0]).val();

            var dateTimeValue = dateValue + " " + hourValue + ":" + minuteValue + ":00";
            var fromDate = String.format('{0:yyyy}-{0:MM}-{0:dd}T{0:HH:mm:ss}Z', new Date(dateTimeValue));
            // do the same 5 lines above for your ending datetime field
            // OR say you want to make appointments 30 min long
            var myDateTimeField = new Date(Date.parse(dateTimeValue));
            var myEstEndMinutesField = new Date(Date.parse(myDateTimeField) + 30*60*1000);
            var toDate = String.format('{0:yyyy}-{0:MM}-{0:dd}T{0:HH:mm:ss}Z', new Date(myEstEndMinutesField));

        checkAvailabilityAsync(myList, fromDate, toDate);
    }());

    function checkAvailabilityAsync(targetList, frDate, tDate) {

    var viewXml = { 
       viewXml: "<View>" +
                "<Query>" + 
                "<OrderBy><FieldRef Name='EventDate'/></OrderBy>" +
                "<Where><And><Leq>" + 
                "<FieldRef Name='EndDate'/>" + 
                "<Value Type='DateTime' IncludeTimeValue='True'>" + tDate + "</Value>" +
                "</Leq><Geq>" +
                "<FieldRef Name='EventDate'/>" +
                "<Value Type='DateTime' IncludeTimeValue='True'>" + frDate + "</Value>" +
                "</Geq></And></Where>" +
                "</Query></View>"
    }

    var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/lists/getbytitle('"+targetList+"')/GetItems(query=@v1)?@v1=" + JSON.stringify(viewXml);
    $.ajax({
            url: requestUri,
            type: "POST",
            datatype: "json",
            headers: {
                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                 "Accept": "application/json; odata=verbose",
                 "Content-Type": "application/json; odata=verbose"
            },

            // success handler
            success: function() {
                // in the outer function are available here
                if (data.d.results.length > 0) {
                    console.log('there is already an event on this day');
                } else {
                    console.log('go ahead and create the event the user wants')
                }
                // resolve the jQuery.Deferred here if implemented
            },
            // failure handler
            error: function(sender, args) {
                alert("There was a problem running checkAvailability: \n" + args.get_message());
                // reject the jQuery.Deferred here if implemented
            }
    );
    // return the jQuery.Deferred.promise() here if implemented
}

Here are some helpful source articles I've found during my search:

• This article explains why you cannot use toISOString() and how to use Today or Now in your CAML query.
• Making CAML query with REST API