Reminder Flow 2 days before due date

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

Bg

Scherwin

[tom_riha]

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

 

[SchednNedn]

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 

[tom_riha]

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.