How to create a PTO/vacation request form and approval process in SharePoint

I was recently asked to develop a PTO request form that accomplished the following:

  • Allowed users to submit PTO, Remote work, Training, and Other requests
  • Auto-lookup of the employee’s manager
  • Send confirmation to submitter
  • Notify manager and seek approval/rejection with comments
  • If approved, send notification with an iCal link users can save to their calendar, and add the PTO to our shared departmental calendar (minus any comments)

Note: This particular build was done on SharePoint Server 2016 and uses a SharePoint Designer workflow.

Create the PTO request form

Thanks to Gregory Zelfond for sharing the idea of using a Tasks web part for this as opposed to a list. Much of this first section is inspired by his post here, with some adjustments for my purposes and preferences.

  1. Add a tasks app and name it PTO Requests

  2. Go to List settings for the new tasks app
  3. Configure Versioning settings as follows

  4. Back under List settings, configure the following Advanced settings as seen here

      

  5. Under Views, keep All Tasks, My Tasks, Approve/reject Items, and Calendar. Delete the rest. Rename All Tasks and My Tasks to All Requests and My Requests.

  6. Back under List Settings, under Columns, create the columns shown in green here, and rename those in red. Duration should be the last column you create (number format), and its formula follows the screenshot. Category needs to be the types of “Out of Office” a person could request (like PTO, Remote, Training, Other).
    Click to enlarge

     

    =IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

    I like the Duration column because it will allow us to show how many weekdays (not including weekends) the PTO Request includes. So those multi-week or broken-week requests show an accurate number for management purposes.

  7. Back under List Settings, select “Task” from under Content Types. Configure each column (hide, make optional or require) as follows:
    Click to enlarge

     

  8. Your new form should resemble this now:
         
    Click to enlarge

     

  9. You might want to spend some time making your views a nice dashboard as well. Here’s what I did for “All Requests.” Notice I grouped by a different calculated column which determines whether the PTO is Upcoming, Current, or Past and then sub-grouped by category.

    Click to enlarge

Create the workflow for approval and calendar addition

In SharePoint Designer, add a new 2010 platform List Workflow for the PTORequests list.

  1. Go to Workflow Settings
  2. Check all “Start Options”
  3. Click “Edit workflow”

Here’s a preview of what we’re going to build:

Click to enlarge

Because this workflow has many steps, I’m only going to show some high-level tips for completing it. Build your workflow to suit your needs. If you have specific questions on how I built mine, please comment.

The approval request email

For the “then Email Current Item: Approver” step, here’s how I did the email:

The link for approving and rejecting is as follows, and you’ll need to replace the red text with a lookup to the current item’s ID. The easiest way to get this might be to do a test submission and copy the link to the approval page where the manager will choose Approved, Rejected, or Pending. You can find this page by viewing a submission, then clicking “Approve/Reject.”

Then just be sure to swap the specific ID with the lookup field for ID.

https://YOURSITE/sites/it/_layouts/15/approve.aspx?List=%7BLISTGUID%7D&ID=[%Current Item:ID%]&Source=%2Fsites%2Fit%2FLists%2FPTO%2520Requests

This will reduce the number of clicks your approvers/managers have to do significantly. Their process will be:

  1. Click big link in email
  2. Select decision and enter comments
  3. Save/Submit

If denied email

No bells and whistles here. You might even include a link to the dash suggesting they submit another request if they wish.

If approved, create calendar item

This is fairly straightforward. If approved, we create an item in a different list. I even edited the “Calendar” view of my PTO requests by adding an “app part” for the separate calendar instead of using the built-in calendar view the task list provides. I then just minimized and hid the chrome of the “Calendar” web part that was already on the view. This allowed me to keep some overlays and other processes related to the second calendar, but see if in context of PTO Requests.

Also, to protect the privacy of those submitting “Other (please explain)” requests (likely FMLA or something private) I have the workflow changing “Other” to just “Out of Office” for the purposes of adding to our shared calendar.

If approved email

