Posted on

How to create an automated, chat-based team availability system using Power Automate and Microsoft Teams

Efficiently managing and communicating team availability in a hybrid world is crucial for smooth operations in any organization. This guide will show you how to create an automated yet interactive system for tracking and communicating the availability of general staff members using Power Automate, Microsoft Teams, and SharePoint.

Step 1: Understanding the Business Process

  • Weekly Availability Surveys: Each Friday, staff members receive a personalized survey through a Teams chat message to submit their availability for the following week.
  • Confirmation and Preview: Upon submission, staff members get a confirmation message and a preview showing the team’s availability as of the time they submitted their own.
  • Comprehensive Monday Update: On Monday, everyone receives a complete overview of the team’s availability for that week, helping them make the most of everyone’s availability.

Video demonstration

The following video will demonstrate steps 2-3 (building the SharePoint list, and walking you through the Power Automate flow).

Step 2: Setting Up SharePoint

  • Create a SharePoint List: Establish a SharePoint list for logging staff availability. Ensure it’s accessible to all staff members who will be participating and/or reviewing availability of teammates. It needs:
    • A person column type for Employee
    • 5 single line of text columns – one for each weekday. In my demo, I’ve repurposed and renamed the default Title column for Monday.
The supporting SharePoint list upon creation | Click to enlarge
  • Manage Permissions (optional): Assign appropriate permissions to staff members to view and edit the availability list. In order for someone to submit their availability via the chat request, they’ll need Contribute or Edit access at least. If you added the list to a site accessible to all relevant team members, they already have access to edit the list by default.

Step 3: Automating with Power Automate

  • In-Office Status Survey Flow:
    • Trigger: Schedule to run every Friday at 10:00 AM.
    • It should first gather data from the SharePoint list (Get items) to know whom to ask for their availability
    • Before asking an employee for their availability, I use the following in my Update item step’s Monday, Tuesday, Wednesday, Thursday, and Friday fields to reset them each week:
⏳ Requested
  • Post an Adaptive Card : The chat message to each employee uses the ‘Post an adaptive card and wait for a response’ action. For the Message of the adaptive card step, utilize the provided JSON below. The only thing you’d want to update is the company logo URL to include your own. You could also adjust things like ‘Telework’ to ‘Work from home’ or any other verbiage you choose.
{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.3",
    "msTeams": {
        "width": "full"
    },
    "body": [
        {
            "type": "ColumnSet",
            "columns": [
                {
                    "type": "Column",
                    "width": 5,
                    "items": [
                        {
                            "type": "TextBlock",
                            "text": "Good morning!",
                            "weight": "Bolder",
                            "size": "Medium",
                            "style": "heading"
                        },
                        {
                            "type": "TextBlock",
                            "text": "Let us know where you'll be working next week so we know how best to collaborate with you.",
                            "isSubtle": true,
                            "wrap": true
                        }
                    ]
                },
                {
                    "type": "Column",
                    "width": 1,
                    "items": [
                        {
                            "type": "Image",
                            "url": "https://i0.wp.com/natechamberlain.com/wp-content/uploads/2022/08/cropped-Nate-Chamberlain-logo-icon-e1661192398264.png",
                            "altText": "Company Logo",
                            "size": "auto"
                        }
                    ]
                }
            ]
        },
        {
            "type": "Container",
            "items": [
                {
                    "type": "ColumnSet",
                    "columns": [
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Monday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Monday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Tuesday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Tuesday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Wednesday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Wednesday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        }
                    ],
                    "bleed": true
                }
            ]
        },
        {
            "type": "Container",
            "items": [
                {
                    "type": "ColumnSet",
                    "columns": [
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Thursday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Thursday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Friday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Friday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33
                        }
                    ]
                }
            ]
        }
    ],
    "actions": [
        {
            "type": "Action.Submit",
            "title": "Submit"
        }
    ]
}
  • Get the updated list of availability (Get items 2) including the new response, making sure to use the following filter query to only get rows where people have responded for the coming week:
