Intermediate | Flow of the Week: Get me an digest of today’s inspections in a single email
This week, we’ll look at a Flow scenario brought to us by someone from our user community – daily digest email of scheduled site inspections. With this Flow, we’ll demonstrate two common Flow patterns – how to work with dates and how to join data.
The scenario
Let’s imagine that we store our site inspection reports in a SharePoint list. An inspection consists of fields like the Site Name, Site Location, Technician, Inspection Date, and Notes.
Our flow runs every day and gets all inspections that are to be conducted today using the Filter array action. Once we’ve got today’s inspections, we’ll create an HTML table with each row. Finally, we’ll send an email that has all the data collated together.
Let’s look at the flow in detail.
The trigger
Let’s schedule our Flow to run every day at 8AM. With recent changes to the Recurrence action, you can now easily and precisely set when a flow should start.
Get today’s items
Once the flow has been triggered, we’ll Get all list items and then use a Filter array action to hone in on just the work items whose Inspection Date is marked as today.
The magic sauce to getting today’s inspections is the ticks() expression. Given a timestamp in string format, the Ticks expression returns the number of ticks (100 nanosecond intervals) since 1st January, 1601. By using ticks, we can compare two different timestamp values.
In our flow, we’ll check if the number of ticks for the Inspection Date field (the left hand side of the Filter array action) are equal to the number of ticks for Today’s date (the right hand side of the Filter array action). i.e. –
ticks(item()?[‘Inspection_x0020_Date’])
=
ticks(utcNow(‘MM/dd/yyyy’))
In SharePoint, the Inspection Date is stored with only the Date format, e.g. 10/20/2017. The ticks() expression will calculate the number of ticks for 10/20/2017 at 12:00AM. Similarly, the number of ticks for utcNow(), which is today’s date and time must be qualified with the ‘MM/dd/yyyy’ format, to ensure that we get the ticks for today’s date at 12:00AM and not the ticks for the current time (which would be greater than 12:00AM)
Create an HTML table for filtered results
Now that we have a filtered array of today’s inspections, we’ll create an Apply to each loop to iterate over each result. In the Apply to each loop, we’ll use the Compose action to create an HTML table row per inspection.
To reference inspection fields, like the Site Name or Title, we’ll use an expression like the following: items(‘Apply_to_each’)?[‘Title’]. We’ll use the same approach to reference the Location field – items(‘Apply_to_each’)?[‘Site_x0020_Location’] and the Display Name of the Technician – items(‘Apply_to_each’)?[‘Technician’]?[‘DisplayName’]. Note – Technician is a Person field in SharePoint.
Send an email with an HTML table
Outside of the Apply to each loop, we’ll use the Send an email action. In the action, set the Is HTML property to Yes and then insert the results of the Apply to each loop as a table in the Body.
To get the results of the Compose action, we’ll use the join(actionOutputs(‘Compose’),”) expression. This simply collates each output (i.e. an HTML row) using the blank character as a separator.
Now every day, you’ll get an email like this with details about today’s inspections.
You can learn more about the expressions used in this post in the Workflow definition language documentation. We hope you found this post useful! As always, please share your feedback using the comments below. If you have a question or idea, leave it below or head over to the Microsoft Flow Community.