Since I created a calendar item if approved, I’m including an iCal link in the approval email for users to open the .ics file and save it to their calendars. Here’s a post on how to structure iCal links. Just instead of creating a calculated column, construct the URL in SharePoint Designer:

Click to enlarge

Improve the look with script (optional)

The following script improves the look of both your views and your forms. It will:

  • Get rid of  “See also” items when viewing requests
  • Change “New task” to “New request” in your views
  • Make sure all fields are visible, and not hidden behind “Show More”

Just copy and save the following script as a separate .js file in your Site Assets and follow the instructions beneath the script to add it to your views and forms. 

Views

  1. Go to a view
  2. Settings –> Edit Page
  3. Insert/Add web part –> Media and Content –> Content Editor Web Part
  4. Add link to script saved on site
  5. Save and repeat for each view

Forms

Go to your list –> List –> Form web parts

Click to enlarge
  1. Select a web part
  2. Settings –> Edit Page
  3. Insert/Add web part –> Media and Content –> Content Editor Web Part
  4. Add link to script saved on site
  5. Save and repeat for each form

Good luck!

SharePoint calendar all-day events showing as previous day in content search web parts

Despite your regional settings being correct, all-day events for some reason are using UTC time when they’re stored and are likely showing as the wrong day in content search web parts and similar web parts.

Though they look correct as an individual item or on a calendar, the way they’re stored doesn’t acknowledge your regional settings and, when pulled through a search web part, render in UTC as beginning 6 (or other) hours earlier than they actually do.

For example, I have a content search web part that pulls all events in our organization and show’s “today’s events.” If there’s an all day event, it shows as starting 6:00 PM the day prior to its actual day.

I could not find a straightforward solution to fix all affected events. And my solution is not ideal, but it accomplishes a need. You could instead explore the possibility of creating a calculated column that adds hours to fix the alignment. But please share if you’ve encountered the same issue and have resolved it a better way.

To get around this issue, I created a SharePoint Designer 2010 Workflow on calendars that:

  • Triggers on creation or modification
  • “Unchecks” the All Day Event box
  • Sets a specific start time on the start date (I chose 7:00AM)
  • Sets End Time to the original end time (if you don’t do this, it sets end time to blank).

SharePoint workflow troubleshooting: “Item does not exist. It may have been deleted by another user.”

This error message tends to reveal itself whenever you’re dealing with item-level permissions. Try the following to resolve this error.

Note: For any change to a workflow, you’ll need to start/restart your workflow to test it. Resuming a workflow will only resume the previous version of the workflow (still broken).

Continue reading “SharePoint workflow troubleshooting: “Item does not exist. It may have been deleted by another user.””

Solution: “The user who attempted to complete the task is not the user to whom the task is assigned”

You might run into this issue when running task processes in SharePoint Designer.

“Reason: The user who attempted to complete the task is not the user to whom the task is assigned.”

Assuming the person should actually be able to complete the task, check the following:

  • Make sure the user who was assigned the task does not have multiple accounts. If they do, the task could have been assigned to the account that the person isn’t currently signed in as when attempting to complete the task.
  • If it’s someone completing the task on behalf of another, make sure that individual is either:
    • A Site Owner (site settings, site permissions, people and groups, site owners) or
    • Task process owner (in SPD 2010 workflows, go to the properties for the task step and set task process owner. Click OK and republish workflow.):

SharePoint Designer (SPD) 2010 vs 2013 Workflows

I frequently reference two resources linked at the bottom of this post that speak to the features unique to 2010 and 2013 workflows. Unfortunately, once you pick which workflow platform you’ll be building upon you can’t switch. So it’s important to use these lists in your evaluation phase to make sure you’ll be picking the right platform for the job. Keep in mind, you can always start a 2010 workflow from within a 2013 workflow.

Continue reading “SharePoint Designer (SPD) 2010 vs 2013 Workflows”

Use Microsoft Flow to create a “today” column for use in SharePoint list calculations

Note: I previously shared how to do this in SharePoint Designer. The following method utilizing Flow is better, and does not use loops/pauses.

