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

Send email if user is 2 times absent in 30 days

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

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @bakgou1,

 

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

 

absentinlast30days.pnggroupby_view_absent.png

 

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'))

 

groupby_subtotal_renderoptions.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


View solution in original post

4 REPLIES 4
Expiscornovus
Super User
Super User

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 😀



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


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
Responsive Resident
Responsive Resident

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
Super User
Super User

Hi @bakgou1,

 

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

 

absentinlast30days.pnggroupby_view_absent.png

 

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'))

 

groupby_subtotal_renderoptions.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,615)