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

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 is the 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 live in a fanatical environment where everyone or everything wants 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 the perfect romantic and traditional place for you!

Casa Sr. José (Mr. Joseph’s house) is my AIRBNB host, a traditional and recently renovated house that is within 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 out 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 the guests and me:

• 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 handwritten and type everything to an only form.
• And so on.

Again, all of these are time-consuming tasks.

This week, as I’m 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 do Microsoft Forms and Power Automate flow to allow 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 decided 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 guests 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 options under the “…” 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).
  • Número 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, which 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 added it because, without this question, the guests had to scroll until the bottom of the form to submit it. For example, if I were hosting a couple, they had to fill in Person 1 and Person 2 information, but then they had to scroll down, passing all the questions of Person 3, 4, 5, and 6 to finally submit the form.

Again, to improve and provide the best user experience to my guests, I decided to create this question and apply some logic inside, allowing me to create 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 one 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 remaining questions and go directly to the end – Submit.

Here is what 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, which 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 paste 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 choosing 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 choosing 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 choosing 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, kind 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, at 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 choosing 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 receive an email with this format:

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

And by the way… if you’re 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

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

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