Microsoft Flow: How to pass the SQL data table results in a markdown-formatted table into an Email notification or Approval Requests

Microsoft Flow: How to pass the SQL data table results in a markdown-formatted table into an Email notification or Approval Requests

Last Friday while checking the Twitter I found a twitter from Jon Levesque asking if someone had a sample on how to took SQL data table results and passed a markdown-formatted table into an Approval Request. By coincidence, two week ago, I was helping a colleague of mine doing something similar, in that case, it was just adding a well formatted table with the results of an SQL expression to a notification email. Two different but similar scenarios.

To be able to create this simple proof-of-concept you should:

  • 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

Because this is a simple POC and it supposed to be a notification email we will be using the Scheduler trigger to start the Flow from time to time. For that you need:

  • On the Flow designer, on the Trigger event enter “Schedule” and select the option “Schedule – Recurrence”

Microsoft Flow: markdown-formatted table - Schedule – Recurrence

  • For this POC, we want to receive a notification on a daily, so on the Schedule Trigger configuration set the:
    • “Interval” property as “1”
    • And the “Frequency” property has “Day”

Microsoft Flow: markdown-formatted table - Schedule – Recurrence configuration

  • Add the next step by clicking the “+New step” button and then choose the “Add an action” option

Microsoft Flow: markdown-formatted table - Add action

  • On the “Choose an action” window, enter “SQL Server” and select the action “SQL Server – Execute a SQL query”

Microsoft Flow: markdown-formatted table - SQL Server action

Note:
This can be a different action like, get rows or execute a stored procedure.
    • After you connect to the proper SQL Server and database, on the Execute a SQL query action configuration:
      • On the “query” property, type
SELECT [Id],
       [Name],
       [Age],
       [Nacionality]
FROM [dbo].[Persons]

Microsoft Flow: markdown-formatted table - SQL Server action configuration

  • 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”

Microsoft Flow: markdown-formatted table - Add Variable

    • On the Variable action configuration:
      • On the “Name” property, type “lines”
      • On the “Type” property, specify the type as “String”
      • On the “Value” property leave it blank

Microsoft Flow: markdown-formatted table - Add Variable configuration

Note:
Initialize variable needs to be performed on Top Level, in other words, outside any cycle operation (Do Until or For Each). Initialize variable inside cycles are not permitted.
  • 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”

Microsoft Flow: markdown-formatted table - Append variable action

    • On the Variable action configuration:
      • On the “Name” property, select “lines” variable
      • On the “Value” property type
<tr>
<td style="border:1px solid black; border-collapse: collapse;">@{items('Apply_to_each')?['Name']}</td>
<td style="border:1px solid black; border-collapse: collapse;">@{items('Apply_to_each')?['Nacionality']}</td>
</tr>

Microsoft Flow: markdown-formatted table - Append variable action configuration

Note:
@{items(‘Apply_to_each’)?[‘Name’]} and @{items(‘Apply_to_each’)?[‘Nacionality’]} are inserted and selected from the list of “Execute a SQL query” action tokens
  • Because we can have multiple rows retrieved from SQL Server, the Flow designer will automatically place this “Variable – Append to string variable” inside a Loop that will iterate thru each SQL row.

Microsoft Flow: markdown-formatted table - Append variable inside loop

Now that we have our SQL rows partially well formatted in a markdown-formatted table, what we need to do is create our notification/Approval Request email. To do that we need:

  • 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 “Office 365 Outlook” and select the action “Office 365 Outlook – Send an email”

Microsoft Flow: markdown-formatted table - outlook action

  • On the Office 365 Outlook action configuration:
    • On the “To” property, type the email for which you want to send the email
    • On the “Subject” property, type the subject of the email
    • On the “Body” property, type:
<h1>Summary</h1>
<table style="border:1px solid black; border-collapse: collapse;">
@{variables('lines')}
</table>
by www.devscope.net

Microsoft Flow: markdown-formatted table - outlook action configuration

Note:
@{variables(‘lines’)} is inserted and selected from the list of “Variables” tokens

Now the final trick for you to receive this type of email:

Microsoft Flow: markdown-formatted table - outlook email final result

instead of this:

Microsoft Flow: markdown-formatted table - outlook email initial result

On the Office 365 Outlook action configuration you need to:

  • Click on “Show advanced options”.

Microsoft Flow: markdown-formatted table - outlook advance options

  • And on the “Show advanced options”:
    • On the “Is HTML” property, select “Yes”

Microsoft Flow: markdown-formatted table - outlook advance options HTML

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

Processing Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Microsoft Flow & Power BI

Processing Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Microsoft Flow & Power BI