Title ne '⏳ Requested' and Title ne null
  • After someone responds, they’ll get a chat preview of the whole team’s availability for the week.
  • In-Office Status Results Flow:
    • Trigger: Schedule to run every Monday at 9:00 AM.
    • This flow compiles survey responses into an HTML table (similar to how we did the first flow) and distributes it to the team via a Teams group chat message where they can ask follow-up questions, provide additional context, coordinate meetings, etc.
    • Important – Exclude the Sender: The sender/owner of the flow should not be included in the Monday communication to recipients (assuming they’re using their credential to connect for that step in the flow). Since they’re the one connecting to Teams via Power Automate, they’re already included in the eventual group chat. Attempting to include them will result in an error.

Conclusion

By following these steps, you can set up a streamlined and automated process to manage the availability of your team. This system not only saves time but also ensures clear and consistent communication among all staff members.

Posted on

How to Alternate Request Assignment to Responsible Persons for various categories

animals birds feathers flamingos

I had a unique challenge recently to alternate assignment of requests (tracked in a SharePoint list) to individuals as they were created. User #1 would get requests 1, 3, 5, etc. and User #2 would get requests 2, 4, 6, etc. To make it more interesting, different pairs of experts might be assigned to different request topics, and sometimes a single individual would be responsible for a particular topic. The overall idea, though, was a somewhat evenly dispersed workload given quickly and efficiently to those who were responsible for those request areas.

One (less than ideal) option to accomplish this is to use a Get Items (SharePoint) step on the requests list with a Top Count of 1 and Order By OData query (Created desc) for the most recently created request, see who the assignee is, and then assign to the other person in that category’s assignee field (when applicable). But I needed something that was better-performing, sophisticated, simpler in design, and not dependent on any historical items in case they were deleted, re-assigned, etc.

So, to determine the assignment as requests randomly trickle in over time, I decided to have Power Automate determine whether a new request’s ID number (auto-generated by SharePoint) is odd or even. Odd requests would go to the first assignee of a subcategory, and even would go to the last. If there was only one assignee in a category, they’re both the first and the last so it still works.

A general good practice when building flows is to make sure you don’t put a particular assignee’s identity explicitly in a variable, task, or email step. For example, you wouldn’t want to put user #1’s email directly in an Outlook email step’s To line. This makes sure that if an employee leaves the company, that the flow won’t need a revision. Instead, I created a list in the team’s SharePoint site with request categories, sub-categories, and responsible persons. My flow refers to this list each time it needs to make an assignment, making sure it has up-to-date contacts for each subcategory.

Example of a request categories list | Click to enlarge

The managers of the process keep this updated with no more than two assignees per subcategory. This way, when new employees join, the list gets an update for what subcategories they’ll be handling, and the flow continues without a hiccup.

What you’ll need to build this solution

Overall, for this process to work, you’ll need:

  • A list of request categories with these columns:
    • Category (choice)
    • Subcategory (text or choice)
    • Assignees/Responsible (person with multi-select enabled)
  • A list of requests (submitted via a SharePoint form, Microsoft Forms form via Power Automate, or Power Apps app via Power Automate)
    • I’d recommend Power Apps if you have the time and resources – this way your choices (categories and subcategories) in the form can be dynamic (only have to be managed in one place) and reference your categories list without granting access to the main request list, or even the SharePoint site, itself
    • Consider adding additional fields for your assignees like Notes, Status, Priority, etc. for them to add additional context, reminders, and status updates as they work the requests
  • The Power Automate flow for assignment determination

How to build a flow that assigns tasks evenly and by category

The following video will walk you through the necessary components to build this flow in Power Automate. The result will dynamically assign tasks to responsible persons based on categories they’re responsible for, as well as alternating tasks to help distribute workloads more evenly within each category.

Ideas to enhance your categories list with additional fields for different assignment scenarios

