Building a Leave Booking System with SharePoint Lists and Lightning Forms

In this post, we’re going to step through how to build a leave booking system for your employees using a SharePoint Site, a few SharePoint Lists, and some customized forms using Lightning Forms. The solution promises to be simple for employees to book time way from work via SharePoint, Teams, or Mobile. It will be easy to keep track of the amount of time each employee has used from their holiday allocation, and will also include an approval process. It will take an hour to build from start to finish! That will be proven in the webinar recording that follows this post!

So, here are the steps to get started!

Creating the Site and Lists

1. Create yourself a Modern SharePoint Site and call it “Leave Booking Site”.
2. Create a Custom List called Leave Booking. To do this, click the cog in the top right corner of your new site, choose Add an App, and select the Custom List template. When prompted, call the list “Leave Booking“
3. Create another Custom List called Book Leave. To do this, click the cog in the top right corner of your new site, choose Add an App, and select the Custom List template. When prompted, call the list “Book Leave“
4. Create a third list using the Calendar List Template called Leave Booking Calendar.

You should now have three lists within your site called Leave Booking, Book Leave, and Leave Booking Calendar.

Adding the Fields To Your Lists

First, let’s get to work on the Leave Booking List which will be used to track holiday requests, and show employees how much time they have left to take within the year.

1. Open the Leave Booking List by navigating to Site Contents from the left hand navigation pane.
2. Leave the Title column as it is, we will use this as part of our solution.
3. Add a new column by clicking Add column and choose Person as the field type. Name the field Employee, and accept the other defaults. Choose Save.
4. Add a new column by clicking Add column and choose Person as the field type. Name the field Manager, and accept the other defaults. Choose Save.
5. Add a new column by clicking Add column and choose Date and time as the field type. Name the field Period Start Date, and accept the other defaults. Choose Save.
6. Add a new column by clicking Add column and choose Date and time as the field type. Name the field Period End Date, and accept the other defaults. Choose Save.
7. Add a new column by clicking Add column and choose Number as the field type. Name the field Allocated Days, and accept the other defaults. Choose Save.
8. Add a new column by clicking Add column and choose Number as the field type. Name the field Days Taken, and accept the other defaults. Choose Save.
9. Add a new column by clicking Add column and choose Number as the field type. Name the field Days Carried Over, and accept the other defaults. Choose Save.
10. Add a new column by clicking Add column and choose Number as the field type. Name the field Remaining Days, and accept the other defaults. Choose Save.

You should now have a leave booking list containing the columns: Title, Employee, Manager, Period Start Date, Period End Date, Allocated Days, Days Taken, Days Carried Over, Remaining Days.

Let’s now create the list allowing employees to book leave.

1. Open the Book Leave list by navigating to Site Contents from the left hand navigation pane.
2. Leave the Title column as it is, we will use this as part of our solution.
3. Add a new column by clicking Add column and choose Date and time as the field type. Name the field Start Date, set the Default value to Today’s Date using the Default Value drop down list.. Choose Save.
4. Add a new column by clicking Add column and choose Date and time as the field type. Name the field End Date, and accept the other defaults. Choose Save.
5. Add a new column by clicking Add column and choose Choice as the field type. Name the field Leave Type, and enter the choices as Annual Leave, Sick Leave, Other. Choose Save.
6. Add a new column by clicking Add column and choose Number as the field type. Name the field Days Taken, and accept the other defaults. Choose Save.
7. Add a new column by clicking Add column and choose Yes/No as the field type. Name the field Approved, and set the default value to No . Choose Save.
8. Add a new column by clicking Add column and choose Single line of text as the field type. Name the field comments, and accept the other defaults. Choose Save.

You should now have a book leave list with the columns: Title, Start Date, End Date, Leave Type, Days Taken, Approved, and comments.

We’ll leave the Calendar List as it is for now.

Getting to Work with Lightning Forms

Now that we have our lists created, we’re going to get to work on customizing those list forms to make them more logical and to bring the lists together to build the leave booking solution.

Initially, we’re going to work on the layout of the Leave Booking List. Currently, if you go to create a new item in the Leave Booking List, it will look like the below:

