This guide accompanies Shane’s video which walks through how he solved some real-life problems at PowerApps911 by automating reports and reminders through Power Automate and SharePoint.
The Problem
The problem we’re trying to solve with this flow is we want a weekly email on leads that are due to be followed up on. The concepts in this video could be used for all sorts of challenges: follow-up on contract renewals, overdue tasks, etc.
Click the video to start following along!
Click the link to view more videos from Shane Young!
Getting your Data Source Ready (3:08)
To begin, you’ll need your SharePoint data source ready to go. If you don’t want to play around with your live production data, you can make a copy of a live SharePoint list:
Go to your SharePoint Site contents screen.
Click New > List
Choose “From existing list”
This will give you an empty copy of your live list. You’ll have all the same columns, but you can create some dummy data to play around with while you’re building and learning.
To best follow along with this video/guide:
Add a Date and Time column to your list and name it Dateforfollowup. Set it to Date only. Create some records where this column is blank, create some records where this column has a date in the past, and create some records where this date is in the future.
Add a Choice column called Status with various choice options related to status, but make sure that one option is Quote Sent. Then make sure some of your records have their Status set to Quote Sent and others are either blank or set to one of the other choices.
Add a Person column and call it WhoSentQuote and add some colleagues who don’t mind you sending them a test message, or if you have the ability to create a dummy account, use that account, or do a mix of people, but you want a valid person chosen in this column for all records.
Add a Single line of text column called Customer (or rename the Title column to Customer). Make sure this field has data for all records.
Add a Single line of text column called Description and make sure this field has data for all records.
Once you’ve got your data source ready, let’s build the flow!
Create the Scheduled flow (3:51)
To begin, go to https://make.powerautomate.com and click the +Create button on the left.
Give the flow a descriptive name
Choose the desired start date.
Choose the desired start time.
Choose how often it should repeat. For this guide, we’re doing a weekly flow so keep the “Repeat every” box set to 1 and choose “Week”. The screen will change to show you options for a weekly flow.
Deselect each day you don’t want the flow to run. You only want the desired run day to be filled in solid blue, the others should be gray.
Once that’s all set, click Create.
Add a SharePoint Get Items action (4:42)
The first thing we want our flow to do when it runs is gather the information from SharePoint.
Click + New step
Search for “sharepoint get items”
Choose the SharePoint “Get items” action from the results.
Choose your Site from the Site Address dropdown.
Choose the List where the data you want to work with lives in the List Name dropdown.
Expand “Show advanced options”
Let’s think about this for just a moment. The way this action is currently set up, it will go to SharePoint and retrieve all the records from the selected List. But remember the problem we’re trying to solve, we don’t want a weekly email with ALL leads, we just want the leads that need to be followed up on. While we could have Power Automate grab all the records from SharePoint and then filter that set down to what we want, that’s a lot of extra work that eats into Shane’s nap schedule, so let’s make this more efficient by having the flow only ask SharePoint for the records we want. To do this, we’re going to use the ”Filter Query” field with our good friend ODATA queries.
Use ODATA Query to filter the Dataset from SharePoint (5:11)
To build an ODATA query, you need to know the system name (5:45) of the column you want to filter on. This can be different than the display name of the column. To find the system name of the column:
Go to List Settings
Scroll down to the list of Columns. Find the column you want to filter on, for our example, it is called “Follow Up Week”. Click on the column name.
The Edit Column screen will open with the details of the column. Look at the URL for this page in your browser’s address bar. At the end of the URL, look for the portion that says &Field=
What immediately follows that = is the system name of the column. So, copy or make note of that. Note: Spelling and capitalization counts!
Now let’s jump back to our flow.
In the Filter Query box, paste or type the column name exactly as it appeared in the previous step.
After the column name type eq null For our example, the query will read:
Dateforfollowup eq null This tells SharePoint to only send us records where the Dateforfollowup column is empty.
Let’s do a quick sanity check to verify our query is good. (6:24)
Click + New step
Search for Compose
Choose the Data Operation Compose action from the results.
Click the Expression tab
Type length()
Place your cursor inside the parenthesis then click the Dynamic content tab.
Choose the “value” placeholder from the Get items step.
Click OK
Save your flow
Click Test
Choose Manually and click Test
Click Run Flow
Click Done.
In the flow run results, click the Compose step and verify that the number of outputs matches the number of records in your data source that have null (blank) values in the Dateforfollowup column.
That gets us part of the way there, but we also want the records with follow-up dates that are within this week, because we want the reminder to alert us to records that don’t have follow up dates plus records that have follow ups scheduled for this week. Let’s go back to our Get items step and add to our filter query to get everything we want. (7:37)
Put your flow back in Edit mode and click on your Get items action.
Go to the end of your Filter Query and press the spacebar
Now we’re going to use the operator or to add another query to this filter. Type or paste your column name again followed by lt for less than and press the spacebar again.
Because we want to know if the date in that column is within this week, we need to do some math, and not just any math, we want to do some date math. So in the box that is hovering to the right, click the Expression tab.
The expression should be
addDays(utcNow(),6,’yyyy-MM-dd’) Let’s break that down. addDays is a function that does just what it says, it adds days to a given date. The first argument in addDays is our base date, the day we want to add to, so we want this to always be the date that the flow is running, so we’re using utcNow(), which always returns the current date. Then we’re just looking for days that fall this week, so our next argument is 6. And then finally, we want the result to be formatted the way ODATA wants it, so our last argument is ‘yyyy-MM-dd’
Click OK
That places our expression as an expression block in our query. But when you use dates in ODATA, it wants the date to be wrapped in single quotes, so place a single quote on both sides of the expression block.
Do another sanity check to make sure your additional filter is working correctly. So, test run your flow and expand the Compose step and make sure the resulting number of records is correct compared to what is in your data source.
Next, we need to add more filtering to our query. (10:04)
Go back to Edit mode and wrap your whole Filter Query in ( ) because we want that whole query to be true, but in addition to that, we also need another query to be true, so type and after the closing parenthesis and then get ready for an additional query because we want the records we’re getting from SharePoint to have a status of Quote Sent.
In your Filter Query, after the and which follows your first two queries which are wrapped in parentheses, type or paste the name of your Status column then type eq ‘Quote Sent’
Your query should look like:
(Dateforfollowup eq null or Dateforfollowup lt ‘addDays-expression-block’) and Status eq ‘Quote Sent’If you want, do a sanity check again by testing your flow and checking the Compose’s output.
Let’s say you decide you want to include the additional status of “Discovery” in your dataset, you would go to the end of your query and type or Status eq ‘Discovery’ then you would wrap everything after the and in ( ). Your query is saying "I want records where this first group of queries renders a true AND where the second group of queries renders a true". Any time you want to include other conditions, you would just follow this principle of grouping column checks with or operators and then linking that group of queries to the main query with an and operator.
So now our dataset retrieval is good, if you want, you can delete the Compose step. (Although it doesn’t hurt to keep it in there for troubleshooting.)
Select Data Operation to Get All of the Email Addresses (13:53)
Now we want to move on to grab the email addresses that the weekly message needs to go to.
Click + New step
Search for Select and choose the Data Operation Select action from the results.
The From box is where you will specify the dataset we got back from SharePoint, so we’ll choose value from the Dynamic content available from that step.
The key field for the Map section is what we want to call the data we are pulling out of that dataset, you’re free to call this anything you want, but we’re calling it EMAIL in our example.
For the value associated with the EMAIL key, we want to use the properties available to us because our WhoSentQuote field is a Person field. So, we don’t just want to pick the WhoSentQuote column, rather we want to pick WhoSentQuote Email, because we only want the email property of the person specified in this field.
The Select action gets us a sub-table with only the email addresses of the people found in the WhoSentQuote field of the records SharePoint returned to us. Some of those records are going to have the same person in the WhoSentQuote field, so we don’t want to bombard them with a flurry of emails every week; we need to whittle the table of email addresses down to just the distinct addresses. How do we do this? We do this with the Union expression. Let’s take a look.
Getting Distinct Items in Power Automate flow using Union (15:13)
The first thing we need to do is loop through that sub-table of email addresses.
Click + New step
Search for apply to each.
Click the Control Apply to Each action.
In the “Select an output from previous steps” box, click the Expression tab and type union()
Place your cursor inside the parentheses, click on the Dynamic content tab and choose the Output of the Select step, add a comma and then click the Output of the Select step again. So your final expression should look like this:
union(body(‘Select’),body(‘Select’))Click OK
Inside your Apply to Each loop, click Add an action and add a Compose action.
For the Input of your Compose 2 action, scroll down to the bottom of the Dynamic content and choose the Current item from this Apply to Each step.
Do a test run to verify. There should be 1 Compose card inside this Apply to Each loop for each distinct email address found. Notice that the data for the result is inside curly brackets, that means the Select is returning the data as a record, and we just want the text of the email address from within that resulting record.
Go back to Edit mode.
Click on Current item inside your Apply to Each loop’s Compose action. Press CTRL + C to copy this block.
Now on the right, switch to the Expression tab.
Paste the info you just copied into the expression box.
Delete the @{ from the beginning of the expression and delete the } from the end of the expression.
So the expression should now be items(‘Apply_to_each’) which returns the record of the current item in the loop. But as we said, we don’t want the whole record, we just want the email address text. So at the end of the expression type ?[‘EMAIL’] This is very similar to in Power Apps using the . at the end of an object to specify a property of the object, but in Power Automate, instead of a . you use a ? and wrap the desired property in [‘ ’] Your expression should be:
items(‘Apply_to_each’)?[‘EMAIL’]Copy this expression, then click OK.
Still within the Apply to Each loop, click Add an action. Now we want to filter the dataset we received from SharePoint down to just the records containing the current item in the loop’s email address.
Search for Filter.
Click the Data Operation Filter action.
From should be set to the value of the Get items step.
For the first value in the comparison logic, choose the WhoSentQuote Email property.
For the second value in the comparison logic, click the Expression tab and paste in the expression you just copied.
Click OK
Now we just need to build a table of the information for our email body and send the email. Let’s keep going!
Create HTML Table Data Operation to Build Our Email Body (21:00)
Still inside the Apply to Each loop, click Add an action
Search for Create HTML and choose the Data Operation Create HTML Table action.
For the From on this action, choose the Body of the Filter array step.
Click the Show advanced options link to expand for more options.
Change the Columns drop-down from Automatic to Custom.
The headers for these columns can be whatever you want, they don’t necessarily have to match the column names of your SharePoint List. But you will want to look up and make note of the system names for these columns, they will be needed for the Values. For our example, we want a header for the following:
Customer
Status
FollowUpDate
Description
For the Values of these columns, we don’t just want the associated columns from the Get Items step from SharePoint. We actually want the associated columns from our Filter Array step. To do this, we need to use an expression. Click the Value field next to our Customer header.
In the box to the right, click Expression.
Your expression will begin with Item()? Followed by the system name of the associated column wrapped in [‘ ‘]. So for our example, our Customer column was originally the Title column so the expression needs to be: Item()?[‘Title’]
Click OK
Repeat steps 7-10 for the remaining columns, using the appropriate system names for each column. Hold on though! Remember that your Status column is a Choice column. You’ll need to treat that one differently. If you use the expression Item()?[‘Status’] it will return the whole record of the selected choice inside the choice column. We just need the value, so for the Status column, the expression should be Item()?[‘Status/Value’]
Now our HTML is ready to go, all we need to do is have the flow send an email.
Send the Follow-Up Email with the Outlook Connector (25:20)
Still within our Apply to Each loop, choose Add an action.
Search for Outlook send
Choose the Office 365 Outlook Send an Email (V2) action.
The To: field will be the expression we used earlier in our Filter Array step. But think about building this flow in 2 modes: Testing and Production. When you’re testing, you might fire this flow off 5, 6, or 50 times to fine tune the output. You don’t want to pester the person who will ultimately be receiving the email while you’re testing. So let’s build this step first for our testing phase, and then we’ll explain what to do when you’re ready to move this flow to Production.
Since we’re testing, let’s hard-code your own email in the To: field.
In the To: field, search for yourself, pick the result.
Give the Subject a descriptive name, like Leads for Follow up or something to that effect.
In the Body of the email, click the Output of the Create HTML Table step.
Press Enter and type: Email would have went to:
Then click the Expression tab to the right, and use the expression to get the email we filtered by: items(‘Apply_to_each’)?[‘EMAIL’]
Click OK
Save and Test run the flow and you should receive the emails for any records that need followed up on, and that line at the bottom should tell you who each email should have gone to.
When you are ready to put this flow into live production use, remove the “Email would have went to: “ and items expression from the body of the email and put the items expression into the To: field.
Additional Learning:
Power Platform University
Are you looking for full, personalized Power Platform training? Do you want a mentor to help guide you on your journey? Do you prefer real world content instead of academic concepts? Then Power Platform University from PowerApps911 is for you.
Learn more here.