Power Automate

COVID19 Building Access App: Per Space Approval

This week I’ll start a series on incorporating changes into the COVID19 Building Access App. The first post will show how to change the 1.1 version to include per space approval.

By default the Building Access App includes an approval process that can be automatic, based on the manager configured on Active Directory or through a post into a specific Teams channel with adaptive cards for those users that don’t have a manager configured.

The number one request I get from customers deploying the app for the back to office scenario is to have the ability to have specific users responsible for specific spaces.

The following instructions can be used to learn about the application, power apps or even to incorporate the feature in your implementation. Please be aware that those changes only work in the 1.1 version of the app.

If you don’t want to follow the steps to change your setup and just need the feature in your environment, I published a new package on GitHub with the required changes already incorporated into the 1.1 package. I versioned it as 1.11.  

https://github.com/cristianoag/BuildingAccess/releases/tag/1.11

20200811_113400000_iOS20200811_113313000_iOS20200811_113304000_iOS20200811_113320000_iOS20200811_113335000_iOS

Here are the changes that need to be implemented.

SharePoint Online

The first step consists on the creation of two additional columns on the BAR_Spaces SharePoint list to store space approvers and the flag to override the default approval flow.

  • Open SharePoint Online as administrator. Open the SPO site being used to store data for the BAR application.
  • Create an additional column of type person (Approver) in the BAR_Spaces SharePoint list.
  • Create an additional column of type Yes/No (HasApprover) in the BAR_Spaces SharePoint list. Select the default value No.

Building Admin

Now you need to update the Building Admin app to incorporate changes into the spaces detail form as well as in the gallery used to present spaces for configuration.

  • Open PowerApps as admin and edit the Building Admin app.
  • Go to Data and refresh the BAR_Spaces connection.
  • Go to Tree view and expand the Screen Space Detail page, click FormEditSpace, select Edit Fields and include the Approver and HasApprover additional fields. Change properties as required.
  • Select the DataCardValue in HasApprover (switch) and update the OnCheck event with:
Set(b_hasapprover,true);
  • Select the DataCardValue in HasApprover (switch) and update the OnUncheck event with:
Set(b_hasapprover,false);
  • Select the Approver Datacard and replace the value for Visible by b_hasapprover
  • Go to Tree view and expand the Screen Spaces page.
  • Edit GallerySpaces and include an additional label with the text “Approver:”. Make the font weight Samibold on properties.
  • Still on GallerySpaces, include an additional label connecting Text to the following formula:
If(ThisItem.HasApprover,ThisItem.Approver.DisplayName,”Default”)
  • Save and publish the app.

Building Access

The main app needs to be changed to pass the appropriate parameters to the request creation flow.

  • Open PowerApps as admin and edit the Building Access app.
  • Go to Data and refresh the BAR_Spaces connection.
  • Go to Tree view and expand Screen Availability. Expand GallerySpace and select imgAddRequest_1.
  • In the Action pane, go to OnSelect and change the content to include two additional parameters to the colChosenRequests collection. The content needs to be changed.
From this:

Collect(colChosenRequests,{FloorID:ThisItem.ID,BuldingID:ThisItem.BuildingID,DateValue:varSelectedDateString ,FloorTitle:ThisItem.Title,DatePickerValue: DatePicker.SelectedDate,BuldingName:varSelectedBuilding.Title, FloorName:ThisItem.Title,BuildingMaxOccupancy:Value( varSelectedBuilding.MaxOccupancy) ,RequestorName:varUser.displayName,RequestorEmail:varUser.mail,

OverCapacity:If( (CountRows( colDateRequests) >=varSelectedBuilding.MaxOccupancy) || varSelectedBuilding.MaxOccupancy = 0,true,false)

}) ;

UpdateContext({btnAddRequestInfoVisible:true});

//Notify(varString.RequestAddedPopupMsg,NotificationType.Success,3000)

To this:

