SPJS Resource Management – No more double booking

I got this request from Cory

Alexander,

I was curious if you’d be interested in brain-storming a javascript idea for SharePoint calendars?
I’d be more than happy to buy you a couple beers to support it… 🙂

Here is what I was looking at doing.

1. Using Javascript, validate a new (or edited) calendar entry prior to save.
2. The save will check for overlapping dates and/or times.
3. The save could (if possible) validate pre-blocked times for either allowing entries or disallowing entries.

Real-world example.

We have an appointment calendar. I envision having within the same calendar (or for the sake of organization) in a seperate calendar,entries of what days and times are allowed to be “booked” and/or entries of days and times that are block from being “booked”

When a user attempts to create a new entry, the “booking” layer is evaluated to see if this new entry violates any of the currently exisiting rules. If it does, a notice is displayed.
Next, the new entry will be evaluated if it violates any currently exisiting entries. If it does, a notice will be displayed.

Do you think this might be possible via Javascript and/or SP Services?

Thanks!


The solution

I have made a solution that lets you set the time period of each week day that a resource (like a meeting room) can be booked. This is done by specifying a choice field (Meeting room) in the setup of this solution, and to add records in the settings list for each selection in the choice field (like Meeting room 1 or Meeting room 2).

This solution will ensure the room can be booked in the available time period only, and will also prevent users from double booking the resource by checking for overlapping time periods before saving the new or edited record.

The user will be presented with informative messages regarding time period or dates out of range, and/or double bookings so they can correct their input before attempting another save.

This solution is designed for both SharePoint 2007 and SharePoint 2010.

Here is a few screenshots before we walk trough the setup
IMG

In this example, Team 1 has booked room 1 for their morning meeting:
IMG

Then Team 2 tries to book the same room for a sales meeting:
IMG

The setup
  1. Download the latest version of “spjs_utility.js” from here
  2. Download the file “SPJS-ResourceManagement.js” and the CEWP code from here
  3. Upload “spjs_utility.js”, “SPJS-ResourceManagement.js”, jQuery and jQueryUI (if you prefer a local copy), to a shared document library, or a folder created in SharePoint Designer. Ensure all user have read access to the location where you put the files.
  4. Change the variables in the top of the CEWP code, and the script src to jQuery, jqueryUI, the jQueryUI css file, “spjs-utility.js” and “SPJS-ResourceManagement.js” to reflect your local copies.
  5. Upload the CEWP code as a txt file to the same location as the other files.
  6. Add a CEWP below the form web part in NewForm.aspx / EditForm.aspx and use the “content link option” to link to the CEWP code you uploaded in the previous step. You may also use the HTML form web part and put the CEWP code in the “source editor” directly.
  7. Enter edit mode by clicking the © below the form in NewForm.aspx.
  8. You may supply a password in the GUI to prevent user from editing the settings.

When you first enter the edit mode, you are prompted to create the configuration list:
IMG
Click “OK” to create it:
IMG
You will never have to hand edit the configuration list, but you find it under “All Site Content” of the site you specify as “SPJSRM_settingsListBaseUrl” in the CEWP code.

This is PARTS of the CEWP code
There are also a chunk of HTML code that must be included. You find the complete code in the download referred above.

<link type="text/css" rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/themes/sunny/jquery-ui.css">
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<script type="text/javascript" src="/test/Scripts/spjs_utility - blog/spjs-utility.js"></script>
<script type="text/javascript" src="/test/Scripts/SPJSResourceManagement/SPJS-ResourceManagement.js"></script>
<script type="text/javascript">
var SPJSRM_listName = "SPJSRM"; // Preferably use the List GUID
var SPJSRM_settingsListBaseUrl = L_Menu_BaseUrl; // The base url of the settings list
var SPJSRM_resourceFieldInternalName = "MeetingRoom"; // the FieldInternalName of the choice field that holds the name of the resources
var timeFormat = 24; // 12 for 12 Hour format or 24 for 24 Hour
// Validation messages
var alreadyBookedMsg = "This resource can be booked between {0} and {1}";
var overlapMsg = "The selected time period is not available. It has been booked by {0} between {1} and {2}.";
var endBeforeStartMsg = "End time cannot be less than or equal to start time.";
var startDateOutOfRange = "{0} is out of range.nnStarting date cannot be prior to {1}.";
var endDateOutOfRange = "{0} is out of range.nEnd date cannot be after {1}.";
</script>

These are the primary variables that needs attention

  • SPJSRM_listName: This is the list GUID or the DisplayName of the list you want this feature to apply to. In SharePoint 2010 you can use the variable “_spPageContextInfo.pageListId”, but for SharePoint 2007 you must find the GUID as described here
  • SPJSRM_settingsListBaseUrl: This is the base url of the settings list. Use “L_Menu_BaseUrl” to refer the current site, or supply another baseUrl to place the configuration list in another site – use “” to refer the root site.
  • SPJSRM_resourceFieldInternalName This is the FieldInternalName of the choice field that holds the name of the resources. Go here to learn how to find the FieldInternalName for a field
  • timeFormat: Use 12 for 12 Hour format or 24 for 24 Hour. Only cosmetics regarding the time as displayed in validation messages.


Buy me a beer!
If you like the solution – buy me a beer!

110 thoughts on “SPJS Resource Management – No more double booking”

  1. Alexander, I have been thinking about a solution like this for a while, but here is my major concern: what if two people try booking the room at the same time? I am afraid they’ll both see the room available, and it’ll result in double booking.

    1. Hi,
      The availability check is done between hitting the save button and the actual post back to the server. I believe a list item is submitted to the DB instantly, thus making it appear as occupied if one has submitted before the other. I cannot guarantee that this is how it actually is though…

      Alexander

  2. Hi Alexander, I followed your steps to create this but no luck, it is still allowing multiple entries for the same time and room, I do not know what I have done incorrectly, all of the JQueries are kept in folder and the links are all correct, I have added the calendar guid also, and the internal name is correct, can you help me :), this would be really useful for our calendars in sharepoint. Am I creating a calendar for this and a resource list? Or just one calendar? Also instructions 7 and 8 I do not get these messages, sorry if I asking a silly question 🙂
    Kris

  3. I am currently developing a SP 2007 calendar as a employee time tracker. PTO, sick time, working from home that sort of thing.

    I was wondering ifi this idea could be adapted to check if the requester has any list items on the same list that overlap, that way if someone has a day set an all day event for a work at home day, then they go to try to put in a new entry for the same day as a sick day, they would get the overlap error?

    1. Hi,
      I do not think I will change this solution to support your scenario, but you could adapt it yourself – by doing the check by “Author” and not “Room”. The query would be something like this:

      <Where>
      <And>
      <Eq>
      <FieldRef Name='Title' /><Value Type='Text'>"+SPJSRM_pageId+"</Value>
      </Eq>
      <Eq>
      <FieldRef Name="Author"/><Value Type="Integer"><UserID Type="Integer"/></Value>
      </Eq>
      </And>
      </Where>
      

      Alexander

    1. Not sure what you mean here, but if you use a third party date picker and it’s not working, I’m afraid you have to look for a fix on your own…

      Alexander

  4. I see your solution valuable but unfortunately our people have so gotten used to reserving meetings using Outlook: They can add the meeting request for a bunch of ppl and just add the meeting room as one person in the list. This is very fast and no additional step of adding the meeting room request in SharePoint is needed.

    For people accustomed to this approach – do you think it would be possible to somehow use jQuery to show combined shared meeting room mailboxes from Exchange servers in SharePoint?

  5. Hi Alexander, got it all working great, it was my fault I had not put “” around the L_Menu_BaseUrl, did this and got the prompt, was able to set the deafault information for each room, now the problem lies with trying to book the room, I keep getting a ‘validate error’, check the date format matches the regional settings, I have checked the settings they are UK settings dd/mm/yyyy, however no matter which way I set this in the ‘edit settings’ mode it still throws the error, what am I doing wrong? Thanks for your help 🙂

    1. Hi, Use dd/mm/yy

      You are not supposed to wrap quotes around the variable L_Menu_BaseUrl, this is a variable supplied by SharePoint. In some cases where the master page has been modified, this variable is missing . You then have to supply the baseurl manually. Alexander

  6. Hi Alexander, I have changed the settings back to L_Menu_BaseUrl and removed the quotes and still works so do not know what I did wrong before.
    However I am still getting the validate error for the date format, I have tried every which way but to no avail, do not know where I am going wrong :(, your help and advice is much appreciated 🙂

  7. Hi Alexander

    I have found a bug where if a room is booked as a recurring event it will not allow another booking until the last recurrence.

    Thanks

    Andrew

    1. Hi,
      Sorry for the late reply, but I have not been able to fix this issue without having to put in *to much* time.

      As this solutions stands, you can not have recurring events. Sorry.

      Alexander

      1. Hi Alex,

        You have come up with some really great SharePoint solutions. I just started using DFFS, and I was wondering if you’ve come up with a solution for recurring meetings as of 7/30/14.

        Very respectfully,
        Mayur

  8. Hi Alexander. Another great solution, thanks!.
    One quick question – if a resource is booked say from 10am to 11, the script will not allow the resource to then be booked from 11 to 12 as it classes this as an overlap. Is there a quick tweak I can make to the SPJS-resourceManagement.js to allow resources to be booked consecutively?

    Cheers

    Jim

  9. Hi Alexander,
    I have just started following your solutions and I must say they are impressive, but my biggest issue at this point is the fact that i do not get any prompt for the SPJS Resource Management config list creation. I have followed all steps but i still get a blank page.
    I have referenced the .js and the css files.
    I have set this SPJSRM_listName to my list name.
    I have even reference the CEWP Code with the CEWP file i uploaded to my doc library and i have directly added the code to my CEWP with no success…
    am i missing anything?

  10. Alexander…
    Thank you for the script, I have it working for several areas already.
    I have one question which could throw a monkey wrench in there.

    Can the script accomidate minutes?

    For example – 09:00 – 09:30 and 10:30 – 11:00
    Or 09:00-09:15, 09:150-09:30, 09:30-09:45, etc…

    I have a few offices that would like to use the script but their appointment time ranges are sometimes less than hourly blocks.

    1. Hi Cory,
      No modification needed – use the “time picker” part of the date picker to set the time.

      I’ll look into the problem with consecutive booking, but until then, you must book the next “meeting” five minutes later for the script to sees the time as available.

      Alexander

  11. Hi Alexander,
    Great script! Thanks very much for sharing.
    I’m having the same problem as Jim. Is there a way in your SPJS-resourceManagement.js file that the end time it is checking can be changed to 1 minute less (like if the resource is scheduled until 11am, the checkDateOverlap will see it as 10:59am)? This, I think, would solve the problem and allow consecutive booking, but I don’t know enough about javascript to modify the file.

    Any help would be greatly appreciated.

    1. Thanks for the suggestion, Cory. Unfortunately, when I change the variables within the qb.push statement, it no longer prevents double-booking. I’m not sure how the end or start variables are stored, so I don’t know if the get and setMinutes methods are correct.

    2. Hi Alexander,
      Thank you so much for all your work! v1.2 works when booking the next time slot available; however, if you try to book the one before a currently booked time, it gives the error. For example, I booked a room from 2pm-3pm and could book the room after from 3pm-4pm. But, when I tried to book the room from 1pm-2pm it gave the overlap error. This will work for me for now, but I will need to fix that in the future. I can’t tell yet what modifications you made, but will keep looking.

      Thanks again for sharing all of this!
      Jaime

    3. Thank you for testing it – I did obviously not think this trough.

      Look at v1.3 and I believe the consecutive booking problem should be fixed. The “magic” happens around line 508.

      Alexander

    4. Obviously I didn’t think about that either. 🙂

      This works as long as I didn’t change the original CEWP code. I saw that you had added a new version of that in v1.3 so replaced both pages first. After doing that, the double booking didn’t work at all anymore. When I changed the CEWP code back to the original, everything worked great! No more issues with consecutive appointments.

      Thanks again for all your hard work!

  12. Alexander,

    I am having a problem with the solution. When I try to create a new configuration a popup dialog appears with the text:

    “[doSaveSettings]

    null”

    Any idea what I might be doing wrong?

  13. Thanks Alexander.
    V1.3 did not work as expected for a dd/mm/yyyy environment. (it was throwing an error up about DateArr being null or not an object)
    I changed ‘yy’ to ‘yyyy’ on lines 454 and 501 of your 1.3 version of SPJS-Resource-management.js though and it now works perfectly. Thought I’d flag up in case anyone else was not getting 1.3 to work as expected who have a similar date format on their installations.

    Cheers

    Jim

  14. Does this solution work with recurring events? Everything looks to be working fine but when I try to create a recurring event I receive the following javascrip error ” ‘fullDate’ is null or not an object”…Anyone having a similar issue? Thanks for your work on this project it has been very helpful!

    1. Hi,
      I do not think this will work with recurring events. The CAML has to be modified to support this. I might be able to add this to a future release, but at the moment I’, fairly sure it won’t work.

      Alexander

    2. thanks for the replay Alexander. The reason I thought it worked was because there was a comment from Andrew talking about recurring events.

      “Andrew Says:
      March 6, 2012 at 4:52 pm | Reply

      Hi Alexander

      I have found a bug where if a room is booked as a recurring event it will not allow another booking until the last recurrence.”

      Maybe I am misreading if he is really using recurring events. A future release with recurrence would be awesome. Thanks again, cheers.

  15. I have this working with a Choice field type, but I can’t get this working with Content Types. Are there any known issues/work arounds to get this working with content types?

  16. Alexander, you are absolutely the BEST! I have to do so much with so little, and your codes have saved me! I couldn’t get our organization to install the room reservations template for me, so I finally had to just create a simple calendar. Of course, my boss wanted a way to not double-book and your code worked flawlessly! I owe you at least three dozen beers for the phenomenal coding you do!

  17. Hi Alex,
    I have followed all the steps, but getting an error

    popup error message when i click ‘edit settings’ above the form:

    The value “MeetingRoom” specified in the variable “SPJSRM_ResourceFieldInternalName” in the CEWP code cannot be found

    help me in sorting this out 🙂

  18. Place the CEWP below the main calendar web part, the above error should go off.

    I am still facing the

    “[doSaveSettings]

    null”

    error even after changing ‘yy’ to ‘yyyy’ on lines 454 and 501 of your 1.3 version of SPJS-Resource-management.js code as commented earlier.

    Any idea why I keep on getting this error when configuring and click on ‘Creat eNew’ button?

    Thanks
    JK

  19. Hi Alexander I’m so hoping you can help. I’ve deployed your solution and it works great except I have a problem when I have a clash. Whilst the action is stopped and no post back is made, I don’t get the error message to tell me who has already booked the room. I’m sadly having to use IE 6 due to the company I work for, but I’m hoping its something I’ve perhaps missed.

    When I debug in explorer I just get Error: ‘dare[…]’ is null or not an object.

    Any ideas??

    Thanks

  20. Hi Alexander,

    Thank you for posting this wonderful solution. I have noticed that when I am testing it (with your latest build) – the solution is displaying 12PM as 12AM for some reason.

    If I go in and reserve a room from say 12PM-1PM, or 11AM-12PM, when I test to make a conflicting reservation it will display as “x has reserved the room from 12AM-1PM or 11AM-12AM.”

    Have you heard of this occuring, any fix for it?

    1. Hi, It’s most likely a bug where I have mixed AM with PM. I’m not that familiar with the AM/PM format as we use military time in Norway.

      I’ll look at it when I come home from my holiday.

      Alexander

  21. Thank you Alexandar. I hope you are having a good holiday. I’m new to your blog – hope to learn a lot here reading through many of your articles. Thanks for being an active educator in the SharePoint community. It is much appreciated and fantastic material.

  22. Hi Alexander,

    This solution looks wonderful. though I am having trouble getting values to appear in the ‘Resource Value’ field. I have set choice values for the choice field that I am using but nothing appears when in the ‘Resource’ field when I click ‘Edit Settings’ on the copyright symbol.

    Any suggestions?

  23. Hi Alexander I’m hope you can help me. I used your solution and works fine but I have a strange bug. I want to book resource from 3.4.2013 to 4.4.2013 and your script says that is already booked by a user from 25.03.2013 to 27.03.2013 !!!! Could be the problem is the date format???

    Thanks in advance.
    Bye
    Paolo

  24. Hi Alex,

    Do you know if there is a way to add a CEWP on a NewForm for a Calendar on SP2007 without using SharePoint Designer? ToolPaneView=2 works but doesn’t give me the option to add CEWP.

  25. Hello Alexander
    I Install your script but i don´t get it run. I got the following error if i press at edit Settings.
    The value MR specified in the variable “SPJSRM_resourceFieldInternalName” in the CEWP code cannot be found.
    The field MR is set at the list.

    The forms (newform and editforms are modified with the tool Nintex. Could you help me to get it run.

    Thanks
    patrick

  26. Apologies Alexander, I must have been on crack the day I poseted that code, please remove it as its wrong.

    I finally got a chance to take a look at my changes, I traced it with Firebug again today and noticed the dates in the query were wrong so switched back to your code and the same thing happened. It turned out we hadnt changed the date format in the settings from mm/dd/yyyy to dd/mm/yy so the dates were coming out rather skewed. All is now working correctly with your code as it was.

    Thanks
    Todd

  27. Alexander,

    This solution is great!

    I wanted to test it out but I tried to download the CEWP code, however it seems the download link is not working.

    I was able to download the .js file, however when clicking on the CEWP, page loads with an error.

  28. Hi Alex,
    I have followed all the steps, but getting an error

    popup error message when i click ‘edit settings’ above the form:

    The value “Facilities” specified in the variable “SPJSRM_ResourceFieldInternalName” in the CEWP code cannot be found.
    Either the field has not been added to the content type, or the field does not exist.

    Please help to solve this.

      1. Hi Alex,

        I have follow your instruction to find the “FieldInternalName”. But I still got the error.

        Can you give me the answer to my questions :
        1. SPJSRM_listName –> Use GUID from the calendar?
        2. SPJSRM_resourceFieldInternalName –> What type must be user for this column? Choice Column? Lookup Column?

        Thanks for helping me.

        Ronny

  29. Hi Alex,

    I try this step on my Sharepoint 2010 (I use new Calendar and Category Column which default by Sharepoint) :
    1. Create new Calendar
    2. Upload CWEP code.txt, SPJS-ResourceManagement.js, spjs-utility.js to my Shared Document at root site.
    3. I change CWEP code.txt variable into :
    a. SPJSRM_listName = “3085f6ca-9c11-4ebf-b668-9ba1965ebcd9”;
    b. SPJSRM_settingsListBaseUrl = “”;
    c. SPJSRM_resourceFieldInternalName = “Category”;
    d. src=”/Shared%20Documents/spjs-utility.js
    e. src=”/Shared%20Documents/SPJS-ResourceManagement.js
    4. Add CEWP using /NewForm.aspx?toolpaneview=2. And then fill content link with “/Shared Documents/CEWP code.txt”
    5. Created List SPJS Resource Management for the first time.

    I still got the same error as before.
    Am I missing something?

    Thanks,
    Ronny

  30. Hi Alex,

    I love this solution, however would like to check with you on how could i change the checking condition for the room names such that it does not need to completely match the full room name.

    For example, i have the following choices:
    – room 1 + room 2
    – room 1
    – room 2

    Hence option 2 and 3 is a subset of option 1, if i book option 1 and there is a double booking for room 1 already, it wont proceed to book room 1 + room 2, vice versa. Hence I would like the condition to check partial matching room name if possible, so i can book multiple room at once. Hope you are able to assist on this

    Cheers
    Terence

    1. Hi,
      You could test changing these lines in the function “checkDateOverlap”:

      qb.push("<Eq>");
      qb.push("<FieldRef Name='"+SPJSRM_resourceFieldInternalName+"' /><Value Type='Text'>"+getFieldValue(SPJSRM_resourceFieldInternalName)+"</Value>");
      qb.push("</Eq>")

      to

      qb.push("<Contains>");
      qb.push("<FieldRef Name='"+SPJSRM_resourceFieldInternalName+"' /><Value Type='Text'>"+getFieldValue(SPJSRM_resourceFieldInternalName)+"</Value>");
      qb.push("</Contains>")

      I’m not sure if this helps, but it’s worth a try,

      Alexander

      1. Hi Alex,

        Thanks for the reply, I tried to change to contains, but it does not work 🙁

        Another other suggestions?

        Regards
        Terence

  31. Alexander –
    Thanks for all your work.

    I’ve gotten to the ‘Edit Settings’ screen, and everything appears to populate correctly, but it is not clear how I create configurations.

    I click on ‘Create New’ and get a “[doSaveSetting] null” error message. Where are the new configurations created?

    Thanks again!

  32. Alexander – this is a great resource!

    I’m almost there: I’ve been able to get the script to work, although I’m getting an error when creating new items: in the Start Date field I get this error: “End time cannot be less than or equal to start time”, even though the dates and times are valid.

    I’m using the latest spjs-utility v 1.172, Resource Management v1.3.
    I’m using jQuery 1.6.2 and jQuery 1.8.16

    I’ve been stepping through the JavaScript debugger, but haven’t really found anything yet.

    I’m not sure if others have had similar issues.

    Thanks,
    Dan

    1. I may have found something in the settings list. The information stored is:
      {“dateFormat”:”mm/dd/yyyy”,”resourceVal”:”MR1″,”minDateFin”:”EventDate”,”maxDateFin”:”EventDate”,”dateRangeArr”:[“EventDate”,”EventDate”],”minDate”:null,”maxDate”:null,”days”:{“0”:{“min”:6,”max”:18},”1″:{“min”:6,”max”:18},”2″:{“min”:6,”max”:18},”3″:{“min”:6,”max”:18},”4″:{“min”:6,”max”:12},”5″:{“min”:6,”max”:18},”6″:{“min”:6,”max”:18}}}

      I see that EventDate (StartDate) is stored twice in the array (and I don’t see a reference to EndDate), and when the date checker goes through, it uses that Start Date twice for the time/date validity check – it increments the first instance by 1 minute and decrements the second instance by 1 minute, so in fact the ending time is earlier than the start time

      1. Feeling a bit stupid… user error – I didn’t set the configuration for “From date” and “To date”, so the defaults don’t work!

        Suggestion: having the default config values select “Start Time” and “End Time”, respectively may reduce some user error (or was that just me!).

        Anyhow, excellent product again!

  33. Hello Alex. Thanks for the great work that you’ve done on this. I have a requirement for this to work on SharePoint 2013. Would the same settings and scenario work.

  34. i couldn’t quite figure out where to start from .

    1> created a calendar : calendar1 . add choice column: to ‘MeetingRoom’ to the same with choices .
    2>created a doc library in SPD in the site and added the files there :
    a> jquery-1.11.1.min.js
    b>jquery-ui.min.js
    c> spjsrm_min.js
    d> spjs-utility.js
    e> CEWP code.txt

    3> edit the NEW/EDIT Forms of calendar1 to include a CEWP : CEWP1 . Added source code from CEWP code.txt

    4> Change the Source code added in Step3 above to
    a> change the reference locations for the css and js files to location on your server.
    b> change SPJSRM_listName to List GUID
    c> SPJSRM_resourceFieldInternalName to ‘MeetingRoom’

    5 > Exit edit mode .Nothing happens

    6> Tried creating and editting meeting room bookings . nothing extra happens.Feeling super tired. Wish there was a video . Will make 1 if i succeed.Dont know what wrong.

    Mine is Sp 2007/IE8.

  35. No errors on going through F12 . in CEWP.txt we have the function : init_SPJSRMSettings(true) . Where is that function located ? which js file. Didnt find any place yet.

  36. I wanted to try this out to see if it could help me meet my requirement of preventing duplicate calendar bookings in SP2007. However like others, I am not able to proceed to step 7. F12 > console tells me “The value of the property ‘init_SPJSRMSettings’ is null or undefined, not a Function object”

Leave a Reply