Flow for due dates for multiple items

[RRMX1]

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!

[annajhaveri]

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

[RRMX1]

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!

[annajhaveri]

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

[annajhaveri · Accepted solution]

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

 

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

[RRMX1]

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.

[annajhaveri]

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

[RRMX1]

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

[annajhaveri]

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

[RRMX1]

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

[annajhaveri]

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

[RRMX1]

Thank you so much that is it working perfectly now! 

[dklbwf03]

Thanks so much for this!  Very helpful.  Can you share how exactly you setup the email?  Did you just add the send email step or did you create html table first and then send email from html table step?

[takoye]

Dear Anna,

Your flow will list item that will be due for current month.

Appreciate that if you could advise what are the changes to be made in the flow if I want to list the items expiring 2 months later?