Reminder Flow 2 days before due date

(0) Share

Report

Posted on by SchednNedn

Hey Guys,

im trying to build a Reminder Flow wich is linked to my Excel Online (Business)

So in this step i Compose my Excel "Date" data into a format wich i can use.

So fine so good, I get every Date I want in an Normal Date format.

This are the Compose actions from top to down :

if(empty(items('ApplyEach_Dates')?['Datum']),'',addDays('1899-12-30',int(items('ApplyEach_Dates')?['Datum']),'yyyy-MM-dd'))

Here im trying to filter the Empty Fields in my Date Row and it should give me the Dates.

formatDateTime(utcNow(),'yyyy-MM-dd')

This one captures the current time

if(empty(items('ApplyEach_Dates')?['Datum']),'',addDays(outputs('ComposeWorkDate'),2, 'yyyy-MM-dd'))

here it gets the Dates from the Date Row again and adds +2 days to it.

So im not sure how i should do my conditions so my Co Workers get an Email 2 Days before the Due Date comes.

Here i tried the Condition :

outputs ('ComposeWorkDate') is less than outputs('Compose')

and

Condition 2:

outputs('Compose_2') is less than or equal outputs('Compose')

I always get the entries with the Co Workes with current Dates

what am i doing wrong here ?

Scherwin

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

tom_riha 10,185 Most Valuable Professional on at

Like (1)

Report
Hello @SchednNedn ,

maybe you could do the filtering directly using the Filter Query in the 'List rows present in a table' action.

The assumption is, that Excel will provide you the date as a sequence number. Therefore, if you were able to turn today's date + the 2 days in advance into a sequence number, you could use a Filter Query

Datum eq 'Today+2days sequence number'

You can take the sequence number for today by calculating a difference between today's date and the 1899-12-30 (as used for the conversion of the Excel date number to actual date).

Calculate: ticks(...) for utcNow() minus ticks(...) for the date of '1899-12-30' as used in the conversion from Excel sequence number to a date. 1 tick is 100 nanoseconds, so you should turn it into days by dividing the result by 864000000000.

div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000)

That's the sequence number for today. Now, you want the rows with date in 2 days from today, you should increment the result by 2.

add(div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000),2)

And this is an expression you can use in the Filter Query and which should return only the rows with date 2 days from today. You can then send email to all the rows it returns, without any additional conditions.

Datum eq '@{add(div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000),2)}'

Was this reply helpful? Yes No
SchednNedn 21 on at

Like (0)

Report

Hi @tom_riha

I tried to follow your steps.

I composed it giving me these Outputs :

So im getting these Numbers, but how do i use these Numbers to create an Email that gets send out 2 days before ?
Im trying to find a Email Connector where i can time this somehow.
Sorry if i did your suggestion wrong, im a newbie at this Flow thing 🙂

Thanks for your quick reply anyway, I really appreciate it !

Bg
Scherwin

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

You should use the scheduled trigger instead of a manual trigger. Manual trigger must be always triggered manually, scheduled will run automatically on a predefined schedule.

Create a new flow with 'Scheduled trigger', schedule the flow to run once a day, list all the rows that need a reminder, and send the reminder email.

Was this reply helpful? Yes No