cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccranberg
Frequent Visitor

How Do I Send a Email Reminder for Dates in Multiple Columns in Excel?

Hi All,

 

Problem/Question:

I am building off of an older flow that worked great and would send me email reminders when a date in a single column in an Excel document was seven days away from the present date. My current flow aims to do the same thing but simultaneously look over multiple columns in an Excel document. I have looked at Filter Query in the "List rows present in a table" item and tried adding a Filter Array item, but neither option seems capable of looking at more than a single column. Is there a way I can have the Flow look through multiple columns and send an email if a cell in any of them contains a date that meets my criteria?

 

Pictures of Flow:

ccranberg_0-1669922052114.pngccranberg_1-1669922066567.png

 

Other Idea:

I also tried concatenating all of the dates into a single column in Excel and added a Filter Array item that would filter out cells containing my "Reminder Date" variable; however, it seemed to select every single cell and send an email, regardless of date. I wondered if the flow was somehow pulling anything with a date that matched the format of my variable's date, but not the actual numbers of my variable's date. Kind of unsure why this didn't work out either.

 

 

Thanks in advance for any help you can provide!

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ccranberg ,

The cause of this problem is
1. There is an error in the formula, [or] should be lowercase
2. 'field_1' should write item()?['field_1']

 

Because of the PA itself, there is no hint to write the formula in [Filter Array]


1. I suggest to test this formula with Compose first,
If you can get the execution result in Compose.


2. Then copy this formula and put it in [Filter Array]

 

Here is a link about Filter Array for your reference:

Power Automate Filter Array + Examples - EnjoySharePoint

 

Best Regards

Cheng Feng

View solution in original post

5 REPLIES 5
v-chengfen-msft
Microsoft
Microsoft

Hi @ccranberg ,

Do you want:
1. Check excel to get the row whose date is listed seven days later
2. Stitch the row together and then send the mail as content?

 

Here is test for your reference:

Here is excel :

vchengfenmsft_0-1669963142749.png

Here is flow:

 

vchengfenmsft_3-1669964022801.png

vchengfenmsft_4-1669964028866.png

 

 

Here is result:

vchengfenmsft_5-1669964046510.png

 

Best Regards

Cheng Feng

Hello,

 

This seems like a step in the right direction and a more efficient way of sorting for dates days out. However, I am looking to apply this to multiple columns (~20 columns). Would it be possible to somehow expand this so it can send me a notification if a date in any of the 20 columns is 77 days away? That's where I'm having trouble.

HI @ccranberg ,

Contains can only be used once in a FilterQuery of [List rows present in a table]

 

Please use [Filter array] after [List rows present in a table]

Choose "Edit in advanced mode"

vchengfenmsft_0-1670320566140.png

Use @or(equals(),.........) in it

 

Twenty columns can be used or, if any of the date is equal to the date after 77 days, it will be filtered out

Put the filtered results into the Html table, and the form will be generated

 

Best Regards

Cheng Feng

 

Oh this seems very promising. I'm trying it out, but I'm getting an invalid expression in the filter array error. Is my syntax correct:

 

@OR(equals('field_1', variables('VarReminderDate'),equals('field_2', variables('VarReminderDate')))

 

I'd like to keep adding field_#'s, but it doesn't seem to work with just the two. Perhaps I need to format the expression differently?

 

Thanks again for your help!

Hi @ccranberg ,

The cause of this problem is
1. There is an error in the formula, [or] should be lowercase
2. 'field_1' should write item()?['field_1']

 

Because of the PA itself, there is no hint to write the formula in [Filter Array]


1. I suggest to test this formula with Compose first,
If you can get the execution result in Compose.


2. Then copy this formula and put it in [Filter Array]

 

Here is a link about Filter Array for your reference:

Power Automate Filter Array + Examples - EnjoySharePoint

 

Best Regards

Cheng Feng

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,030)