Sunday, September 22, 2019

Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a temporary pass.  This process can be time consuming and is further exacerbated when the person who needs to approve the check-in is not available.  Luckily, we can use flow to make our life much easier.

The premise of this flow is that for each location of a company, when an employee gets close to an office, an email would be sent from the corporate address to the security desk with information about the employee indicating that a pass should be provided and identifying them.  Here's how you can built it.

SharePoint

Create a Custom SharePoint list that includes the following fields
  • Title - name of the location
  • Latitude
  • Longitude
  • Security Email - the persons who will receive the email with the request for access
  • Instructions - some text to include in the email about the access
  • City - the location of the office.  The data must match the city names in the employee's Office 365 profiles

Power Automate

There are two flows that make up this solution.  Trigger flows (in green) are replicated for each office location, while the main flow is shared.



Location Trigger Flow

The trigger flows leverage the experimental When I enter or exit an area location trigger.  When you create the flow, record the longitude and latitude of your location.  Notice that each coordinate is made up of six decimal places.  



Now, create a new entry in the SharePoint list.  Give it a name in the Title field, the name of the City, an email for the Security Email that will receive a notification, and optional instructions.  For the Longitude and Latitude, enter the first 5 decimal places and truncate the rest (don't round the numbers).  

This flow will be triggered when an employee either enters or leaves the area defined by the radius in the trigger.  In our case, we only want the main workflow to run when the employee enters the area (transitionType equals 1).  In that case, the user principal name, latitude, and longitude are passed to the second Flow.


Main Flow

When the main flow is triggered, then we know which employee (by UPN) entered and the location they entered into.  So, we look up the location of the in SharePoint and return the item.  Remember in the first Flow that I mentioned that only the first 5 decimal places should be saved?  Here's why - when you create a Number field in SharePoint, you can only specify up to five decimal places (this holds true in the Classic and Modern experience)

So, to avoid situations of rounding and missing the exact address, I am limiting the decimal places to 5.  Here's how


ActionBeforeAfterExpression
Multiply the float by 100000 and convert to string43.68293856"4368293.856"
string(mul(100000,variables('Latitude')))
Retrieve the sub-string before the period"4368293.856""4368293"
if(equals( IndexOf(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),'.'),-1), outputs('Multiply_latitude_by_100,000_and_convert_to_string'), substring(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),0, IndexOf(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),'.')))
Convert string to float and divide by 100000"4368293" 4368293
div(float(outputs('Remove_all_fractions_from_latitude')),100000)

At this point, we have coordinates that should match exactly what is in SharePoint.


Next, we look up the items in the list with the same coordinates bu non-matching cities.  The latter condition will ensure that an employee is not generating emails to their own security email each they come to their own office.  The information in the items is used to notify the front desk security for that location.




Saturday, September 21, 2019

Build you own Raffle Solution using the Power Platform and more

If you're a presenter or organize events, then you may agree that one thing that always attracts an audience are raffle prizes.  In the past, I used to ask users random questions in the audience, which works great, but given that most of my presentations in the past couple of years have been in the Office 365 space, I thought it would be a great idea to build an app for it.  This particular app uses a number of technologies - Microsoft Forms, Flow, PowerApps, SharePoint, and and IoT button.



Solution Overview

The way the app works is that my attendees will register to my event using a QR code (try it).
The QR code will take the attendees to a Microsoft Form, where all they provide is their name and email address.  Once submitted, their name is entered into a SharePoint list.  When the app is started, it periodically checks for any new submissions (in case anyone wanted to enter the raffle at the end of my session).  I then use a Flic IoT button to trigger when the app starts to run through the list of raffle registrants and display their names.  When I click the button a second time, the timing between each name increases (like a slot machine) and eventually stops with a great display of fireworks.  A third click of the button will clear the name and start the process over.  Let's see how I built it.

Solution Design

Microsoft Forms

For this solution, I created a simple Form that you can access via the QR code.  I change the title and logo of the form based on the event I'm presenting at.

SharePoint List

To capture the attendees names, I use a Custom List with 2 fields in it
  • Title - Single Line of Text, mandatory
  • Email - Single Line of Text, optional
The email field is not required for this app.  I'm capturing it to send a courtesy email to the attendee thanking them for attending my session and providing them with my contact information.

PowerApps

I built the app as a Canvas app.  The look and feel is very flexible.  Let's look at some of the specific needed to run the solution.  I have used several controls as the core of the solution:

  • imgCongratulations - that is the image (animated GIF in my case) that appears when the counter stops and the winner is announced
  • Timer1 - when this timer ends, it will iterate through the name
  • flicTimer - calls a Flow to determine if the button has been pressed 
  • attendeeTimer - this timer will periodically refresh the list of attendees from SharePoint
There are a number of companies selling such IoT button, including Flic (by Shortcut Labs) and bttn.  If you don't have a button, you can always adjust the solution to use buttons in your App.  Now, let's discuss the customization of these controls

Screen

When the app first loads, I load the participants from the SharePoint list into the participants collection.  There are also a number of variables I set:
  • index - index of the attendee in my collection
  • dur - duration between name changes in the raffle (in milliseconds)
  • stopping - indicates whether the button has been pressed a second time to slow down and stop the raffle
  • refreshCounter - indicates whether the Dur counter was refreshed
  • winner - winner name
  • clicked - response from Flow on whether the Flic button has been clicked
OnVisible:  ClearCollect(participants,'Participants'.Title);
UpdateContext({index:0,dur:50,stopping:false,refreshCounter:true,state:0,winner:"",clicked:""})

imgCongratulations

In order for the image to appear, two things need to occur.
  • The state needs to be set to 2 - stopping
  • The duration needs to be set to 1000 - indicating that it's no longer changing
Visible:  (2=state) && (1000 = dur)

attendeeTimer

This timer triggers every second and refreshes the list of attendees from the SharePoint list.

Autostart:  true
Duration:  1000
Repeat:  true
OnTimerStart:  Refresh('Participants');ClearCollect(participants,'Participants'.Title)

flicTimer

The flicTimer is used to send requests to a Flow and wait for a response.  Depending on the state that the app is in, the action of the button will have different impacts.
  • state = 0 - this is the initial state.  The names are not shown and the index of the participants array is set to the beginning.  As well, the duration is set to 50 ms for quick updating.  Stopping, which will slow down the display of the names is also set to false.
  • state = 1 - this is the state, where the names are being displayed in 50 ms intervals.
  • state = 2 - in this state, the display of the names slows down from 50 ms to 1000 ms intervals and eventually stops.  Once stopped, imgCongratulations is displayed
The timer is set to 12 sec so that there wont be too many calls being made to the Flow at once.


Autostart:  true
Duration:  12000
Repeat:  true
OnTimerEnd:  UpdateContext({clicked: GetFlicButton.Run().response});
If(
    clicked = "clicked",
    If(state = 0,
       UpdateContext({startTimer: true,stopping: false,index: 0,dur: 50,refreshCounter: false,state: 1}),
     If(state = 1,UpdateContext({stopping: true,state: 2}),
        UpdateContext({index: 0,dur: 50,stopping: false,startTimer: false,refreshCounter: true,winner: "",state: 0}))));
flicTimer.Reset

Timer1

This is where the magic happens.  The timer is triggered in state 2, once the button has been clicked for a second time.  As you can see, the duration in this timer is set to the dur variable, which changes as the timer is clicked.  As long as the duration is less than 1000 ms, the timer will continue to run.  Once it hits 1000 ms, it will stop and the winner variable is set to the first name of the item in the SharePoint list.

Autostart:  false
Duration:  dur
Repeat:  true
Start:   If(dur < 1000,startTimer,false)
OnTimerEnd:  If(index = CountRows(participants)-1, UpdateContext({index:0}),UpdateContext({index:index+1}));
UpdateContext({winner:First(LastN(participants,CountRows(participants)-index)).Title});
If(stopping,UpdateContext({dur:dur+50}));If(dur>1000,UpdateContext({startTimer:false}))

Flow

There are two Flows that are used in this solution.  The first one captures the Form submission and stores them into a SharePoint list and send out a courtesy email to the attendee.



For the second Flow, I used the approach discussed in my previous article Adding a Flic button trigger to a Flow triggered by PowerApps.  In essence, I trigger the Flow from my PowerApp and wait for a button click.  If the button is clicked, the the flow returns clicked.  Otherwise, it returns nothing.  This is the value that is stored in the clicked variable described in the PowerApp screen description above.