cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbubakrWorking
Helper I
Helper I

Power Automate condition to send only the related request to the approvers

Hello Community,
I have a SharePoint list for time tracking hours where employees log their hours and there is a field named approver name and another field named approver email. Now the thing I need is that the approvers should be sent an email every Monday regarding their pending requests to approve. The format should be like below:

Hi Approver Name 

 

This is to inform you that the timesheets by the following staff are ready for review: 

Insert Employee No.1 Name 

Insert Employee No.2 Name 

Insert Employee No.3 Name>

 

Please click here to review and approve these. 

 

The below staff have now completed last week timesheet and have been notified to complete this: 

Insert Employee No.4 Name

Insert Employee No.5 Name

Insert Employee No.6 Name 

 

There should be another email to be sent to the employees that haven't logged their hours in the last week.
Thanks in Advance

1 ACCEPTED SOLUTION

Accepted Solutions

In your Get items action, you could use the following Filter Query to return only the records that you need - so you wont need to use the Filter array action:

2021-12-02_21-41-51.png

This part of the flow will resemble:

2021-12-02_22-26-02.png

 

The next part of the flow loops through each unique approver with a few small changes compared to your flow:

2021-12-02_22-28-24.png

2021-12-02_22-31-37.png

I have added a Select action from which I will create a HTML table:

2021-12-02_22-32-23.png

 

2021-12-02_22-33-38.png

 

2021-12-02_22-37-04.png

 

We should then get an email like this:

2021-12-02_22-52-33.png

 

Once you have the emails working you can then add some HTML table formatting and even a link to the employee time sheet record:

2021-12-02_22-48-33.png

 I tested the above flow using a very simple SharePoint list. Let me know how you get on.


Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

9 REPLIES 9
ekarim2020
Super User
Super User

I can offer some advice but you will need to think through some of the logic and try out some steps..

 

(1) In the SharePoint list, what are the data columns staff are filling in? It would be good to some data along with data types (eg. string, Date, choice field and Person field etc)

 

(2) What are the rules or logic used to determine what is sent to the approver?

For example:

  1. How do you determine that an employee timesheet is ready for review and approval?
  2. How do you determine that an employee timesheet is not ready for review and approval?
  3. How do you determine that staff have completed last week's timesheet?
  4. How do you determine that staff have not completed last week's timesheet?
  5. How do you determine which employee has not logged their hours for the last week?
  6. How do you identify employee records in SharePoint for last week?

 

For example:

  1. Q: How do you determine if an employee timesheet has been approved? A: There is an Approval column and it  will have the text: Approved. The column is a text column.  This column can have the values "Submitted", "Approved", "Rejected" etc...

You may want to consider another option: Build Timesheet Solution with PowerApps & SharePoint

 

As it stands at the moment there is not enough information to begin to design the flow. You need to think about these questions and have clear answers to them, then building the flow will far easier and the outcomes of the flow should then meet your needs.

 

Ellis

 

Hello @ekarim2020 ,

In the SharePoint list, there are many columns like project id, project name,workDoneBy, weekday1date,weekday7date, approverName,approverEmail and status etc.
The weekday 1 date is inserted in the weekday1date column and the approver is actually the manager that the employee is assigned in AD. Whenever the employee submits the log hours the status gets pending by default. Then the whole week entries of employees should be sent to the approver email every Monday morning and the approver email is present in the approveremail column. I have tried to build a flow for it but its currently sending 2 emails to a single approver(one email with all the content including HTML table and the other with only HTML table). There should be only a single email that should be sent to the approver containing all his reportee names.

Approval Flow:

Capture.PNG

Capture1.PNG

Capture2.PNG



Email sent to the approver(changed myself as approver for test purposes):

First Email:

Capture3.PNG

2nd Email:

 

Capture4.png

 



In your Get items action, you could use the following Filter Query to return only the records that you need - so you wont need to use the Filter array action:

2021-12-02_21-41-51.png

This part of the flow will resemble:

2021-12-02_22-26-02.png

 

The next part of the flow loops through each unique approver with a few small changes compared to your flow:

2021-12-02_22-28-24.png

2021-12-02_22-31-37.png

I have added a Select action from which I will create a HTML table:

2021-12-02_22-32-23.png

 

2021-12-02_22-33-38.png

 

2021-12-02_22-37-04.png

 

We should then get an email like this:

2021-12-02_22-52-33.png

 

Once you have the emails working you can then add some HTML table formatting and even a link to the employee time sheet record:

2021-12-02_22-48-33.png

 I tested the above flow using a very simple SharePoint list. Let me know how you get on.


Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

Thanks, @ekarim2020 for this flow. It's working great now. Can you help me with sending the email to the Office365 users who haven't logged their hours in the last week. I have a column named formSubmitterEmail through which I can use to filter out the employee emails that have logged hours in the last week. 

Please show me that part of the flow again and we'll try to work how to fix it.

 

Ellis

@ekarim2020  I am trying to create a separate flow for it but it isn't working according to my needs. I am currently not at Send Email step because the outputs are either coming blank or it gets all the emails without filtering the ones present in sharepoint list.

tempsnip.png

Capture.PNG

tempsnip1.png

tempsnip2.png

  

@ekarim2020 can you take a look at this flow above

I wasn't clear on the logic of your flow, but have a look at some of the following steps that may help with the second part of your requirements.

 

Here we get a list of all users from AAD, and select only email addresses:

2021-12-06_10-11-24.png

 

We execute the Get items action, select only the submitters email addresses and remove any duplicate submitter email addresses:

2021-12-06_10-20-23.png

 

The Filter array AAD Users action - takes the form submitters email addresses and returns a list of emails addresses that are in your Azure Active Directory (i.e. if you were to use Search Users - it would find that form submitter based on their email address).

 

The Filter array AAD Users not found action - takes the form submitters email addresses and returns a list of emails addresses that are not found in your Azure Active Directory (i.e. if you were to use Search Users - it would not find that form submitter based on their email address)

2021-12-06_10-18-14.png

 

Let me know if this helps. Please break down the details further if needed.

 

Ellis

AbubakrWorking
Helper I
Helper I

@ekarim2020  It worked like a charm. Thanks alot for the help

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,476)