Delivering a Consolidated Snapshot from Multiple SharePoint calendars

Home / Blog / Delivering a Consolidated Snapshot from Multiple SharePoint calendars

Calendar-Based Information is a key foundation for Intranets

Businesses need to share multiple types of date-based (calendar) information with their employees.  Most of us have at least three categories of dates we publish to our staff.

  1. Official Company Events, such as all-employee meetings, important HR paperwork deadlines, and social events.
  2. Notices and Alerts, to let people know about operational activities, such as IT's maintenance windows, facility issues like out-of-order services, and important on-site visitors.
  3. Employee Announcements, such as new hire start-dates, employment anniversary dates, assignment transitions, promotions, and employee birthdays.

An important part of an intranet’s job is to get this information out of eMail, and deliver it in a relevant and easily consumable way--Consolidating it down to a What’s Happening This Week or Today’s Events area on the Home Page.

Company Events (Events)
Operational Notices & Alerts (Notices)
Employee Announcements (Announcements)

While we need this information front & center, we also need to keep some lines of demarcation between the category buckets.  People think about each of these buckets differently, and each has its own levels of urgency.  Notices & Alerts are likely to change regularly throughout the day.  Company Events aren’t changing as frequently; but when they do, it is important everyone knows about them.  And for most of us, the employee announcements only need to be checked once a week or so.

Each of the events have multiple related categories, tags or metadata.  A lot of value can be extracted from this metadata.  When embraced by the organization, it is an important and easily accessed record of activities eliminating the need for anyone to manage their own archive of this info--which we all know many people do.

SharePoint Doesn’t handle this in a Modern & fluid way yet

Unfortunately, SharePoint’s Modern functionality hasn’t yet delivered an easy way to manage this information natively.  Today’s SharePoint Event Calendar is a Modern wrapper on legacy functionality.  The Modern list, library and page functionality that many of us now rely on, hasn’t yet been introduced to Calendars.  I have my fingers crossed that it will reach calendars at some point soon, but there isn’t confirmation from MS, other than “we’re thinking about it.”

Metadata and flexibility to reveal this information in different ways on a page, are where I see the biggest foundational gaps today.  The updated Event web part looks nice, but if you open-up the hood, it is still the same old lawn-mower engine in there.  If Modern SharePoint is a sportscar (which I think it is), Events are a Karmen Ghia, not a Porsche.

So how can we deliver this important functionality today?

I’ve gone through each of the approaches available, tested them, analyzed them, and come-up with the approach that I think is best.  It is a native O365 solution, it keeps the data clean, and it should support an easy transition to whatever solution Microsoft delivers in the future to solve the calendar gaps.

During the process, I tracked my exploration using a MindMap flowchart, which I’m sharing here in case you're interested in seeing the pros & cons of the various approaches..

circle back to review detail after you've read the full article, otherwise it could be overwhelming.

There’s really two parts to this issue:

  1. Data entry
  2. Revealing the right information in an easily consumable format on a SharePoint page

Data Entry

In my idealized scenario, I've segregated major categories of events with separate communication sites for each.  This allows you to maintain News articles and calendars that are separate and still retain the single native “Category” calendar field for “sub-categories" (shown here).

Company Events (Events)
Official events - Important deadlines - Training opportunities - Sponsored events - Holidays & closure dates
Operational Notices & Alerts (Notices)
Visitors - Maintenance windows - Operational alerts - Facility alerts - Equipment Alerts - Change Management reminders
Employee Announcements (Announcements)
Promotions - Team/Staff Reassignments - New Hires - Staff Exits - Birthdays - Employment Anniversaries - Personal Announcements
Highlighting an event as an important one

All events are not created equally, some are important, and need to be called-out so people pay more attention to them.  This is a very critical requirement for critical operational and key business communication.

One of my previous posts outlines how to highlight important news articles using two meta-columns. We'll use this same approach for highlighting important Calendar Events.

The only bump is that Contributors must be in a Classic event view when working with the highlight fields. The Modern event interface doesn't reveal custom columns on the create/edit form.  This issue is eliminated by adding a button near all event views, to take the visitor to a monthly view, and then train your contributors to create their events here.

Specs for the additional Metadata fields this approach requires, are at the end of this article.

Exploration of how to Reveal the Information

The key issue here is the need to share this information in an Easily Consumable format--something that only requires a glance, with all relevant information in one place.  A simple overview.

Native SharePoint

After exploring, it was clear that a purely native SharePoint solution doesn’t exist today.

