Intro
I haven’t written a blog post in a while. Strangely enough I’ve been quite busy during the COVID-19 lockdown.
I’m in New Zealand and our prime minister has just announced some slight relaxing of restrictions which will allow business who can operate safely to reopen.
One of the requirements of operating safely is to keep a record of everyone who enters and leaves your premises so you can provide contact tracing data to the authorities if requested.
It’s not unusual for a business to have a sign-in sheet at reception, or indeed an app, which you might have created in Power Apps and run on a tablet at the entrance. That’s all well and good, but anything you have to touch is a possible vector for infection.
So, how can you process sign-ins and sign-outs contactlessly? You don’t want to ask people to download an app or sign up on some website. I had a quick think about this and come up with a way you can do that.
Summary
Logic Apps and Power Automate both have the ability to trigger on an HTTP request, and respond back to the browser with content. My solution involves a couple of Logic Apps/Flows. One is triggered with an HTTP GET and the other a POST.
The GET trigger is performed by a visitor’s smartphone browser via a QR code. You can print out a large QR code and attach it to the outside of your door for people entering the premises and another on the inside of the door for people leaving.
Scan the QR code and follow the link, this triggers a Logic App that serves up an HTML form. On that form you capture the name and phone number of the person and store it in your database.
Easy huh? Here’s how. Firstly the simple way, then an advanced alternative.
Implementation
Simple
Firstly, from now on I’m working in Logic Apps. As I’ve said in my previous blogs, there’s very little difference between the two and for the purpose of this exercise, they’re identical.
Let’s start by creating a new Logic App/Flow with the trigger When an HTTP request is received. The Method should be set to GET
Now we want to compose the response body, which will be the HTML form. You will notice another Logic App URL in the action property of the HTML form. You can put some placeholder text in here for now and come back to it after creating the second Logic App:
Here is my raw code:
<!DOCTYPE html>
<html>
<body>
<h2>COVID-19 Contact Tracing</h2>
<form action="https://prod-01.australiaeast.logic.azure.com:443/workflows/.../triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=..." method="post">
<label for="name">Name:</label><br>
<input type="text" id="name" name="name" placeholder="Enter your name"><br>
<label for="number">Phone number:</label><br>
<input type="text" id="number" name="number" placeholder="Enter your number">
<input type="hidden" id="direction" name="direction" value="@{triggerOutputs()?['queries']?['direction']}"><br><br>
<input type="submit" value="Submit">
</form>
<p>When you submit this form, your name and phone number will be stored securely along with the time you @{if(equals(triggerOutputs()?['queries']?['direction'],'In'),'entered','left')} the premises. The data will be held for COVID-19 contact tracing purposes for 28 days then deleted".</p>
</body>
</html>
If you look at the code block you’ll see some bold highlighting. These first one is a query parameter we’ll add to the HTTP GET (direction=In or direction=Out) to add to the URLs on our QR codes. The point of this is so we can capture the direction someone is going in without them having to tell us. We’ll also use that to change the wording in the information displayed to the user alongside the form so we can show we acknowledge their direction of travel.
This form is very simple. It just captures Name, Phone number and the direction of travel, which is a hidden field in the form that gets its value from the query as I just explained.
Next, we respond to the browser with the HTML form above:
That’s Logic App number one done. You can save that and then create a new Logic App, also with an HTTP trigger. This one uses POST as the method (as per the HTML form action):
Now grab the form data in a compose by typing this expression:
The form data is an array, so you need 3x Filter Array actions to filter by the key:
Do 3 of these, all with the same input (outputs(‘Compose_formdata’)) but each of these has the expression item()[‘key’] being equal to name, number and direction respectively.
The result here is you have 3 arrays with one item in each, one name, one number and one direction.
Now I’m going put these values into an SP list. Notice that because the form data are all single item arrays, I’ve used first() in an expression in each column to avoid a pointless For each loop:
Lastly, respond to the browser to let the user know all is OK:
Now save the Logic App, grab its trigger URL and place it in the action parameter of the HTML form in your first Logic App.
Lastly, you need to grab the trigger URL for your first Logic App, the one with the GET method and add the following to the end of the URL:
&direction=In
&direction=Out
So now you have two URLs that trigger that Logic App with different values for the direction parameter.
Find your favourite free QR code generator on the Internet and paste these two URLs to generate QR codes, In for the outside of your door and Out for inside.
When a person scans the code and follows the link, they get a simple HTML form:
So there’s the easy way, it’s a very quick and dirty solution (took me about an hour with kids distractions and writing this blog at the same time). A proper developer would do a much better job with the look and feel of the form. You could go crazy with this and add a logo and a link to the government COVID guidelines or whatever, but if you just want plain and simple you’d probably be thinking, why not just create a couple of MS Forms and QR code their URLs? Well you could do that, but you couldn’t brand them up and customise them to any great degree, and you certainly can’t use dynamic data, which brings me on to the next phase.
Advanced
The above method can be used to respect people’s privacy and not do any sort of lookup on existing data, but in a corporate environment you could do that and serve it up in the form using a select element so you have more integrity between ins and outs. It’s a bit more complicated but it still only took me about an hour longer.
In the first Logic App, triggered with the GET, we need to initialise a string variable early on.
Now create a branch that switches on the value of the direction query triggerOutputs()?[‘queries’]?[‘direction’]. In the “In” branch, you can place the Compose and Response actions from earlier as-is, and we do the rest of the work in the “Out” branch:
If you can see that screen shot above in enough detail you can see the first action is to Get list of visitors. This is SharePoint Get items action with an Odata filter to only grab items created today:
You have to do ridiculous things with dates when you live in a timezone so far off UTC!
Next two Filter Array actions, one to give me an array of the results filtered by Direction.Value = “In” and one Direction.Value = “Out”
Now for each item in the “Ins” array, another filter array to look for any records in the “Outs” array with a matching Title and a Created date that’s newer than the current item:
We don’t actually do anything with the data in here, just count the number of results:
If there is a result of the previous filter (i.e the length of the array is more than 0), then the person in the current item in the loop has already gone, so we do nothing. If there isn’t a match, that is to say the length of the array is 0, then we append some text to a string variable:
Now, outside of the loop, we have a string variable with a bunch of options we can use in a drop down on our form:
After that we put in another response just like before with this Compose as the body and the content-type header as text/html.
That’s pretty much it. The second Logic App, with method POST will receive the data in the same way, except it won’t have a Phone number because I took that out of the HTML form in the Out branch. You could leave it in to double your chances of getting good data from the user.
William's IT Blog 