How I’m automating my Porto Airbnb host with Microsoft Forms and Microsoft Flow

by Sandro Pereira | Feb 8, 2019 | BizTalk Community Blogs via Syndication

Yes, if you want to visit Porto, my city and a destination that has been fashionable due to the various awards, nominations, and mentions all across the world like:

• Elected as the most romantic city in the world;
• Elected the best destination in the world;
• I think 3 years in a row as best European destination;
• Many travelers believe the most beautiful McDonald’s in the world is in Porto;
• One of the most beautiful libraries in the world;
• and many other crazy nominations…

Personally, I believe that today we living in a fanatical environment of everyone or everything wanting to be the best in the world, the honest reality is that Porto is a beautiful place like many others in the world and I recommend you to visit… and I have just a perfect romantic and traditional place for you!

Casa Sr. José (Mr. Joseph’s house) is my AIRBNB host, as traditional and recently renovated house that is at a walking distance from the Douro River (1 min – 78 m) in the beautiful and typical Porto civil parish of Massarelos. Located in the heart of Porto, next to the Douro River, is a perfect spot that is near to almost everything: Porto´s Unesco World Heritage historical center (10 mins); Foz do Douro (10 min) were Douro river and the Atlantic Ocean meet (beach); and near of 1 most well-known landmarks of Porto: the Clérigos Tower (10 min) and it is available for all you that once again, want to visit Porto.

This is my non-technology pet project and I love it but it also gives me some challenges, challenges that I’m trying to solve with technology.

One of these challenges is that in Portugal, as well as in most European countries, all paid accommodation establishments need to record the stay and collect identification details from all foreign citizens, whether they are from the Member States of the European Union or Third Country Nationals, in order to communicate them within three days to the Immigration and Borders Service. At the moment, I have a very rudimentary process in place:

• A form paper (Ficha de hóspede / Guest card / Carte d’invité) that the guests need to fill once they arrive;
• I have to go to the house to get the form;
  • The guests don’t need to be there if they allow me to enter the house to grab the form;
  • Otherwise, I need to arrange a date hour with them to grab the form;
• I then need to access to the SEF (Serviço de Estrangeiros e Fronteiras – Foreign Service and borders) online portal, authenticate with my account and manually fill an online form (SIBA – Sistema de Informação de Boletins de Alojamento – Accommodation Bulletin) with the information of all guests like:
  • Full name
  • Date of birth
  • Nationality
  • Country of residence
  • And so on
• and submit the accommodation bulletin to SEF.

This is a very time-consuming task and inconvenient for both me and the guests:

• I need to “annoy” the guests to access “their house for a week” or arrange a date/hour for them to meet with me to give me the form;
• I then need to understand the hand write and type everything to an only form;
• And so on

Again all of these are time-consuming tasks.

This week, and I’m being an integration guy, my mind automatically started to think:

• How can I easily automate these tasks?
• But it needs to be user-friendly to my guests;
• And it needs to be easier for my wife to understand, to share with the guest and to control and… she is not a technical person;
  • If I started to speak in terms of Flow, Azure or anything else… she will say no! I don’t know what you are talking about.

In the end, I elaborated a process with two parts:

• Part 1: the first approach to address and expedite our interaction with our guests;
• Part 2: my end goal, fully integrated;

In this post, I will address Part 1, the one that is at the moment in-place.

How Microsoft Forms and Microsoft Flow allows me to better interact and automate my Porto Airbnb host processes?

The first thing I decide to do was an Online Form for the guest to fill, instead of using a paper. This could be done with several options online: Google Spreadsheet and Form; Formstack; Cognito Forms and so on. I choose to use Microsoft Forms, to do that you need to

• Access in your web browser to forms.office.com.
• Sign in with your Office 365 school credentials, Office 365 work credentials, or Microsoft account (Hotmail, Live, or Outlook.com).
• Under “My Forms”, click “New Form” to begin creating your form.

• You should enter a name for your form, and you can also enter an optional subtitle and picture.
  • I decide to call my form name: “Ficha de hóspede / Guest card / Carte d’invité”
  • Add a picture and a subtitle with an explanation of why guest need to fill this form

Then, you need to start creating your questions. To do that you need:

• Click “Add Question” to add a new question to the form.

• By default you have the options “Choice”, “Text”, “Rating”, or “Date” questions, but you can always click to see more option under “…” button, and you can select from “Ranking”, “Likert”, or “Net Promoter Score®” questions.