Collect(colChosenRequests,{FloorID:ThisItem.ID,BuldingID:ThisItem.BuildingID,DateValue:varSelectedDateString ,FloorTitle:ThisItem.Title,

FloorApproverEmail: ThisItem.Approver.Email,

FloorHasApprover: ThisItem.HasApprover,

DatePickerValue: DatePicker.SelectedDate,BuldingName:varSelectedBuilding.Title, FloorName:ThisItem.Title,BuildingMaxOccupancy:Value( varSelectedBuilding.MaxOccupancy) ,RequestorName:varUser.displayName,RequestorEmail:varUser.mail,

OverCapacity:If( (CountRows( colDateRequests) >=varSelectedBuilding.MaxOccupancy) || varSelectedBuilding.MaxOccupancy = 0,true,false)

}) ;

UpdateContext({btnAddRequestInfoVisible:true});

//Notify(varString.RequestAddedPopupMsg,NotificationType.Success,3000)

  • Save and publish the app.

BARCreateRequests Flow

The BARCreateRequests flow needs to be changed to update the appropriate parameters in the request.

  • Open Power Automate as admin, click My Flows and edit the BARCreateRequests flow.
  • Expand InputRequests and change the schema to include FloorApprover (string) and FloorHasApprover (boolean) input parameters.

From:

{

“type”: “array”,

“items”: {

“type”: “object”,

“properties”: {

“BuildingMaxOccupancy”: {

“type”: “number”

            },

“BuldingID”: {

“type”: “integer”

            },

“BuldingName”: {

“type”: “string”

            },

“DatePickerValue”: {

“type”: “string”

            },

“DateValue”: {

“type”: “integer”

            },

“FloorID”: {

“type”: “integer”

            },

“FloorName”: {

“type”: “string”

            },

“FloorTitle”: {

“type”: “string”

            },

“OverCapacity”: {

“type”: “boolean”

            },

“RequestorEmail”: {

“type”: “string”

            },

“RequestorName”: {

“type”: “string”

            }

        },

“required”: [

“BuildingMaxOccupancy”,

“BuldingID”,

“BuldingName”,

“DatePickerValue”,

“DateValue”,

“FloorID”,

“FloorName”,

“FloorTitle”,

“OverCapacity”,

“RequestorEmail”,

“RequestorName”

        ]

    }

}

To:

{

“type”: “array”,

“items”: {

“type”: “object”,

“properties”: {

“BuildingMaxOccupancy”: {

“type”: “number”

            },

“BuldingID”: {

“type”: “integer”

            },

“BuldingName”: {

“type”: “string”

            },

“DatePickerValue”: {

“type”: “string”

            },

“DateValue”: {

“type”: “integer”

            },

“FloorID”: {

“type”: “integer”

            },

“FloorName”: {

“type”: “string”

            },

“FloorTitle”: {

“type”: “string”

            },

“FloorApproverEmail”: {

“type”: [“string”, “null”]

            },

“FloorHasApprover”: {

“type”: “boolean”

            },

“OverCapacity”: {

“type”: “boolean”

            },

“RequestorEmail”: {

“type”: “string”

            },

“RequestorName”: {

“type”: “string”

            }

        },

“required”: [

“BuildingMaxOccupancy”,

“BuldingID”,

“BuldingName”,

“DatePickerValue”,

“DateValue”,

“FloorID”,

“FloorName”,

“FloorTitle”,

“FloorHasApprover”,

“OverCapacity”,

“RequestorEmail”,

“RequestorName”

        ]

    }

}

  • Expand Scope-CreateRequest. Expand ForEachRequests and create a Get User Profile step just before the CreateRequests step. Use the formula below for the UPN value.

if(items(‘ForEachRequests’)[‘FloorHasApprover’],items(‘ForEachRequests’)[‘FloorApproverEmail’], if(variables(‘varManagerExists’),body(‘Get_manager_(V2)’)?[‘mail’], variables(‘UserEmail’)))

  • Expand CreateRequests. Update the formula for ApproverNameText to:

if(items(‘ForEachRequests’)[‘FloorHasApprover’],body(‘Get_Space_Approver_Profile’)?[‘displayName’],

if(equals(variables(‘varManagerExists’),true),body(‘Get_manager_(V2)’)?[‘displayName’],”))

  • Update the formula for ApproverGuid to:

if(items(‘ForEachRequests’)[‘FloorHasApprover’],body(‘Get_Space_Approver_Profile’)?[‘id’],

if(equals(variables(‘varManagerExists’),true),body(‘Get_manager_(V2)’)?[‘id’],”))

  • In Approver, Update the formula for Claims to:

if(items(‘ForEachRequests’)[‘FloorHasApprover’],items(‘ForEachRequests’)[‘FloorApproverEmail’], if(equals(variables(‘varManagerExists’),true),body(‘Get_manager_(V2)’)?[‘mail’],null))

  • Save the flow and test the application. It should be able to deal with all approval cases, including approval per space.

Dealing with Languages in the Back to Office App

Last week I published about the Back to Office (Building Access) app and the response was AMAZING! This blog had visits from almost 12,000 unique users from 65 countries in a span of 5 days. I couldn’t believe that! Thank you so much.

Since then I’ve been busy supporting dozens of customers with the tool setup and supporting our internal teams to better understand Teams as a platform and how leverage the application for the complex moment we live.

The setup is super easy and lasts less than a hour. The package shared on GitHub has detailed instructions on how to run the configuration flows and import the apps into your environment. 

As I deal with non-English speakers most of the time, there are a few things I would like to document to help others.


“The exhibition mode is invalid” error when running the DeploySPLists flow

You just ran the initial configuration flow (DeploySPLists) and got a error saying something like “the exhibition mode is invalid”. You may get the error message in your native language.

clip_image001

(more…)

COVID19 – Back to Office App

During the last three weeks I had the chance to see an idea become reality in a super short time.

When I started in my current team, my colleagues were just starting to think about scenarios that could be useful for our customers in our new reality and that could show the power of Microsoft 365 platform. No additional licenses, no upselling, just how to leverage Microsoft platform for a real urgent need.

Europe was facing the peak of the COVID-19 crisis, but seeing light. Customers started to think on how they could perform soft openings and welcome employees back to their offices in a responsible way.

The new reality would require reduction in occupancy, social distancing, extra protection and processes to ensure controlled access to buildings and spaces dedicated to work.

The first version of the Back to Office App is now ready! Three weeks from an idea to a functional app. Built in Power Apps, tight integration with Microsoft Teams and available free of charge for any Office 365 customer (no additional licenses required).

As economies and businesses reopen, organizations need a way to gradually open their office facilities for their workers while keeping everyone safe. Facilities teams are working to restructure building layouts, and seating arrangements to maintain social distancing norms and control building occupancy thresholds. They need a way to manage, track and report employee onsite presence.

Annotation 2020-06-22 154521Annotation 2020-06-22 154354Annotation 2020-06-22 154606Annotation 2020-06-22 154849

Solution Overview

  • Suite of three Apps – Building Access user app for employees to request access, Building Admin companion app to manage building availability, occupancy thresholds & eligibility criteria and Building Security app to verify onsite access and check in users.
  • Released as production ready ‘open source’ App template on GitHub that can be configured and adapted by organizations based on their business needs. https://github.com/microsoft/powerapps-tools/tree/master/Apps/BuildingAccess
  • Built using Power Apps, Power Automate, SharePoint and Power BI with deep integration with Teams for collaboration.
  • Included Power BI Dashboard provides executives and facility managers insights need to manage facility readiness with current and projected occupancy rates.
  • Leverages seeded Power Platform licenses in Office 365 for the three Power Apps.
  • Available in 44 different languages from day 1.

Personas

Four different personas/profiles interact in a collaborative process to ensure safe return to office.

Executives & Facilities Managers

Employee (Requestor)

Manager (Approver)

