Unanswered

Send a reminder email 3 days before due date

(0) Share

Report

Posted on by tissuebox882

Hi,

I have a sharepoint list with 2 columns among many other columns called 'Action Due Date' and 'Action Status'. Action Due Date has the date in the format of dd/mm/yyyy and Action Status has text either 'Open', 'Closed', Cancelled'.

I'm looking to set up an automated flow that checks everyday if the Action Status is 'Open' and the Action Due Date is within 3 days of today's date. If so, I wish to send an automated email to myself.

I've tried looking at some of the existing responses online but still seem to get stuck. Here are the issues I'm facing.

1. 'Get Items - Filter Query' Issue with format

filter query.png

2. Condition issue

condition issue.png

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (1)

eric-cheng 5,171 on at

Like (0)

Report

Hi @tissuebox882 ,

When you say within 3 days, did you want the items which are upcoming up to 3 days or has past up to 3 days?

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
tissuebox882 13 on at

Like (0)

Report

Upcoming 3 days

Was this reply helpful? Yes No
Verified answer

eric-cheng 5,171 on at

Like (1)

Report

Hi @tissuebox882 ,

Try the below:

Based on this list, it should only return the first item.

Expression in Filter Array is below. You may need to swap out the column name to yours marked in bold

@div(sub(ticks(formatDateTime(item()?['ActionDueDate'], 'yyyy-MM-dd')), ticks(formatDateTime(utcNow(), 'yyyy-MM-dd'))), 864000000000)

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
tissuebox882 13 on at

Like (0)

Report

Hi, thanks for your response.

I've input the same formula in the filter array, however I'm still getting emails even though I set the Action Due Date on 28th of October 2021.

div(sub(ticks(formatDateTime(item()?['ActionDueDate'], 'yyyy-MM-dd')), ticks(formatDateTime(utcNow(), 'yyyy-MM-dd'))), 864000000000)

Also, is there a way to get the date format into dd-mm-yyyy?

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report

Hi @tissuebox882 ,

Is your column also called ActionDueDate? I think yours is called Action Status? Please check the output of Get Items using a Compose action and update the part in red.

div(sub(ticks(formatDateTime(item()?['ActionDueDate'], 'yyyy-MM-dd')), ticks(formatDateTime(utcNow(), 'yyyy-MM-dd'))), 864000000000)

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
tissuebox882 13 on at

Like (0)

Report

I have both Action Status and Action Due Date.

In the Get items Filter Query I have " Action Status eq 'Open' "

In the Filter Array I have div(sub(ticks(formatDateTime(item()?['ActionDueDate'], 'yyyy-MM-dd')), ticks(formatDateTime(utcNow(), 'yyyy-MM-dd'))), 864000000000)

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report

Hi @tissuebox882 ,

Can you output the Filter Array body to Create HTML table and post a screenshot of what it looks like

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No
tissuebox882 13 on at

Like (0)

Report

sorry, I don't understand what HTML table does. Mine is blank

Was this reply helpful? Yes No
tissuebox882 13 on at

Like (0)

Report

all good, i got it working.

Thanks a lot for your help

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (0)

Report

Hi @tissuebox882 ,

Your flow should look like the following:

The condition will use @length(body('Filter_array')) to check if an item exists. It will only send an email if yes.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Was this reply helpful? Yes No