• In my case I added:
  • Two Date questions:
    • “Data de entrada / Check-in date / Date d’arrivée”
    • “Data de saída / Check-out date / Date de départ”

• • 8 text questions:
    • “Nome completo /Full name / Nom complet (Person 1)”
    • “Data de Nascimento / Date of Birth / Date de Naissance (Person 1): dd/MM/yyyy”
    • “Nacionalidade / Nacionality / Nationalité (Person 1)”
    • “Local de Nascimento / Birthplace / Lieu de résidence (Person 1)”
    • “Local de Residência / Country of residence / Pays de résidence (Person 1)”
    • “País de residência / Country of residence / Pays de résidence (Person 1)”
    • “Numero de documento / Document number / Numéro de document (Person 1)”
    • “País emissor do documento / Country issuing the document / Pays délivrant le document (Person 1)”

• • 1 choose question
    • “Tipo de documento / Document type (Person 1)”

• • And finally, a true/false question, that is basically another optional question
    • “Têm mais hóspedes? / Do you have more guests? / Avez-vous plus d’invités?”

And here is where the fun begins. Why this last question?

The first reason is that I need to deliver to SEF the information of all the guests and my house can host a maximum of 6 guests. But there is more… Because my house can host a maximum of 6 guests, I have to duplicate these questions 6 times, with the exception of the first two:

• “Data de entrada / Check-in date / Date d’arrivée”
• “Data de saída / Check-out date / Date de départ”

Because all of them are from the same booking. That’s why in my final form you will see the same question duplicated with the suffix: Person 2, Person 3… Person 6

But my first form draft didn’t have this last question. I add it becaus, without this question, the guests had to scroll until the bottom of the form to submit it, for example, if I was hosting a couple, they had to fill Person 1 and Person 2 information but them they had to scroll down passing all questions of person 3, 4, 5 and 6 to finally submit the form.

Again, to improve and provide the best user experience to my guest I decide to create this question and apply some logic inside allowing me to create like an array of guest information data – this doesn’t exist in Microsoft Forms by the way.

To accomplish that at the end of this list of questions for each guest I’m adding this last question:

• “Têm mais hóspedes? / Do you have more guests? / Avez-vous plus d’invités?”

And add a branching logic to my form so that it changes according to the responses to that question. This way, If the guest says:

• “Sim / Yes” it will show the list of questions for the second guest… and so on.
• If “Não / No”, it will hide the rest of the questions and “go” directly to the end – “Submit”

Here is how the experience will look like if you say “No” or the default behavior:

And here is the behavior if you select or change to “Yes”:

For example, despite all questions in the form are mandatory, if I select or refer that I only have two guests, the form is smart to know that this is an optional pack of questions:

• If you select that have a new guest you need to fill the questions for that specific guest, otherwise, it allows you to submit the previous questions, without having to fill dummy information for guests 3 to 6 for example.

This improved a lot the Form user experience with the guests.

To accomplish this, you need to:

• Click the ellipses button (…) on the main menu, and then select Branching.

• On the Branching options page, there are dropdown menus that appear with each answer under every question. Click the arrow to see a dropdown menu.

• • In this case, if I select the “Sim / Yes” option this should “Go” to question 13 that is the beginning of a new guest form
    • Basically, it shows the “second part” of the form
  • If you select “Não / No” it should go to the end of the form
    • Basically, it hides the rest of the form

And of course, I can share the form thru a URL, QR Code, thru an iframe on a webpage and by email.

Being the Microsoft Form

Microsoft Form is great to help to improve the interaction with my guests, but it allows me to go to a certain point but not solve all my problems/concerns.

Now that I have retrieved the data from my guests, the question is how I can automate my process my SEF and how can I notify the persons that are managing the house (me, my wife, my brother-in-law, and sister-in-law).

For that, I choose to use a Microsoft Flow that is triggered each time a new form Is submitted and automated these tasks. Unfortunately, do some restrictions I still cannot integrate directly to SEF thru API. I had to request access and I’m still waiting for their approval.

That is the reason why this is PART 1… end goal is to be fully automated without any manual process (PART 2).

So, to minimize the manual work, I decide that is this phase the Flow will go thru each question and notify us by email that a new form was submitted but also give us on the body email all the information submitted for us to be better to copy that information and fill the online SEF form and submit them – basically it would be a copy and past operation, instead of adding to manually type all the data which can lead to many mistakes. This way we are:

