Using the Power Platform to Improve Employee Experience – The Technical Solution

In Part One of this series, I outlined how we used the Microsoft Power Platform and low-code solutions to build a workflow that improves employee’s in-office experience.

That post can be found here: Using the Microsoft Power Platform to Improve Employees’ In-Office Experience.

In Part Two, I’ll dive deeper into the technical components. I’ll point out issues I ran into, and solutions to work through them so you won’t spend time troubleshooting.

Build the In-Office Event Table

This part of the solution was the easiest to build. It involved creating a table to hold our In-Office Events (whether they be employee-focused or company-focused) so we could report on them. The table was created with a few columns:

  • Name (automatically set to the employee’s name in the next step with the actionable emails)
  • Date
  • Type (automatically set Employee in the next step with the actionable emails, or set to Company when our office coordinator creates our company’s culture events)
Note, for the Type field we also added Column Level Security so that only administrators, our office coordinator, or other designated users can create a “Company” type of In-Office Event. All other employees can only create and manage their own events.

Build the Actionable Email

The next step was to create an Actionable Email using Adaptive Cards. This was the most complicated step as there was a lot of troubleshooting along the way with why it wasn’t working the way we expected. There are a couple of links I’ll drop here that were helpful with our building out of the adaptive card which may be helpful to you as well:

The first thing we needed to do was to go to https://adaptivecards.io/designer/ and create our Adaptive Card. 
 
When you first land on the page, change the host app to Outlook Actionable Messages.
 
 
Next, we needed to start building out the look and feel of our card.
 
To remind you, in our previous post, we wanted the card to look like the following:
 
 
We’ll use the above end result to walk through the steps needed to build out the adaptive card. 
 
Click on the New Card button in the top left, and select Blank Card as we’ll build it out.
 
Now drag a TextBlock from the left pane of Card Elements, to the canvas in the middle. 
 
As you do this, you’ll start seeing the Card Payload Editor on the bottom start building out the JSON we’ll need for our Cloud Flow later.
 
 
 
On the right Element Properties panel, you can set properties of the element selected, and it too will affect the JSON in the Card Payload Editor. 
 
Or you can edit the Card Payload Editor directly.
 
For this TextBlock, we’ll want to set the following properties:
  • text:  You can leave this as whatever you’d like as this will be replaced dynamically with the Cloud Flow
  • wrap: true
  • id: give it a unique id such as “CompanyEvents”

We’ll add a few more card elements now in the order below, with the following properties set:

  • TextBlock
    • text:  “Please select the days next week you plan to be in the office, and click Submit.”
    • wrap: true
    • id: give it a unique id such as “Heading”
  • Input.ChoiceSet
    • Choices (title and value):  Monday, Tuesday, Wednesday, Thursday, Friday
    • placeholder:  Can keep the default “Placeholder text”
    • id:  give it a unique id such as “choiceSet
    • isMultiSelect:  true
  • Input.Text
    • placeholder:  Can keep the default “Placeholder text”
    • isVisible: false
    • value: set to something whatever you want as this will be replaced with the Cloud Flow
    • id: name
  • Input.Text
    • placeholder:  Can keep the default “Placeholder text”
    • isVisible: false
    • value: set to something whatever you want as this will be replaced with the Cloud Flow
    • id: emailid
  • Input.Text
    • placeholder:  Can keep the default “Placeholder text”
    • isVisible: false
    • value: set to something whatever you want as this will be replaced with the Cloud Flow
    • id: weekstart
The last 3 Input.Texts are hidden and will be used in the weekly Cloud Flow that sends each user their individual actionable Outlook email. 
 
We’ll need to send the users name, email, and date so that when they respond, we can pull that data back into the response Cloud Flow to create records in D365 for us.
 
To finish the adaptive card, we’ll need to add an Action. If you select the entire card in the canvas, an “Add an action” button will appear in the bottom right corner. Click that, and then click on Action.Http.
 
 
 Set the following properties on this action:
  • title: Submit
  • method: POST
  • id: response
  • url: Put anything in for now – this will be replaced by the URL of you Response Cloud Flow
  • body: This part is a little tricky and requires creating JSON to pass the values from the actionable card to the Response Cloud Flow.  The format you’ll need to enter this JSON is below (and it includes the id’s of your card elements from above):

    {
    “choices”:”{{choiceSet.value}}”,
    “name”:”{{name.value}}”,
    “email”:”{{emailid.value}}”,
    “weekstart”:”{{weekstart.value}}”
    }

You’ll now also need to add an HTTP header since a Cloud Flow that’s triggered from an HTTP Request requires this and this will be our Response Cloud Flow. Add the following headers:
  • Authorization: [LEAVE THIS BLANK/NULL]
  • Content-Type: application/json
After you’ve built out this adaptive card, your Card Payload Editor should have JSON that looks like the following (highlighted in red are the parts that will dynamically change in the next flow).
 
