Posted on Leave a comment

Create And Format HTML Table Using Microsoft Flow

 

ntroduction

Nowadays, the buzz word in the market is MS Flow. I would say that without MS Flow, SharePoint Online is incomplete. When we talk about SharePoint Online, we must talk about other related technology like MS Flow, PowerApps, SPFx, Teams, etc. These all go side by side. As Microsoft is focusing more on SharePoint Online, the on-premise version is not what they are recommending to use, not even the SharePoint designer workflow. In this context, we could say that MS Flow is a recommended/alternate approach of the modern way of developing a workflow, instead of designer workflow.
There are plenty of advantages of MS Flow – I am not going in-depth for this as the purpose of this article is to create HTML in MS Flow, so my focus would be on how we can make use of Create HTML action inside MS Flow.

Use cases of HTML table in MS Flow

There are many use cases in our SharePoint application. I’ll briefly discuss a few – let’s say your boss has assigned tasks to the team members and at the end of the day, he/she wants to get the consolidated task status auto-emailed. Then, the MS Flow with “Create HTML” is the right choice. One more example – let’s say we have a “Customer-Sales” list which holds the details of customer sales summary information and at the end of the day, an auto email should be sent to the sales manager who will summarize the daily sales. In this case also, MS Flow with “Create HTML” action is the right choice one.
In this article, I would just read a “Customers” list data and will send an email in the form of HTML table as POC (proof of concept).

Actual Implementation – step by step walk through

I assume that you know how to create that list with custom columns, so I am skipping the list creation step, hence here is my already created “Customers” list.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Using the MS Flow, I will send these list data to the email in the form of HTML. So let’s start — how can we do this?
From your “Customers” list page, click on the “Flow” dropdown list and then click the “Create a flow” button, as mentioned below.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Click on the “Show more” dropdown list.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Select “When a new item is added in SharePoint, complete a custom action” template as mention below.
Note
In a real-time scenario, you need to start your flow based on some specific conditions. Here, I’ll show a POC, i.e., whenever an item is created in SharePoint list, this flow will get triggered.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Click on the “Continue” button as below.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Then, we’ll land on the below page.

https://tinyurl.com/y3vfo4yb

Note
  1. https://<country>.flow.microsoft.com/ in this URL <country> is dynamic, this will be based on the country from where you are accessing, here it displays as “india” as I am accessing from India.
  2. And in the parameters.sharepoint.site=<your SharePoint online site>
“Site Address” and “List Name” pre-populated – make sure these are correct.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
At this moment lets name this flow as “Create and Send HTML Table to Email” and click on “Save” button then click on “+Next step” button
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Then search with “SharePoint” and select “Get Items” action as mentioned below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Select the “Site Address” and “List Name” as mentioned below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Note
In Show advanced options – we can get the data based on specific query (like camel query) – in this we are not handling those, we’ll get all data from the “Customers” list – just showing the screen, then click on “+Next step” button.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Add data operation “Compose” action: search for “Compose” text and select the “Data Operations” as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
From the next screen, select the “Select Data Operations” action.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
We will get the empty “From” and “Map” in “Select” operation action – you need to click on the from box and need to double click on the “value” (from Dynamic content) tab.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online

How to do dynamic mapping with the columns?

I have created all columns and correspond mapping but shown here in screen shot only for Email ID mapping to show how to create – create a meaningful mapping name corresponding to your column, then click on second box (add dynamic content), under dynamic content tab in search box type your column name which you want to map , then select your column under “Get Items” section.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Click on “+ New step” button
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Select “Create HTML Table” action like below – search with “Create” text then click on “Create HTML Table” action
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Now we can see “Create HTML Table” action as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online

Select

