Send email 14 days before a date

[undenius]

Hi guys,

I am quite new to building flow using power automate.

I want to create a flow that send an email 14 days before a date occurs (Uppsägningsdatum).

I constantly give me False.

Any help is highly apricated.

Fx: formatDateTime(items('Apply_to_each')?['Upps_x00e4_gningsdatum'],'yyyy-MM-dd')

Fx: addDays(utcNow(),-14,'yyyy-MM-dd')

[Bobron]

I'm not too sure since I'm also a bit new, but formatting the date-time is done like this I think (without "-" special character):

Edit: Tested it just now, but I'm wrong. "-" works fine

I've got a similar Flow to delete items from a list which are older than a month.

My expression in the condition is like this:

[Date of request] <is less than>[addDays(utcNow(), -31)]

[Date of request] is in the format: dd-MM-yyyy hh:mm

Maybe your Flow will work like this also?

[Jondoesflow]

Hi,

Try this

formatDateTime(adddays(items('Apply_to_each')?['Upps_x00e4_gningsdatum'],-14),'yyyy-MM-dd')

---------------------------------------------------------------------------


If I have helped you with an answer, please accept this post as a solution and thumbs up my reply. Thanks !

Blog - JonDoesFlow
Twitter - JonDoesFlow
YouTube - JonDoesFlow

[grantjenkins · Accepted solution]

At the moment you are returning ALL items and looping over each one with a condition which is extremely inefficient. You should be adding a Filter Query to your Get items action, so it only returns items that are due in 14 days.

See full flow below. I'll go into each of the actions:

Reoccurrence would be as you have yours which I assume runs once a day.

Get items includes a Filter Query to only return rows where the Review Date is equal to the date 14 days from now. In my example, the internal name of my column is called ReviewDate. The expression used to get the date 14 days from now, formatted as yyyy-MM-dd is below.

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

The full Filter Query is below:

ReviewDate eq '@{formatDateTime(addDays(utcNow(), 14), 'yyyy-MM-dd')}'

You can then simply loop over each of the items returned which will only be items that are due in 14 days. So, you don't need any condition here. If you had 1000 items in your list, but only 5 were due then it would only loop 5 times.

[undenius]

Thank you it works 👍

But I seams to have  another problem.
The value I use is from a calculated cell.

Is there any way around this?

I am trying to build a contract manager.
Where I want to input the Agreement end date and subtract the notice period and send a email 14 days before the termination date.

[grantjenkins · Accepted solution]

Unfortunately, we can't filter on calculated fields in Get items - but we can within a Filter array.

See flow below that assumes my ReviewDate is a calculated field.

Get items doesn't have a filter since we can't filter on calculated fields.

Filter array takes in the value from Get items and has the filter using the calculated field. The expression for the date is:

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

Apply to each will now take in the Body from Filter array, and you will need to access the fields directly from Filter array instead of Get items. To get the email of the person that last modified the item you would use the following expression.

//Get the Email from the person that last modified the item
items('Apply_to_each')?['Editor/Email']

//To get other fields you would use:
items('Apply_to_each')?['NAME_OF_FIELD']

[undenius]

It works perfectly, Thank you very much for the very well described information 🙏🏼