If you know me, you know that I’m committed to a native environment, so the next step was to explore other options within the Office 365 ecosystem that wouldn’t require the sort of custom programming that could “break” with a future update.

Office 365 Options outside of SharePoint

Using the SharePoint calendars as a data source, Power BI is able to deliver a reasonable view.  It has the added benefit of being accessible most anywhere—including via. Cortana, which is nice value-add.  But the design/visuals fell short, and as a citizen developer (not a true developer), I’m concerned about the impacts on page performance, which seemed pretty evident on the surface.

Once I dove into PowerApps, it was clear that I’d be able to consolidate all the information needed into a small amount of space, and have it make immediate sense to any user.  It looks lovely and can be pulled into any page through the existing web part.  This approach has the added benefit of delivering this key information as a mobile app, making it even more accessible to employees.

I'm going to explain this approach in great detail.  I've also decided to share this PowerApp with the community, so you can use it to jump-start your own Intranet's calendars.  A download link is at the bottom of this article.

I would love to hear feedback, but I won't provide specific technical support to you set it up in your environment.

2-Minute Video Demo

Anatomy of the Company Happenings Power App

Site Architecture Snapshot

My intranet's calendars are each on their own site.  All sites are Modern Communication sites.  The News & Events site is my Intranet's "Home" and it is a Hub Site for my other communication Sites.  I've renamed each of my site's events calendars to reflect their purpose, rather than having 3 calendars all titled "Events".

The data source options between SharePoint and PowerApps is flexible.  You could have multiple calendars residing within one site, or many different sites with many different calendars.

Company Happenings App "Home" Summary Screen

  • Brand with your Intranet's color palette
  • Reveal on any SharePoint page using the PowerApps web part

The Next 7 Days

  • Shows only next 7-days’ items
  • Color-coded section for each of the calendars.
  •  View detail screen for that Calendar
  • Each Calendar’s list scrolls individually when there are more items to see
  • notes important items tagged by contributor in custom “highlight” column
  • All lists in chronological order.
  • Next 7 Days - Data shown:
    • Calendar (Primary Category)
    • Weekday of Event
    • Event Title
    • Highlight
    • Filtered to show:
      • Start date is less than 7-days from now
      • End date/time has not passed

All Important Events

  • Only pulls from Events calendar
  • Shows ALL items tagged for highlight, no matter how far in the future they are.
  • All Upcoming Events – Data Shown:
    • Events calendar items only
    • Date/Time of start
    • Filtered to show:
      • End date/time has not passed
      • Flagged "Highlight" in "Highlight Indicator" field

Calendar’s Detail Screen

  •  Return to app Home screen
  •  Refresh data
  • Items’ Start date & End date are shown vertically at start/end of item listing
  • notes important items tagged by contributor in custom “Highlight Indicator” column
  • Scroll to see all of the event’s “body” text
  • Bottom row for each event includes:
    • Location
    • Category
  • Click on “More” to open event’s SharePoint page in the full window.
    • Legacy detail page
  • Background picture is event page’s Header image when one is input by the contributor.  Otherwise blank.
  • Filtered to show:
    • Start date is less than 7-days from now
    • End date/time has not passed
    • Exception for Important Events, shows all future events tagged as important.

All Screens of the Company Happenings App

Next Steps

I'm giving this away for free, but it doesn't come without a price
Your price is feedback

I'd like to hear how you use it, and your feedback on ideas that would make it better.  If they're suggestions that are applicable to a broad audience, I may revise and publish updates.  I don't care too much if you don't like my color scheme, or selection of fonts, so please don't share your thoughts about that.  But true, actionable feedback that could make the app better would be appreciated, and may be rewarded.  😉

Send feedback to Beth.Hall@StraightenTheMaze.com

If you're interested in using the Company Happenings App on your Intranet.

 

  1. Download the package.
  2. Instructions on how to import a PowerApp package to your environment
  3. Once Imported, Open the App in edit mode
  4. Create Data Connections to your SharePoint Calendar Lists and change all of the data sources to your data sources.  You will need to change all of the sources on all of the screens.
  5. Follow the configuration instructions below for the Metadata fields on your SharePoint calendars and the App filters.

 

Power App Package

