Send a designed email table to users exist in a column with missing data

[orengaliki]

Hi All, 

I'm having trouble with managing a flow.

I need users to recieve a weekly notification that contains a single table that consolidates all missing approval dates that are in their responsibilities. The users enter their name in a person column in the list. 

I have created a scheduled flow to consolidate missing approval dates that exist in a list.

When I arrive to the point of sending an email, it automatically converts it into apply to each which sends tons of single emails (per item instead of a single table). 

How can I create a table that will be sent to a specific user listed in this person column on a weekly basis?

For reference, the person column is called : CTC, and the approval column is called : "Approval date".

Thanks in advance! 

Oren

[creativeopinion · Accepted solution]

@orengaliki To send a single email to each user with their items you need to do the following:

• Get items with missing approval dates
• Get a unique list of users who have items assigned to them
• Loop through each user and send them a list of item assigned to them

Tip: Rename your actions to keep your flow organized!

---

Get Items with Missing Approval Dates

Add a Get Items action to your flow to return the items from your SP List.

Filter Query

Compose a Filter Query to return the items that meet your criteria. In my case, I'm returning items that do not have a Task Status equal to Complete. In your flow, you are looking for items where the Approval Date is empty.

Your Filter Query may look something like:

ApprovalDate eq null

You will need to use the internal column name of your Approval Date column. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

Top Count

Depending on how many items you have in your SP List, I would recommend limiting the Top Count to a smaller number than the total number of items in your list. This will increase the speed of your flow runs. Instead of returning all items in your list—it'll limit the Get Items action to the number entered here.

Return Item Count

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. 

Insert a Compose action. Add an Expression. Use the length() function. 

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function. 

Run a test.

Confirm that the number of items returned is what you are expecting. If the number returned is zero this means the Get Items action hasn't returned any items and you may need to adjust the Top Count (if you've used it) or your Filter Query may be incorrect.

Add a Condition (optional)

This is an optional action that you can add to your flow. If you are always expecting your list to have items that have an empty Approval date you can skip onto the next section.

Add a Condition action to your flow to check if there are any items returned. Insert the outputs from the Compose action above into the first value field. Change the operator to is not equal to and enter a zero into the second value field.

This way you can stop the flow from continuing to run if there aren't any items returned. This will prevent your flow from failing if no items are returned.

Get List of Unique Users

Next, you'll need to pull the details of each user who has a task assigned to a task returned from the Get Items action. Remember that if you've limited the top count, not all users will be displayed. 

Add a Select action. If you are using the Condition action above—insert these actions into the YES branch.

In the from field insert the value dynamic content from the Get Items action. You can enter any text you'd like in the Key Column. to keep it simple I've used Name and Email. In their respective value fields, insert the appropriate dynamic content from the Get Items action that returns the Display Name and Email address from the person column.

Run a test. Review the outputs. You should see an array of users Names and Emails. If you aren't seeing more than one user returned—you may need to adjust your top count to return a more items.

Get Unique List of Users

Now that you have a list of all the users assigned to each item (from the Get Items action). You need a way to return a list of unique users. You'll need to use a Compose action and an expression. 

The union() function takes two arrays and combines them into one. 

union([array1],[array2])

Click on the dynamic content tab and insert the output from the Select action above (that contains an array of email addresses). Add a comma and insert the output of the Select action above again. 

It will look something like this. 

Run a test. Confirm that you have a unique array of email addresses.

Loop through Each Unique User

Since the Compose action is storing an array of users Names and Emails you are able to loop through the array of users with an Apply to Each action.

Compose Current Email

Insert a Compose action to store the email address of the user being looped through. You'll need an expression. To return the content from the current item, you will need to use the key—which is what was entered into the Select action (also it appears in red in the prev screenshot between the double quotes).

If you entered a different key than what I entered—you'll need to adjust the expression below to match your key.

item()?['Email']

Compose Current User Name

Insert a Compose action to store the name of the user being looped through. You'll need an expression—just like above.

Run a test. Confirm the outputs of the Compose actions. 

Get List of Items Assigned to User

Since you've already returned a list of items from your SP list in the root of the flow—you want to avoid using the Get Items action again. Instead, use a Filter Array action to filter out the items for each user currently being looped through. To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded. Note: The Scope action is optional. I like to use them in my flows to quickly collapse multiple actions at once. 

