COVID19 Building Access App: Questions per Building

Part two of the Building Access App improvements article. This time I’m documenting steps to incorporate questions per building. With this new version you will not only be able to have different approvers per space but you will be able to configure different sets of questions per building.

You can use this version for scenarios where a specific building needs to have additional screening to make employees eligible to make reservations.

20200812_165218000_iOS20200812_165226000_iOS20200812_165239000_iOS20200812_165342000_iOS20200812_165349000_iOS

All the steps required to perform the required changes into the v1.1/v1.11 version are documented below. You can also access the modified package on GitHub using the link below:

https://github.com/cristianoag/BuildingAccess/releases/tag/v1.12

SharePoint

We will create two additional columns on the questions list stored SharePoint to create the relationship between buildings and questions. Additionally we will also create a column on the AppSettings list to store a flag that will control how if we will present questions per building or not.

  • Open SharePoint Online as administrator, access the site used to store data.
  • Include two additional columns (Building and Building:ID) in BAR_KeyQuestions.
    • Click Add Column, then select More…
    • In column name type Building. Select column type Lookup. Get information from the BAR_Buildings list and select Title from the “In this column” drop down. Select ID in “Add a column to show each of these additional fields”.
  • Include an additional Yes/No column (KeyQuestionsPerBuilding) in BAR_AppSettings. Select Yes/No Type. Default Value No.

Building Admin

The Building Admin app needs to be changed to include an additional configuration option to enable/disable the ability to present questions per building. We will also configure the forms to allow the relationship between buildings and questions.

  • Open PowerApps as administrator. Edit the Building Admin app.
  • Go to Data and refresh the BAR_KeyQuestions and BAR_AppSettings connections.
  • Go to Tree View and expand the Screen Key Questions Detail screen.
  • Click Edit fields and add the Building field.
  • Go to Tree View and expand the Screen App Settings screen. Select FormAppSettings. Click Edit fields and add the KeyQuestionsPerBuilding field.
  • Move the field up to be between Require Key Qestion completion before creating reservations and Key Questions failure message.
  • Select the field, select Advanced in properties. And Unlock it to change properties.
  • Change DisplayName to “Present Questions per Building”
  • Save and Publish the app

Building Access

Below are the steps to modify the Building Access app to change the screen flow. We need to perform those changes to ensure the building selection before present health questions. Users will need to pick a building so we can filter and present just questions for the specific building.

  • On Tree View, select App, go to OnStart event. Search for the colNav definition and change the first part of the JSON to navigate to the ScreenBuilding by default.

From:

Id: 0,
ScreenName: varString.NewRequest,
Description: varString.NewRequestDesc,
Screen: If(varAppSettings.KeyQuestions,’Screen Key Questions’ ,’Screen Building’),
Image: ic_fluent_building_24_regular

To:

Id: 0,
ScreenName: varString.NewRequest,
Description: varString.NewRequestDesc,
Screen: ‘Screen Building’,
Image: ic_fluent_building_24_regular
  • Expand Screen Building, select HeaderControlBuilding and change the NavigateScreen data field.

From:

If(varAppSettings.KeyQuestions,’Screen Key Questions’ ,’Screen Home’)

To:

‘Screen Home’
  • Still on Screen Building. Select ButtonCheckAvailability and change the OnSelect event to:
Set(varSelectedBuilding , First(colSelectedBuilding) );
If(varAppSettings.KeyQuestions,Navigate(‘Screen Key Questions’,ScreenTransition.None),
Navigate(‘Screen Availability’,ScreenTransition.None));
  • Expand Screen Key Questions, select ButtonSubmitKeyQuestions and change the OnSelect event as documented below.

From:

If( CountRows(Filter(colQuestionAnswersResponse,Answer =1)) >0, Set(varKeyQuestionsPass,false) , Set(varKeyQuestionsPass,true));
If(varKeyQuestionsPass,Navigate(‘Screen Building’),Navigate(‘Screen Key Question Confirmation’))

To:

If( CountRows(Filter(colQuestionAnswersResponse,Answer =1)) >0, Set(varKeyQuestionsPass,false) , Set(varKeyQuestionsPass,true));
If(varKeyQuestionsPass,Navigate(‘Screen Availability’),Navigate(‘Screen Key Question Confirmation’))
  • Still on Screen Key Questions, select the HeaderControl and change the NavigateScreen to:
If(varAppSettings.KeyQuestions,’Screen Key Questions’, ‘Screen Building’)
  • Click again in Screen Key Questions, on the OnVisible event, perform the changes below:

Replace:

If(IsEmpty(colKeyQuestions),ClearCollect(colKeyQuestions, Filter(BAR_KeyQuestions ,State.Value =”Published”)));

By:

