Posted on

Alertus integration in SharePoint via Microsoft Flow

I’m by no means an HTTP request expert, but the requirement of being able to integrate business applications is common and, in this case, important. I took some time to figure out how we could implement Alertus integration and am sharing what worked here.

Background

Alertus is a program we use at LMH Health to push mass-notification desktop and mobile alerts to the organization during times of severe weather, evacuation procedures, or just software downtime.

Image from alertus.com

We use SharePoint as a supplement for “breaking news” by adding a banner to the top of our site during times of urgency. We wanted to integrate Alertus with SharePoint so that when an alert fired, it created a breaking news banner in SharePoint as well. This makes sure users who aren’t on a managed device, such as working from a home computer, are still able to get important news if they happen to be on the intranet.

In times of emergency, it’s best to utilize as many channels as possible to inform your users. Integration of these two services meant there’d be minimal delay in broadcasting a single message through all available channels.

How it works

To send the Alertus alert data to SharePoint, we needed something capable of receiving an HTTP request so Flow came to mind first. Once configured, your Alertus administrators will just send alerts as usual, being sure to include SharePoint in the alert profile.

  1. Create and send an alert in Alertus
  2. Flow receives HTTP request with alert data
  3. Flow creates item in SharePoint list used for breaking news

And, of course, with Flow we could then do any number of actions including additional HTTP actions, sending to email or phone, etc.

Note: This works for SharePoint server and SharePoint online. If using server, you’ll need the on-premises data gateway so that Flow can connect to your environment.

Setting it up

We need to do a few things to get this working:

1. Start creating a Flow (to get the HTTP POST URL)
2. Create the Alertus Service for “SharePoint”
3. Add the Alertus Service “SharePoint” to any Alert profiles for which you want to include SharePoint for distribution
4. Finish the Flow
5. Test

Start creating a Flow

1. Create a new Flow with the When a HTTP request is received trigger

2. Paste the following into the Request Body JSON Schema

{
    "type": "object",
    "properties": {
        "Content-Type": {
            "type": "application/json"
        },
        "id": {
            "type": "string"
        },
        "clientName": {
            "type": "string"
        },
        "clientVersion": {
            "type": "string"
        },
        "sender": {
            "type": "string"
        },
        "message": {
            "type": "string"
        },
        "sentDateTime": {
            "type": "string"
        },
        "expires": {
            "type": "string"
        }
    }
}

3. Expand Show advanced options and make sure method is POST

4. Copy the HTTP POST URL at the top of the step using the copy icon

Create an Alertus alert service for SharePoint

1. Go to Alertus, then click Configure System > Alert Services

2. Click +Add Alert Service

3. Select HTTP Request for Service Type, then name and describe the service.

4. Configure the rest of the alert service as follows, pasting in the content provided below

Click to enlarge

Activation Headers

Accept: application/json, text/plain
Content-Type: application/com.alertus-v1.0+json

Activation Body

{ "id":"${alert.originId}",
 "clientName":"${alert.clientName}",
 "sender":"${alert.sender}",
 "message":"${alert.text}",
 "sentDateTime":"${alert.sentDate.yyyy-MM-dd'T'HH:mm}",
"expires":"${alert.expirationDate.yyyy-MM-dd'T'HH:mm}"
}

5. Click Save Alert Service

Add the service to alert profiles and/or preset alerts (optional)

Your service is now available to be added to alert profiles (pre-selected sets of services to notify under certain circumstances, such as an evacuation) and preset alerts (customized alerts and notification groups).

Alert profile example of adding the new SharePoint service we created
Preset alert example of adding the new SharePoint service as a delivery method for the alert

You could, alternatively, choose not to include it in anything preset and only use it manually upon creation/configuration of an alert.

Finish the Flow

1. Go back to the Flow you started.

2. Add a HTTP Response step and make sure the Status Code is set to 200. This lets Alertus know the request was received and prevents an error from occurring on the Alertus side.

3. Add a SharePoint: Create item step and connect to the site and list for which you want to create an item from the alert details.

Test

From Alertus, do a Custom Activation of a test message just for the SharePoint service/delivery method.

In the example below, I also threw in an email alert as a step but you can see the whole process in Flow’s run history still only took 2 seconds.

Check in SharePoint that the item was added. In my case, we use custom script to display the most recent list item as an active alert.

Additional Configuration (optional)

Now that you’ve done the basics, you may wish to make adjustments.

  • Check out Alertus’ Knowledgebase (must be a customer with login credentials) at https://my.alertus.com/ .
    • Search for HTTP Request (Documentation) where you’ll find additional placeholder variables you could use in the Activation Body and JSON Schema fields to send additional details.
  • Add additional steps in Flow
    • An approval process before posting the details
    • An email or text message to a person or group
    • Update a spreadsheet/log
    • Post a message to Teams

Posted on

Use Microsoft Power Automate (Flow) to automatically convert RTF files to DOC