Let’s add the improvements using Lightning Forms (If you don’t have Lightning Forms installed yet, you can grab a trial using the button below.

1. Open the Leave Booking List
2. Click Lightning Forms Customization Dialog from the command bar in the list.

3. Click the Customize button next to the NewForm.aspx

4. Add a Rich Text control to the top of your form.

5. Within the Rich Text, add a heading and if you like a description on how to use the form. e.g. Please use the below form to book and keep track of your holiday bookings.

6. Beneath the Title that you created, Add a Tab control and add three tabs called Book Leave, Leave Calendar, and Leave Allocation.

Note: To rename the tab, just double click the tab label. To add a new tab, click the + icon on the far right of the tab control.

7. Click the + icon in the Book Leave tab to add the Book Leave list. We will create a lookup column automatically to join the lists together.

8. The Book Leave list will be added to the tab. Before clicking OK, we will join the lists together using a Lookup Column by following step 9 below.
9. Check the Use as Sub item under the Sub item settings.
10. Click the + to add a lookup field to the parent list and accept the name of this column by clicking Add.

11. Click Ok.

Next, we will add the Leave Booking Calendar to the Leave Calendar tab. This time, the lists will not be linked via a lookup column.

12. Select the Leave Calendar tab.
13. Click the + icon and add the Leave Booking Calendar from the Lists & Libraries category.

14. Click Ok.

Next, we will add the fields to the Leave Allocation tab.

15. Select the Leave Allocation Tab.
16. Hover over the first row, and choose the row configuration icon, and then set the layout options to two columns. This will give us more space and a better layout for our form.
17. Click Ok.

18. Arrange the fields onto the Leave Allocation tab and into the two columns by dragging them and dropping them.

19. We have now completed the initial layout. and will concentrate on the calculations, logic, and actions.
20. Save your changes so far by clicking Save.

Adding logic to our Leave Booking Solution

Now that the initial layout is complete, we will add our logic to the form.

1. We are going to set an initial value to the Title field to the Title under the Leave Allocation tab. Hover over the Title field and click configure expressions.

2. In the Expressions configuration pane, click the configure expressions button for the Calculated property.
3. Configure an expression to set the initial value to the Employee Name, Period Start Date and End Date concatenated. You can double click each field, and use the keyboard to add your operators.

4. Click Save.
5. Click Ok to the Expressions configuration pane.

6. Hover over the Employee column, and choose configure expressions. Set the initial value to pick up the current user name. Using the expression builder, expand context objects, User, and double click Title. This will then default to the display name of the current user.
7. Click Save, and then Ok to close the Expression Configuration.

6. Without using the Express Builder, set the Allocated Days Initial Value to 25.

7. Using the Expression Builder, we will calculate the Days Taken from the Book Leave Sub List.
8. Hover over the Days Taken field and choose the configure expressions.
9. Click the configure expressions button next to the Calculated property.
10. Using the expression builder, navigate to the SubLists, Book Leave, Days Taken, and double click Sum.

11. Click Save, and then Ok to the configure expressions pane.
12. As a finishing touch, Click the Form Settings button in the top left, and set the initial size of the form to Large.
13. Click Save & Close to leave the design experience of the Leave Booking List.

Our calculations are now complete. Next we will add some buttons to the form to configure some actions.

Adding Action Buttons to Your Form

In this section, we will add two buttons with actions. One will be to request leave, and the other will be to approve. These will be added to the Book Leave form.

1. Open the Book Leave List.
2. Click the Lightning Forms Customization Dialog.
3. Click the Customize Button for the NewForm.aspx
4. In the design experience of the Book Leave list, choose Actions, and then Command Bar. We will create an action to request leave.

5. Click the + to add a new action.
6. Set the Display name to Request Approval and choose an appropriate icon.

7. Click Configure Actions
8. Set the first Action in the Actions Builder to Save Form type.
9. Add another action and choose Send Mail and complete the Send Mail parameters as below.

10. Add Another Action Button by repeating Step 4 and step 8. Name your Action button Approve
11. Optionally, set a Visible Expression to only chose the button to certain users. You can use the IsMemberOfGroup expression to only show the button to Approvers if the group exists. =[[@User.IsMemberOfGroup(‘Leave Booking Approvers’)]]
12. Click Configure Actions
13. Set the Action to Set Form Field Value, and set the Approve field to Yes

Note: You can add additional actions here to inform the user that their request is approved.

Getting the same customizations to the Display Form and Edit Form.

Now that we have completed a form design, we can export the design of the customized form, and import it into the New Form and the Display Form.

1. Open each list that we customized and click the Launch Lightning Forms Configuration Dialog button.
2. Click the NewForm.aspx link to open the designer.
3. Click Export/Import button and export your configuration.
4. Click Save & Close
5. Open the Launch Lightning Forms Configuration Dialog button again, and click the customize button for the EditForm.aspx.
6. Click the Export/Import button and choose Import form. Browse to your exported file.
7. Once the changes have been imported, click Save & Close. You can repeat these steps for the Display Form.

We’ve now completed the Booking System! Of course, there is much more that you could do to your forms, but hopefully this has got you started!