Some time ago I wrote a blog post on how to Process Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Logic Apps, Azure Functions and Power BI, at that point there weren’t so many of the functionalities that we have at the moment both on Logic Apps and Microsoft Flow: like concept of variables, Case operations, more expressions an so on. I will not address here in this blog post if can we redesign to be different or better. The question I raise and will address here is: can we do the same with Microsoft Flow, instead of using Logic Apps?

And the reason I ask this question is that Microsoft Flow is more targeted to Business Users which fits perfectly with scenarios in which we want to extend a product to fit each business users’ requirements/scenarios. If you have an Office365 you can use Microsoft Flow and you will have 2,000 runs per month, so you don’t need to pay extra for using Logic Apps, you can instead use Microsoft Flow.

And the answer is: Yes, you can!

The problem and scenario will be exactly the same: How can we easily convert paper in data to generate additional value? How can we perform operations on it and easily gain insights?

Processing Feedback Evaluations Paper: The Problem

But in this case, to solve this problem, in which I wanted to have the evaluations forms to be processed in real-time, i.e., as the attendees handed in the forms, the results were presented in a public Power BI dashboard in a matter of seconds, we will be using:

  • DevScope SmartDocumentor OCR that, not also allowed me to extract the data from my documents and easily integrate with other systems, but also to intelligently set my OCR streams (flows), defining:
    • Different receive locations, like FTP, file or directly from scanner devices;
    • Create/test my recognition templates and review/ validate the data which is gathered;
    • But also, enabled me to connect and send the metadata, XML or JSON, through any kind of HTTP service, I could even extend it by using a PowerShell provider that would enable me to execute a PowerShell script.
  • Microsoft Flow that allows all type of Business Users to create and automate workflows across multiple applications and services without the need for developer help in a very simple and fast way. These automated workflows are called flows.
  • And finally, Power BI to create interactive data visualization (dashboards and reports)

Processing Feedback Evaluations Paper: The Solution

Processing Feedback Evaluations: The solution

SmartDocumentor: to process and extract metadata from paper

Again, I’m not going to explain in details how the solution is implemented inside DevScope’s SmartDocumentor for it is not the point of this article, and if you want to know more about it, you can always contact me. However, let me contextualize you:

  • SmartDocumentor OCR flow will be listening in two receive locations: Share Folder and directly from the scanner device;
  • After receiving and extract the data from the scanned documents (paper), SmartDocumentor will send the metadata to a Microsoft HTTP endpoint.

SmartDocumentor OCR Review Station: Survay Template

  • Inside the SmartDocumentor processes, we optional can specify if we want to review the documents – SmartDocumentor Review Station – before you sent to the Microsoft Flow (or any other system).

02-Flow-Processing-Feedback-Evaluations-paper-SmartDocumentor-Flow-Solution

Power BI to deliver interactive data visualization (dashboards and reports)

Regarding Power BI, Microsoft Flow Power BI connector (is the same of the Logic Apps connector) only accepts you to use streaming datasets (this has advantages and some disadvantages that we will see further on), that allows you to easily build real-time dashboards by pushing data into the REST API endpoint. To create your streaming datasets, you should access to Power BI with your account:

  • Select your ‘Workspace à Datasets’, and then on the top right corner click ‘+ Create’ and then ‘Streaming dataset’

Processing Feedback Evaluations Paper: Create Power BI Streaming Dataset

  • In the ‘New streaming dataset’, select ‘API’ and then click ‘Next’
  • In the second ‘New streaming dataset’, give a name to your dataset: “FeedbackForm” and then add the following elements:
    • SpeakerName (Text) – represents the name of the speaker that is obtained in the evaluation form according to the session.
    • ContentMatureFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
    • GoodCommunicatorFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
    • EnjoySessionFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
    • MetExpectationsFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
    • SpeakerAvrg (Number) – A simple average calculation (sum all the above metrics divide by 4)
    • WhoAmI (Text) – represents the type of attendee you are (developer, architect, …) that is obtained in the evaluation form
    • SpeakerPicture (Text) – picture of the speaker according to the session that is that is obtained in the evaluation form.

Processing Feedback Evaluations Paper: Power BI Streaming Dataset

  • And because we want to create interactive reports in order to have more insights from the event. We need to enable ‘Historic data analysis’ and then click ‘Create’

Unfortunately, streaming dataset is meant to be used for real-time streaming and is a little limited in terms of want we can do with it. For example, it doesn’t allow you to combine different sources, for example, a “table” that can correlate speaker to get their pictures, or to make aggregations of metrics like “Speaker average”. Which means that we need to send all of this information from Microsoft Flow.

Microsoft Flow to create an integration process flow

