cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RRMX1
Helper IV
Helper IV

Flow for due dates for multiple items

Hi,

 

Is it possible to set up a flow that does the following;

 

1) Looks at the due date column in a list

2) Runs, say on 1st of each month

3) Each due date that falls into that month is noted

4) Email is sent to relevant person will all the line items due in that time frame

 

I can set up a reminder email based on the due date column but I would rather the person receives just one email listing all the items due that month/week etc. instead of a separate email for each item.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

@RRMX1 please follow below steps:

 

1. Add Initialize Variable action and name the action as Year, name the variable as CurrentYear, type as Integer and use following expression in the value

 

int(formatDateTime(utcNow(), 'yyyy'))

 

 

2. Add Initialize Variable action and name the action as Month, name the variable as CurrentMonth, type as Integer and use following expression in the value 

 

int(formatDateTime(utcNow(), 'MM'))

 

 Screenshot 2021-03-17 183327.png

 

3. Add compose action, rename it to IsLeapYr and use following expression in this action

or(and(equals(mod(variables('CurrentYear'), 4), 0), not(equals(mod(variables('CurrentYear'), 100), 0))), 
equals(mod(variables('CurrentYear'), 400), 0))

 4. Add Compose action, rename it to 'Days in month' and use following expression in action

if(equals(variables('CurrentMonth'),2), if(outputs('IsLeapYr'), 29, 28), if(or(equals(variables('CurrentMonth'),1), equals(variables('CurrentMonth'),3), equals(variables('CurrentMonth'),5), equals(variables('CurrentMonth'),7), equals(variables('CurrentMonth'),8), equals(variables('CurrentMonth'),10), equals(variables('CurrentMonth'),12)), 31, 30))

5. Add Compose action, rename it to 'First of Month' and use following expression in action

startOfMonth(utcNow())

6. Add Compose action, rename it to 'End of Month' and use following expression in action

 

addDays(startOfMonth(utcNow()), add(outputs('Days_in_month'), -1))

 

 

 

7. Now you have got the start of month date and end of month date, now to retrieve items from SharePoint list, use Get Items action with following filter query, replace Created with your DueDate column name

Created ge '@{formatDateTime(outputs('First_of_Month'),'yyyy-MM-dd')}' and Created le '@{formatDateTime(outputs('End_of_month'))}'

 

 

 

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

11 REPLIES 11
annajhaveri
Super User
Super User

@RRMX1  you can create scheduled flow with recurrence trigger to run the flow on 1st of every month. Then you can query the data to find items for which duedate falls in that month, then create HTML table from items retrieved and then send the email with html data. Are you using SharePoint list? 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

Hi,

Yes I'm using a SharePoint list for the due dates.  Can you detail the steps I need to add for the query data part please? I can set up the trigger and create table and email part (I think!).

Thanks!

 

@RRMX1  first we need to get the first day of the month and last day of the month, then use Get Items with filter query on due dates to get items which are due in current month, i will give you step by step.

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

@RRMX1 please follow below steps:

 

1. Add Initialize Variable action and name the action as Year, name the variable as CurrentYear, type as Integer and use following expression in the value

 

int(formatDateTime(utcNow(), 'yyyy'))

 

 

2. Add Initialize Variable action and name the action as Month, name the variable as CurrentMonth, type as Integer and use following expression in the value 

 

int(formatDateTime(utcNow(), 'MM'))

 

 Screenshot 2021-03-17 183327.png

 

3. Add compose action, rename it to IsLeapYr and use following expression in this action

or(and(equals(mod(variables('CurrentYear'), 4), 0), not(equals(mod(variables('CurrentYear'), 100), 0))), 
equals(mod(variables('CurrentYear'), 400), 0))

 4. Add Compose action, rename it to 'Days in month' and use following expression in action

if(equals(variables('CurrentMonth'),2), if(outputs('IsLeapYr'), 29, 28), if(or(equals(variables('CurrentMonth'),1), equals(variables('CurrentMonth'),3), equals(variables('CurrentMonth'),5), equals(variables('CurrentMonth'),7), equals(variables('CurrentMonth'),8), equals(variables('CurrentMonth'),10), equals(variables('CurrentMonth'),12)), 31, 30))

5. Add Compose action, rename it to 'First of Month' and use following expression in action

startOfMonth(utcNow())

6. Add Compose action, rename it to 'End of Month' and use following expression in action

 

addDays(startOfMonth(utcNow()), add(outputs('Days_in_month'), -1))

 

 

 

7. Now you have got the start of month date and end of month date, now to retrieve items from SharePoint list, use Get Items action with following filter query, replace Created with your DueDate column name

Created ge '@{formatDateTime(outputs('First_of_Month'),'yyyy-MM-dd')}' and Created le '@{formatDateTime(outputs('End_of_month'))}'

 

 

 

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Thanks so much! I have inputted all of these steps, it is failing at the Get Items part with this error message: 

 

Unable to process template language expressions in action 'Get_items' inputs at line '1' and column '14352': 'In function 'formatDateTime', the value provided for date time string 'startOfMonth(utcNow())' was not valid. The datetime string must match ISO 8601 format.'

 

Is this to do with my column settings?  They would be in English UK date format with no time displayed.

@RRMX1  can you try below expression for filter query in Get Items

Created ge '@{formatDateTime(outputs('First_of_Month'),'yyyy-MM-dd')}' and Created le '@{formatDateTime(outputs('End_of_month'),'yyyy-MM-dd')}'
Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

Hi,

 

I'm now getting an error at the end of month stage;

Unable to process template language expressions in action 'End_of_Month' inputs at line '1' and column '14350': 'The template language function 'add' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'. Please see https://aka.ms/logicexpressions#add for usage details.'.

@RRMX1  thats weird, can you please let me know what value are you getting for days in month action?

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

I am getting all the correct info from previous steps (2021, 3, 1st - 31st March etc.) but getting yet another error message when I test it;

The expression "Service Due ge '2021-03-01' and Service Due le '2021-03-31'" is not valid. Creating query failed.
clientRequestId: 373f6d25-783c-45f3-926b-30d0277e6ce1
serviceRequestId: 373f6d25-783c-45f3-926b-30d0277e6ce1

@RRMX1  you are using display name of the field in the query, you need to use internal name of the field in query. How do you find the internal name of the column, go to your List Settings, then click on the specific column, then look at the browser URL, take the name what appears after the Field=

E.g. http://test.sharepoint.com/sites/test/_layouts/15/FldEditEx.aspx?List=%7BA4E78CC7-F74F-4733-832A-099... is my URL when I click on Link field in List Settings, here my internal field name is Link, which can be used in the filter query.

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

Thank you so much that is it working perfectly now! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,782)