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

[AbubakrWorking]

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

[ekarim2020]

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

[AbubakrWorking]

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:

 

 

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

First Email:

2nd Email:

 

 

 

 

[ekarim2020 · Accepted solution]

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:

This part of the flow will resemble:

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

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

We should then get an email like this:

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

 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.

[AbubakrWorking]

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. 

[ekarim2020]

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

Ellis

[AbubakrWorking]

@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.

 

 

  

[AbubakrWorking]

@ekarim2020 can you take a look at this flow above

[ekarim2020]

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:

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

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)

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

Ellis

[AbubakrWorking]

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