Send an email when text column date is today + 5 days away...

[Kosenurm]

Hi

I have the below flow which I want to send an email when the date (which is coming from a text column in Dataverse) is equal to today plus 5 days. So, if the date from the dataverse column is 05/11/2021, it would send the email today (five days before). I am in the UK so all dates are dd-MM-yyyy.   I also want to ensure that the email is only sent when another column's value is equal to 'Home'. 

The flow succeeds, but does not send any email as it is saying that, even when the column's field is 03/11/2021, the condition is not met. Do I need to format this into a date format (bearing in mind dd-MM-yyyy in my scenario) for the text column which holds the date?

For the date's equal to value, I am using expression of

formatDateTime(addDays(utcNow(),+5),'dd-MM-yyyy')

Screenshots

Any ideas how I can get this working?

Thanks

K.

[tom_riha]

Hello @Kosenurm ,

I'd try using the standard ISO 8601 format for the date 'yyyy-MM-dd'. The UK format of the date might be caused by your system localization, but on the background it's ISO date.

[Kosenurm]

Thanks for your reply. I now get an error...

"Unable to process template language expressions for action 'Condition' at line '1' and column '21748': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'."

The value should not be Null as there are 5 rows in the Dataverse table which should meet the conditions.

Any ideas?

Thanks

K.

[v-LilyW-msft]

Hi @Kosenurm 

When working date values within Flow, the date values would be converted into UTC format automatically.

In addition, the Time Zone of your PowerApps is based on your local time zone in default.

You can use "Convert time zone" action to convert UTC to your local time zone.

I set up a simple flow, you can refer to the screenshots below.

This is my test Table(My current date is 2021/11/2)

Flow in details:

After flow runs,the first record will be judged as true.

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kosenurm]

Thanks for the reply, however, it is still running with the output as null.

The column in Dataverse is text, not date, but displaying in UK format (so 02/11/2021). Do I need to convert this to date in the left-hand condition? if so, how would I do this?

Thanks

K

[v-LilyW-msft]

Hi @Kosenurm 

You can format the date on the right to the same format as the date in Dataverse , and try again.

Best Regards

[Kosenurm]

This is still failing as it is saying that the date on the left is null?

Thanks

K.

[tom_riha]

Hello @Kosenurm ,

if it says that the date on the left side is null then there's probably no date in the Dataverse for the specific row. Try to add a 'Compose' action before the condition and store the date there. Then you'll see in the flow run history if it really contains value for all the rows or if it's empty for some of them.