Consider taking this concept further by adding additional columns to your categories list. Things like:

  • Sequential assignment (Yes/No) – Assign to the first person in the multi-select person field and if they don’t mark it complete within 2-3 days, add the second person as an assignee.
  • Assign to all (Yes/No) – Maybe some subcategories should have all assignees involved in the resolution. A simple checkbox would have the flow assign to both (when applicable).
  • Request specific assignment (Yes/No) – Not everything can be randomly or evenly assigned. You may wish to choose on a case-by-case basis. Set your flow up to send an adaptive card to the process manager(s) asking for selection of an individual for each request that comes in with this Yes/No checked.
Posted on

How to use the Mod function in a Power Automate expression to determine if a number is odd or even

Using the Mod function in an expression in Power Automate, you can quickly determine whether any numeric value is odd or even. In this post’s examples, I’ll be using a SharePoint item (or document) ID field as my numeric value, and I will determine if it is odd or even.

Did you know?
Every item and document in SharePoint is automatically assigned a unique ID number within its list or library. The field is called ID.

What is the Mod Function?

The Mod function takes two arguments: the number you want to divide and the divisor. It returns the remainder of the division. For example, Mod(5,2) returns 1, because 5 divided by 2 has a remainder of 1.

To check if a number is odd or even, you can use the Mod function with 2 as the divisor and compare the result (using the equals function) with 1. If the result is 1, then the number is odd. If the result is 0, then the number is even.

You can use the if and equals functions together to write this logic as a conditional expression. Here is an example using a SharePoint item ID as the number:

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This expression says: if the SharePoint item ID divided by 2 has a remainder of 1, then return ‘Odd’, otherwise return ‘Even.’

You can use any numeric field instead of the SharePoint item ID, as long as it is available in your flow.

You can learn more on the Mod function here.

And explore a real scenario in which I used this idea in my other blog post.

How to Add the Expression to Your Power Automate Flow

Tip: I have a video of these steps if you’d prefer.

First, to begin adding your expression, click within a step’s field where you want Odd or Even to be returned, such as a variable or condition statement. Then, in the dynamic content panel that pops out, select Expression and you’ll be provided with a formula bar.

How to add an expression to a Power Automate flow | Click to enlarge

In your expression formula bar, use the following formula (modifying the field reference to your own numeric field):

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This says if my numeric field divided by 2 has a remainder of 1 (making it an uneven quotient) then it’s odd, otherwise it’s even.

You can then use the result of this expression (Odd or Even) in further conditional expressions or condition blocks like this:

A condition block based on the result of odd/even expression | Click to enlarge

How to Insert a Dynamic Content Field into the Expression

If you are not sure how to find the reference for your numeric field, you can use the dynamic content panel to insert it into your expression. Here are the steps:

  1. Write your expression up until the point you need to enter the field name