How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Click on “+ New step” as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Select “Send an email” action as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Enter the mandatory parameters as below for the email action.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Note
In the above configuration “Is HTML” should be selected as “Yes” otherwise we’ll not get tabular format output – so it’s very important, though it is an optional.
Now we are done with the basic configuration – lets test this flow at this moment, before that we must save it.
Click on “Test” button as highlighted below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
From the next screen, select the first radio button and click on “Save & Test” button as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Wait for a few seconds – you will get the below screen,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
As the above highlighted message says – add a new item to the SharePoint “Customers” list to trigger the flow.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Come to your flow screen(tab) – if everything goes fine you should see the below flow ran a successful message.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Open your outlook office 365 mail box using the below url,
https://outlook.office365.com/owa/?realm=SPRND.onmicrosoft.com&exsvurl=1&ll-cc=1033&modurl=0
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Here you can see an email has come with all customer details in the html tabular format in the email body – we are done with our basic purpose of this article but the html table in the email body does not look nice, however, this is the default behavior. Now we’ll format it a little to look better.

How to format the HTML table in MS flow?

We’ll add padding and color to the above html table – insert a new step in between “Create HTML table” and “Send an email” action like below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Click on “Add an action” link like below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Select “Compose” data operation action like below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Rename your default “Compose” to Format Customer Table as below,
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online

Enter the below text like the below screen

replace(body(‘Create_HTML_table’),'<table>’,'<table border=”3″ bgcolor=”87ceeb”>’)
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Now, double click on “Send an email action” and select the appropriate output from “Format Customer Table” as below and save the flow.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
We are done with the basic formatting – however, you can explore more about this on your own.

How to test the flow without going to the SharePoint list?

Click on the “Test” button from the top right corner as we did in our previous test and perform the below operation as mentioned in the order and finally, click on the “Test” button (no 3).
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Now, you can see your flow execution status – if everything goes fine you will see the below successful message.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online
Now go to your office 365 outlook email tab again, you can see the new email has come with proper customer details with a formatted table.
How To Create And Format HTML Table Using Microsoft Flow And SharePoint Online

Summary

Thus, we have learned the below topics from this tutorial:
  • Introduction – a brief introduction to Microsoft flow.
  • Use cases of HTML table in MS flow.
  • Actual Implementation – step by step walk-thru.
  • How to run the flow from your previous successful run without going to list.
  • Formatting HTML table in MS flow.

Source: Create And Format HTML Table Using Microsoft Flow

Posted on Leave a comment

Use expressions with conditions. – Power Automate | Microsoft Docs

Use expressions in conditions to check multiple values

In this walkthrough, you’ll learn to use expressions and Conditions to compare multiple values in Advanced mode.

When you create a flow, you can use the Condition card in basic mode to quickly compare a single value with another value. However, there’re times when you need to compare multiple values. For example, you may want to check the value of a few columns in a spreadsheet or database table.

You can use any combination of the following logical expressions in your conditions.

TABLE 1
Expression Description Example
and Takes two arguments and returns true if both values are true.
Note: Both arguments must be Booleans.
This expression returns false:
and(greater(1,10),equals(0,0))
or Takes two arguments and returns true if either argument is true.
Note: Both arguments must be Booleans.
This expression returns true:
or(greater(1,10),equals(0,0))
equals Returns true if two values are equal. For example, if parameter1 is someValue, this expression returns true:
equals(parameters(‘parameter1’), ‘someValue’)
less Takes two arguments and returns true if the first argument is less than the second argument.
Note: The supported types are integer, float, and string.
This expression returns true:
less(10,100)
lessOrEquals Takes two arguments and returns true if the first argument is less than or equal to the second argument.
Note: The supported types are integer, float, and string.
This expression returns true:
lessOrEquals(10,10)
greater Takes two arguments and returns true if the first argument is greater than the second argument.
Note: The supported types are integer, float, and string.
This expression returns false:
greater(10,10)
greaterOrEquals Takes two arguments and returns true if the first argument is greater than or equal to the second argument.
Note: The supported types are integer, float, and string.
This expression returns false:
greaterOrEquals(10,100)
empty Returns true if the object, array, or string is empty. This expression returns true:
empty(”)
not Returns the opposite of a boolean value. This expression returns true:
not(contains(‘200 Success’,’Fail’))
if Returns a specific value if the expression results in true or false. This expression returns “yes”:
if(equals(1, 1), ‘yes’, ‘no’)