If(varAppSettings.KeyQuestionsPerBuilding, ClearCollect(colKeyQuestions, Filter(BAR_KeyQuestions , State.Value =”Published” && Text(varSelectedBuilding.ID) = ‘Building:ID’.Value)), ClearCollect(colKeyQuestions, Filter(BAR_KeyQuestions ,State.Value =”Published”)));
  • Expand Screen Availability, change to the OnVisible event and paste the code below after the existent code:
Set(varBuildingControlsVisible,false);
// Get all floors/spaces for current buildind
ClearCollect(colFloors,Filter(BAR_Spaces,BuildingID = varSelectedBuilding.ID ));
Clear(colChosenRequests);
Set(varAvilabilityDatePickerDefault, Blank());
Set(varAvilabilityDatePickerDefault,Today());
Reset(DatePicker);

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.

New Stuff!

July marks the start of the new Microsoft fiscal year. Multiple announcements and news. In this post I’m aggregating some of the new stuff.

New Return to the Workplace App

You all remember the announcement we made a few weeks ago on the Building Access App (a return to Office scenario). Well, the idea got a lot of traction inside Microsoft and this week Satya announced the Return to the Workplace App during our Inspire/Ready event. (If you want to go directly to the announcement, please click here).

The Return to the Workplace App is a bit different from the Building Access App. The Building Access App has the feature to create booking requests for spaces inside the buildings and don’t require any additional licenses beyond the ones included with Office 365.

The app announced by Satya is super complete, offers Location Readiness, Health Safety Management, Workplace Care Management but still doesn’t support reservation of office spaces (location management). It also requires Power Apps and Power BI Premium/Pro licenses.

System at a Glance Welcome screen  Start survey

You can get more information about the new app at https://powerplatform.microsoft.com/en-us/return-to-the-workplace/

Instructions to setup it are available at https://docs.microsoft.com/en-us/powerapps/sample-apps/return-to-workplace/overview

Desk, Parking Spots and Granular Reservations

In multiple interactions with customers a common request is to have the ability to perform more granular reservations, including the ability to deal with multiple reservations in the same day, reserve tables, parking spots, etc.

Well, that’s why I love our community. I found multiple alternatives to complement what we are providing and expand. Few examples below:

  • April Dunnam created a desk booking and reservation template. She published it on GitHub and created a video demonstrating some of the app aspects. Video is available here and the app package on GitHub here.
  • Workspace Scheduler – This is a simple app that allows the management of buildings and book workspaces. It can be used to complement the other back to workplace apps and can be used inside teams in a channel or using a personal tab. The app is available here.

Great Ideas

Meet the new Teams App Template… Great Ideas. https://github.com/OfficeDev/microsoft-teams-apps-greatideas

Great Ideas app is an “innovation challenge” system that lets employees submit and share ideas and insights, with voting right within Microsoft Teams. Employees can submit an innovative idea in a selected category to be visible to all colleagues and leadership, ideas can be voted upon and a leader board of best idea contributors can be shared.

All Ideas

Begin with the Solution overview to read about what the app does and how it works.

When you’re ready to try out Great Ideas app, or to use it in your own organization, follow the steps in the Deployment guide.

Dataflex

This thing has the potential to change how we create Power Apps integrated to Teams.

Microsoft Dataflex delivers a built-in, low-code data platform for Teams, and provides relational data storage, rich data types, enterprise grade governance, and one-click solution deployment. Microsoft Dataflex enables everyone to easily build and deploy apps and intelligent chatbots in Teams with Microsoft Power Apps and Microsoft Power Virtual Agents.

Microsoft Dataflex is built on top of the Common Data Service, which reached general availability four years ago. Since that time, the Common Data Service added over 1,000 features and introduced support for Microsoft Dynamics 365 and Power Apps

Pllaceholder

Dataflex is being included with Teams licensing with additional capacity and features provided with a premium version called Dataflex Pro.

https://powerapps.microsoft.com/en-us/blog/introducing-microsoft-dataflex-a-new-low-code-data-platform-for-microsoft-teams/

Endpoint Data Loss Prevention

Data Loss Prevention is crucial in the overall application and business process automation for Teams. The ability to control and monitor how information is exchanged and block possible leaks is key for the security teams.

So far the Microsoft DLP strategy lacked the ability to manage the endpoint directly. Not anymore. We are announcing public preview of the Microsoft Endpoint Data Loss Prevention.

Microsoft 365 customers only need to create DLP policies once in the Microsoft 365 compliance center. They can then apply the policies to Exchange, Teams, SharePoint, OneDrive for Business, and now – to endpoints as well. All that is required is for the endpoint to be onboarded in your environment using your established device management onboarding process.