I once ran into situations with users where some data files are exported from software solutions in .rtf or .txt format only. To improve the user experience once these files are dropped into SharePoint, we can convert any .rtf file automatically to .doc.

Why convert from RTF?

When RTF files are opened in the browser, the option to “Edit in browser” is grayed out since the file type isn’t compatible with that functionality.

Click to enlarge

Okay, but why .doc and not .docx?

Unfortunately, we can’t convert from RTF to DOCX without the use of Azure functions (thanks, Pieter Veenstra for that info). But if we can at least convert it to DOC for users, they’ll get the “Edit in Browser” option which will then prompt them to convert the file to DOCX in two clicks (Convert –> Edit). Then we finally have a .docx file.

Click to enlarge

How to auto-convert .rtf to .doc using Power Automate (Flow)

Update 12/3/2020 (big thanks to Dawid Ziolkowski for the tip on trigger condition)

Create the flow (.rtf to .doc)

  1. Create a new flow with the trigger When a file is created or modified (properties only) and select (or enter) your site and library name.
  2. Click the ellipsis and Settings.

3. Add a Trigger Condition as @endsWith(triggerOutputs()?[‘body/{FilenameWithExtension}’],’rtf’) and click Done.

Now the flow will only run if the file’s extension is RTF.

Next, configure each next step as pictured below (beginning with the blue-outlined Get File content using path step), careful with file paths not to include the whole URL – just the relative path beginning something like /Shared Documents/…

  1. Trigger step for when file is created (we already configured this with the trigger condition)
  2. Get file content using path (gets contents of the .rtf file – use File name with extension dynamic data from step 1/red)
  3. Create file (creates new .doc file using the .rtf contents – use dynamic data from step 2/blue)
  4. Delete file (deletes original .rtf file to avoid confusion – use Identifier from step 1/red)
Click to enlarge

That’s it! Now when an RTF file is added, it’ll be replaced with a DOC version and the RTF file will be deleted. Any non-RTF file won’t be touched, and won’t trigger/count as a flow run.

Expand the flow (optional – delete .doc if .docx created)

Users can easily convert .doc to .docx in two clicks in the browser. But it leaves the original file (.doc) behind which can be confusing for users. So we can create a separate flow to delete a .doc file IF a .docx by the same name is created.

It starts out the same as our first flow, with a “when a file is added” step paired with a trigger condition but looking for .docx this time.

Create the second (optional) flow (delete .doc version if .docx version with same name exists)

  1. Create a new flow with the trigger When a file is created or modified (properties only) and select (or enter) your site and library name.
  2. Click the ellipsis and Settings.

3. Add a Trigger Condition as @endsWith(triggerOutputs()?[‘body/{FilenameWithExtension}’],’docx’) and click Done.

Then use a “Get file metadata using path” step to get any file by the same name from that library (use the “Name” dynamic content from the trigger step and add a .doc). Then in your “delete file” step, simply use the ID from the get file metadata step.

Now when a user converts a .doc to a .docx file in the browser, your Flow will delete the .doc that’s left behind if there’s a match. Note that this runs on ANY .docx file in the library when added/modified, and will fail if no .doc name match is found. There’s probably a cleaner way to do this.

While these are certainly not ideal workarounds, it may be the best you’re able to do with the resources on hand. Not everyone has Azure, or developers, premium add-ins/vendors or the budgets for such things. Best of luck!

Posted on

“The selected file doesn’t contain template elements” error in Microsoft Flow

I recently tried to use the “Populate a Microsoft Word template” step in Microsoft Flow (currently in preview) to insert text into content controls, but ran into the error above.

The selected file doesn’t contain template elements.

The issue was that my content controls in the template were of rich text format and date. This preview step currently only supports plain text, combo box, and dropdown content controls.

Plain text, combo box, and drop down are the only supported content controls as of the publishing of this post.

So for all of your text fields, make sure you use the correct (plain text) control:

Once I replaced my rich text content controls with plain text, the content controls showed up in Flow as options for populating:

To keep this organized, I recommend giving each content control a title (in its properties) so you can easily identify each field when in Flow (select content control, then “Properties” from developer tab).

Remember, you’ll only see supported content type fields in Flow. Even if you have a date content control, you won’t be able to populate it using Flow.
Posted on

Use the Flow recurrence trigger to run flows only on weekdays

Running a flow on every weekday or certain weekdays

Rather than using Flow’s recurrence trigger with a frequency of “days” combined with switch cases/conditions, you can actually just use the “Week” frequency time unit and select days from a drop-down with no further effort required.

For weekdays, just select Monday-Friday. Or if you just want MWF, you could do that as well.

A techie way to do it

The alternative is to initialize a variable such as

@and(greater(dayOfWeek(utcNow()),0),less(dayOfWeek(utcNow()),7))

with a switch case to determine if today’s date is, in fact, between 0 (Sunday) and 7 (Saturday).

Compare today’s date with holiday calendar

