Note: Applies to SharePoint Online/O365 and modern experiences only.
In SharePoint Server/on-prem, we have to manage content types and allow links to documents before we can link to documents outside the current document library. But in SharePoint Online/O365, there’s a Link option on the New menu that does all the work for us, and without even needing to adjust the library’s content type settings.
Modern experience in SharePoint Online/O365
In a modern-view document library, simply use New > Link.
Then paste a URL to the file, or select it from recent files which, yes, will include files modified even outside the current library.
This will add a link/shortcut within your document library to the document stored/managed elsewhere.
Today I ran across an issue where someone had created links within a classic document library that redirected users to documents stored in a different library. This is easy to do, but for some reason those links were now leading users to blank .aspx pages instead of the intended document.
Note that users weren’t taken to an “invalid” or “can’t be found” error page, but a completely blank page with a URL ending in .aspx. If you’re being redirected to anything other than a blank page the following solution probably won’t apply to you.
I figured out that, somehow, the library in question no longer had the “Link to a document” content type included. You normally can’t delete a content type that is in use, but with the right permissions and perhaps a migration tool or script, anything is possible. Without the content type on the library, the links that once worked under that content type now could not.
Important: The links are not necessarily broken – do not delete them. Once the content type is added again, they should work unless the original URLs have actually changed.
To re-add the link/shortcut content type to the library, follow these directions (same as if you were adding it for the first time):
1. Go to Library > Library Settings
Click to enlarge
2. Choose Advanced settings
3. Set Allow management of content types to Yes.
4. Click OK to save changes.
5. Under Content Types choose Add from existing site content types
Let’s create a Flow in Power Automate that accomplishes the following:
Sends an email every Monday morning
Includes a table of upcoming training opportunities from a SharePoint calendar
Table has a column with “Add to calendar” download links
1. Create a new flow of type Scheduled – from blank
2. Name the flow and set the schedule to whatever frequency and times are appropriate for your needs.
Click to enlarge
3. Choose your site and list name. In my case, my calendar isn’t listed so I’ll type its name, Events, after select Enter custom value.
Click to enlarge
4. To filter to just the events coming up in the next week, we’re going to expand “Show advanced options” and enter filter criteria.
5. If you, like me, are going to filter based on “Start Time” you’ll need to know its internal name which is EventDate. For our filter, we can only use internal field names. To get started, copy and paste the following filter query into your Filter query box:
EventDate gt '' and EventDate lt ''
6. Now click between each set of apostrophe ( ‘) marks and change the dynamic content panel to Expression as seen below.
First expression: utcNow()
Second expression: addDays(utcNow(),8)
Click to enlarge
7. Now add a Create HTML table step. Use the dynamic content panel to set “From” to value from the Get items step (1). Then Show advanced options (2) and set Columns to Custom (3).
Click to enlarge
8. Under Header, type each column header you want in your table in the order you want it to appear horizontally. For Event I’ve mapped its Value to the “Title” field on my SharePoint calendar by selecting Title from dynamic content. You may also wish to add columns like Location and Category. Get your headers in the order you like, but leave Date and Save/Add to Calendar blank for now.
Click to enlarge
9. Once you’ve gotten all but Date and Save mapped to a SharePoint calendar field, it’s time to use Expression (on the dynamic content panel) to set up the remaining two. You can copy and paste Date’s formula as long as you’re using Start Time (EventDate) for the date. Save, however, you’ll need to update with your own site collection’s URL (where the calendar lives) and replace the GUID with your own calendar’s GUID. Follow steps 1-2 in this post to get your calendar’s GUID.
Save: concat(‘<a href=”https://YourSiteURL/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EA8795A%2D3B0D%2D43D7%2DA48E%2DB3CCD4BFE950%7D&CacheControl=1&ID=’,item()?[‘ID’],’&Using=event.ics”>Add to my calendar</a>’)
Click to enlarge
10. Now add a Compose step. This will make sure our links are clickable in the email (helps decode the HTML so that it parses and renders correctly). We’ll populate this, again, by using an Expression.
11. Lastly, add a Send an email step. I preferred using the html markup option and added an image to my header. You can use either the default rich text or html editor. To insert your table, make sure you insert Outputs (from dynamic content > Compose step).
Click to enlarge
That’s it! In 11 steps, you’ve scheduled a weekly digest that will email recipients a table of clickable “Add to calendar” links for upcoming opportunities.
To test your flow, rather than wait for the start of your schedule, use “Test.”
To elevate this concept to the next level, you can adjust your HTML filter to only get a certain category and send multiple emails to subscribers of different categories (say, only PowerApps subscribers getting PowerApps courses). You could also create multiple HTML tables and show “SharePoint courses” in one table, “OneNote courses” in the next and so on.
When working with calendars, a big request I hear is to make it more like Outlook or to make it easy to add an event to your calendar, at least. In SharePoint online, this is easy! The Events web part on modern pages includes an Add to my calendar button on events by default.
Click to enlarge
However, when working with classic pages or SharePoint Server/on-prem, it’s not so easy. There are two ways we might utilize Power Automate or SharePoint Designer to help us out:
We could create a hyperlink column if we want something on-page/in-item, then populate it using a workflow.
Usage idea: A landing page for upcoming training opportunities displayed in list (not calendar) format with a column designated for “Add to my calendar” links
If it doesn’t need to be clickable on the item or page within SharePoint, we could just build the URL within the workflow and include it in an email message.
Usage idea: A Flow that runs weekly to “Get items” coming up that week and sends a list out with clickable links for adding items of interest to recipients’ calendars
URL structure
No matter the tool, Power Automate or SharePoint Designer, the most important part to know is how to build the URL. That won’t change from one tool to the other.
1. Go the the list settings for the calendar hosting the events
Click to enlarge
2. Copy the list GUID from the URL in the browser. This includes everything in the address bar after “List=”. This should begin with %7B and end with %7D. This is your calendar’s GUID in hyperlink-friendly formatting.
Click to enlarge
3. Update the following URL template with your site’s path, and paste in the list/calendar GUID you copied from step 2 where GUID is. Leave the [ID] as it is for now.
https://Site or Subsite path/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=GUID&CacheControl=1&ID=[ID]&Using=event.ics
If not updating a field or creating a variable that pieces the URL together, you can create expressions (via Dynamic content panel) to concatenate the different parts of the URL. For example, if I’m creating an HTML table, for Value I’d use the dynamic content panel > Expression and enter a formula like:
concat('<a href="https://natechamberlain.sharepoint.com/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EA8795A%2D3B0D%2D43D7%2DA48E%2DB3CCD4BFE950%7D&CacheControl=1&ID=',item()?['ID'],'&Using=event.ics">Add to my calendar</a>')
In this specific example, I’m creating the table after a “Get items” step. The formula above is what I’m using for the value of the “Save” (Add to Calendar) column.
Create iCal (.ics) links using SharePoint Designer
In SharePoint Designer, we can set a hyperlink field to our iCal link to make it easy to add an event to your calendar. This could be placed as a main column in a list view, or just on item display forms like this:
Let’s set the hyperlink field via workflow:
1. Create a variable (set workflow variable) and use the string builder
2. Paste your almost-finished URL from earlier in this post. Replace [ID] with a lookup to the current item’s ID
3. If setting a field (skip if just using the link elsewhere) add , Add to Calendar (or whatever link text you want) to the end of the string. It just has to be a comma, a space, and the text.
Click to enlarge
4. Set the hyperlink field (iCal in my example above) to your new variable.
If you’re not setting a field in your list, maybe you’re emailing new events to people and want an easy link in the email body itself. In that case, just skip step three above and your variable will just be the URL, ready to be used in email actions.
Click to enlarge
Why can’t we just use calculated columns?
Once upon a time, I blogged about creating Automatic iCal hyperlinks using a calculated column. This almost works. It creates the hyperlinks for all existing items at the time of the calculated column’s creation. But then if you add a new item or modify an existing item, the [ID] field drops out the hyperlink which, of course, breaks the link.
The appeal of calculated columns is that it won’t create another version of an item when the link is generated and it doesn’t require Power Automate or SharePoint Designer to work. Unfortunately, if the link doesn’t work after item edit or creation, then the point is lost anyway. So let’s pretend that method doesn’t exist except for one-time uses or lists that will never change again.
Setting a hyperlink column’s value using Power Automate is a bit different than setting other column types’ values. In SharePoint, a hyperlink column has two components – address and description. If you update this column type using Power Automate’s “Update item” action, your address and description are both set to the same value.
In SharePoint Designer workflows, we could do this easily with the usual “Set field” action:
But in Power Automate, we only get one field which maps to the URL/address part of the hyperlink field, and is duplicated as the description as well in SharePoint.
And if you’re thinking it could work with a comma, as some other field types might, it won’t. You’ll get “Enter a valid uri.”
To get this to work so we can set both address AND description as separate values, we have to use an HTTP request action instead of (or in addition to) the Update item action. This isn’t as complicated as it may sound. Here are the steps:
1. Add the Send an HTTP request to SharePoint action
2. Choose or enter your site for Site Address
3. Set Method to POST
4. Set the Uri to the following, changing List Name to your list’s name, and replacing [ID] with the ID field from dynamic content
Test that out. When it’s working and you’re ready to take it to the next level, you can replace the static url (like Google’s in my example) with dynamic content. For example, you could create convenient one-click links to an item’s version history:
If you’re trying to create column validation using [Today], you’ll likely get the following error:
Sorry, something went wrong. The formula cannot refer to another column. Check the formula for spelling mistakes or update the formula to reference only this column.
Commit this to memory: when [Today] or Today fails in a formula, try Today().
While [Today] may work in list view filters, it’s not the correct format for column validation or calculated columns. You can, instead, use Today() for column validation.
For example, you could use EndDate<=Today() to require users to enter an EndDate that is on or before the date of submission.
The following column validation formula worked fine in SharePoint on-prem (2016 specifically, in my case), but returned an error when used in the exact same context in SharePoint Online’s modern UI:
EndDate<=Today()
The expected behavior, in SharePoint Server/on-prem OR SharePoint Online/O365, is that if someone enters a date beyond the current date, they’ll get an error message and cannot submit the form until it’s corrected and the validation formula resolves to TRUE.
Troubleshooting in SharePoint Online
I used this formula in SharePoint Server/on-prem, and it worked fine. Then I tried using the modern UI in SharePoint Online by using the column’s menu > Column settings > Edit.
But when you try to save the exact same formula (specifically from the modern experience side panel) you get the error “A formula has a syntax error.”
Then I decided to try the classic view of settings to compare on-prem and online as closely as possible. I went to Settings > List settings and selected my column.
And, as you already know, it WORKED when entered on this classic column settings page (in SharePoint Online still) instead of the modern column settings side panel accessed directly from the list view.
When I go back through the modern UI now that my formula saved successfully, I see what caused the problem. The modern UI requires that you begin the formula with an equals sign (=). When I created the formula through the classic column settings method, it automatically added the equals sign for me in the background.
Solution
So if you’re creating column validation formulas in the modern experience (or even in classic), just remember to add an equals sign (=) to the start of your formula.
Click to enlarge
The difference is simply which formats are accepted.
Classic: Start formulas with or without equals sign
As a rule of thumb (not to mention for improving your Secure Score), you should limit the number of people who have the “global admin” role in your organization. Microsoft recommends fewer than 5 global admins. That makes it important to get to know the other roles available and assign the least permissive role (a phrase you’ll see frequently if seeking certifications) rather than blanket roles that often include more permissions than what are necessary (or secure).
Global admins can assign other admin roles, purchase additional products and subscriptions, reset all (including each others’) passwords, and manage absolutely everything in your tenant. So of course you can see why we’d want to restrict how many are working with these capabilities simultaneously.
You may end up assigning five different, non-global admin roles to a user instead of the single global admin role, but your security will be improved significantly.
There are a couple places to assign admin roles: the Azure AD portal, and the M365 admin center. My goal with this post is to consolidate and simplify information on the roles, including which are only available in Azure. I’ve combined information from:
Those marked with * are only available to assign from Azure AD. All others are in both the M365 admin center AND the Azure portal.
Note: Most role descriptions are copied directly from the resources listed above as of date of publish and are subject to change. Always check Microsoft documentation prior to making significant decisions.
Full access to Microsoft Search, assigns the Search admin and Search editor roles, manages editorial content, monitors service health, and creates service requests.
Can manage calling and meetings features within the Microsoft Teams service. Assigns telephone numbers, creates and manages voice and meeting policies, and reads call analytics.
Shifts, formerly StaffHub, is an app available for use within Microsoft Teams to share schedules and allow employees to submit requests. There is also a mobile app that can be used for many of the same desktop features, and adds a clock in/out functionality for members.
In this post, I’ll go over some Shifts basics to give you an idea of what’s possible. Please note you must be a Team owner to set up Shifts for your team.
Set up Shifts for the first time
To get started using Shifts, open Teams and select the ellipses from the left menu. Here you can select the Shifts app.
Next, you’ll choose the Team for which you’re configuring Shifts. Click Create next to the correct Team.
Next, confirm the time zone you want to use for this team. Once correct, click Confirm.
Add groups/types of shifts
Next, you’ll want to do some planning. What types of Shifts are you going to be scheduling? For each “type” of shift, you’ll create a group.
Click Add group and enter a name for each group/type of shifts or roles.
Click to enlarge
Once you’ve added all the possible types of shifts, you may wish to reorder the shift groups. Just click the “move” button on any of the groups, then drag the groups into the order you wish and save.
Click to enlarge
Add members to groups/roles
Choose who will have shifts in each group by selecting the “add people to group” icon.
Add members you wish, then close the dialog.
A person can be a member of multiple roles. For example, I may have shifts in concessions AND the admin office. You must add these members manually to each group they’ll appear in – they are not automatically added to all groups.
Add shifts for members
You can double-click in any square on the schedule to add a shift.
Click to enlarge
If you add a shift to “Open shifts” you can assign it later.
Click to enlarge
Share/publish the schedule
As you add Shifts, changes will be marked with an asterisk meaning only you as an owner can see them. You must Share with team to publish changes and let others see the schedule.
Click to enlarge
After clicking Share with team, you choose which dates to “publish” and whether to notify the entire team or just those affected by the changes/additions.
Click to enlarge
Remember to “share” every time you make changes so members are aware of your edits or new schedules.
Adding time off for members
To add time off for a team member, just right-click a square in their row and choose Add time off. Time off will appear in ALL groups for that team member – you can’t have a sick or vacation day in one shift group, and work in another.
You can classify these time off shifts in different ways, and save when satisfied.
If someone requests time off after they’ve already been scheduled you can move their affected assigned shifts to open shifts to re-assign later or have them make a request for someone to cover them.
Requests
Your team members, not just owners, can make their own requests for time off, swaps, or offers (giving a shift up without taking another in return). This takes some of the administrative burden off owners and makes employees accountable for their own changes and communication.
Time off requests
Employees can request time off, which sends a request for approval to the manager/owner.
When time off has been requested, a pulsing orange dot will appear with the request on the schedule itself, not just in the Requests tab.
Swap requests
Members can offer to exchange shifts with another person. The other employee must accept the proposal for it to take affect. When making the request, your employees will be able to tell which “group” the shift is in easily.
Offer requests
Sometimes employees just want to give a shift away without taking another in return. They can “Offer” shifts to other employees which sends the other employee a request for approval (similar to swaps).
Click to enlarge
Printing schedules
Not all members may have easy access to Teams at all times, so you can easily print the schedule and post anywhere.
Using the “scale to one page” setting to make sure it will fit on your desired paper size.
Click to enlarge
Time clock
The next logical piece, after scheduling, is the actual shift performance itself. Shifts even includes a Time Clock feature which allows mobile clock-in and clock-out with optional location detection for reporting.
Click to enlarge
If you choose to turn on location tracking, you must enter the coordinates from which you expect employees to be clocking in and out.
From the Teams mobile app, team members can then clock in and clock out. They can edit clockings before confirming (in case they forgot to clock in/out) but the export/report will note the entry was edited. When clocking in/out, members will be notified if they’re noticed as off-location but can still clock in/out with that note added to the record.
The export (Shifts > Settings > Time Clock > Export) will highlight those clock-ins and clock-outs that were off-location:
Click to enlarge
Conclusion
Shifts comes at no additional cost with your O365 subscription. It could easily replace your Excel sheet schedules or expensive clock in/out software. While it may not be the best solution for all scenarios, I’d argue it could be a game-changer and cost-saver for many organizations and committees or event staff.