It’s well-known that SharePoint calculated columns don’t permit [Today] to be used as a formula for a calculated date column. And the “default to today’s date” setting only works upon creation, and doesn’t update daily. But we can create a standard date column and have Microsoft Flow automatically update it daily for us, therefore allowing us to effortlessly perform calculations against today’s date such as:

  • Age =(TodayDate-Birthday)/365
  • Years of Service =(TodayDate-StartDate)/365
  • Days Past Due =(TodayDate-DueDate)
  • Weeks until summer break =(SummerStart-TodayDate)/7

Here’s how to create your own, always accurate/updated, today column (see bottom of post for video):

Continue reading “Use Microsoft Flow to create a “today” column for use in SharePoint list calculations”

Generate and send reports, files or lists regularly with Microsoft Flow’s “recurrence” trigger

Capture.PNG

Automatically create and send reports, files and lists on a regular schedule using Microsoft Flow’s recurrence trigger. Whether hourly, daily, weekly or monthly you can deliver the most current and relevant data from SharePoint or OneDrive to interested parties via email without lifting a finger. Combine this with calculated columns in SharePoint and conditions for some awesome possibilities:

Alerts Calendar Relevance Routine
Report costs or expenditures above a certain amount Current month’s birthdays and/or workiversaries to your secretary Send expenses per department or individual to that department or individual Budget and salary or payroll figures weekly
Notify when an open ticket is idle for a week or incomplete Upcoming events per location Share evaluation status with supervisors for just their employees Recently closed deals and contracts
Survey responses or reviews under 3 stars Upcoming deadlines per department Client info and updates to proper salespeople based on location or product Distribute new hires’ contact/location info to the organization in weekly batches

Continue reading “Generate and send reports, files or lists regularly with Microsoft Flow’s “recurrence” trigger”

How to change Microsoft Flow’s default limit of 100 items for “Get Items” and “Get Rows” actions

100itemlimit

The first time I created a flow for a list with over 100 items, I noticed an “Apply to Each” block stopping at 100 items. It’s a simple fix:

Continue reading “How to change Microsoft Flow’s default limit of 100 items for “Get Items” and “Get Rows” actions”

How to make a Microsoft Flow mobile button to be emailed Microsoft Forms or SharePoint data as Excel link or attachment

screenshot_20171109-143752.png
Microsoft Flow mobile buttons are magical. One touch on your mobile device, and gears start turning to retrieve and deliver the data you need when and how you need it. Recently, I set out to deliver all Microsoft Forms responses to a recipient on-demand as an excel file using a Microsoft Flow mobile button they could press whenever they wanted the results. I also created a button someone could use to be sent all the birthdays coming up in the next week for our organization whenever they need it. You can adjust the following steps to fit your situation and tools, but the following outlines two ideas:

  • Sending someone all responses to a Microsoft Forms survey whenever they press the button (Take a snapshot in time of responses, or pull up-to-the-minute feedback into your meeting)
  • Sending someone SharePoint list items in an excel sheet that match a certain criteria (Projects ending in the next two weeks)

Continue reading “How to make a Microsoft Flow mobile button to be emailed Microsoft Forms or SharePoint data as Excel link or attachment”

Convert SharePoint documents to PDF using Microsoft Flow

convert.PNG

Edited Dec 10, 2018 to include “for a selected item” function in modern sites.

Can you convert SharePoint documents to PDF without leaving SharePoint? Heck, yeah!

Basically we’ll create this flow:

  1. “When a file is created or modified” in SP -OR- “For a selected item”
  2. Create document in OneDrive for Business -OR- OneDrive
  3. Convert document (OneDrive action in Flow)
  4. Create document in SP

It’s a bit of a hack but we get exactly the result often requested: convert SharePoint docs to PDF automatically. Here’s how to set this up. A video walkthrough using the “created/modified” trigger is available at the bottom of this post.

Continue reading “Convert SharePoint documents to PDF using Microsoft Flow”