Beginning of conditional Mod expression in Power Automate | Click to enlarge
  1. With your cursor active where you want to enter the field name (right after Mod( in my example), select Dynamic content, then select the numeric field you are using. In my case, it’s the ID field from a SharePoint list item.
Insertion of dynamic content in a Power Automate expression | Click to enlarge
  1. The selected field reference will automatically appear in your formula where your cursor was at the time of insert.
  2. Finish your expression. It might resemble this if you’re referencing a SharePoint item’s ID from a trigger like When an item is created:
if(equals(Mod(triggerOutputs()?['body/ID'],2),1),'Odd','Even')

Video: How to determine if a number is odd or even in Power Automate using the Mod function

That’s it! You have learned how to use the Mod function in Power Automate to determine if a numeric value is odd or even. I hope this helps you create more powerful and sophisticated flows. Good luck!

Posted on

Power Automate solution: OneNote action errors involving invalid or inaccessible Notebook Keys and sections

When using OneNote (Business) in a Power Automate flow, you may be attempting actions such as Create section in a notebook, Get sections in notebook, or Create page in a section but getting errors when trying to select the relevant notebook and section.

And depending on what you’re using as the Notebook Key and/or Notebook section value(s) you may get any of the following specific errors:

  • Error; the requested notebook or section may have been deleted or is otherwise inaccessible.
  • Invalid notebook key
  • The specified resource ID does not exist.
  • The section id is invalid. If a custom value was entered, please try selecting from the supplied values.

I’ll show you how you may be able to solve this issue in this blog post by using a custom value for Notebook Key. To insert a custom value for Notebook Key, use the dropdown arrow in the Notebook Key field and select Enter custom value.

Enter custom value option for Notebook Key (click to enlarge)

Now you can type text freely. You’ll need to format your notebook key one of two ways, depending on whether it’s a personal (OneDrive for Business) notebook or a shared (SharePoint/Teams) notebook. Both solutions are below.

Solution #1: OneNote Notebook key API format for your own notebooks (stored in OneDrive for Business)

For OneDrive notebooks, such as the default one you get like Nate @ Contoso, format your notebook key as seen below, replacing highlighted parts with your own notebook name, organization URL, and email address (with underscores instead of the usual symbols).

Nate @ Contoso|$|https://contoso-my.sharepoint.com/personal/nchamberlain_contoso_com/Documents/Nate @ Contoso

Still not working? Your organization may have a .com added (even if you don’t see it in your notebook name). Try adding .com to your notebook name in both locations (beginning and end):

Nate @ Contoso.com|$|https://contoso-my.sharepoint.com/personal/nchamberlain_contoso_com/Documents/Nate @ Contoso.com

Solution #2: OneNote Notebook key API format for shared notebooks (stored in SharePoint and used there or in Microsoft Teams)

For shared notebooks, such as the default one you get with every Microsoft Teams team or SharePoint team site, format the notebook key as follows. Be sure to replace bold components of the key with your own notebook name, organization URL, and notebook location:

Notebook Name|$|https://COMPANY.sharepoint.com/sites/SITENAME/NOTEBOOK LOCATION/NOTEBOOK NAME

For example, all default notebooks are stored in a SharePoint site’s Site Assets folder so a complete Notebook key for a notebook like that may resemble the following (yes, you can leave the spaces in notebook names):

Mark 8 Project Team Notebook|$|https://contoso.sharepoint.com/sites/Mark8ProjectTeam/SiteAssets/Mark 8 Project Team Notebook

Or if it’s not the default notebook, and it was created in a document library a couple folders deep, it might resemble the following. Just replace Shared Documents with the name of the library, and replace the folder structure as appropriate:

Policies and Procedures|$|https://contoso.sharepoint.com/sites/Compliance/Shared Documents/Folder 1/Folder 2/Policies and Procedures

As long as you enter the key correctly using either solution, your flow will connect to the notebook properly and, when relevant, the Notebook section dropdown will refresh and allow you to simply select the section you want rather than entering an API URL.

Notebook section dropdown functioning properly with a correct Notebook Key (click to enlarge)

Additional troubleshooting when sections are still not appearing

If you are certain you followed the instructions above correctly, made no typos, and you used the correct type of key format based on the notebook’s location (OneDrive or SharePoint), and you’re still seeing “Could not retrieve values…” for Notebook section, it might be a simple fix.

Just cut (Ctrl+X) and paste (Ctrl+V) the Notebook Key you entered into the field again and it may refresh and fix the second dropdown.

References

OneNote (Business) – Connectors | Microsoft Docs

Posted on

Solution: SharePoint Designer email action’s To: line has a valid email address, but is removed and doesn’t send when triggered

Photo by Snapwire from Pexels

If you’ve created a workflow in SharePoint Designer and an email action appears to be configured correctly, but emails aren’t being sent to some individuals in the To: line, you may need to turn the email being used into a workflow variable and use that instead of direct addition to the To: line of the email step.

This often happens for group email inboxes or external recipients that aren’t just a “normal user.” If there is a mix of recipients, some may receive the message but the troublesome addresses don’t even appear in the To: line, as if they’re removed before sending.

Note: If you’re sending to external recipients specifically and this post doesn’t solve your problem, check out my other post for additional help: Sending emails via SharePoint Designer workflow to external recipients using Gmail, Yahoo, Hotmail, etc. addresses

To “variablize” an email (this is using a SP 2010 platform workflow type):

1. Set a new workflow variable of type string to the email address(es) that aren’t receiving emails as expected.

Click to enlarge
Click to enlarge
Click to enlarge

2. Now in your email settings, use the “Workflow Lookup for a User” option and select your new variable from “Workflow Variables and Parameters” data source.

Click to enlarge
Click to enlarge
Click to enlarge

3. Publish the changes to your workflow and test.

Posted on

Create a direct link/URL to version history for a SharePoint list item or file

Imagine getting an email with a direct link to review the version history of an item or file. Or being able to have a single click from a list view to an item’s version history without going through menus.

The link itself is easy to structure manually. You could also structure it automatically using SharePoint Designer workflows or Power Automate flows.

No matter your method, you’ll need to be able to get two pieces of information: the list GUID and the item/file ID.

Getting required info

Get the list GUID

The list GUID is easy to get. Just go to the list –> List Settings and copy everything in the URL after List=.

Click to enlarge

Example:

http://SiteURL/_layouts/15/listedit.aspx?List=%7BAC2DE34F%5GH6IJ%KL789M%N01OP2%QR3STUV4WX4YZ56%7D

Get the item/file ID

In SharePoint Designer or Power Automate, you can just use the ID field provided as a lookup option. This way it’s always dynamic.

But to get the ID manually without workflow, view/open the item and check the end of the URL: for the number following “ID=”. Example:

http://SiteURL/ListName/DispForm.aspx?ID=532

For document libraries, or if you’re using modal dialogs (pop-ups), your URL won’t show ID. In this case, you can add the default ID column to your list view to get it.

Tip: For list items (not documents) you can also hover your pointer over the link to the item to see the URL preview at the bottom of your browser before clicking it.

Building the link

Manually construct it

The basic structure, no matter which method you use, is as follows:

https://SiteURL/_layouts/15/Versions.aspx?list=GUID&ID=ID

Just replace the SiteURL, list GUID, and item/file ID.

Tip: The easiest way may be to copy the entire URL when getting the list GUID, change listedit to versions, and add &ID=ID to the end.

Use SharePoint Designer

In SharePoint Designer, you can use String Builder when creating hyperlinks in emails, or when populating fields (such as a hyperlink column).

Click to enlarge

Use Power Automate

In Power Automate, you can initialize a variable to structure the version history URL so that you can use it repeatedly in different steps/conditions without having to structure it every time. We do this similarly to how we did for SharePoint Designer above. Type almost the entire URL which doesn’t change, then use the dynamic content panel to add ID to the end.

Click to enlarge

Then you can send an email and use a little code to hyperlink text with the variable used as the URL. If you’re not comfortable with code, you can just put the entire URL in the body of the message. The following shows an Outlook step in “Code View.”

First, click “Code View”

Alternative ideas

Calculated column

Note: You could use a calculated column for this, but the [ID] field needed will only populate once, then remove itself on the next property edit. The only way around this is creating another column like ID1 as single line of text and using a workflow (SPD or Power Automate) to set that field to the ID number. Then you can use [ID1] in your calculated column with a formula such as this:

="https://site/_layouts/15/Versions.aspx?list=GUID&ID="&[ID1]

Hyperlink column

Consider using SharePoint Designer or Power Automate to set a “hyperlink” type field to the URL. This could then be used in a list view to have a one-click link to version history.

Posted on

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. 

<style type="text/css">
/* Show all fields in form */
.ms-formtable tbody {
display: table-row-group !important;
}
.ms-recommendations-panel{display:none !important;}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script&gt;
<script>
$(document).ready(function(){
var spans = document.getElementsByTagName("span");
for(var i=0;i<spans.length; i++) {
if(spans[i].innerHTML == "new task") {
spans[i].innerHTML = "new request";
break;
}
}
});
</script>
view raw PTORequests hosted with ❤ by GitHub

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!

Posted on

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).
Posted on

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.):
Posted on

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”