In the From field, insert the value dynamic content from the Get Items action. Since the Apply to Each action is looping through email addresses, we can use that to compare to the email addresses in the AssignedTo column. In your case it would be the CTC column.

Leave the operator as is equal to. 

In the second value field I've inserted the outputs from the Compose that is storing the current users email address.

Add a Compose action to store the length of items returned from the Filter Array action. 

Insert the body outputs from the Filter Array action into the length() function.

Run a test. Confirm that the Apply to Each action is looping through each email address and returning the appropriate number of items assigned to that user.

Review the Filter Array action outputs. You will need to reference the content keys in the next action. Refer to this section of a YT Tutorial I uploaded on how to get dynamic content from a Filter Array action.

Create an HTML Table

There are many ways you can present the information. The easiest way would be to use the Create an HTML table action. 

In the From field, insert the Body dynamic content from the Filter Array action. Select Custom from the Columns dropdown. 

In the Header fields, enter the header text for your columns. In the Value field, insert expressions to return the dynamic content from the Filter Array action. 

Use the item() function. Insert the key between the single quotes for the dynamic content you'd like to return.

item()?['']

Run a test. Review the Create HTML Table outputs.

Send an Email

Add a Send an Email action. Ensure you are adding this action inside the Apply to Each action—as you'll be sending an email to each user you are looping through.

Important: Whenever you are testing a flow. Always insert your own email address in the recipient field until you've confirmed the output of your email. 

Compose your email using the dynamic content from your previous actions. In my subject line I've used the output of items from the Filter Array action as well as the user's full name.

In the body of the email. I always like to add a line to confirm where the email will actually be sent (while i'm testing). This is optional. I've inserted the outputs of the Compose action storing the user's email.

I also like to add an indicator to any automated emails that it's in fact an automated email. Especially if it's coming from my email address.

Get Users First Name

To return the users first name only, you can use an expression. First you need to split the Display Name at the space. Insert a split() function and then insert the compose action that is storing the users Display Name. 

Add a comma and single quotes with a space (between the single quotes). You'll need to wrap your expression in the first() function to return the first name only (first item from the split string). 

Place your cursor at the front of the expression: Tip: Press the up arrow key. Insert:

first( 

Go to the end of the expression. Tip: Press the down arrow key. Insert a closing bracket. 

Run a test. Review the outputs. The HTML table isn't nicest to look at but as I mentioned it's the easiest.

Style the HTML Table

With a bit of simple CSS you can style the table so it looks a bit nicer. Keep in mind—some email clients may not display CSS properly. 

Add a Compose action after the Create HTML Table action and insert this CSS.

<style>
table {
width: 100%;
border-collapse: collapse;
}

th, td {
padding: 8px;
text-align: left;
}

th {
background-color: #f2f2f2; /* Light grey background for headers */
font-weight: bold;
color: #333; /* Darker text for headers */
}

</style>

At the bottom of the Compose action, insert the outputs from the Create HTML Table action.

In the email body, insert the outputs from the Compose action (instead of the outputs from the Create an HTML Table action).

Customize your email to suit your requirements.

Adjust Get Items Action

If you've used the Top Count field in your flow, make sure you remove that before you run the flow. If your SP list has more than 100 items, you will need to toggle on Pagination and up the threashold.

Concurrency Control

If you aren't using any variables in your flow. You can speed up the flow run by toggling on the Concurrency Control in the Apply to Each action. Click on the three dots and select Settings. 

You can adjust the number to the highest (50). If you are using variables in your flow, you need to leave this off. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[orengaliki]

Thank you so much @creativeopinion  for your detailed reply! I have followed your instructions and successfully created this scheduled flow. 

I simply love this community!

One last question: When the flow will be ready to be sent to the users, I just use the 'Compose- Current user email' in the "To"?

Thanks again!

[creativeopinion]

@orengaliki You're welcome—that's great to hear.

That's correct. Or you can use the expression. It's up to you. However, I find that it can be a bit more clear when you use Compose actions to store dynamic content expressions. This way you can run a test to verify the outputs before using it in your flow. 

[orengaliki]

Thanks! 

and to make things even better for people who don't use the default date format, I have modified the format for ddMMMYYYY in the following expression:

FormatDateTime(item()?['field_3'], 'dd-MMM-yyyy')​