• Minimizing mistakes and misspellings;
• Minimize the time I need to fill these SEF forms;

To accomplish that you need to:

• Access the Flow portal: https://flow.microsoft.com/ and sign in with your account
• In flow.microsoft.com, select “My flows” in the top navigation bar, and then select “+ Create from blank” to create a Flow from scratch.
• On the “Create a flow from blank”, select “Create from blank” to start the process of creating an empty Flow
• On the Flow designer, on the Trigger event enter “Microsoft Forms” and select the option “When a new response is submitted” and pick the form you like from the dropdown list

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Variables” and select the action “Variables – Initialize variable”
  • On the Variable action configuration:
    • On the “Name” property, type “EmailBody”
    • On the “Type” property, specify the type as “String”
    • On the “Value” property leave it blank

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Control” and select the action “Apply to each”
  • On the Apply to each action configuration:
    • On the “Select an output from previous step” property, select from the list of tokens the “Key” token “List of response notifications” from the “When a new response is submitted” trigger

• Inside the loop, add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Microsoft Forms” and select the action “Get response details”

• • On the action configuration:
    • On the “Form Id” property, pick once again the Form from the dropdown list
    • On the “Response Id” property, type the following expression

@{items('Apply_to_each')['resourceData']['responseId']}

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Variables” and select the action “Variables – Append to string variable”
  • On the Variable action configuration:
    • On the “Name” property, select “EmailBody” variable
    • On the “Type” property leave it blank

Ficha de hóspedes: 

<b>Data de entrada: </b> @{body('Get_response_details')['rfb0478a71c77463eb855db4a6617b596']}
<b>Data de saída: </b> @{body('Get_response_details')['rbad828de65664088a332fbbad18ba4c3']}


Hóspede 1:
<b>Nome completo: </b> @{body('Get_response_details')['r9bde314fe5ea4c74883f46750183bd0f']}

<b>Data de nascimento: </b>@{body('Get_response_details')['rf3e762220a604c578cca006c83d98756']}
<b>Nacionalidade: </b>@{body('Get_response_details')['r8b7428bfbda94eb684c0e43edbff0a87']}

<b>Local de nascimento: </b>@{body('Get_response_details')['r2c926c630529444e86ba53359b6adaf4']}

<b>Local de residência: </b>@{body('Get_response_details')['rddbf3f41f16e402b9dba65f49631c27c']}
<b>País de residência: </b>@{body('Get_response_details')['r52c69618890540e392dca6fd4f8f5fc0']}
<b>N. documento: </b>@{body('Get_response_details')['r9b9ee64cf84d42c89bd7770389ea94d9']}
<b>Tipo documento: </b>@{body('Get_response_details')['r48f5e4ecf66a48328269b656b034ddd1']}

<b>País emissor: </b>@{body('Get_response_details')['raa2bcdec2c6b4b5d8b3d17cd6f946a8a']}

Basically, all the tokens were added from the tokens list

And what we are doing is creating a string, king of a string builder, with the HTML email body with the guest information

Then we need to check if another guest was added and append that information by:

• And then add a new condition by selecting “Add new action”, enter “Control” and select the action “Condition”
  • On the “Condition” configuration add the following condition:
    • If the first “Têm mais hóspedes? / Do you have more guests? / Avez-vous plus d’invités?” question value is equal to “Sim / Yes”

• On the “If yes” branch
  • Choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Append to string variable”
  • And add the same previous code but this time with the data of the second guest

• On the “If no” branch
  • Leave it without any action

Now we just need to do the same for the other 6 guests:

Finally, on the end send an email with that data to our host email address, to do that we need to:

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Mail” and select the action “Send an email notification”
  • On the action configuration:
    • On the “to” property, type your email
    • On the “Subject” property, type “Guest notification”
    • On the “Email Body” property, select from the list of tokens the “Key” token variable “EmailBody”

Give a proper name to the flow and save it

The end result

Each time a guest submits an Accommodation Bulletin I will be receiving an email with this format:

That will help my task to manually fulfill the SEF online form…. Until I completely automate this task.

And by the way… if you planning to visit Porto, have a chat with me, stay in our house and I may get you a discount: https://www.airbnb.pt/rooms/18272087?guests=1&adults=1

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira