Send email if user is 2 times absent in 30 days

[bakgou1]

Hello Community,

I need help in creating a power automate. I have data in sharepoint list and would like to create a flow that send emails if a user is absent two times in 30 days. Do I need to create a recurrence flow? Please help...

[Expiscornovus]

Hi @bakgou1,

Can you share a screenshot of the setup of your SharePoint list and how you are storing the absent data?

Normally I would probably use a recurrent flow for this, but I can assess it a bit better when I have more details about your setup 😀

[bakgou1]

Hi @Expiscornovus ,

Basically, Whenever someone is absent, admin will enter the information about the reason for absence in sharepoint. It has Name, DateTime of Absent, Reason for absence. Just 3 columns. I'm doing a group by in sharepoint list view to count the number of absence occurrences. 

Hope this helps! Thank you! 

[leo85]

Hi @bakgou1 ,

it depends a bit on when you want to send the email.

One possibility:

1. You can use the admin input as a trigger (when an item is modified).

2. Get all items within the last 30 days (assuming you are interested in the last 30 days and not any 30 days). You can do this with the ODATA query.

3. Use the Filter action to filter the result by the user in the triggering item.

4. Check the length of the result of the Filter action. If it is greater than 1, there are at least 2 entries in the table for  the specified user.

Limitation:

If the users first absense was for example 35 days ago, but he/she was absent for 10 days. This will not show up.

[Expiscornovus · Accepted solution]

Hi @bakgou1,

Do you have a view like below? With subtotals per person and a filter on the AbsentDateTime with [Today]-30.

If that is the case you could use a Send an HTTP request action and retrieve those subtotals of your specific view.

Below is an example of that approach.

1. Add a Send an HTTP request to SharePoint action. Use the ID of the view which has the group by person name and Totals Count applied to it. Use the following xml for the body:

{
"parameters": {      
 "RenderOptions":5707271,       
"OverrideViewXml":"<View><Query><GroupBy Collapse=\"FALSE\"><FieldRef Name=\"PersonName\"/></GroupBy></Query></View>",       "AddRequiredFields":true                       
 }
}

2. Add a Filter Array. Filter your results so that you only get rows which have a subtotal of 2 or more.

Expression in From

outputs('Send_an_HTTP_request_to_SharePoint')?['body']['Listdata']['Row']

Expression in where

int(item()?['PersonName.COUNT.group'])

3. Add a Select action. Select the name of the person and the number of times absent.

4. Use an intersection to get the distinct user name. In the select it will retrieve each row from the list with the same totals count value. You can use the outputs of the compose in your e-mail action.

intersection(body('Select'),body('Select'))