At the beginning of this article, I told that one of the advantages of Microsoft Flow is that allows Business Users to create and automate workflows across multiple applications and services without the need for developer help. So, for this example, I will try to abstract from the fact that I am a developer and “try” to implement as if I were a business user: no custom code is allowed!

Microsoft Flow it will be the component that will allow us to extend a product (OCR software) that, as all products, are limited to certain features and capabilities to be able to communicate with a huge range of SaaS applications that appear all day on the market.

In order to integrate SmartDocumentor OCR with Power BI, we need to create a Microsoft Flow that:

  • Accept a JSON through an HTTP Post. For that, we use a ‘Request – When a HTTP request is received’ trigger.

Processing Feedback Evaluations: Flow process - HTTP trigger

    • And, because we do have friendly tokens to access the elements of the message, we will use a sample of the JSON message to be able to generate the correct JSON Schema. For that you should:
      • From the trigger configuration box, select the option “Use sample payload to generate schema” and past the above sample:

[
{
“Key”: “RecognitionRating”,
“Value”: “100”
},
{
“Key”: “RecognitionStatus”,
“Value”: “Final”
}
]

      • To generate the JSON schema, click “Done”

Processing Feedback Evaluations: Flow process - HTTP trigger Schema

  • Next, we need to create the following support variables, that we will use to store the data extracted from the SmartDocumentor JSON message – that is a Name/value typed message – to be sent to Power BI dataset:
Name Type Initial Value
count Integer 0
speakerName String
Picture String
ContentClear String
GoodCommunicator String
EnjoySession String
MetExpectation String
whoAmI String
    • For that, we need to add a ‘+New step’, ‘Add an action’ and then enter Variables’ into the search box. Select “Variables – Initialize variable” from the actions list.

Processing Feedback Evaluations: Flow process - Variables

    • And repeat the same task for all the variables.

Note: Unfortunately, to the date, there is still no way to create multiple variables using a single shape. In my opinion, this could and should be made as a table approach instead of the existing approach.

Now that we have all our variables that we need to store the information that we need to extract from the SmartDocumentor JSON message, we need to start extracting based on the Evaluation form. The first part of the form is: “What session are you evaluating”:

Processing Feedback Evaluations: Flow process -Evaluation Form

and for us to find out what session is being selected so we can “map” the speaker and picture name we need to look for the keys “S1”, “S2”, “S3” and “S4” and see which on them is filled. To do that we need to:

  • Add a ‘+New step’, ‘… More’ and then select ‘Add a switch case’

Processing Feedback Evaluations: Flow process - Switch case

    • On the “On” property of the switch condition configuration, select from the list of tokens the “Key” token from the “When a HTTP request is received” trigger

Processing Feedback Evaluations: Flow process - Switch case configuration

    • Because this is a Key/Value messages with multiple records, the Flow designer will automatically place this switch condition inside a Loop that will iterate thru each key/value pair.
  • In the first case, let rename it ‘Case S1’ branch configuration:
    • On the “Equals” property, type “S1”
    • And then add a new condition by selecting ‘… More’ and then select ‘Add a condition’
      • On the ‘Yes’ branch
        • Choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
        • And set the “Picture” variable to the desired value
        • Do the same steps this time to set the “speakerName” variable
      • Leave the ‘No’ branch as it is

Processing Feedback Evaluations: Flow process - Switch case 1

  • Add 3 more new Case branches and repeat the same steps, this time configuring the values to look for “S2”, S3” and “S4”.

The second part of the form is: “What is my evaluation”:

Processing Feedback Evaluations: Flow process - Evaluation Form

To extract this information, and because we already have a Switch case condition in place, what we need to do is:

  • Create 4 new different branches in the Switch case condition, one for each question – “Q1”, “Q2”, “Q3” and “Q4” – and then:
    • On the “Equals” property, type “Q1”
    • And then Choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
      • And set the “ContentClear” variable to the rate provided by selecting from the list of tokens the “Value” token from the “When a HTTP request is received” trigger
    • Do the same steps for
      • “Q2” to set the variable “GoodCommunicator”
      • “Q3” to set the variable “EnjoySession”
      • “Q4” to set the variable “MetExpectation”

Processing Feedback Evaluations: Flow process - Set variables

Finally, the last section is about “Who am I”

12-Flow-Evalation-Who-am-I

This is probably the more complicated section. Here, we basically want to travel from a range of position and if this is selected we append to a string separated by a comma, something like “Developer; Student”. To do that we need to:

  • On the Default branch, add a new condition by selecting ‘… More’ and then select ‘Add a condition’
    • In the condition expression, select “Edit in advanced mode” and then type the condition to treat the key/name pair if “count” variable is >13 and less or equal with 28
