0

Am looking to move our meeting room schedules onto SP 2010. I've created a choice column with the options as the meeting room names.

Is there a way that if a meeting room is already booked for the time the user is trying to add, that it will advise them and tell them they need to book a different time or a room?

Again, many thanks in advance to the great help from this site.

1 Answer 1

0

There is no out of the box way to handle this scenario. There are two way you can solve this

  1. Using JavaScript and JSOM and figure out if any entries exists
  2. By creating an Event Receiver and cancelling the item

For the second option I found this link which can help you https://blogs.technet.microsoft.com/sharepointdevelopersupport/2012/08/01/how-to-not-allow-a-user-to-add-multiple-items-of-a-meeting-at-the-same-time-date-in-a-calendar-list-for-a-user-in-sharepoint-2010/

I am adding the code here for easy reference

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace doublemeeting
{
    public class PreventDoubleBooking : SPItemEventReceiver
    {
        /// <summary>
        /// This event is triggered when the user adds a new item
        /// </summary>
        /// <param name="properties"></param>
        public override void ItemAdding(SPItemEventProperties properties)
        {
            //Our query string variable
            string strQuery = null;

            try
            {
                //Get the Sharepoint site instance
                using (SPWeb oWebsite = new SPSite(properties.SiteId).OpenWeb(properties.RelativeWebUrl))
                {

                    //Get the collection of properties for the Booking item
                    SPListItemCollection collItems = oWebsite.Lists[properties.ListTitle].Items;

                    //Get the Calendar List that we will be querying against
                    SPList calendar = oWebsite.Lists[properties.ListId];

                    //Get the internal name of the fields we are querying. 
                    //These are required for the CAML query
                    string start_internal = collItems.List.Fields["Start Time"].InternalName;
                    string end_internal = collItems.List.Fields["End Time"].InternalName;
                    string Category_Internal = collItems.List.Fields["Category"].InternalName;

                    //Get the query string parameters
                    string start_str = properties.AfterProperties[start_internal].ToString();
                    string end_str = properties.AfterProperties[end_internal].ToString();
                    string Category_str = properties.AfterProperties[Category_Internal].ToString();

                    //Construct a CAML query
                    SPQuery query = new SPQuery();

                    //Create the CAML query string that checks to see if the booking we are attemping
                    //to add will overlap any existing bookings
                    strQuery = string.Format(@"

    <Where>

        <And>
            <Or>

                <Or>
                    <And>
                       <Leq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Leq>

                       <Gt>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Gt>
                    </And>

                    <And>
                       <Lt>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Lt>

                       <Geq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Geq>
                    </And>
                </Or>

                <Or>
                    <And>
                       <Leq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Leq>

                       <Geq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Geq>
                    </And>

                    <And>
                       <Geq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Geq>

                       <Leq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Leq>
                    </And>
                </Or>

            </Or>

            <Eq>
                <FieldRef Name='Category' />
                <Value Type='Choice'>{2}</Value>
            </Eq>

        </And>

    </Where>
    <OrderBy>
        <FieldRef Name='EventDate' />
    </OrderBy>
", start_str, end_str, Category_str);

                    //Set the query string for the SPQuery object
                    query.Query = strQuery;

                    //Execute the query against the Calendar List
                    SPListItemCollection existing_events = calendar.GetItems(query);

                    //Check to see if the query returned any overlapping bookings
                    if (existing_events.Count > 0)
                    {
                        //Cancels the ItemAdd action and redirects to error page
                        properties.Cancel = true;

                        //Edit the error message that will display on the error page
                        properties.ErrorMessage += "This booking cannot be made because of one or more bookings in conflict. <BR><BR>";

                        //Here you can loop through the results of the query
                        //foreach (SPListItem oListItem in existing_events)
                        //{
                        //   ....
                        //}

                        properties.ErrorMessage += "Please go back and schedule a new time.";
                    }

                }
            }
            catch (Exception ex)
            {
                //Cancels the ItemAdd action and redirects to error page
                properties.Cancel = true;

                //Edit the error message that will display on the error page
                properties.ErrorMessage = "Error looking for booking conflicts: " + ex.Message;
            }

        }

        /// <summary>
        /// This event is triggered when the user edits an calendar item
        /// </summary>
        /// <param name="properties"></param>
        public override void ItemUpdating(SPItemEventProperties properties)
        {

            string strQuery = null;

            try
            {

                //Get the Sharepoint site instance
                using (SPWeb oWebsite = new SPSite(properties.SiteId).OpenWeb(properties.RelativeWebUrl))
                {

                    //Get the collection of properties for the Booking item
                    SPListItemCollection collItems = oWebsite.Lists[properties.ListTitle].Items;

                    //Get the Calendar List that we will be querying against
                    SPList calendar = oWebsite.Lists[properties.ListId];

                    //Get the internal name of the fields we are querying. 
                    //These are required for the CAML query
                    string start_internal = collItems.List.Fields["Start Time"].InternalName;
                    string end_internal = collItems.List.Fields["End Time"].InternalName;
                    string Category_Internal = collItems.List.Fields["Category"].InternalName;
                    string guid_internal = collItems.List.Fields["GUID"].InternalName;

                    //Get the query string parameters
                    string start_str = properties.AfterProperties[start_internal].ToString();
                    string end_str = properties.AfterProperties[end_internal].ToString();
                    string Category_str = properties.AfterProperties[Category_Internal].ToString();
                    string guid_str = properties.AfterProperties[guid_internal].ToString();

                    //Construct a CAML query
                    SPQuery query = new SPQuery();

                    //Create the CAML query string that checks to see if the booking we are attemping
                    //to change will overlap any existing bookings, OTHER THAN ITSELF
                    strQuery = string.Format(@"

    <Where>
        <And>

            <And>
                <Or>

                    <Or>
                        <And>
                           <Leq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Leq>

                           <Gt>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Gt>
                        </And>

                        <And>
                           <Lt>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Lt>

                           <Geq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Geq>
                        </And>
                    </Or>

                    <Or>
                        <And>
                           <Leq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Leq>

                           <Geq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Geq>
                        </And>

                        <And>
                           <Geq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Geq>

                           <Leq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Leq>
                        </And>
                    </Or>

                </Or>

                <Eq>
                    <FieldRef Name='Category' />
                    <Value Type='Choice'>{2}</Value>
                </Eq>

            </And>

            <Neq>
                <FieldRef Name='GUID' />
                <Value Type='GUID'>{3}</Value>
            </Neq>

        </And>

    </Where>

    <OrderBy>
        <FieldRef Name='EventDate' />
    </OrderBy>
", start_str, end_str, Category_str, guid_str);

                    //Set the query string for the SPQuery object
                    query.Query = strQuery;

                    //Execute the query against the Calendar List
                    SPListItemCollection existing_events = calendar.GetItems(query);

                    //Check to see if the query returned any overlapping bookings
                    if (existing_events.Count > 0)
                    {

                        //Cancels the ItemAdd action and redirects to error page
                        properties.Cancel = true;

                        //Edit the error message that will display on the error page
                        properties.ErrorMessage += "This booking cannot be made because of one or more bookings in conflict. <BR><BR>";

                        //Here you can loop through the results of the query
                        //foreach (SPListItem oListItem in existing_events)
                        //{
                        //   ....
                        //}

                        properties.ErrorMessage += "Please go back and schedule a new time.";
                    }

                }
            }
            catch (Exception ex)
            {

                //Cancels the ItemAdd action and redirects to error page
                properties.Cancel = true;

                //Edit the error message that will display on the error page
                properties.ErrorMessage = "Error looking for booking conflicts: " + ex.Message;
            }

        }

    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.