How to get emails notifications on approaching due dates from excel spreadsheet

[Lauriane]

Hello,

I am quite new to Flow so please excuse my lack of experience!

I have a database in excel with several dates fields. The idea would be to get notification by email when we are less than 7 days before the due dates.

How can I Achieve this ?

Thanks a lot !

Lauriane

[v-LilyW-msft · Accepted solution]

Hi @Lauriane 

Thank you for posting.

According to your description, you want to filter the rows that less than 7 days before the due dates and send an notification.

I create a Flow and can achieve your needs

You can refer to the screenshots as below:

Expression：formatDateTime(addDays('1899-12-30', int(item()?['Expirydate'])), 'yyyy-MM-dd')

Expression：addDays(utcNow(), 7, 'yyyy-MM-dd')

Expression：formatDateTime(addDays('1899-12-30', int(item()?['Expirydate'])), 'yyyy-MM-dd')

This is my excel:

After flow runs, I will receive a notification as below:

Hope the content above may help you.

Best Regards

Lily

[Lauriane]

Hello Lily,

thanks for your message.

I am fine until the create html table. I cannot select the namefield.

Can you please clarify ? Thanks a lot

[v-LilyW-msft]

Hi @Lauriane 

Please enter Item()?['name'] in expression to get the value of name.

Best Regards 

Lily

[Lauriane]

Hi Lily,

Thanks again for your help but it still not working.

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@lessOrEquals(formatDateTime(addDays('1899-12-30', int(item()?['nextcatchup'])), 'yyyy-MM-dd'), addDays(utcNow(), 7, 'yyyy-MM-dd'))' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Do you have any suggestion ?

.Thank you again !

[v-LilyW-msft]

Hi @Lauriane 

What is the type of your 'nextcatchup'? Is it a date format? 

Could you share your excel screenshot to me? Thanks!

Best Regards

[Lauriane]

Hi Lily,

Here we go.

A very big thank you for your help again !

[v-LilyW-msft]

Hi @Lauriane 

Because the date '14/04/2022' was not the correct date format in Flow, the date need match ISO 8601 format.

So please change the date to 04/14/2022 and try again.

For more info about ISO 8601 you can refer to:

https://en.wikipedia.org/wiki/ISO_8601

Best Regards

[Lauriane]

Hi Lily,

The flow works but I do not get the correct data. There must be a mistake somewhere. Do you have an idea ?

This is what I receive by email:

I assume the 1899-12-30 corresponds to empty cells in my Spreadsheet.

However I have just run it, we are 16/06/2021 today so I should not be able to see the second item in the list for example as the idea is to get the info about futures meeting we need to organize.

Do you know why ?

Thanks a lot,

Lauriane

[v-LilyW-msft · Accepted solution]

Hi @Lauriane 

If you only want to get the rows with a specific date, please change 'is less than or equal to' to 'is equal to'

Best Regards

[Lauriane · Accepted solution]

Hello Lily,

It finally works! Thanks very much for your precious help !

I have a last question.

On column C, I have a status: Contractor or Employee, ideally, I'd like to get notification for Employees only, how can I filter this ?

Thank you again !

lauriane