@and(greater(variables('count'), 13),lessOrEquals(variables('count'), 28))

Processing Feedback Evaluations: Flow process - condition advance expression

    • On the ‘Yes’ branch
      • We need to check if “tag” is selected or not. If yes set we need to add to the “whoAmI”, otherwise we don’t do nothing. To do that we need an extra if condition: Add a new condition by selecting ‘… More’ and then select ‘Add a condition’
        • On “Choose a value” property select the “Key” token from the “When a HTTP request is received” trigger.
          • On the condition set “Is not equal to”
          • And in the other “Choose a value” property leave it empty

Processing Feedback Evaluations: Flow process - Anothe condition

      • However, to set properly the “whoAmI” variable we need another additional condition to check if the “whoAmI” variable is empty or not. If empty set the “whoAmI” variable with the value, otherwise append to the existing value a comma and the new value. To do that we need to:
        • Add a new condition by selecting ‘… More’ and then select ‘Add a condition’
          • On “Choose a value” property select the “whoAmI” token from the “Variable” context.
          • On the condition set “Is not equal to”
          • And in the other “Choose a value” property leave it empty
      • On the “Yes” condition, choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
        • And set the “whoAmI” variable to the desired value – select the “Key” token from the “When a HTTP request is received” trigger.
      • And on the “No” condition, 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 set the “whoAmI” variable to the desired value – select the “Key” token from the “When a HTTP request is received” trigger.

Processing Feedback Evaluations: Flow process - condition chain

  • Finally, after the Switch case condition, and just before finishing the “Apply to each” cycle
    • Choose an action” window, enter “Variables” and select the trigger “Variables – Increment variable”
      • On the “Name” property select the “count” variable
      • And on the “Value” set as 1

Processing Feedback Evaluations: Flow process - increment count

To finalize the entire process, we just need to:

  • Calculate the rate average – the sum of all question results divided by the number of questions.
  • And send it to Power BI

To accomplished that we need to:

  • Add a ‘+New step’, ‘Add an action’ and then enter “Variables” into the search box. Delect the trigger “Variables – Initialize variable”
    • On the “Name” property select the “SpeakerAvrg” variable
    • On the type property set to be Integer
    • And in the Value property, from the context dialogue box, select “Expression” tab and set the following expression:
int(div(add(add(int(variables('ContentClear')), int(variables('GoodCommunicator'))) , add(int(variables('EnjoySession')), int(variables('MetExpectation')))),4))

Processing Feedback Evaluations: Flow process - calculate average

  • In the last step of the Logic App: we push the data into the Power BI streaming dataset created earlier by using the new ‘Power BI’ Connector. To do this we need to:
    • Add a ‘+New step’, ‘Add an action’, and then enter ‘Power BI’ into the search box. Select “Add row to streaming dataset” from the actions list.
      • Select the name of the workspace and then the name of the streaming dataset
      • The next step is to select the Table titled “RealTimeData”
      • And finally, map the properties with the different variables tokens has in the picture

Processing Feedback Evaluations: Flow process - Power BI

Give a proper name to the flow and save it

The end result

After saving the Microsoft Flow and process the Evaluation forms, the result is this beautiful and interactive report that we can present in a monitor during the breaks of our events:

Processing Feedback Evaluations paper: SmartDocumentor Logic App process Power BI dashboard

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

I Love My Flow Sticker

I Love My Flow Sticker

I don’t know exactly why but maybe because I’m always talking about BizTalk Server on every major event my friends have a tendency to say that I love my server and I live on my personal island:

Sandro Pereira: BizTalk ServerI Love My Server

Don’t get me wrong I do really love BizTalk Server and I don’t mind talking about the product, It’s something I do with pleasure because I talk about something I do really like but the main reason I talk every single time about BizTalk Server in major events is because the organizers ask me to speak about BizTalk Server – and I will never say no to that regardless of whether I would like or not to talk about other topics.

But despite that BizTalk Server is not the only tool I like, in fact, as I spoke about in my last talk in INTEGRATE 2018, you should use the best tool to address each particular problem, and me being an Integrator Magician, I do like other tools like API Management, Service Bus, Event Hubs, Logic Apps, Azure Functions, Power BI, PowerApps and Flows for example.

So, in response to one of my dear friends – Kent Weare – that normally say that I’m a server lover I created this my new sticker: “I Love My Flow”. Why, for two reasons:

  • obvious to annoy and play around with Kent – I know that he is happy with this;
  • but mostly because I love Flow and I think it has huge potential to be used in certain scenarios both in Enterprise or Personal context

The sticker has 3 similar variations but my favorite one is the first:

I Love My Flow Sticker

I Love My Flow Sticker 02

I Love My Flow Sticker 03