{
    “type”: “AdaptiveCard”,
    “$schema”: “http://adaptivecards.io/schemas/adaptive-card.json”,
    “version”: “1.4”,
    “body”: [
        {
            “type”: “TextBlock”,
            “text”: “New TextBlock“,
            “wrap”: true,
            “id”: “CompanyEvents”
        },
        {
            “type”: “TextBlock”,
            “text”: “Please select the days next week you plan to be in the office, and click Submit.”,
            “wrap”: true,
            “id”: “Heading”
        },
        {
            “type”: “Input.ChoiceSet”,
            “choices”: [
                {
                    “title”: “Monday”,
                    “value”: “Monday”
                },
                {
                    “title”: “Tuesday”,
                    “value”: “Tuesday”
                },
                {
                    “title”: “Wednesday”,
                    “value”: “Wednesday”
                },
                {
                    “title”: “Thursday”,
                    “value”: “Thursday”
                },
                {
                    “title”: “Friday”,
                    “value”: “Friday”
                }
            ],
            “placeholder”: “Placeholder text”,
            “isMultiSelect”: true,
            “id”: “choiceSet”
        },
        {
            “type”: “Input.Text”,
            “placeholder”: “Placeholder text”,
            “isVisible”: false,
            “value”: “value“,
            “id”: “name”
        },
        {
            “type”: “Input.Text”,
            “placeholder”: “Placeholder text”,
            “isVisible”: false,
            “value”: “value“,
            “id”: “emailid”
        },
        {
            “type”: “Input.Text”,
            “placeholder”: “Placeholder text”,
            “isVisible”: false,
            “value”: “value“,
            “id”: “weekstart”
        }
    ],
    “actions”: [
        {
            “type”: “Action.Http”,
            “title”: “Submit”,
            “method”: “POST”,
            “id”: “response”,
            “url”: “https://www.microsoft.com/en-us/“,
            “body”: “{\n\”choices\”:\”{{choiceSet.value}}\”,\n\”name\”:\”{{name.value}}\”,\n\”email\”:\”{{emailid.value}}\”,\n\”weekstart\”:\”{{weekstart.value}}\”\n}”,
            “headers”: [
                {
                    “name”: “Authorization”,
                    “value”: “”
                },
                {
                    “name”: “Content-Type”,
                    “value”: “application/json”
                }
            ]
        }
    ]
}

Start your Response Cloud Flow

We’ll need this part created so that we can use the URL generated for the next couple of steps.  

Create a Cloud Flow that has an “When an HTTP request is received” trigger, and a single Initialize Variable step. This way we can save the Cloud Flow and grab the value that’s populated in the 

For the trigger, put the following JSON in the Request Body JSON Schema field:

{
    “type”“object”,
    “properties”: {
        “choices”: {
            “type”“string”
        },
        “email”: {
            “type”“string”
        },
        “name”: {
            “type”“string”
        },
        “weekstart”: {
            “type”“string”
        }
    }
}

The Flow should look like the following, and you’ll want to grab the HTTP Post URL value:


Actionable Email Provider

We’ll need to create a new Provider for the Actionable Email and get it approved before the email that’s sent out via our Cloud Flow using the JSON created above will display in your users inbox.

To do this, we’ll need to go to the Actionable Email Developer Dashboard and create a new provider by clicking the + New Provider button.

For development and testing, you can create a Provider with the Scope of Submission being “Test Users” and it’ll be automatically approved. These emails will work for those sent from yourself, to yourself.

However, for primetime, you’ll need to create a Provider where the Scope is Organization. Your Exchange Administrators will receive a notification for approval.  

Provide the Provider a friendly name, the email address that your actionable email message will be sent from, and the URL from the Cloud Flow in the step above. 

Note: When you push the Cloud Flow above to different environments (e.g., Dev to Test, and then Test to Prod), each of those environments will have a different URL. Therefore, you’ll need to put ALL URL’s in the Target URL’s section of your provider for all environments to work.

Note, if your Exchange Administrator isn’t notified, have them navigate to https://outlook.office.com/connectors/oam/Admin and they should see an approval pending.

Once approved, you’ll want to capture the value in the “Provider Id (originator)” and save it for a future step below.

Also note that it could take up to 24 hours after approval for your emails to start working per the note below.

Weekly Polling Cloud Flow

The next step is to create the Cloud Flow that’ll run weekly, and use the adaptive card JSON created above to send out the Actionable Emails.  

We won’t go into all the details of creating the Cloud Flow, as a lot of the actions are typical actions for other automations. At a high level, this flow is performing the following steps every Friday at 10am CT:

  1. Initializing some string variables
  2. Finding In Office Events that are Company Events to include in the polling email
  3. Finding Users that are associated with the Chicago corporate office
  4. For each user, generating the JSON and sending the email
Step 4 is the one that we’ll dive deeper into, but at a high level, this is what the steps of this Cloud Flow should look like:
 
 
Now the “Apply to each User” step is where we’ll use the adaptive card we created previously. This step should take the list of values from the previous “List Enabled Users” step.
 
 
The first step inside this loop will be a Compose step where you’ll copy/paste the JSON from your adaptive card you created previously above.  It should look similar to what’s below:
 
 
Note that we highlighted a few areas above:
  • You’ll need to add an “originator” value using the “Provider Id (originator)” copied from the section “Actionable Email Provider” above. 
  • You’ll need to change the version from 1.4 to 1.0.  We’re not sure why but leaving it as 1.4 generates a blank email.
  • We also highlighted one of the first dynamic pieces of content that we’ll insert into this JSON from the other steps in the Cloud Flow.  All of areas to update in the JSON are:
    • “text” of the “CompanyEvents” id:  Set this to the Company Events you found in a previous step. We used a variable to store this information.
    • “value” of the “name” id:  Set this to the expression:
      concat(items(‘Apply_to_each_User’)?[‘firstname’],’ ‘,substring(items(‘Apply_to_each_User’)?[‘lastname’],0,1))
    • “value” of the “emailid” id: set this to the domainname (User Name field) from the “List Enabled Users” step.
    • “value” of the “weekstart” id:  set this to the expression utcNow()
    • “url” of the “Action.Http” Submit button:  set this to the URL from the “When a HTTP request is received” Cloud Flow you created above.  Note:  We actually used an Environment Variable here because, as mentioned before, this URL will be different in each environment (Dev, Test, and Prod).  Therefore, to avoid having to modify the Cloud Flow every time we do a deploy, we stored that different value in an Environment Variable and can just have that value updated there.

The last step is to send the email. Make sure to add html to your email and include the Output from the Compose step above, wrapped in between the following <script> tag:

<script type=\”application/adaptivecard+json\”></script>
 

Completing the Response Cloud Flow

We had previously built this Cloud Flow with an initial “When an HTTP request is received” trigger, and an Initialize Variable step. We’ll now enhance this with a few more steps to record the submission.

First off, we added a Compose step with the following JSON. This JSON is to respond back to the Adaptive Card to let the user know that their submission was successful. We know this should come at the end of the flow, but we noticed the flow taking longer than the adaptive card wait time for a response. Therefore, the adaptive card never received the response and instead threw an error.

The JSON we created was:

{
  “type”: “AdaptiveCard”,
  “version”: “1.0”,
  “body”: [
    {
      “type”: “Container”,
      “items”: [
        {
          “type”: “TextBlock”,
          “text”: “Thank you for your response.  Your response has been successfully submitted”,
          “id”: “response text”
        }
      ]
    }
  ],
  “$schema”: “http://adaptivecards.io/schemas/adaptive-card.json”
}

The next step was an HTTP Response step sending back a 200 response with the JSON above, and a “CARD-UPDATE-IN-BODY” header.

To get the days of the week that the user responded with, we used the expression split(triggerBody()?[‘choices’],‘,’) and stored them in an array variable.

Then we looped through these responses with another “Apply to each” step for each value in the array variable.

For each of the responses, we searched our In-Office Events table for an existing record for the current employee and date in the variable. If one existed already, we didn’t create a dupe. Otherwise, we created an In-Office Event for the employee and date. 

We used this expression to get the actual date from the values provided in the choices passed from the adaptive card:

if(equals(‘Monday’,variables(‘varChoicesReturned’)[variables(‘varCount’)]),addDays(formatDateTime(variables(‘varWeekStart’)),3),if(equals(‘Tuesday’,variables(‘varChoicesReturned’)[variables(‘varCount’)]),addDays(formatDateTime(variables(‘varWeekStart’)),4),if(equals(‘Wednesday’,variables(‘varChoicesReturned’)[variables(‘varCount’)]),addDays(formatDateTime(variables(‘varWeekStart’)),5),if(equals(‘Thursday’,variables(‘varChoicesReturned’)[variables(‘varCount’)]),addDays(formatDateTime(variables(‘varWeekStart’)),6),if(equals(‘Friday’,variables(‘varChoicesReturned’)[variables(‘varCount’)]),addDays(formatDateTime(variables(‘varWeekStart’)),7),null)))))

Remember, our varWeekStart was also passed in from the Actionable Card, and that was utcNow() which was a Friday since we sent out our Actionable Card email on Fridays. Therefore, we add 3 from that Friday to get the Monday, 4 for Tuesday, 5 for Wednesday, 6 for Thursday, and 7 for Friday.

Final Piece of the Pie - The Weekly Summary Email

The last piece of the automation is the Weekly Summary Email that’s sent out on Sundays indicating what company events are happening that week, and how many people will be in each day, with links to the Power BI report (we didn’t cover this above, but it’s a calendar visual off of the In Office Events table), as well as a link to D365 for the user to modify any of their In Office Event selections.