Security Team

  • Define availability and occupancy threshold for each building.
  • Define key eligibility criteria (health related questions) for onsite access
  • Define criteria for auto approval vs manager approval
  • Ensure only approved workers are allowed onsite
  • Track workers onsite at any location worldwide and projected turnouts
  • Publish important instructions for onsite access
  • Leverage insight for Planning
  • Request onsite building access for one or more days (in daily blocks)
  • Check on submitted requests and track approvals and rejection.
  • Withdraw requests as plans change
  • Ability to check-in and check-out
  • Pull up the approval QR Code for security check in (for restricted entry)
  • Check on latest company news
  • Approve onsite access for direct reports once the auto approval thresholds are met
  • Ability to bulk approve/reject requests
  • Track team members that will be onsite at an office location
  • Confirm worker is approved for entry prior to allowing access to premises
  • Check in workers by scanning a QR code or by pulling up information using key employee data
  • Provide emergency approvals for onsite access

The package is now available on GitHub. Talk to your Customer Success Manager, engage our partner ecosystem, install the tool, customize if required. You’re free to use it to support a safe return to Office.

Reach out if you have any questions or need help to understand the tool, requirements, etc.

Cristiano.

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

Using Power BI to Report Bookings Events

During COVID times we are getting more and more customers using Bookings. The service, which is part of Microsoft 365, now has the ability to use Microsoft Teams to offer virtual sessions.

Annotation 2020-05-22 165853

Possibilities are endless. Banks that allow customers to interact virtually with account managers or investments experts, hospitals and clinics that offer virtual consultation (tele medicine), remote psychology sessions in challenging times, are just a few scenarios where customers can leverage Bookings.

As soon as Bookings get setup and people start using it to create appointments and have virtual sessions the work refocus to how report.

How about a way to connect Power BI to bookings and extract appointment information? That is possible.

When a new business is created on Bookings, a new user calendar is created in Exchange Online and we can connect Power BI to extract that information and build reports.

In the sample below I created two businesses in Bookings: TestHospital01 and TestHospital02.

Annotation 2020-05-20 161608

As you can see in the Booking Page status, each Hospital has a correspondent user created on Exchange Online/Office 365 and the user calendar is used to manage the appointments for each one of the hospitals.

Now that we have a correspondence between each business/hospital to a Exchange Online calendar we can leverage Power Platform to do a few interesting things. We can use Power Automate to fire specific actions based on events related to schedule/cancel appointments and we can also user Power BI to report on top of information extracted from calendars.

Focusing on Power BI, we can connect to calendars created by Bookings and report. There are two ways to do that: using Microsoft GRAPH and Power BI ODATA connector or using the native Exchange Online connector. The following steps show how to acquire data using the native ExO connector.

1. Open Power BI Desktop and click Get Data, search for the Exchange Online connector and click Connect.

Annotation 2020-05-22 174210

2. In Mailbox Address type the SMTP address of the user associated to the first business you created on Bookings. If you don’t know the SMTP address, open Bookings, click on Booking Page and copy the address from the Booking Page Status. Image below show the SMTP address in the Booking Page Status and the Power BI Mailbox Address text box.

UntitledAnnotation 2020-05-22 175233

3. If prompted to authenticate, please specify the credentials of the user that created the business in Bookings.

4. Select Calendar and click Transform Data.

Annotation 2020-05-22 175348

5. If you have more businesses to include on the report, click New Source and repeat the process I just described specifying the SMTP addresses for each business. In my case I’m adding two businesses. Test Hospital 01 and Test Hospital 02.

6. Now we need a way to consolidate all the information in a single table so we can create better reports. You can do that using the Append Queries command in Power Query. Just Click Append Queries and select Append Queries as New.

Annotation 2020-05-22 175641

7. Select the tables you are going to consolidate and click OK. In my case I’m creating a third table appending data from two other tables that have data from Test Hospital 01 and Test Hospital 02.

Annotation 2020-05-22 175751

8. The append command will create the Append1 table. In my case I decided to include an additional column on the resultant table to indicate the source (which hospital). To achieve that I used the Advanced Editor in Power Query to edit the M formula used to create the table. The code I used is below as a reference.