Source: Use expressions with conditions. – Power Automate | Microsoft Docs

Posted on Leave a comment

How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger

Introduction

MS Flow is one of the key Apps on Office 365, which helps in quickly automating tasks and easily build complex business logic on list item level events. With MS Flow adding more actions and triggers, there is almost no need to create Event Receivers anymore. But one significant feature which Event Receivers offered was to disable the event firing when we are updating the same list item with the event receiver.
But MS Flow doesn’t provide the provision to stop a trigger.

Problem

Consider the below example where we have a list with 3 columns.
How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
A simple flow can be configured which triggers whenever an item is created or modified, which will update ColumnSum with a sum of ColumnOne and ColumnTwo. The MS Flow steps may look like below.
How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
This Flow keeps triggering itself infinitely, because of the ‘Update Item’ action.

Solution

As MS Flow doesn’t provide any provision to disable the re-trigger, we need to bring in a workaround in our flow structure which stops the Update Item’ action, when the update happens from the Flow itself. There are many workarounds but most of them are specific to the logic of the flow and use cases.
Here is a generic workaround which will work for most of the scenarios.

How do we do it?

Here is how the high-level flow structure should look like
  1. Trigger
    ‘When an item is created or modified’
  2. Action
    Use ‘Send an HTTP request to SharePoint’ to get make a call to the SharePoint REST API for getting the last 2 versions of the list item
  3. We maintain one column called ‘FlowFlag’ in the list to track if the update event is triggered by the Flow itself or not
  4. Compare the value of the ‘FlowFlag’ column from the last 2 versions of the list item
  5. If the value is the same then proceed with the Flow, if they are not the same which means Flow has triggered, then terminate the Flow
  6. And whenever we update the list item, also update the value of FlowFlag column by incrementing it by 1, so that its value is different compared to the previous version and the next Flow trigger will not update the list item again.

Building the Flow with a workaround

Create a column in the list of type number, called ‘FlowFlag’. Then proceed with creating the Flow as mentioned below

  1. Choose the trigger as ‘When an item is created or modified’
    How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
  2. Add an action – ‘Send an HTTP request to SharePoint’, and update the properties with the appropriate values
    Site Address: Select the site collection where your list is present
    Method: Enter as GET
    Uri: _api/web/lists/getbytitle(‘Additions’)/items(@{triggerBody()?[‘ID’]})/versions?$top=2 – This is a normal sharepoint REST API, where we are reading the list item (using ID) and calling versions and requesting only the top 2 versions.
    Headers: Enter the Key as ‘accept’ and value as ‘application/json;odata=nometadata’

    How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger

    And rename the step as ‘GetVersions’ to make it easy to understand.

  3. Next, add the Select Step, to parse the output of the previous step and prepare an array with only the FlowFlag value
    From: This expects an array. Add the expression – body(‘GetVersions’)?[‘value’] for this property
    Map: Enter the key as flag and value as an expression – item()?[‘FlowFlag’]

    How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
  4. Add a Condition step next, and check the value of ‘FlowFlag’ in the first and last rows from the Select step
    How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
  5. In the Yes section of the condition, add steps to perform the actual operations or add your processing logic. And in the No section, terminate the flow with the status as success.We don’t want to do anything when the flow itself has triggered the action.
    How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger

Enable Versioning on the List

As this workaround relies on the versions of the list item, the List Versioning should be enabled. Go to the List Settings -> Versioning Settings and enable it.
How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger
Maintain at least the last 2 major versions so that we can check the versions in the flow.

Summary

With this workaround, we can handle the infinite triggering problem of MS flow when it is updating the same list item. This flow could still trigger twice for an item add/update action, but it stops after the second trigger due to the condition check. Hope this helps.

 

Source: How To Stop MS Flow From Re-Triggering On An Item Created/Modifed Trigger