Hope you enjoy! In the download, you will find the sticker in vector format that you can use as you wish. My next task will be to print the sticker so I can offer in my next Flow speaking engagement.

Special thanks to my two coworkers at DevScope: António Lopes, the designer, and creator behind my idea.

You can download BizMan, The BizTalk Server SuperHero sticker from:

I Love My Flow Sticker (2,6 MB)
Microsoft | TechNet Gallery

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

Flow’s to the help: How to extend SmartDocumentor OCR receive processing points (or locations) easily with Microsoft Flow

Flow’s to the help: How to extend SmartDocumentor OCR receive processing points (or locations) easily with Microsoft Flow

Following my last blog post on how to “Processing Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Logic Apps, Azure Functions & Power BI”, I mention that you can try the solution yourself, at least part of the solution – the most important part – by sending an email with your survey to sandro.pereira.demos@gmail.com with the following subject “SmartDocumentorSurvey“ and that I had a very simple Microsoft Flow listening to this mailbox that:

  • Extracts the attachment;
  • Sends it to SmartDocumentor OCR
    • Send file to an on-premise folder;
  • And notifies you that the file was received;

In this post I will explain you can you use Microsoft Flow to easily extend any existing product, in my case, how I was able to easily extend SmartDocumentor OCR without me being an expert on that product – this DevScope team is super busy to spend to much time with me explaining me everything – jokes apart, I intension don’t want there help the product should be easy to use without requiring too much knowledge and I also want to see and try how people can easily extend and interact with the product in different ways.

For example, in this particular case we actually didn’t need any Flow because SmartDocumentor has the capability to listen to an Email but in that case, I confess that my knowledge of SmartDocumentor product and its tools are a little reduce and I will probably need the help for my coworkers to teach me how to do it. Nevertheless, I had my demo working in two different receive processing points (or locations):

  • Directly from the Scan device that I used on all my presentation demos;
  • Or thru a receiving folder.

I knew how to change that receive folder, I knew how I can easily connect and extract attachments using Microsoft Flow and I knew a very stupid and simple way to send that file to an “On-premise” folder using Flow… so, for me, it was a simple math operation: 1+1+1=solution!

As Logic Apps, the beauty of using Microsoft Flow’s is that provides almost all Logic Apps capabilities, once it runs on top of Logic Apps, it is part of my Office365 subscription and there for it provides a very simplify and robust way to also extend the capabilities of my OCR software but also by being very cheaper and fast to implement without requiring too much technical knowledge.

Flow’s to the help: The Solution

In order to be able to extend SmartDocumentor OCR receive processing points (or locations) with Microsoft Flow, we need to create a Flow and for that, we 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.

Flow's to the help: SmartDocumentor Listener Flow Create a blank Microsoft Flow

  • On the “Create a flow from blank”, select “Create from blank” to start the process of creating an empty Flow

Flow's to the help: SmartDocumentor Listener Flow Create a blank Microsoft Flow

  • On the Flow designer, on the Trigger event enter “Gmail” and select the option “Gmail – When a new email arrives”

Flow's to the help: SmartDocumentor Listener Flow Trigger Gmail

  • We want that all the email received with the “Subject: SmartDocumentorSurvey” will trigger this flow, so on the Gmail Trigger configuration:
    • Confirm that “Inbox” is configured in the “Label” property
    • Specify the filter, in my case all email that has the “Subject” equal to “SmartDocumentorSurvey”
    • And confirm that “Include Attachments” property is set to “Yes”

Flow's to the help: SmartDocumentor Listener Flow Trigger Gmail Configuration

  • Add the next step by clicking “+New step” button and then choose the “Add an action” option

Flow's to the help: SmartDocumentor Listener Flow Add action

After we receive the email we want to store/save the Survey attachment file on a specific folder where SmartDocumentor is listening, and there are several ways to archive this:

  • The most common will be using the File System connector that allows you to connect to any File Systems on your local or network machines using the On-Premises Data Gateway but this is also the most technical option which needs you to configure an On-Premises Data Gateway
  • Another option is to make use of Dropbox desktop client, that runs on Windows, Mac, or Linux operating systems and gives you access to your Dropbox directly through a folder on your hard drive. The advantages of this approach are that:
    • You don’t need any technical skills (no need to configure an On-Premises Data Gateway) and all users knows or are familiar will Dropbox;
    • We can easily store files in Dropbox using Flow;
    • And once synchronize to your local machine running SmartDocumentor, SmartDocumentor will peak that file and process it (deleting that file from the folder and from Dropbox) – and there isn’t too much latency on the file synchronization, Dropbox client is actually really good.