Annotation 2020-05-22 180200

let
Source = Table.Combine({Table.AddColumn(TestHospital01,”Source”, each “Hospital 01″)
, Table.AddColumn(TestHospital02,”Source”, each “Hospital 02”)})
in
Source

9. Now we have Append1 table with the consolidated data and we’re ready to clock  Close & Apply and start building reports.

Good luck.

-Cristiano

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

Post Moderation with Yammer (part2)

This is a post related to my previous article on Yammer moderation.

This time we will expand the idea I presented previously. In the first article I presented an alternative to moderate posts in Yammer where Power Automate was being used to send Teams approval cards to content administrators and after approval/rejection content was kept or removed depending on the admin desire.

That approach works ok for the vast majority of cases, however there are some situations where the content posted by end users cannot be kept in the Yammer group while we wait the content admin intervention.

The Power Automate flow I’m presenting now solves that issue storing the post content in a SharePoint list and then, when approved, the message is reposted to the Yammer community/group.

There are two versions of the new flow.

The first version uses the Yammer post body to store a tag that indicates the message was approved. Based on that information the flow decides if the message needs to be deleted and stored temporarily in SPO until its approval or if it can just be left on Yammer group.

The second version builds on top of the previous and use a random post passwords to ensure nobody can workaround the control and create the tag manually to pre-approve the post.

In order to use the samples you will need:

  • Admin credentials for your M365 tenant.
  • Access to Power Automate premium connectors as we will use HTTP premium action to delete messages posted on Yammer.
  • The Yammer API authorization token (I’ve shown how to create it on the part 1)

Here are the steps to build the flows and play with the sample:

SharePoint List

Create a SharePoint Online list with the following columns:

  • Message ID (text, single line)
  • Group ID (text, single line)
  • Message (text, multiple lines)
  • Status (choice with ‘New’, ‘Approved’, ‘Rejected values’)

Here is a screenshot of the list I have in my test tenant.

image

Power Automate Flows

First Flow

The first flow has two parts. The first part intercepts the message posted on Yammer, check if the [APPROVED] tag is present and based on that saves the post content on the SPO list, deletes the message from Yammer and sends the approval card for the content administrator.

Annotation 2020-05-20 160413

After the answer to the approval card send using Teams, the flow continues the execution based on the content administrator decision. If the admin approves, we create a new post to Yammer with the [APPROVED] tag and update the status column on SPO with the Approved status. If the admin rejects, we just update the SPO status value.

Annotation 2020-05-20 160809

Second Flow

This flow is a bit more elaborated and use a random generated password as the approved tag. Instead of just tag approved messages with [APPROVED] we are using a password that users cannot guess, thus unable to bypass the approval flow.

Annotation 2020-05-20 161117

This sample is storing the approval password on the body of the message. Best alternative could be store on the Yammer post topic collection. That approach will introduce additional complexity as Yammer deals with topics using collections. To simplify the example I’m using a string stored on the post body.

Annotation 2020-05-20 161408

If the post has the Password: string the body, then we need to check if that value is existent on the SPO list that controls the approval process. If it is approved, then we do nothing and the message is posted to Yammer. If it is an attempt to bypass the system, then we just delete the message from Yammer.

Annotation 2020-05-20 161608

If the message doesn’t have the approval/password tag, then the flow is similar to the previous one, and the message gets stored on SPO, deleted and the approval message is sent to content administrator.

Let me know if you need additional info to build the flows.

Have fun. –cristiano

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

Post Moderation with yammer (part1)

Moderation with Yammer.

Always a hot topic to discuss. When I was in sales I found myself in that discussion multiple times with customers and always tried to articulate the moderation requirement versus the nature of the social network… which is freedom to create unstructured content and collaborate.

Anyways. The requirement still exists and cannot be a blocker for customers to realize the value of the Microsoft 365 platform. I don’t want to see customers looking into competitor solutions and spending more just because of that!!