image.png

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.

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 Deep Links in Custom Apps to Navigate in Teams

Got an interesting request this week. One of our customers would like to create a button on left side of Teams (where the custom apps are hosted) and redirect to a custom tab in a specific channel created in a team. That can be easily achieved using what we call deep links. Deep links in Teams can be used for:

  • Navigating the user to content within one of your app’s tabs. For instance, your app may have a bot that sends messages notifying the user of an important activity. When the user taps on the notification, the deep link navigates to the tab so the user can view more details about the activity.
  • Your app automates or simplifies certain user tasks, such as creating a chat or scheduling a meeting, by pre-populating the deep links with required parameters. This avoids the need for users to manually enter information.

Using deep links in custom apps is very powerful. It is possible, for example, to create a gateway application that coordinates navigation to multiple teams, channels and tabs. You can have buttons that guide the user to custom apps actually running in different channels across Teams.

Coming back to the specific request, there are some steps to follow:

Create the tab in the channel

No need to describe the required steps here. You just need to click on the channel you want to create the tab, click the + sign and add the tab that will be the target for your navigation.

Get the deep link for the tab

With the tab created, click … on the upper right hand side and select Copy link to tab. Take a note of the link, we will need in the next steps.

Annotation 2020-06-12 170004

Create a html page embedding the Teams client SDK

Now you need to create a simple HTML page and reference the Teams Client SDK. That will allow us to use a set of functions to actually interface with the teams client and execute the navigation from the personal tab on the left hand side in the client to the tab you just saved the link to.

The code of my HTML page is below:

Annotation 2020-06-12 171120

You need to paste the deep link in the executeDeepLink function in the code above. Now put the html file in a public webserver.

Create the custom app using Teams App Studio

Install Teams App Studio in your teams client, click on the Manifest Editor and create a new App. In Capabilities, create a new personal tab pointing to your webpage. That’s it, now when you click in your app icon on the left hand side the default custom tab will execute the deep link and navigate to your custom tab created in a channel.

Annotation 2020-06-12 171456

Main reference: https://docs.microsoft.com/en-us/microsoftteams/platform/concepts/build-and-test/deep-links

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.

Integrating Microsoft Teams in Custom Apps

The post today is focusing on how integrate Microsoft Teams in custom applications. You may need to create a meeting from your web site, make your CRM system create meetings and send invites to your customers or even incorporate Teams in a custom mobile application you are developing.

To understand what is possible today and what is coming let’s separate the problem in two pieces:

  1. Creating the meeting/join links
  2. Embedding Teams into custom apps

Creating the Meeting

To create the meeting we can use Microsoft Graph. Microsoft Graph exposes REST APIs for multiple Microsoft services, including Teams. We are specially interested in a set of REST APIs related to “onlineMeeting” resource type. They are all documented here.

As documented in create online meeting page we can POST a specific JSON request containing subject, start date and end date and receive a response from the Graph API with all the information we need to allow participants to join the meeting we just created. That is the first step to integrate an application that needs to create meetings in Teams.

If you analyze the documentation you will see that in the production version (1.0) we provide a way to create those meetings only using delegated authentication. That is, your application will need to authenticate with an Azure AD user credential to post requests and receive responses in the API. 

The documentation for the beta API version presents the ability to use application authentication instead, but the sample code I’m showing below uses the explicit user authentication to access the API. The difference is important when integrating the ability to create meetings in custom applications that do not provide the flexibility to perform OAUTH delegated authentication in an easy way.

To understand my sample code below you need some reading. Basically it is required to understand a few Microsoft Graph authentication and authorization concepts, as well as know to register an application in Azure AD and collect information required to perform authentication and authorization.

My example is a single page app (html) using JavaScript. The steps to have the sample code working are:

  • Go to https://portal.azure.com, authenticate as an administrator and create a new App Registration. Assign at least the user.read and onlineMeetings.ReadWrite graph permissions and consent the access. Take a note of the client ID. Open the config.js and update the clientId value.

Annotation 2020-06-05 141618

  • The redirect Uri is important for my sample code as well. It will coordinate the redirection during the authentication process back to the app. I’m using “npx http-server -c –1” to host my app at the 8080 port. If you are using other http server or platform to host your app, change the value accordingly.

There are no additional steps to make the code work. The createMeeting function inside graph.js will generate the JSON request with the current datetime as the start for the meeting and will calculate a 30 mins offset to use as the end (I’m printing both to the console just for debugging purposes). There is also a counter to create multiple meetings sequentially. I then call the Graph API posting the request. With the result I update the app page with information about the meeting, including two links to join using the teams client and the browser.

Annotation 2020-06-05 142549

The code is available for your reference on my GitHub repository here https://github.com/cristianoag/JSCreateMeetingsSample.

Here are a few screenshots of the authentication process and the meeting creation.

Annotation 2020-06-05 152722Annotation 2020-06-05 152848Annotation 2020-06-05 153049

Embedding Teams into your Custom App

In the sample code I’m providing users can just click on the web link and have the browser opening the meeting directly, without prompting to download the teams client. At this moment there is no way to embed that window and the user will have another browser tab/windows dedicated to conduct the Teams meeting.

For iPhones/IOS and Android, out product group is working in a specific SDK that will allow to embed Teams into custom applications. At this moment they are hosting a TAP program and giving access to selected customers. If you have that need, please drop me a message or contact your assigned Customer Success Manager, we will direct you to the right contacts internally in Microsoft.

–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.

Connecting Power BI to Microsoft Graph

Long time ago I remember connecting directly to Microsoft Graph from Power BI using the ODATA connector. Even the Web Connector used to work ok when we used organizational auth. Then this week I saw a video created by Joao Lucindo (TSP here in Brazil) creating a custom connector just to extract information from Graph.

Immediately I thought that was a waste of time, then I figured out that due to a change in the OAuth flow required for some graph operations, the connection using ODATA connector was not working anymore. I learned from multiple posts in the Power BI tech community that the problem was common.

Well if the simple approach doesn’t work anymore, why not just create an app in Azure, give appropriate permissions, and connect using app/service mode customizing the connection in power BI? That is a possibility for any Graph operation that supports application permissions. The code is very simple and can be implemented for all operations that you see available when click + Add a Permission on the API permissions for the App registration in Azure.

Annotation 2020-05-28 140231

For sure you need to create an App registration, take note of Tenant ID, Client ID, and create a secret to allow access. Then keep in mind that only permissions available when click Application Permissions are possible with this approach.

In Power BI I created four parameters:

  • Azure Graph API Url: Contains the base URL to connect (below I’m using the beta URL)
  • Azure Tenant ID: This is the tenant ID you get when register the App in Azure (available on the overview page)
  • Azure Application ID: This is the client ID for the app you registered (available on the overview page)
  • Azure Application Client Secret: You need to click API permissions and create a secret value. Get the secret value to be used on the parameter.

Annotation 2020-05-28 140737

Then I created a function to generate the Application Access token (auth-v2-service token) following the requirements documented at https://docs.microsoft.com/en-us/graph/auth-v2-service. The detailed instructions to register the App and give permissions are also documented there.

Here is the code for AzureAccessToken M function:

let
     TokenUri = “
https://login.microsoftonline.com/” & #”Azure Tenant Id” & “/oauth2/token”,
     ResourceId = “
https://graph.microsoft.com”,
     TokenResponse = Json.Document(Web.Contents(TokenUri,
     [
     Content = Text.ToBinary(Uri.BuildQueryString([client_id = #”Azure Application Id”, resource = ResourceId, grant_type = “client_credentials”, client_secret = #”Azure Application Client Secret”])),
     Headers = [Accept = “application/json”], ManualStatusHandling = {400}
         ]
     )),
     AzureAccessToken = TokenResponse[access_token]

in

     AzureAccessToken

Then in the Power BI query, you can use either Odata.Feed or Web.Contents to return the result of the Graph query:

Annotation 2020-05-28 141516

What happens with the Graph operations that do not support application permissions? For example Microsoft Bookings related operations like list appointments cannot be returned using application based authentication, just with org User based authentication as you can see in https://docs.microsoft.com/en-us/graph/api/bookingbusiness-list-appointments?view=graph-rest-beta&tabs=http

Annotation 2020-05-28 141657

For those cases, we now need to follow Lucindo’ s approach and actually create a custom connector to implement the full auth-v2-user flow as described in https://docs.microsoft.com/en-us/graph/auth-v2-user.

You can check Lucindo’ s video and code at https://www.linkedin.com/feed/update/urn:li:activity:6670823169047306240/ and https://lnkd.in/giGnCBa. Great job João…

I created a sample connector as well and shared it on my GitHub at https://github.com/cristianoag/PowerBIGraphCustomConnector.

I also learned that data sets created from data returned from custom connectors need to have an On-premises Data Gateway to be refreshed automatically from the cloud service. Joao also shows how to install the custom connector and configure the gateway at https://lnkd.in/g59Tewr

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.

One-Time
Monthly
Yearly

Make a one-time donation

Make a monthly donation

Make a yearly donation

Choose an amount

$5.00
$15.00
$100.00
$5.00
$15.00
$100.00
$5.00
$15.00
$100.00

Or enter a custom amount

$

Your contribution is appreciated.

Your contribution is appreciated.

Your contribution is appreciated.

DonateDonate monthlyDonate yearly

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.