Intro:
This solution will enable users to select a day and see the available appointments for that day.
Environment:
It uses SharePoint on-premise (I'm using 2013 but any on-premise environment will do) MS SQL database, Nintex Workflow Enterprise edition, Nintex Forms Enterprise edition(Responsive Form).
Reason for this post:
I have seen many posts on the community asking about creating an appointment system or another type of system where once a selection has been submitted from a dropdown that selection will not show for the next user. This solution will be for appointments but you could easily use it to reserve a room or car from the fleet for a day using the same concept. I have made it self maintaining so there should be little to no need for an administrator to have to intervene.
There will be 16 appointments a day from 9:00am to 4:30PM (every 30 minutes) but this can be easily altered as I will explain later.
Reading posts on the community forum users wanted to select a time and then not have that time available for the next user. The posts seem to indicate that they wanted to somehow query the list of booked appointments and work out what time has been booked and only display those which have not. With some mind blowing skills or Javascript, querying for what is not there could possibly be achieved. I thought about it and came to the conclusion that it would be easier to have every appointment available and just mark off which ones are taken and only display those that were not.
Deciding on the best tool for the job:
I looked at using a SharePoint list to hold the appointments. The issue here is that when you do a lookup for the date the lookup control does not have a "Unique" feature where one of each date would appear in the lookup control. This meant that 16 of the same day appears in the lookup which is not user friendly at all. The user will pick a date and all available times will appear. I didn't want 16 dates to appear.
I had recently answered a post on doing cascading dropdowns using SQL request controls. I know that you can do a DISTINCT query which will only return one entry if multiple are found which will get around the duplicate date in the lookup issue. SharePoint and Nintex already use a database so there is no additional cost in adding a new database to the SQL server to hold the appointments.
Let's Start:
Database Creation:
I proceeded to create my database called Appointments and created a table called Appointments. It has 3 columns, Date, Time, Booked and a Primary Key column called id. All are varchar Data Type (strings to the uninitiated). As this is just for display I did not feel the need to have to mess with Date and Time data types and it just simplifies things. To make sure the dates stay in order I will sort by id.
Generating the Appointments:
When using SQL it is always better to use stored procedures to stop the possibility of SQL injection attacks so that is what I will be doing.
First stored procedure takes a parameter for the day that you want to generate appointments for and then generates the 16 appointments. It is here where the available appointments (Rooms,Cars, etc if you are repurposing this concept) can be adjusted. If you need a break for lunch or want hourly appointments the values can be altered accordingly. Execute the below script to create the stored procedure on your Appointments database.
CREATE PROCEDURE GenerateAppoinments (@new_date AS nvarchar(MAX))ASBEGIN INSERT INTO dbo.Appointments (Date, Time, Booked)VALUES (@new_date, '9:00','N'), (@new_date, '9:30','N'), (@new_date, '10:00','N'), (@new_date, '10:30','N'), (@new_date, '11:00','N'), (@new_date, '11:30','N'), (@new_date, '12:00','N'), (@new_date, '12:30','N'), (@new_date, '13:00','N'), (@new_date, '13:30','N'), (@new_date, '14:00','N'), (@new_date, '14:30','N'), (@new_date, '15:00','N'), (@new_date, '15:30','N'), (@new_date, '16:00','N'), (@new_date, '16:30','N'); END
To test, run the following on your Appointments database in SQL Server Management Studio:
EXECUTE GenerateAppointments @new_date = '20/09/2019'
You should see the message 16 rows affected.
When doing a select all on the Appointments table you will see the 16 entries for the day.
Self Maintaining Appointments:
In my solution I will have 1 - 2 weeks of appointments in advance. To start the cycle off I will just manually run the stored procedure 10 times to create Monday - Friday appointments for two weeks. On the Monday of the second week I will have a scheduled site workflow that will run weekly from then on to populate the database for the following week.
If you only want to keep a certain amount of previous appointments data you could create a delete stored procedure and delete the data by adding to the end of this workflow.
SharePoint:
In SharePoint create a list that will store all the information you need as well as the appointments Date/Time and a Yes/No column to keep track if the Appointment has been cancelled. In this example I will leave the Title as is and use it to store a name and add a single lines of text column for Date and one for the Time.
The Form:
I will be using a Nintex Responsive form for input. In my example I have 4 controls all connected to the columns in my SharePoint list.
I replaced the Date control with an SQL Request control. It calls a Stored Procedure to return all the dates. The below script will create the stored procedure. I ended up using a Group instead of using Distinct to filter duplicate dates.
CREATE PROCEDURE QueryDatesASBEGIN SELECT Date,Sum(id) as ids FROM [Appointments].[dbo].[Appointments] WHERE Booked = 'N' Group by Date,Booked Order by idsEND
The Time control is also an SQL Request control. It too calls a stored procedure but it has a parameter of Date which is cascaded off of the date control.
The stored Procedure that is called can be created with this script:
CREATE PROCEDURE QueryTimes (@date_filter AS nvarchar(MAX))ASBEGIN SELECT * FROM dbo.Appointments WHERE Date = @date_filter AND Booked = 'N'END
The Date control queries the database looking for Dates that have available times. If all the Times are marked as "Y" in the Booked column, that date will not be shown in the drop down. The Time control then queries the database for the available times that are not marked as "Y" filtered off of the date from the Date control.
The Workflow:
When a Nintex form is submitted a List workflow configured to start on item creation is executed.
It has one Execute SQL action that runs the below script. The script simply changes the Booked column from 'N' to 'Y' based on the Date and Time chosen so that it does not show up as an option on future forms.
UPDATE [Appointments].[dbo].[Appointments]SET Booked = 'Y'WHERE Date = '{ItemProperty:Date}' AND Time = '{ItemProperty:Time}'Note: A stored procedure could be used here but as the query is being called from a workflow it is much safer than being called from a web browser so I used the script instead.
Cancel Appointment:
On the form I have a cancel checkbox that is hidden on new mode but shown on Edit mode. When checked the control straight away disables so it cannot be unchecked. If a user decided not to cancel they can click the cancel button on the form. The dates and times cannot be changed in Edit mode. When the cancel check box is checked and the form saved a workflow configured to run on conditional item modification changes the "Booked" column in the database for that appointment from 'Y' to 'N' using the query as above but setting a N and not a Y. That appointment will become available again from the form.
Thanks for reading. Leave me a Like or a comment. Any questions welcome.