Notes:

  • I created the app in, and exported it from my Lab environment.
    • I successfully loaded it into my production tenant, which does NOT have the same architecture (actually has very different architecture).
    • These two tenants are on different release cycles for SharePoint.
    • It worked fine in both.
  • Uploading the app to my second tenant and changing the data sources was a pretty simple process.  But if you don't know PowerApps, it may be a challenge.  I'd suggest that this is not a good 1st timer activity in PowerApps--you'll do better with a little experience in the environment first.
  • It is important that you don't delete my data sources until you create yours and point to them.
    • Otherwise, you may lose the filters and sorts.
    • Filters and sorts were a real pain to set-up, so be careful here--especially if you don't know PowerApps.
  • PowerApp doesn't automatically see SharePoint Calendars as data sources when you load a SharePoint Site.
    • However, if you type in the name of the calendar in the "Enter custom list name" field, it will connect to it just fine.
  • My data source calendars all include the custom Metadata fields "Highlight Indicator" and "Highlight expires" as detailed below.
    • This is what triggers the Star element on important events.
    • If you do not have these fields on your lists you will get an error.
    • If you do not want this feature:
      • Delete all of the "Star" elements from all of the galleries.
      • Modify the home screen to delete the bottom "Important Events" section
      • Delete the Important Events Detail screen
    • If you want to create the same functionality, but base it on yes/no fields rather than the Managed metadata, your filter will be simplified, and will reference the actual field data rather than the GUID.
      • I did this in my lab, here's the filters that worked for me.
        • Filter for all Star items, on all screens and galleries, in the "Visbile" field
          • If(Highlight=true,true,false)
        • Important Items Gallery Data Items (load filter)
          • Filter(Events,EndDate>Now()
            And
            Highlight=true
            )

 

Metadata Configuration

This app is set-up to work in my environment, following the "Straighten The Maze approach."  I use Universal Site Columns to manage metadata fields.  I do this so that I can have consistency throughout the ecosystem, and only have to change the information in one place, and have it propagate everywhere.  I use Term sets for all multiple choice columns that are used in more than one place.  The approach I've described for highlighting and promoting content using Metadata, like I've done with the calendar items and stars, is an approach used throughout my ecosystem. Setting it up as a Managed term set gives me utmost flexibility for future changes, and consistency.

That said, it requires a little-bit of set-up, but then you can use it to highlight other things (like important news articles) as well.

Metadata Set-Up

Term Store Group

In your tenant's Term Store, set-up the following Term structure and terms

  • Term Group:  "Intranet Operations"
    • Term Set: "Highlight Item"
      • Term: "Don't Highlight"
      • Term: "Highlight"

Calendar list custom columns

Create the following columns in all of the calendars you are going to use with the app.

Highlight Indicator

  • Type:  Managed Metadata
  • Description:  "Flag item for short-term promotion"
  • Required: No
  • Enforce Unique Values: No
  • Allow Multiple values: No
  • Display term label in field
  • Use Managed Term Set: "Highlight Item"
  • Allow Fill-in: No
  • Default value: Don't Highlight

Highlight Expires

  • Type: Date & Time
  • Description: "Provide last date for short-term promoting"
  • Required: No
  • Enforce Unique Values: No
  • Date & Time Format: Date Only
  • Display Format: Standard
  • Default value: None

 

App Configuration for Metadata

When using Managed Metadata, the Terms you select aren't actually stored in the list columns, a reference to them is.  This reference is called the term's GUID.  The reason for this is that it allows you to change the term, or move it to someplace else in the term hierarchy, and the column data will reflect that change.  This means that when you pull these columns into Power Apps or Flow, the words aren't in the column, a long code of characters is.

In this app, the filtering for important events is done with the GUID for the term "Highlight"  This GUID code will be different on your site/tenant, than it is in mine.  There's a secret list on every site that lists the GUID for all terms used on the site, so we'll go to that secret list to copy it and then change the filters in the App.  Note that for a term to show up in this list, it must be used, it cannot be just in the term set, but must be selected somewhere on your site.  So before we move forward, create an event and mark it to highlight it.

Now go to the secret list and find the GUID for the term "Highlight".  It will look something like this:  b10eb84d-e653-4ec0-8241-ad08d8490746

Access your hidden list here:  <Tenant>/<Site>/Lists/TaxonomyHiddenList/AllItems.aspx

The filter code that you need to change the GUID for is for the star element that is used in 3 of the home page galleries, and on each of the detail screens.  To change, select the Highlight Star, and go to the "Visible" field, change my GUID to yours.  Here is an example of the filter code:

If(ThisItem.Highlight_x0020_Indicator.TermGuid="b10eb84d-e653-4ec0-8241-ad08d8490746",true,false)

the GUID is shown in red.

Good luck, I hope you try this puppy out and please let me know how it goes, and how folks like it.

Cheers!   Beth

Leave a Reply

Your email address will not be published. Required fields are marked *