So that being said, the limitation of using the “Week” frequency for weekdays might be if you want to prevent it from running on holidays when nobody is in the office even if it is, in fact, a Monday. In that case, you could add a condition that checks to see if @utcNow() matches items from a SharePoint list (holiday calendar?).

  1. Initialize variable (integer) value 0
  2. Get items from SP list (calendar)
    1. You could add an ODATA filter in the Get items step to only filter to items with Start times greater than today
  3. Apply to each –> IF utcNow()=SP item, THEN increment variable by 1, ELSE nothing
    1. Use the expression builder to formatDateTime both dates to be sure they match when being compared
  4. IF variable is greater than 0, do nothing (don’t run), else run the rest of the Flow
Posted on

Flow now supports multiple condition controls for advanced if/then scenarios within a single step

Say goodbye to nested if/then statements in Flow taking up fourteen monitor widths. Flow now supports nested if/then statements all in a single, vertical step. For example, the following requires that WeekDayNum is not 0 or 7 AND requires that either Bob or Nate created the item. And I didn’t have to scroll horizontally at all to see it!

The next time you use a condition control, enjoy rethinking how you might structure your various requirements for conditions to be met.

To get started, just add a condition control as you normally would:

  1. Add an action
  2. Condition control


Enjoy!

Posted on

Use Microsoft Forms and Flow to create Mad Libs

Have a holiday party coming up? Staff meeting you want to spice up? Send a form out to attendees in advance to collect adjectives, nouns and verbs and showcase your favorite completed libs at your meeting. Or just do it for fun – because work should be fun. Go ahead and try my test version to see for yourself!

Make your own (short version)

  1. Create a form at forms.office.com with questions for adjectives, nouns, etc. Be sure to collect email addresses as well so you can send participants the completed mad lib. You can use my template
  2. Create a flow at flow.microsoft.com that pulls responses into an email template. You can import the flow I built

Detailed steps

Create the form to collect words

Go to http://forms.office.com

Create an account if you don’t already have one (it’s free!)

Note: It must be an organizational account – Flow cannot currently connect to “personal” Forms accounts.

Create a new form or use my template (open link and click “Duplicate it” at the top)

  • If you just created an account
  • If you already have an account

Add a title, subtitle/instructions and then any questions/word parts you want. You must include email address as a required field if you intend to email the results to someone.

If you’re giving people multiple mad libs to choose from, you must also require a choice field like in my example.

Create the flow to send completed mad libs

This is the part that takes form submissions and turns them into the actual mad libs. It’s easiest to import the flow I built.

  1. Log into your account at https://flow.microsoft.com
  2. Go to your flows, click Import and upload the zip file you downloaded

Now select your existing connections for Forms and Outlook.

If you don’t already have an Outlook and/or Forms connection, you’ll need to click “Create new” and add them, then come back to connect them in the previous step. You can also modify the Flow to use Gmail or HTML emails instead. If you use HTML emails, however, they’re more likely to go to spam or be blocked since they come from a well-known “marketing” address rather than an individual (yourself).

Once you’ve set your connections, click “Import” (you should no longer see red x’s next to the connections under “Related resources”)

Once imported, click “Open flow”

Check every step, especially the “Forms” step to set the correct Form connection, and correct other fields like “email body” variables as needed.

Note: My Flow template has multiple mad lib options. If you just have one, you don’t need the “switch” at all (which is really just a conditional statement).

When finished, click “Save” in the upper right, go to your flows and make sure it’s “On”.

Finally, copy the “Share” URL from your form and send it to people to complete! Have fun!

Posted on

Use Microsoft Flow to get the number of items in a SharePoint list or library

Sometimes you need the number of items in a list or library for reporting, notifications, or just curiosity. The following details three methods you can use to get the count of items for different purposes.

  1. Use Microsoft Flow to get the number of items and use in various ways
  2. Add “count” to the top of a classic view SharePoint list for all to see
  3. Quickly find “count” just for your information in site contents or list settings

(Video at bottom of first section)

Continue reading “Use Microsoft Flow to get the number of items in a SharePoint list or library”

Posted on

Microsoft Flow vs SharePoint Designer (SPD) Approvals

WORKFLOWvs

Perhaps one of the most useful automated processes out there is the ability to do approval processes. We fortunately have two tools on-prem or online that allows us to perform this action. Microsoft Flow offers some incredible connectivity between services (like approve a Tweet and post it, approve something from Google Docs and have it moved to SharePoint, etc.), but the approval process itself is very simple at this point and doesn’t offer some of the more robust features and customization options we get in SharePoint Designer 2013 approval processes.

I also will use both tools in the same business process occasionally, because they both have unique strengths.

But which do you use for approvals?

The quick answer to the question is: Use Flow for simple approvals, or approvals that involve multiple sites or external services. Use SPD for more complicated processes and customization options for approvals that involve a single site.

Continue reading “Microsoft Flow vs SharePoint Designer (SPD) Approvals”

Posted on

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”