Hazard Reporting Tutorial Series (Part 3): Microsoft Flow - SharePoint Filtering, Data Operations, Tables and Reporting

Introduction

Welcome back to our Hazard Reporting Solution Tutorial Series. This is Part 3 of the hazard reporting scenario we are building.

In Part 1 of the series, we built a Hazard Reporting PowerApps and a SharePoint list to store that data from that app.

In Part 2 of the series, we added functionality to the solution to classify items as emergencies or not. We created Microsoft Teams and an item Approval & notification Flow for the same.

In Part 3 of the series, we will create another standalone flow to create and send a Daily / Scheduled Report about the approved and pending Hazardous items to the people responsible for those items.

This article includes step-by-step instructions on building and testing a daily reporting flow. Here’s an outline of the process:

1. Create a Microsoft Flow for Daily Reporting

1.A. Set up the Daily Reporting Flow

1.B. Get Items to Report

1.C. Select Data to Display for the Items to be Reported

1.D. Create an HTML Table of the Selected Data

1.E. Set up an Email

2. Test the Flow

2.A. Run the Flow

2.B. Test the Successful Processing of the Flow

2.C. Email Received in Outlook

Rather Watch a Video

Let’s get started with the build of this flow.

1. Create a Microsoft Flow for Daily Reporting

We are building a third flow for the PowerApps we built in Part 1 of this blog series. Here, we will create a recurring process to look for items that haven’t been resolved. Let’s get started with the build of this flow.

1.A. Set up the Daily Reporting Flow

Step 1:

  • Go to the flow portal, flow.miscrosoft.com.

  • You will see the Flow portal dashboard.

  • Click on + Create in the left menu.

  • Click on Scheduled Flow.

Screen Shot 2019-09-04 at 12.55.16 PM.png

Step 2:

  • Name the flow: Hazard Reporting - Daily Report

  • For scheduling this, Start this flow on the current date.

  • Set the time as 9:00 AM for everyday.

  • Set it to Repeat every 1 Day.

  • Click on Create.

  • This sets up our recurring schedule. The trigger is automatically set up to run every day.

  • Note: We can adjust the frequency whenever we want by clicking on the edit option of this trigger in the Flow.

1.B. Get Items to Report

Step 1:

  • Click on + New step.

  • Search for ‘Get items’ in the search box under Choose an action.

  • Click on Get items from SharePoint, under Actions.

Step 2:

  • In this action, for Site Address, choose the Site we’ve been working with—Incident Reporting site.

  • For List Name, choose Hazard Report.

  • Click on Show advanced options.

Step 3:

  • In Filter Query box, type: Status eq ‘Under Investigation’

This will help get all the items that are under investigation.

  • In Order By box, type: Created desc

This means that all the items that are under investigation will show in a descending type order.

Screen Shot 2019-09-04 at 1.05.50 PM.png

1.C. Select Data to Display for the Items to be Reported

Next we will use a Data operation. These actions are very useful when you have some data that needs to be adjusted in your Flow.

Here, we will perform a Select operation with the data - this will take specific columns from the output of the response from the Get Items action.

Step 1:

  • Click on + New Step.

  • In the search box under Choose an Action, type Select.

  • Choose the Select option.

Screen Shot 2019-09-04 at 1.19.29 PM.png

Step 2:

  • Click on the action’s From placeholder.

  • In the pop-up box, select ‘value’ under Get Items.

  • Note: Using this Select action, we will specify which column we want to work with from the data source. We will change some of the columns that are displayed, through this action.

Step 3:

  • To map the first column, in the Enter Key box, type Id.

  • For Enter value box, click in the box and search for Id in the pop-up Dynamic content search box, and select ID.

Step 4:

  • Click on Add dynamic content, to enter information about another column.

  • In second Enter Key box, type Description.

  • For the Enter value box, search for description in the pop-up Dynamic content list, and select Description.

Step 5:

  • Setup Created and Created By as shown above.

1.D. Create an HTML Table of the Selected Data

Here, we will create an HTML table based on the outputs of the Select action that we set up in the previous section.

Step 1:

  • Click on + New Step.

  • Type HTML in the Choose an action search box.

  • Select Create HTML table from Data Operations.

Screen Shot 2019-09-04 at 1.23.47 PM.png

Step 2:

For the From box, select Output from the Select operation showing in the pop-up Dynamic content box.

1.E. Set up an Email

Step 1:

From the output of the Select action, we created an HTML table, which we are finally going to email to the people responsible for the reported items.

  • Click on + New Step.

  • Type email in the Choose an action search box.

  • Select Send an email (V2).

Screen Shot 2019-09-04 at 1.25.49 PM.png

Step 2:

  • Specify the Subject of the Email as: Items Under Investigation

  • Go into HTML mode, by clicking on Code view icon: </>

  • Click inside the email Body, and select Output of Create HTML table action in the pop-up box.

  • In the To section, specify the stakeholders that will receive this email.

  • This completes the work of the Flow.

Screen Shot 2019-09-04 at 1.27.44 PM.png

To summarize, when the trigger is set off, we Get items out of SharePoint that are under review. The items are sorted in a descending type order. Next, the data is reduced into columns, an HTML table of the columns is created, and an email containing the table is sent.

2. Test the Flow

After setting up the flow, test it before implementing it with your team.

2.A. Run the Flow

Step 1:

  • Click on Save on the top right of the screen to save the Flow.

  • Click on Test in the top right corner.

  • In the Right pane called Test Flow, click on I’ll perform the trigger action.

  • Click on Save & Test.

Screen Shot 2019-09-04 at 8.12.55 PM.png

Step 2:

  • Here, you will see the right pane called Run Flow.

  • Click on Run Flow at the bottom.

  • The Run Flow pane will show a message saying: Your flow run successfully started

2.B. Test the successful processing of the flow

Step 1:

  • Go back to the Main flow, by clicking on the left-arrow icon on the left side of this flow’s name on top.

  • On the Flow dashboard, you will see the latest entry of the flow under Runs.

  • You will see its Status as Succeeded, as we had a successful run of the flow.

Screen Shot 2019-09-04 at 1.57.16 PM.png

Step 2:

  • Click on the latest flow entry to see the run history. This will give us more details about what happened in the flow

  • You will see a success message on the top: Your flow ran successfully.

  • You will see that the Recurrence trigger worked, then you Got Items, the items got reduced to Columns, the HTML table got created and the table was sent into an Email.

  • The green check marks next to each action shows that it all worked out successfully.

Screen Shot 2019-09-04 at 2.01.37 PM.png

2.C. Email received in Outlook

  • Go to the Outlook or email inbox of one of the stakeholders or recipients specified under the email action.

  • You will see an email with the subject: Items Under Investigation

  • When you open that email, you will see the table we create earlier.

  • You will see that there are two items in the Under Investigation status.

  • You will see a bit of information about each item—IDs, descriptions, dates of creation, and who they was created by.

Screen Shot 2019-09-04 at 2.04.17 PM.png

This completes the third instalment of our Hazard Reporting scenario.

Now, you have the ability to create a Daily reporting system based on your data. You can imagine the various uses of this as a Reporting tool. 

You may want to do a little bit of formatting on the data for sending it out regularly, but this blog gives you a foundation to get started with.

We hope that you enjoyed this tutorial on Hazard Reporting.

We wish you luck in your building. 

Stay tuned for more additions to the Hazard Reporting solution and for other uses of Microsoft tools.