Because we want to keep this really simple and familiar to Business users we will go with the Dropbox approach, so for that, we will need to:

  • On the “Choose an action” window, enter “Dropbox” and select the trigger “Dropbox – Create file”

Flow's to the help: SmartDocumentor Listener Flow Dropbox create file

  • On the Dropbox action configuration:
    • Specify the folder in with you want to store the file, in my case: “/publicdemos/smartdocumentorsurvey”
    • On the “File Name” property, select from the list of tokens the “Name” token from the “When a new email arrives” trigger
    • On the “File Name” property, select from the list of tokens the “Content” token from the “When a new email arrives” trigger

Flow's to the help: SmartDocumentor Listener Flow Dropbox create file Configuration

    • Because we can have multiple attachments on a specific email, the Flow designer will automatically place this “Dropbox – Create file” inside a Loop that will iterate thru each attachment.

The last step we want is to notify the user that is testing the SmartDocumentor Survey solution that his email was received and is being processed, to do that we need to:

  • Add the next step by clicking “+New step” button and then choose the “Add an action” option
  • On the “Choose an action” window, enter “Gmail” and select the trigger “Gmail – Send email”

Flow's to the help: SmartDocumentor Listener Flow Gmail Action

  • On the Gmail action configuration:
    • On the “To” property, select from the list of tokens the “From” token from the “When a new email arrives” trigger
    • On the “Subject” property, specify the email subject, in my case: “SmartDocumentor submitted document”
    • On the “Body” property, we will specify the name of the file we are processing as well as the URL where you can consult the Survey Report. So, we are combining static text with some tokens provide from previous actions as you will see in the picture below.

08-SmartDocumentor-Listener-Flow-Action-Gmail-Configuration

    • Again, because we can have multiple attachments on a specific email, the Flow designer will again automatically place this “Gmail – Send email” action inside a Loop that will iterate thru each attachment.

At the end the flow will look like this:

Flow's to the help: SmartDocumentor Listener Flow Process

And once is triggered:

  • It will copy the attachment to a Dropbox folder that is being synchronized, with the use of the Dropbox desktop client, to our local SmartDocumentor server;
  • SmartDocumentor OCR will process this survey picture and it will do the process that was described in my previous post;
  • And finally, will send an email to the user that is testing this SmartDocumentor OCR solution

10-SmartDocumentor-Listener-SmartDocumentor-Flow-process-notification

I intentional want to keep this a first simple as possible, so there are some limitations in current approach:

  • You can send multiple form pictures in attach but you shouldn’t send any other type of pictures in attach (that aren’t surveys). Again, I can easily create some flow rules to validate but I didn’t implement nothing like that.
  • Avoid sending email signature with pictures, the process will work but it ends up having some SmartDocumentor and Logic Apps runs with failure (didn’t implement these type of validations but it is possible)

I will improve this approach in the future, stay tuned!

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

How to setup Flic Smart Button and trigger a Microsoft Flow

How to setup Flic Smart Button and trigger a Microsoft Flow

Trigger your Microsoft Flows by pressing a physical button, known as a Flic Button or Flic Smart Button provide by Shortcut Labs is quite a simple task. I was impressed by how easy and fast it was to set up and put it working, it was literally 5 minutes!

And then you can use it for fun: “Wake Up Kent Weare” (the example we will be using here) or for more realistic and interested scenarios like pressing a Flic button to track your working hours (1 click to start tracking, double-click to stop tracking), block your calendar for next next hour, count visitors at an event, notify me if I have a visitor, save geographical locations, or how is your day/are you happy with the service (one click for happy, double-click for unhappy – if you have only one button)

Of course, to do that we need to have:

  • A physical Flic Smart Button

Flic Smart Button

  • And downloaded Flic’s Android or iOS mobile app, that you will need to use it to pair one or more Flic Buttons.

Flic Smart Button Mobile App Android

After you download the Flic mobile app, to configure the Flic Button you need to:

  • Make sure that your mobile has Bluetooth activated;
  • Open the Flic mobile app from your mobile phone, sign in with your credentials using Facebook, or by creating your Flic Account;
  • Once you login in the app, on the phone tab, click “Add Flic to Phone” at the bottom of the screen

Flic Smart Button Mobile App Phone tab

  • Once the “Searching for Flics” screen appears, take or physical button an click it once to pair it with the mobile application

Flic Smart Button Mobile App Button pairing

  • Once the “Pairing complete!” screen appears, your button will be successfully paired with your phone. Click “Finish” to complete.

Flic Smart Button Mobile App Button pairing completed

  • And a button will be added to the “Phone” tab

Flic Smart Button Mobile App Phone button

Now that the physical button is pair with our mobile phone, we need to configure the Flic’s events that the button will be triggering. For each button we can specify 3 types of events:

  • click (one quick press)
  • double-click (two quick presses)
  • hold (one long press)

To accomplish that you need to:

  • Press the “My Flic” button present in the “Phone” tab
  • This will open the button configuration screen, there you should:
    • Change the button name
    • And then configure one or all the 3 trigger events
  • In our case, we will configure the “Click” trigger event to be able to trigger a Microsoft Flow. For that, we need to click on the “Click” option

Flic Smart Button Mobile App Phone button configuration

  • On the “Click” trigger event list screen, select “Tools” category option

Flic Smart Button Mobile App Phone button tools

  • And on the “Tools” category list screen, select “Flow” option

Flic Smart Button Mobile App Phone button Flow

  • On the Flow screen you may or may not select to include GPS location and then you need to click “SAVE ACTION” button

Flic Smart Button Mobile App Phone button Flow save

And our button will now be properly configured to trigger a Microsoft Flow once someone click (one time) the physical button

Flic Smart Button Mobile App Phone button Flow action

Now the only thing that is missing is for us to create a Flow that will be associated with this trigger event to fire up a Flow process.

Once again, this demo is entitled “Wake Up Kent Weare” and will be just for fun to try to annoy the dear friend Kent Weare and try to wake him up at 4 AM US (9 AM Portugal), if he didn’t forget to disable email notifications at night, simply by clicking on a button. To do that we need to:

  • Sign into Microsoft Flow.
    • You can create one flow from scratch
    • or by typing “flic” into the search box and select one of the several templates provided by Flow team.
  • Click “My flows” option on the menu and then click “+ Create from blank” to create a Flow from scratch.

Create a blank Microsoft Flow

  • On the “Create a flow from blank”, click “Create from blank” to start the process of creating an empty Flow

Create a blank Microsoft Flow

  • On the Flow designer, on the Trigger event enter “Flic” and select the option “Flic – When a Flic is pressed”

Microsoft Flow Flic Trigger

  • On the Flic Trigger configuration
    • On “the Flic button” combo box, select the button we add create on the Mobile App earlier
    • And on the “Events” combo box, select “click” event that was the only one that we have configured

(of course, the first time you use this trigger, it will ask for permissions and you need to provide the authentication to your Flic account)

Microsoft Flow Flic Trigger configuration

  • Add the next step by clicking “+New step” button and then choose the “Add an action” option

Microsoft Flow Add action

  • On the “Choose an action” window, enter “Outlook” and select the trigger “Office 365 Outlook – Send an email”

Microsoft Flow Outlook Action

  • On the Send an email action configuration, you need to specify
    • The address of the recipient on the “To” property field
    • The subject of the email on the “Subject” property field
    • And the body of the email on the “Body” property field

Microsoft Flow Outlook Action Configuration

  • Be sure you give a proper name to your Flow, mine will be “Wake Up Kent Weare” and save it

Now… PRESS THE BUTTON! PRESS THE BUTTON!

The Flow was successfully triggered once I press the button

Microsoft Flow runs

However, the result was not what I expected 🙂

Microsoft Flow runs end result

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

New version of Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio is now available on GitHub

New version of Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio is now available on GitHub

Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:

  • BizTalk Server
  • Microsoft Azure
    • Azure App Service (API Apps, Web Apps, Mobile Apps and Logic Apps)
    • Event Hubs, Event Grid, Service Bus, …
    • API Management, IoT, and Docker
    • Machine Learning, Stream Analytics, Data Factory, Data Pipelines
    • and so on
  • Microsoft Flow
  • PowerApps
  • Power BI
  • PowerShell
  • Infrastructure, IaaS
  • Office 365
  • And many more…

Microsoft Integration Azure Stencils Pack Visio

What’s new in this version?

With the growing number of stencils in this package, it was becoming hard to find or look for the right shape/representation and based on some feedback I received from the community and some tips, I focused most of the work in this new version in providing search capacity to this package, but it wasn’t the only one:

  • Search Capabilities: Defining the correct metadata information and keywords for all the shapes for a better search functionality.

Microsoft Integration Azure Stencils Pack Visio Search capabilities

  • New shapes: of course, that has happened in all other versions, new shapes were added, in particular: Generic, Microsoft Flow and PowerApps shapes

Microsoft Integration Azure Stencils Pack Visio Microsoft Flow

Microsoft Integration Azure Stencils Pack Visio PowerApps

You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from:
Microsoft Integration Azure Stencils Pack VisioMicrosoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio (18,6 MB)
GitHub

Or from:
Microsoft Integration Azure Stencils Pack VisioMicrosoft Integration and Azure Stencils Pack for Visio 2016/2013 v3.1.0 (18,6 MB)
Microsoft | TechNet Gallery

The post New version of Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio is now available on GitHub appeared first on SANDRO PEREIRA BIZTALK BLOG.

How can PowerApps and Microsoft Flow allow your Power Users to quickly build Enterprise Mobile Apps video and slides are available at Integration Monday

How can PowerApps and Microsoft Flow allow your Power Users to quickly build Enterprise Mobile Apps video and slides are available at Integration Monday

One of my main goals last year in Portugal was to introduce PowerApps and Microsoft Flow in several Portuguese communities: SharePoint, CRM, .NET and Integration communities. Finally, I was also invited to presented that session on Integration Monday community – I think it was the perfect scenario to deliver one last time that session – don’t get me wrong I will still be talking about these topics in the future but with different demos and context – and you can watch the video recording of the previous session here: How can PowerApps and Microsoft Flow allow your Power Users to quickly build Enterprise Mobile Apps.

That was the eighth session that I deliver for that community:

About my session

Session Name: How can PowerApps and Microsoft Flow allow your Power Users to quickly build Enterprise Mobile Apps

PowerApps and Microsoft Flow Session

Session Overview: Every organization faces constant pressure to do more with less. While technology is often the key to operating more effectively and efficiently, cost and complexity have often prevented organizations from taking maximum advantage of the potential benefits. The growth of SaaS (software as a service) has lowered barriers – no need to deploy servers or to install and configure complex software systems. Just sign up and go.

Microsoft Flow and Microsoft PowerApps will help these people (normally business users) achieve more.

We know not every business problem can be solved with off-the-shelf solutions. But developing custom solutions has traditionally been too costly and time-consuming for many of the needs teams and departments face, especially those projects that integrate across multiple data sources or require delivery across multiple devices from desktop to mobile. As a result, too many technology needs end up unsolved or under-optimized. We piece together spreadsheets, email chains, SharePoint or/and manual processes to fill in the gaps.

PowerApps and Microsoft Flow are both aimed squarely at these gaps. They give people who best understand their needs and challenges the power to quickly meet them, without the time, complexity and cost of custom software development.

In this session, we will look at these two new offering from Microsoft: PowerApps and Flow. What are they? How can I use it? But special we will walk through and create from scratch some live demos showing how to create Enterprise Mobile Application that easily connects with all your enterprise platforms like Office365, SharePoint Online, Dynamic CRM, on-premise SQL, Social Networks and much more and also how they can automate some common tasks using the new Microsoft Flow.

You can watch the video recording of this session here: How can PowerApps and Microsoft Flow allow your Power Users to quickly build Enterprise Mobile Apps.

About Integration Monday

Integration Monday is full of great sessions that you can watch and I will also take this opportunity to invite you all to join us next Monday.

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

Auckland Connected User Group (ACSUG)

Auckland Connected User Group (ACSUG)

February 8, 2017

Auckland Connected User Group (ACSUG)

Filed under: Azure, BizTalk — Tags: Logic Apps, APIM, Flow, PowerApps — mbrimble @ 12:31 pm

We are starting to meet again regularly after a long hibernation.

Thiago Almeida started the group many years ago but it has been in hibernation for  awhile. Mike Howell, Craig Haiden and myself have decided to start it again. We plan to have regular meeting every two months during the year. We have some good speakers lined up and have some fun evenings planned.

If you are interested in joining then join up at https://www.meetup.com/Auckland-Connected-Systems-User-Group/?scroll=true

The first two sessions promise some exciting action;

Tuesday, February 14, 2017   6:00 PM to 7:30 PM – Steef-Jan Wiggers :: Cloud Integration: so many options!
Saturday, March 25, 2017   8:30 AM to 6:00 PM – Global Integration Bootcamp (AKL)

Credits for the picture go to Brian Lai who works on our support desk. He recently won a photo competition run by Microsoft. If you want to see more of his work then check out these links

Microsoft themes: https://support.microsoft.com/en-gb/help/14014/windows-desktop-themes-from-the-community

and search for:

 “Nature’s Grace” or http://download.microsoft.com/download/1/5/4/154E922A-7744-4474-A95A-5BDC043CDBBE/NaturalGraceBrianLai.themepack

“COMMUNITY SHOWCASE: AQUA 3” or http://download.microsoft.com/download/1/7/A/17ABABA3-79C8-4B6D-AA18-384E33D1B055/CommunityShowcaseAqua3.themepack

“North Island” or http://download.microsoft.com/download/7/2/8/728E3928-EE04-4A76-A09F-2A00EA444432/NorthIslandNZBrianLai.themepack

 

Advertisements

No comments yet.

RSS feed for comments on this post. TrackBack URI