Thinking about the problem with the perspective of the whole M365 platform I immediate thought about using Power Automate (former Flow) to intercept Yammer posts and moderate. The issue with that approach is due to how the new post trigger in Power Automate fires up. The message needs to be posted into the group first to have the flow started… but we need to control if it can be posted or not, just after the approval process…

Ok, then what if we can support a light moderation approach? That is, users will post to Yammer and a moderator will receive an approval message in Teams. If message is approved, then nothing needs to be done. If it is rejected then the flow can call Yammer APIs and simply erase the message from the community/group.

Definitely not the ideal solution as the post will stay there until the moderator reject it, but this is definitely good enough for the vast majority of scenarios I’ve seen so far.

Well. With the contribution of my colleague Marcos Zanré we figured out how to implement the scenario. Details below.

By the way, stay tuned for the part 2 of this article. I’ll document how to implement a more elaborate approach to deal with the post staying in Yammer until its rejection issue.

Create a token to authorize calls to Yammer APIs from Power Automate

Open Yammer as admin and select the Apps settings page.

Annotation 2020-05-15 141408

Click on My Apps and then Register New App.

Annotation 2020-05-15 141534

Fill the Register New App form with the name of your application, organization, support e-mail, website and Redirect URI. We don’t need specific values for the website and redirect URI at this moment, so you just need to fill those with any valid URLs.

Annotation 2020-05-15 142010

Select the checkbox after read the Yammer API Terms of Service Smile and then click Continue.

Click on “Generate a developer token for this application”, copy and save the token value. We will need it for the following steps.

Create the Power Automate flow

Open https://powerautomate.microsoft.com and authenticate using an administrator credential.

Click My Flows, and then New. Select Automated – from blank option.

Annotation 2020-05-15 142536

Give a name for the Flow and select the Yammer “When there is a new message in a group” trigger.

Annotation 2020-05-15 142717

Click Create.

Update the trigger object with appropriate values for Group ID and Network ID according to your needs. In the case below I’m using the Leadership group in the Contoso Yammer network.

Annotation 2020-05-15 142900

Click on the + New Step button below the Yammer trigger and search for Teams related actions. You may choose other options here, I’m using the “Post a choice of options as the Flow bot to a user” but you can choose the best options for you, depending on your needs.

In Options Item, include Approve and Reject options.

In the Headline, include the text “Please approve or reject the post to Yammer Leadership group”. Update Recipient with the e-mail address of the moderator. Note that Power Automate will resolve the address to the user object.

In Message, use the “Message List Message Body Text” value that you can find on the Dynamic Content window.

Annotation 2020-05-15 143958

Click Save.

Now you will need to include a condition to take actions depending on the user response in the card sent using Teams. That is pretty straightforward with Power Automate.

Click New Step and selection Condition. In the Condition box, use the Dynamic Content window to select the selectedOption value for the box on the left, choose “is equal to” and type “Approve” in the text box on the right.

Annotation 2020-05-15 144422

Now you will implement an action to delete the message from Yammer if the moderator rejects it. We will use the HTTP Power Automate action to perform that.

Important! Please note that the HTTP action is part of the premium connector set. You will need appropriate licenses assigned to your user to have access to those premium actions when using Power Automate.

More information about how to license Power Automate is available at Microsoft Power Apps and Power Automate Licensing Guide.

Click Add an Action in the red box and search for HTTP. Select the HTTP action.

In Method, select DELETE. In URI use the Dynamic Content window to select the Yammer “Message List Message Url” value.

In Headers, include the key Authorization and the value “Bearer “ combined with the Token ID you generated in the Yammer console before. Very important that you concatenate Bearer, the space, and the token value in a single line.

Leave the other fields blank and click Save.

Annotation 2020-05-15 145733

Test

Go ahead and post a message in the Yammer group you are moderating. The user you included in the Teams action will receive a card to approve/reject the post and the post will be kept or removed from Yammer depending on the result of the approval process.

image  image

Have fun! –Cristiano.

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

%d bloggers like this: